Query is giving me too much info?

[reel knotty]

Ok I asked a similar question on the reports forum but this may be the better location for it..... I have 3 linked tables all linked by a repair order number. The 3 tables are- RO number table which contains the RO number, the customer, and unit number......Inventory parts table which contains the RO number (linked), part number, and quantity.........Sublet Parts table which contains the RO number (linked), Vendor, part number, Description, quantity, and Price. Now I have 3 queries from these 3 tables but my inventory query returns multiple entries for each item. IE it is returning the same item 4 times in data veiw when I should only have 1.

I should mention that the RO number table will have one entry per RO number while both the inventory parts table and the sublet parts table may have 0-10 entries per RO number.

If this is not explained clear enough please ask for more info I really need to get this figured out.

Thanks in Advance
Nate

 

[antomack]

Have you tried using the 'DISTINCT' predicate? Check help under distinct for further details.

Example:
SELECT DISTINCT Table6.User FROM Table6;

would select only one record for each user even if there are multiple occurences of the user in the table.

Other than that it's hard to define the problem without seeing sample data from the inventory table and/or the query you are using.

 

[reel knotty]

There is only 1 record in the table but the query returns it 4 times.

The tables:
RO number-
RO number RO Date customer unit number
343 07-29-02 yadda 774
344 07-30-02 mikes 888

inventory-
RO Number Part Number Quantity
343 3487-4 4
343 194 3
343 777 1
344 194 2
344 54333 1

sublet parts table
RO number Vendor part # Description quantity Price
343 joes 487 light bulb 1 2.50
343 joes 558 brake 2 13.67
344 truck 999 head light 1 18.54

Now the RO Number Query and the sublet parts query returns the proper info but the Inventory Query returns:
RO Number Part Number Quantity
343 3487-4 4
343 3487-4 4
343 3487-4 4
343 3487-4 4
343 194 3
343 194 3
343 194 3
343 194 3
343 777 1
343 777 1
343 777 1
343 777 1
344 194 2
344 194 2
344 194 2
344 194 2
344 54333 1
344 54333 1
344 54333 1
344 54333 1


I did simplify this a bit but I think you can get the idea of what is happening. See if that helps.

Thanks
Nate

 

[antomack]

Can you post the SQL from the query you are having the problem with?

 

[reel knotty]

SELECT [master ro invoice 1].[RO number], [inventory RO tracker].[Inventory number], [inventory RO tracker].Qty, [parts list1].[Part Number], [parts list1].Manufacturer, [parts list1].Description, [parts list1].CURRENT, [parts list1].Cost, [CUSTOMER BASE].[PARTS MARKUP RATE], [Qty]*[Cost]*[CUSTOMER BASE]![PARTS MARKUP RATE] AS [inventory partsprice], *
FROM [master ro invoice 1] LEFT JOIN ([parts list1] RIGHT JOIN [inventory RO tracker] ON [parts list1].[DB part number] = [inventory RO tracker].[Inventory number]) ON [master ro invoice 1].[RO number] = [inventory RO tracker].[RO number], [CUSTOMER BASE] RIGHT JOIN [tractor info] ON [CUSTOMER BASE].[CUSTOMER NUMBER] = [tractor info].[Customer number];

 

[The_Doc_Man]

Whenever you start getting multiple records back for what you think should be a unique relationship, one of a few things is happening.

Either (1) you have an improperly specified relationship or (2) you queried the wrong table or (3) you are joining on unrelated data. These three choices are various flavors of the same problem - a bad JOIN across unrelated data.

My first thought would be to very carefully examine the relationships to make sure that the correct table is on the 'one' side of your one-to-many relationships.

When you get too many records like that, it is because you are getting a permutation of the child records. Permutation joins (also called cartesian products) mean that the JOIN isn't doing what you think it is doing.