Can't get this Count to Work

S

sara

I get data daily from an outside source. It has the date,
Store Number, Cashier Number, Cashier Name and the $amount
he Cashier was off for the day.

Loss Prevention (LP) needs to know anyone who was off +
or -$5.00 or more within the prior 90 days. I figured
this could be done in a query, then put on a report. I
would like to have the 3 (or more) records that were +/-
5.00 all display, so I can print them all. It will allow
us to find "False positives" such as an office error, and
the cashier was not really off.

I only have data since Jan 1 right now, so my date is only
looking back -30 days (but I know I can modify that). And
I'm looking for people with 2 occurrences or more, so I
can get output. (Again, I know I can change that when it
all works).

When I run the query, the results are wrong (no records
found).
I keyed my table on SaleDate/Store/Cashier as this must be
unique.
I'm not getting the cashiers who have more than 1 day of
having +/- $5 in the time period.

Here is the code:

SELECT [T: Cashier OverShort].SALEDATE, [T: Cashier
OverShort].STNum, [T: Cashier OverShort].Cashier, [Static
Store Info].StoreName, [T: Cashier OverShort].OverShort
FROM [T: Cashier OverShort] INNER JOIN [Static Store Info]
ON [T: Cashier OverShort].STNum = [Static Store Info].StNum
GROUP BY [T: Cashier OverShort].SALEDATE, [T: Cashier
OverShort].STNum, [T: Cashier OverShort].Cashier, [Static
Store Info].StoreName, [T: Cashier OverShort].OverShort,
[T: Cashier OverShort].Cashier, [T: Cashier
OverShort].OverShort
HAVING ((([T: Cashier OverShort].SALEDATE) Between DateAdd
("d",-30,[EnterDate]) And [EnterDate]) AND (([T: Cashier
OverShort].OverShort)>4.99 Or ([T: Cashier
OverShort].OverShort)<-4.99) AND ((Count(*))>=2))
ORDER BY [T: Cashier OverShort].SALEDATE, [T: Cashier
OverShort].STNum;



Sample Data: (Just Date, Store, Cashier, OverShort)
1/15 Lynn 5703 (10.00)
1/20 Lynn 5703 10.63
1/17 Swansea 11207 (6.11)
1/4 Swansea 11207 (9.93)
1/27 Worcester 12513 (10.00)
1/27 Lynn 15318 (9.90)
1/27 Norwood 16228 (64.46)
1/30 Worcester 12513 (10.29)
1/3 Weymouth 15894 (5.45)
1/18 Weymouth 15894 9.60
1/24 Weymouth 15894 (8.02)
2/10 Weymouth 15894 (10.00)
1/3 Somerville 2287 9.91


15318 and 16228 have only 1 error in the time
period; many others have 2, one has 3 or 4. I think the
problem has something to do with the Count. Instead of
counting amongst the results of the date selection, it's
counting 1 for each sale date. I want to see the sale
date, but count amongst the range of all dates. I just
can't figure out HOW to fix that.

I am stumped, and, again, ask for (and so greatly
appreciate) your help.
Sara
 
C

chris

Hi Sara,

It sometimes pays when you get stuck like this, to
think "Perhaps what I am trying to do cannot be done in
one query" and then build what you want in little steps.
What you end up with may not be optimum, but it works.

Here is what I suggest...

Query1

SELECT
[T: Cashier OverShort].*
FROM
[T: Cashier OverShort]
WHERE
[T: Cashier OverShort].SALEDATE Between DateAdd
("d",-30,[EnterDate]) And [EnterDate]
AND ABS([T: Cashier OverShort].OverShort)>4.99

This will return all records between the selected dates
where the diff is greater than +/- 4.99
Now do the counting

Query2

SELECT
Query1.STNum,
Query1.Cashier,
[Static Store Info].StoreName,
Count(*) as Times
FROM
Query1
INNER JOIN [Static Store Info] ON
Query1.STNum = [Static Store
Info].StNum
GROUP BY
Query1.STNum,
Query1.Cashier,
[Static Store Info].StoreName,
HAVING
Count(*)>1
ORDER BY
Query1.STNum,
Query1.Cashier;

Then to itemise the entries...

Query3

SELECT
[T: Cashier OverShort].SALEDATE,
[T: Cashier OverShort].STNum,
[T: Cashier OverShort].Cashier,
[Static Store Info].StoreName,
[T: Cashier OverShort].OverShort
FROM
[T: Cashier OverShort]
INNER JOIN [Static Store Info] ON
[T: Cashier OverShort].STNum =
[Static Store Info].StNum
INNER JOIN Query2 ON
[T: Cashier OverShort].Cashier =
[Static Store Info].Cashier

WHERE
ABS([T: Cashier OverShort].OverShort)>4.99
ORDER BY
[T: Cashier OverShort].SALEDATE,
[T: Cashier OverShort].STNum,
[T: Cashier OverShort].Cashier;


This may not work as, without data, it is difficult to
debug. But it should not take much tweeking.

If you have more questions extract my email address and
contact me directly
Chris
-----Original Message-----
I get data daily from an outside source. It has the date,
Store Number, Cashier Number, Cashier Name and the $amount
he Cashier was off for the day.

Loss Prevention (LP) needs to know anyone who was off +
or -$5.00 or more within the prior 90 days. I figured
this could be done in a query, then put on a report. I
would like to have the 3 (or more) records that were +/-
5.00 all display, so I can print them all. It will allow
us to find "False positives" such as an office error, and
the cashier was not really off.

I only have data since Jan 1 right now, so my date is only
looking back -30 days (but I know I can modify that). And
I'm looking for people with 2 occurrences or more, so I
can get output. (Again, I know I can change that when it
all works).

When I run the query, the results are wrong (no records
found).
I keyed my table on SaleDate/Store/Cashier as this must be
unique.
I'm not getting the cashiers who have more than 1 day of
having +/- $5 in the time period.

Here is the code:

SELECT [T: Cashier OverShort].SALEDATE, [T: Cashier
OverShort].STNum, [T: Cashier OverShort].Cashier, [Static
Store Info].StoreName, [T: Cashier OverShort].OverShort
FROM [T: Cashier OverShort] INNER JOIN [Static Store Info]
ON [T: Cashier OverShort].STNum = [Static Store Info].StNum
GROUP BY [T: Cashier OverShort].SALEDATE, [T: Cashier
OverShort].STNum, [T: Cashier OverShort].Cashier, [Static
Store Info].StoreName, [T: Cashier OverShort].OverShort,
[T: Cashier OverShort].Cashier, [T: Cashier
OverShort].OverShort
HAVING ((([T: Cashier OverShort].SALEDATE) Between DateAdd
("d",-30,[EnterDate]) And [EnterDate]) AND (([T: Cashier
OverShort].OverShort)>4.99 Or ([T: Cashier
OverShort].OverShort)<-4.99) AND ((Count(*))>=2))
ORDER BY [T: Cashier OverShort].SALEDATE, [T: Cashier
OverShort].STNum;



Sample Data: (Just Date, Store, Cashier, OverShort)
1/15 Lynn 5703 (10.00)
1/20 Lynn 5703 10.63
1/17 Swansea 11207 (6.11)
1/4 Swansea 11207 (9.93)
1/27 Worcester 12513 (10.00)
1/27 Lynn 15318 (9.90)
1/27 Norwood 16228 (64.46)
1/30 Worcester 12513 (10.29)
1/3 Weymouth 15894 (5.45)
1/18 Weymouth 15894 9.60
1/24 Weymouth 15894 (8.02)
2/10 Weymouth 15894 (10.00)
1/3 Somerville 2287 9.91


15318 and 16228 have only 1 error in the time
period; many others have 2, one has 3 or 4. I think the
problem has something to do with the Count. Instead of
counting amongst the results of the date selection, it's
counting 1 for each sale date. I want to see the sale
date, but count amongst the range of all dates. I just
can't figure out HOW to fix that.

I am stumped, and, again, ask for (and so greatly
appreciate) your help.
Sara




.
Click to expand...
 
Top