Query Duplicates Data

[tim1234]

I have two tables that I need to join in a query. The join I have now duplicates the info for lot #. The tables are as follows;

TipBreakage - Lot#(Primary Key), DateReceived, Part#
and
Process Information - Lot #, TipBlank#, BulkPart # (Primary Keys), Quantity Received Engraving.

All I want to be able to do is get the Quantity Received Engraving information into a query with the Tip Breakage table information without duplicating anything. Thank you very much for your time.

Tim

 

[dcx693]

What's the relationship between the tables? I assume it's a 1-many from the TipBreakage to the Process Information table, and thus for each Lot# from the TipBreakage table, you are seeing multiples from the Process Information table. What's being duplicated? If you only want to see one record from the Process Information table, which record do you want to pick out?

 

[tim1234]

Query Information

What's the relationship between the tables? I assume it's a 1-many from the TipBreakage to the Process Information table, and thus for each Lot# from the TipBreakage table, you are seeing multiples from the Process Information table. What's being duplicated? If you only want to see one record from the Process Information table, which record do you want to pick out?

The relationship shows up as "indeterminate". I can't get a one to one or ne to many. All the information is being duplicated. I really have 25 entrees for a specific lot # in tip breakage table, but 50 show up in my query. I only want the "Quantity Received Engraving" field to compare my total produced against the total returned, by lot #.

Thanks again.

 

[tim1234]

What's the relationship between the tables? I assume it's a 1-many from the TipBreakage to the Process Information table, and thus for each Lot# from the TipBreakage table, you are seeing multiples from the Process Information table. What's being duplicated? If you only want to see one record from the Process Information table, which record do you want to pick out?

This is the table data. The relationship is "indeterminate" when joined by "lot#" field. Lot # is in the primary keys for both tables. Only the "QTY Received" field is pulled from tbl2. All others are from tbl1. As you can see it is repeated for each record. This data is actually only three records. Is there some sort of hide duplicates criteria or something I can use at the query level? Thanks.

Part# Lot# DateReceived(Broken) CustomerNumber Warranty Qty Received
930-013 041 3/24/2004 DUNSJL No 250
930-013 041 3/24/2004 DUNSJL No 250
930-013 041 2/7/2004 LIEBJ No 250
930-013 041 2/7/2004 LIEBJ No 250
930-014 041 3/3/2004 LIMIMA No 250
930-014 041 3/3/2004 LIMIMA No 250

 

[dcx693]

What is the data for this lot# from the TipBreakage table and what is it from theProcess Information table? I just want to make sure I see what's going on here. Are you actually joining the tables in the query? I've never had an indeterminate relationship, but it sounds like you're not actually joining the tables, thus winding up with 3x2 =6 records (otherwise known as the Cartesian product).

 

[tim1234]

Answer

What is the data for this lot# from the TipBreakage table and what is it from theProcess Information table? I just want to make sure I see what's going on here. Are you actually joining the tables in the query? I've never had an indeterminate relationship, but it sounds like you're not actually joining the tables, thus winding up with 3x2 =6 records (otherwise known as the Cartesian product).

I attached a picture of my relationships. It should be easier for you to understand as well. I tried to make a junction table because that's what this Access Book I have said to do. I think I've got it messed up good. Thanks for all your help.

 

[dcx693]

Oh my. What a diagram. There is probably much we could correct, but I don't know the relationship between all those tables.

There is a query setting that you can use to have it filter out duplicates. Open the query in design view, go to the View menu and choose Properties. Now click someone on the query background (the grey area where the tables are shown) to make the property window display properties for the query itself). Change the Unique Values setting to Yes. That should get rid of your duplicates, but it won't get rid of your underlying problem.

For the lot# of 041, please post the data from the two tables you mentioned in your first post. That should help me see exactly what's going on here.

 

[tim1234]

Lot #41

Oh my. What a diagram. There is probably much we could correct, but I don't know the relationship between all those tables.

There is a query setting that you can use to have it filter out duplicates. Open the query in design view, go to the View menu and choose Properties. Now click someone on the query background (the grey area where the tables are shown) to make the property window display properties for the query itself). Change the Unique Values setting to Yes. That should get rid of your duplicates, but it won't get rid of your underlying problem.

For the lot# of 041, please post the data from the two tables you mentioned in your first post. That should help me see exactly what's going on here.

Okay, here is the data from table "TipBreakage".
Lot# DateReceived(Broken) Part#/Description(Finish)
016 1/1/2001 41-511 CPR-1, Bulk
015 8/19/2003 41-511 CPR-1, Bulk
015 8/19/2003 41-511 CPR-1, Bulk
015 8/19/2003 41-511 CPR-1, Bulk
015 8/19/2003 41-514 CPR-4D, Bulk
026 8/21/2003 930-014 Tip, CPR-4D Diamond
041 8/21/2003 930-013 Tip, CPR-3D Diamond
027 8/21/2003 930-014 Tip, CPR-4D Diamond
013 8/21/2003 930-043 Tip, BUC-3 Digger

and Table Process Information
Lot# TipBlankReceipt# BulkPart#/Descripton Qty Received(Engraving)Total Lot
001 tb00079 41-565 Buc-1a bulk 267
001 tb00082 41-566 Buc-3a bulk 266
001 tb00083 41-034 Pst-4 bulk 508
001 tb00086 41-037 Pst-7 Bulk 501
001 tb00086 41-038 Pst-8 Bulk 500
002 tb00081 41-031 Pst-1 Bulk 507
002 tb00081 41-032 Pst-2 Bulk 507
002 tb00084 41-035 Pst-5 bulk 574
002 tb00084 41-036 Pst-6 Bulk 450
003 tb00076 41-024 Perio Tip #7 Bulk 505
004 tb00054 41-517 CPR-7, Bulk 513

Hope this is what you were referring to. Thanks again for all your help.