clever query writers needed...

[ChrisM]

Trying to write a query:

A (fairly complex and slow) query returns the following results:
Basically a series of part numbers, but some parts may have an alternative
part(actually a different but compatable part, not just a different number
for the same part - ie could have a different cost price, supplier etc.)

PartNo AltPartNo
AB12
CD34 XYZ-89
EF56
GH78 VWX-01


What I then want to do is lookup the part numbers in another table to return
a bunch of details about those parts (Name, Supplier, CostPrice etc etc).

Can anyone suggest a way of doing this that doesn't involve the complex
query having to run more than once??

Thanks,

Chris.

 

[Allen Browne]

So some of the AltPartNo are not in the PartNo at all?
And you need one long list containing both, but deduplicated?

Try creating a UNION query:
SELECT PartNo FROM Table1
UNION
SELECT AltPartNO FROM Table1;
You can then use it as if they were all one long list.

If you actually have multiple fields for the AltPartNo (since a part could
have multiple alternatives), it would be better to set up a junction table
between 2 copies of the same table, and indicate the alternatives that way.
In the end, this junction table would give you what you want.

 

[ChrisM]

Hi Allen,

Thanks for your reply, but as I said, I want to select from a query, not a
table, and the query takes a long time to run, so I don't want to run it
twice (for each half of the UNION query).

Any more suggestions?

Cheers,

Chris.