Query Between Unrelated Tables

[AlexaS]

One thing I've noticed in my quest for knowledge with this database is in the Access forums when you ask a question, you get a lot of "Just don't" responses. That being said, I don't learn from "just don't". I know what I need/want to do, this is how (in my limited knowledge) I think I can get it to work, but it's not working. If there is a different/better way, please explain so that I can learn.

That being said I have two tables (Customer & Plant) that I want to use as a lookup for a field in a third table (Details). Instead of having two fields (ShippedToCust and ShippedToPlant, only one of which would ever be filled at a time) on the Details table, I would like just one ShippedTo field. As far as I know, you can't do a lookup from two tables. I've tried to do a simple select query and I've tried to do a union query. The union query just dumped all of my plant records into the customer columns. When I run the select query, it runs, but it brings back nothing.

Is there a way to do this, or do I just need to put two fields in my table and then on the report make whichever one is blank not visible?

Thank you in advance for all your help.

 

[xenou]

Hi,
In a general sort of way your query (report source) should have both fields:
SELECT X, Y, Z, ... ShippedToCust, ShippedToPlan FROM Table ... WHERE ...

But in a report you can show one field with a calculated expression:
=IIF(Nz([ShippedToCust])="", [ShippedToPlant], [ShippedToCust])


Notes:
This can also be resolved in a query. Given your Original Query as Query1, then you write Query2 to query Query1 (or, alternatively use a nested query structure in one query written by hand):
SELECT X, Y, Z, IIF(Nz([ShippedToCust])="", [ShippedToPlant], [ShippedToCust]) AS ShippedTo FROM Query1

Also by using Nz() we assume true NULLs. If the values could be empty strings, then a more robust test is:
IIF(Len([ShippedToCust] & "")=0, [ShippedToPlant], [ShippedToCust])

 

[AlexaS]

Ok so if I'm reading your response correctly in the Details table I need to have two fields. At which point I can create a query that concatenates the fields before I create a report based on that query. Right?

 

[xenou]

Yes, that's correct. Actually concatenation is a good idea if one of the ShippedTo's is always Null. Then you can just query for [ShippedToPlant] & [ShippedToCust] AS ShippedTo. This is a little dangerous if there is any possibility of both fields getting values somehow.

 

[AlexaS]

Yes, that's correct. Actually concatenation is a good idea if one of the ShippedTo's is always Null. Then you can just query for [ShippedToPlant] & [ShippedToCust] AS ShippedTo. This is a little dangerous if there is any possibility of both fields getting values somehow.

And that's one of the reasons I try to take as much "guesswork" as I can out of anything I build for an end user - I have absolutely no faith that they will follow directions. There is no business reason (it should always be one or the other) but I can see them going "why is there a blank here? I must need to put something in". Is there anyway to include a screentip or a checkbox (or something) that will let them know (or only show one based on which checkbox they choose)?

 

[xenou]

Hi,
What is the form for? I didn't know that users might be checking boxes. It's not clear what's up. Normally you'd just take the one that's not null, I suppose. Or if there is a choice between Ship To Customer and Ship To Plant you'd have a field to mark which option was selected (possibly with a default to one or the other).

 

[AlexaS]

My question was more: "Could I put a check box in to say if it's to a plant or a customer and then have the appropriate ShipTo field available for input?"

The form is actually a subform (which I was already hoping to hide or display based on a checkbox in the main form but I haven't figured that part out yet) based on the data entry that needs to be done to file a claim. The information is needed to populate a report (hopefully, haven't built that yet so I'm not sure it's going to work), that they then save as a PDF so they can email the information to someone outside the company. I don't know if that clarifies anything or raises more questions.

Thank you for your help.

 

[AngelJ]

I have absolutely no faith that they will follow directions.

I always assume end users won't follow directions, and that they'll do everything they can to break the applications I develop for them.

 

[AlexaS]

I always assume end users won't follow directions, and that they'll do everything they can to break the applications I develop for them.

Yeah same here. Behind the scenes I say "I try to make my stuff as idiot-proof as possible". To the end user I say "I'm trying to make things as easy on you as I can." LOL

 

[xenou]

My question was more: "Could I put a check box in to say if it's to a plant or a customer and then have the appropriate ShipTo field available for input?"

The form is actually a subform (which I was already hoping to hide or display based on a checkbox in the main form but I haven't figured that part out yet) based on the data entry that needs to be done to file a claim. The information is needed to populate a report (hopefully, haven't built that yet so I'm not sure it's going to work), that they then save as a PDF so they can email the information to someone outside the company. I don't know if that clarifies anything or raises more questions.

Thank you for your help.

I would do it so that there is only one field available for input ("ShipTo"). Then you can have a radio button for the type of ship to (Plant or Cust). When you update the form you put the value in the appropriate field. This can happen in the before update event of the FORM. I'd probably actually have the *real* ShipToPlant and ShipToCust fields on the form but hidden. My Before_Update event for the FORM would contain the code:

PSEUDOCODE

IF RadioButton equals ShipToPlant
    Set hidden ShipToPlant field equal to the value in the ShipTo textbox
    Set the hidden ShipToCust value to Null

Otherwise
    Set hidden ShipToCusts field equal to the value in the ShipTo textbox
    Set the hidden ShipToPlant value to Null



This actually makes me think that you could have actually one ShipTo field in your table, and a ShipToType field to go with it (populate it with a "C" or a "P", or something like that). But it's probably not time to be changing the table. Addresses can be messy to deal with - one of the more difficult ones, since there can be many addresses and contacts associated with other tables.