Querying two tables for one result

[spherey]

Hello, all. I'd love it if one of you could help me solve the latest conundrum.
I have two tables that I want to query with a user-specified parameter. There will only ever be one result in the query, which comes from one table or another, but not both. The query will return three fields for each of the two tables, and the fields are ostensibly the same (Company Name, Last Update, and Notes in one, and Company Name, LastUpdate, and Status in the other). I have two questions. Short of building two separate queries (easier to build, but that means two searches for the end users instead of one), is there a way to specify the same parameter in two different criteria fields? The parameter would be

 [Type the Company Name],

but the result could come from either one of the Company Name fields. Sort of like an OR qualifer, but in this case the two "values" would really be the same value which could come from one of two fields. Next, since only three of the six fields will ever return a value on any given query, is there a way to specify that if a field is empty, that its box be hidden, rather than displayed empty? That question is more a matter of the report, rather than the query, but perhaps there's something I need to be aware of on the query level.
O, and one last one: how should I set up the relationships in this query? If I choose "only where both rows are equal," I'll never get any results, and if I choose either one of the variations on "show all the results from this table and only the ones from the other which are equal," it will amount to basically the same thing, I think - I'll automatically exclude whatever record I'm querying for. Shoulds I just delete the relationship link and have the two tables unjoined within the query, or will that screw things up?

Thanks for any advice anyone can offer!

- Spherey

 

[CosmoKramer]

Check out Access help for the Union query. I think this is exactly what you're looking for....



A common mistake that people make when trying to design something completely foolproof is to underestimate the ingenuity of complete fools.
Douglas Adams

 

[spherey]

Thanks for the tip. Two things, though;

* the Union Query looks like it requires the tables to have something in common, and uses this common value as the criteria for returning the results. In this instance, the two tables wouldn't have anything in common; at least they wouldn't have any specific values in common. If I could make a dummy field in each of the tables, hide it, and make the criteria "IsNull," that might work.
* But, even if I could specify something like "IsNull," I need to be able to specify a user-selected parameter.(

[Type the Company Name]

, which would prompt the user the enter the company name they're searching for.) I don't see a way to specify both the parameter to search on AND a field which the two tables would have in common.

Any thoughts?

Thanks,

Spherey

 

[CosmoKramer]

They wouldn't have to have any values in common. Access help says:

Each SELECT statement must return the same number of fields, in the same order. The corresponding fields are required to have compatible data types with one exception: You can use a Number and a Text field as corresponding fields.

So, you should be able to try something like this:

SELECT [Company Name], [Last Update], [Notes]
FROM TableA
WHERE [Company Name] = [Type the Company Name]

UNION SELECT [Company Name], [Last Update], [Status]
FROM TableB
WHERE [Company Name] = [Type the Company Name];

Let me know if this helps....




A common mistake that people make when trying to design something completely foolproof is to underestimate the ingenuity of complete fools.
Douglas Adams

 

[spherey]

So, the qualifiers in the two

WHERE

statement wouldn't have to be the same in each table? If that's right, then yes, this might be exactly what I'm looking for. Let me give it a try. . .

 

[spherey]

That did it! Thanks! That's exactly what I'm looking for!

I appreciate you taking the time to help me out.

Spherey