Solved Duplicate Items in Query (1 Viewer)

[wmix]

Hello Everyone, I'm struggling with duplicate items in a query and completely stumped as to why it's happening. I have walked away from this issue for a week and came back to it yesterday and today and I'm still as frustrated as last week.

We have a vending business and track a lot of information. In one table we track everything we add to a vending machine. In other tables we track product prices based on the individual vending machine. We have to do this because we have multiple factors that go into how much an item sells for (management company, location, if they make a commission on sales, etc..).

Here's an example of a table that stores prices:

tblVendPricesCandy
CandyVendPriceID - Auto Number Primary Key
MachineID - FK tblmachines
ProductTypeID - FK tblproducts
VendPrice
StartDate
EndDate

This table has thousands of records, for example:

CandyVendPriceID	MachineID	ProductTypeID	VendPrice	StartDate	EndDate
375	89	5	1.00	01/01/2017	05/15/2022
600	89	5	1.25	05/16/2022	08/07/2023
890	89	5	1.50	08/08/2023	12/31/9999

Query # 1 pulls information based on products added to a vending machine - runs perfectly and I use this same data in a lot of other queries.

Query # 2 pulls information from Query # 1 but only shows the Candy items and it works without any issues and I use this information in a lot of other queries also.

Query # 3 - New Query
My new query needs to add the Vend Price so I can give each item sold or expired a price point based on the date.
I started with basic information from Query # 2 and I get 635,527 records (I did this to verify how many records).
Then I added the tblVendPricesCandy and it throws things all off.

ProductInOutID (PK)
MachineID
ProductSold
ProductExpired
ProductTypeID
VendPrice
FillWeek with the following Criteria: Between [tblVendPricesCandy].[StartDate] AND [tblVendPricesCandy].[EndDate]

When I do this I get 3,172,536 records.

What I'm seeing looks something like this, it makes no sense to me at all.

ProductInOutID	MachineID	ProductSold	ProductExpired	ProductTypeID	VendPrice	FillWeek
1	37	1	0	5	1.25	10/23/2017
1	37	1	0	5	1.25	10/23/2017
1	37	1	0	5	1.25	10/23/2017
1	37	1	0	5	1.25	10/23/2017
2	40	1	0	10	.85	10/23/2017
2	40	1	0	10	1.25	10/23/2017
2	40	1	0	10	1.00	10/23/2017
2	40	1	0	10	1.00	10/23/2017
2	40	1	0	10	1.00	10/23/2017

For example, on ProductInOutID # 2 - the price should be $0.85 - as that was the price back then for that machine. Then the price changed to $1.00 then the price changed to $1.25. But as to why it's duplicating I have no idea.

I do this exact same type of query for our "FOOD" products and that query works fine. The major difference between Food and Candy is that all Food items are the same price, no matter what machine they are in. So that table is not based on machine, only on ProductTypeID with a StartDate and EndDate for a VendPrice.

Any guidance that could be sent my way I would greatly appreciate. Thank you!

 

[jdraw]

wmix,

If you provide the table structures/designs including indexes, and the SQL for the query(ies) involved, I'm sure you'll get focused responses.

The critical factor/concept is what field or combination of fields in your query make(s) each record unique. Since you have "duplicates", some field(s) is included that is causing the record to not be unique.

 

[wmix]

wmix,

If you provide the table structures/designs including indexes, and the SQL for the query(ies) involved, I'm sure you'll get focused responses.

The critical factor/concept is what field or combination of fields in your query make(s) each record unique. Since you have "duplicates", some field(s) is included that is causing the record to not be unique.

Thanks jdraw.
The unique identifier is the ProductInOutID.
Here's the SQL of the query:

SELECT qProductsSold_Candy.ProductInOutID, qProductsSold_Candy.MachineID, qProductsSold_Candy.ProductSold, qProductsSold_Candy.ProductExpired, qProductsSold_Candy.FillWeek, qProductsSold_Candy.ProductTypeID, tblVendPricesCandy.VendPrice

FROM qProductsSold_Candy LEFT JOIN tblVendPricesCandy ON qProductsSold_Candy.MachineID = tblVendPricesCandy.MachineID

WHERE (((qProductsSold_Candy.FillWeek) Between [tblVendPricesCandy].[StartDate] And [tblVendPricesCandy].[EndDate]));

 

[plog]

The key to these types of problems is to focus on 1 specific instance and drill down on it. Don't get overwhelemed thinking you have to solve 3 million issues. Fix just one set of duplicates and most likely that type of error is responsible for either all or a vast majority of them.

2 things could be happening:

1. qProductsSold has multiple records with FillWeeks that fall between corresponding STartDate/EndDate

2. You have overlapping data in the tblVendPricesCandy.

So run your query with the dupes, find one MachineID/FillWeek/StartDate/EndDate with duplicates and open qPrioductsSold to it and tblVendPricesCandy to it. Manually, walk through in your mind what your query is doing to each of the records and you will see where the issue is.

 

[Isaac]

The key to these types of problems is to focus on 1 specific instance and drill down on it. Don't get overwhelemed thinking you have to solve 3 million issues. Fix just one set of duplicates and most likely that type of error is responsible for either all or a vast majority of them.

2 things could be happening:

1. qProductsSold has multiple records with FillWeeks that fall between corresponding STartDate/EndDate

2. You have overlapping data in the tblVendPricesCandy.

So run your query with the dupes, find one MachineID/FillWeek/StartDate/EndDate with duplicates and open qPrioductsSold to it and tblVendPricesCandy to it. Manually, walk through in your mind what your query is doing to each of the records and you will see where the issue is.

Access does make it extra easy by allowing you to physically open tables and filter them.
Although beware of the right-click and Filter To type of approach, as it's fraught with the danger of not seeing what you're seeing, and has limitations too

 

[wmix]

I found the issue. I had to change from a LEFT JOIN to an INNER-JOIN

Didn't Work:
FROM qProductsSold_Candy LEFT JOIN tblVendPricesCandy ON qProductsSold_Candy.MachineID = tblVendPricesCandy.MachineID

Fixed:
FROM qProductsSold_Candy INNER JOIN tblVendPricesCandy ON (qProductsSold_Candy.ProductTypeID = tblVendPricesCandy.ProductyTypeID) AND (qProductsSold_Candy.MachineID = tblvendPricesCandy.MachineID)