CODEWITHSUNDEEP
sqlvbams-access
BigDX
BigDX

Reputation: 3547

Creating a union in access with multiple tables and field on one table being the same as multiple fields on others

Here is my current query

Screenshot of my form:

screenshot

SELECT * FROM jdsubs
 INNER JOIN amipartnumbers ON amipartnumbers.oemitem = jdsubs.oempartnumber
    WHERE ((([txtEnterNumber])
    In ([jdsubs].[oemsubnumber],[jdsubs].[oempartnumber])));
UNION SELECT * FROM ihsubs
 INNER JOIN amipartnumbers ON amipartnumbers.oemitem = ihsubs.oempartnumber
    WHERE ((([txtEnterNumber])
    In ([ihsubs].[oemsubnumber],[ihsubs].[oempartnumber])));
UNION SELECT * FROM mfsubs
 INNER JOIN amipartnumbers ON amipartnumbers.oemitem =mfsubs.oempartnumber
    WHERE ((([txtEnterNumber])
    In ([mfsubs].[oemsubnumber],[mfsubs].[oempartnumber])));

Can I simplify this to just do a union on one query then on another query i can compare txtEnterNumber to oemsubnumber and oempartnumber?

I feel like this one query is doing too much work.

Or am i doing this right?

I'm searching about a millions records so I want to make sure this is efficient as possible

Upvotes: 1

Views: 705

Answers (1)

Tom Collins
Tom Collins

Reputation: 4069

You'll have to run it as is. Assuming oemitem, oempartnumber, & oemsubnumber are all indexed, as they should be.

If you union everything first, then try compare your part numbers, you'll be doing so against an un-indexed query result.

A couple of ideas for improvement are:

  • If a part number can match only match just one parts table, then do each query one at a time until you get a result back.
  • Combine all three of your part tables (setting 1 field as a flag to determine part origin), then run your search against that table.

Good luck

Upvotes: 2

Related Questions