Best way for doing something?

[anoble1]

Guys,
I have a table that has 5 Part Number columns. If I want to price those 5 part columns from another table what is the best way? See screenshot. Right now I am linking each field (5) to the same table, all i did was duplicated the table.
This is taking forever to run though. Any ideas? I had to break off in 5 columns cause they are all different.

[PhilOfWalton]

Your picture is very difficult to read, so I think, if you want help, it would be best if you told us a little bit about what you are trying to achieve.
I have a feeling that you have a product that contains 5 parts, and you want to set a price against those parts to get the price if the product, but I may be well wrong.

Phil

[twinnyfo]

anoble1,

One thing that may be slowing down your query is that you area ctually nesting queries within your query, rather than tables. So, think about it this way, for each of the five fields in the parts table, Access must run the same query--for each field. If you have a bunch of records, that could take some time. So, for each record returned by your query, there are five additional queries being run. This may be re-duplicated for each record (I don't know enough about how Access joins queries to queries).

Try joining the underlying table instead of using queries and see if performance improves.

Other than that, it looks like one field is OEM, and then Baldwin 1/2 and Fleetguard 1/2. If none of the items in OEM are found in either the Baldwin or Fleetguard collections, then you might could make smaller Part Number tables to JOIN. But--I don't think that is the best structure for a DB, since all the parts might just be differentiated by brand name (for example).

But, as Phil said, if we knew exactly what you are trying to achieve, we could probably redirect to a different/better solution.

Hope this hepps!