Why sometime dup rows?

[Sara]

I have a query that sometimes outputs dup rows, and
sometimes doesn't. I've been struggling to figure out why
for a week now and I just can't!

Here goes:
The data that comes in is imported right into Access2000
from .csv.
Fields:
Date, Hour, Store#, SalesPerHour, UnitsPerHour,
CustsPerHour, AvgCustSale$, AvgCustUnit#, AvgUnitSale$,
and Percentage fields that I'm not taking in my query.

I have a query that selects data for a date range
(selectdata), and another based on the first to find the
Average of the fields for a store for an hour for the time
period.

First Query:
PARAMETERS [Enter Start Date for Data] DateTime, [Enter
End Date for Data] DateTime;
SELECT HourlyDataTest.Hour, HourlyDataTest.Store,
HourlyDataTest.Saledate, [Static Store Info].StoreName,
HourlyDataTest.SalesPerHour, HourlyDataTest.UnitsPerHour,
HourlyDataTest.CustPerHour, HourlyDataTest.AvgCustSale,
HourlyDataTest.AvgCustUnit, HourlyDataTest.AvgUnitSale
FROM HourlyDataTest INNER JOIN [Static Store Info] ON
HourlyDataTest.Store = [Static Store Info].StNum
WHERE (((HourlyDataTest.Saledate) Between [Enter Start
Date for Data] And [Enter End Date for Data]))
ORDER BY HourlyDataTest.Hour, HourlyDataTest.Store,
HourlyDataTest.Saledate;

Second Query, based on the first:
SELECT [Q: Select Data for Comparison].Hour, [Q: Select
Data for Comparison].StoreName, Avg([Q: Select Data for
Comparison].SalesPerHour) AS AvgOfSalesPerHour, Avg([Q:
Select Data for Comparison].UnitsPerHour) AS
AvgOfUnitsPerHour, Avg([Q: Select Data for
Comparison].CustPerHour) AS AvgOfCustPerHour, Avg([Q:
Select Data for Comparison].AvgCustSale) AS
AvgOfAvgCustSale, Avg([Q: Select Data for
Comparison].AvgCustUnit) AS AvgOfAvgCustUnit, Avg([Q:
Select Data for Comparison].AvgUnitSale) AS
AvgOfAvgUnitSale
FROM [Q: Select Data for Comparison]
GROUP BY [Q: Select Data for Comparison].Hour, [Q: Select
Data for Comparison].StoreName, [Q: Select Data for
Comparison].Store;

Here's the problem: If I run it for 2/22; it's ok. If I
run it for 2/22 thru 2/23, it's ok. (OK means one line
per time slot per store; averages in the columns as I
wanted). If I run it for 2/22 thru 2/24 I get some time
slots OK and others with 2 rows of data! This is killing
me. What am I doing wrong?

Thanks so very much.

 

[John Vinson]

Here's the problem: If I run it for 2/22; it's ok. If I
run it for 2/22 thru 2/23, it's ok. (OK means one line
per time slot per store; averages in the columns as I
wanted). If I run it for 2/22 thru 2/24 I get some time
slots OK and others with 2 rows of data! This is killing
me. What am I doing wrong?

I would guess that there are some values of StNum (in the records for
those dates) that have TWO records in the stores table.

 

[Guest]

Thanks, but there are not any dups. The stores table is
clean. Any other ideas?

 

[Gary Walter]

Hi Sara,

PMFBI

Did you run a "group by count" query on each table
and check for count > 1?

I might imagine the dups could more likely
appear in the csv table.

PARAMETERS [Enter Start Date for Data] DateTime,
[Enter End Date for Data] DateTime;
SELECT HourlyDataTest.Hour, HourlyDataTest.Store,
HourlyDataTest.Saledate, COUNT(*) As DupCount
FROM HourlyDataTest
WHERE (((HourlyDataTest.Saledate) Between [Enter Start
Date for Data] And [Enter End Date for Data]))
GROUP BY HourlyDataTest.Hour, HourlyDataTest.Store,
HourlyDataTest.Saledate
HAVING COUNT(*)>1;

But I was wrong once....
so it could be in the other table.

SELECT
[Static Store Info].StoreName,
[Static Store Info].StNum,
COUNT(*) AS DupCount
FROM [Static Store Info]
GROUP BY
[Static Store Info].StoreName,
[Static Store Info].StNum
HAVING COUNT(*)>1;

Apologies again for butting in, but it will help
knowing what the results are from these 2 queries.

Good luck,

Gary Walter