Solved Duplicate Items in Query (1 Viewer)

wmix

Registered User.
Local time
Today, 10:13
Joined
Mar 16, 2017
Messages
34
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:

CandyVendPriceIDMachineIDProductTypeIDVendPriceStartDateEndDate
3758951.0001/01/201705/15/2022
6008951.2505/16/202208/07/2023
8908951.5008/08/202312/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.
ProductInOutIDMachineIDProductSoldProductExpiredProductTypeIDVendPriceFillWeek
1371051.2510/23/2017
1371051.2510/23/2017
1371051.2510/23/2017
1371051.2510/23/2017
2401010.8510/23/2017
24010101.2510/23/2017
24010101.0010/23/2017
24010101.0010/23/2017
24010101.0010/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

Super Moderator
Staff member
Local time
Today, 11:13
Joined
Jan 23, 2006
Messages
15,394
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

Registered User.
Local time
Today, 10:13
Joined
Mar 16, 2017
Messages
34
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

Banishment Pending
Local time
Today, 10:13
Joined
May 11, 2011
Messages
11,669
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

Lifelong Learner
Local time
Today, 08:13
Joined
Mar 14, 2017
Messages
8,891
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

Registered User.
Local time
Today, 10:13
Joined
Mar 16, 2017
Messages
34
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)
 

Users who are viewing this thread

Top Bottom