Query using multiple tables...relationships???

[MrsTFB]

Hey,
I have a fairly simple database for returns. I have 3 tables...Return Information (main table), Customer Information and Part Information.

I have created a query from all three tables, however....I link the tables by CustNum and PartNum. When I run the query to [Enter Return Number], if there is no customer information or a part number is not entered, it does not pull the return record.

How have I missed the relationships?

Thanks for any help,
MrsTFB

 

[SkipVought]

Hi,

List the Table elements, showing the elements that you have related.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[MrsTFB]

I think I understand what you mean, sorry rookie here.

In my main table I list Return Number, Part Number, and Customer Number....along with many other fields of course.

Then, I have a Customer Information table that links with CustNum in the main table and lists the address, city, state.....etc for each customer.

Then, I have a PartDesc table that lists only PartNum and description...linked with PartNum

I have gone to the relationship window and linked the tables with the 1 to many from the main tables. It is the customer table that won't show the record with an empty field in the main table...I'm still working on it.

Thanks,
MrsTFB

 

[BNPMike]

Relationships aren't necessarily what you think they are. The only way to connect tables is via joins. So firstly you need to double check your SQL has joins in it. We assume it is as it seems your query sometimes works.

Most likely you need to adjust your joins to be outer joins (usually LEFT). You can do this by double clicking the join line in QBE and choosing the relevant option.

 

[SkipVought]

I understand.

You stated...

if there is no customer information or a part number is not entered, it does not pull the return record.

and that is EXACTLY what should happen. You should NEVER have a row in MAIN that does not have a Cust# and corresponding Customer row AND Part# and corresppnding PateDesc row. BOTH must exist.


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[MrsTFB]

I fixed it! I'm just gonna require that a customer number is entered...or N/A...thanks!