Ambiguous Join Fix

[kagoodwin13]

Hi all, I'm trying to build a "checklist" for my current project database. I have multiple tables storing information about the same part number, and I would like to build a query that can collect the information from these tables side by side to show what pieces of information have been collected per part number.

I tried to connect the tables to the central part info table, however I got an error saying there was an ambiguous join, and it would not compute.

Attached is an example database that shows what I am trying to accomplish, however I am having an issue with the query.

Part Number 1 has 100% of information, so all information appears.
Part Number 2 is missing one field (manufacturer), but it does not show the other information stored concerning Part Number 2 (Part Number, Part Name).

Also in the database is a table that shows what I am trying to accomplish.

Any help? Thanks in advance.

DocumentChecklist-mdb.mdb

[rzw0wr]

Your relationships are a little off.
Make a table for just part numbers.
Use autonumber for a PK
add the part number table PK to the other tables.
Remove all of your relationships and make new one.
Relate all tables to the new part number table PK.
Nothing will be in the tables yet for the FK field.
Repopulate your tables using a form and you should be OK.

Dale

[kagoodwin13]

That got the results I needed. Thank you.

Is there a way to filter results based on OR logic? I'd like to return any part number that has any one piece of information in the MANUFACTURER or LOCATION fields, but exclude part numbers that have no information.

In this example, I'd like to return part numbers 1-3, but exclude part number 4, in the qryRESULTS query.

Version 2 attached:
DocumentChecklist-mdb v2.mdb

[rzw0wr]

In a query, the lines under the fields, IF the criteria for the fields are on the SAME row that is an AND, if you put them on different rows that is an OR.

When you do something in design view for a query and want to see the SQL, switch to the SQL view. You will see the AND or the OR there.

Dale

[kagoodwin13]

Yes, simple fix, sorry to bug you. I added "WHERE MANUFACTURERS IS NOT NULL OR LOCATION IS NOT NULL" In Design View, Criteria line under MANUFACTURERS was "Is Not Null" and Or line under LOCATION was also "Is Not Null."

Thanks for your help today. I've got everything I need to move ahead.