how can i search two unrelated tables at the same time Access 200

[Kay]

Hello all,

I have a good understanding of rrelational databasing, but not much about
VB. I am trying to help an associate search two unrelated tables at the same
time. Access is used to hold information as two separate flat files because
Excel cannot hold that many records. Thus..no relational database qualities.
The two tables come off of separate feeds. At least fifteen times a day, the
associate must field questions from our client asking for a specific number.
It could be in either table or both. We want to return a query with the
results or a message saying no record found. 99% of the time the record will
be found. Because each table has as many as four million records, I thought
a union query would run slow if not crash on a standard pc. I would like to
use a dialog form that allows for the entry of the criteria and then searches
both tables. What would you suggest.

 

[Allen Browne]

If you have around 8 million records, I can't think of any valid reason not
to import the data properly into an Access table, so you can search it
reliably.

The alternative is to use OpenRecordset() on each table, with a WHERE clause
that returns only the round record(s), and then open the appropriate
table(s.)

 

[Kay]

Allene,

the tables are dumped into the access database automatically as live feeds.
We do not have a choice over that. I don't want to do an additional task of
importing one table into the other every day, I just want to pass criteria
to a query via a dialog form with the understanding that the code looks at
both tables. If it is both, it returns both, if it is in only one it returns
that record,, if there is no record it display a message that tells us that
number cannot be found. If you think the answer is a union query that then
searches both recordsets at the same time and is not a drain on the system
resources, I am willing to go that way. Please advise...remember, I can't
really write code, I just modify it.

Thanks!

 

[Allen Browne]

A UNION is the obvious approach, but you will need to test if performance is
satisfactory. (Be sure to put the WHERE clause in both SELECTs.)

There are several coding solutions, but - from what you way - it sounds like
that's beyond you at this point.