I think Group By is causing me problems...

  • Thread starter Thread starter Bill Lentz
  • Start date Start date
B

Bill Lentz

I've written a set of queries to pull data from a linked .dbf file
into Access tables. I'm relatively new to Access and SQL, and have
run in to the following problem:

The first query in the set pulls data for a date range (and excludes
some data based on various criteria) from the linked table and puts it
in an access table. The SQL language is:

INSERT INTO tblCommission ( EmpInit, TransDate, CusFname, CusLname,
ReceiptNo, Category, Sku, InvDesc, SellPrice, CloseOut )
SELECT RECEIPT.SALEPER, RECEIPT.TRANSDATE, RECEIPT.FIRSTNAME,
RECEIPT.LASTNAME, RECEIPT.RECEIPTNO, RECEIPT.CATEGORY,
RECEIPT.STOCKNUM, RECEIPT.DESCS, RECEIPT.PRICE, RECEIPT.SHOWCASE
FROM RECEIPT
GROUP BY RECEIPT.SALEPER, RECEIPT.TRANSDATE, RECEIPT.FIRSTNAME,
RECEIPT.LASTNAME, RECEIPT.RECEIPTNO, RECEIPT.CATEGORY,
RECEIPT.STOCKNUM, RECEIPT.DESCS, RECEIPT.PRICE, RECEIPT.SHOWCASE,
RECEIPT.VOIDTRANS, RECEIPT.PAYMENT, RECEIPT.SLUSH
HAVING (((RECEIPT.TRANSDATE) Between [Beginning Date] And [Ending
Date]) AND ((RECEIPT.VOIDTRANS)<1) AND ((RECEIPT.PAYMENT)="3" Or
(RECEIPT.PAYMENT)="R") AND ((RECEIPT.SLUSH) Is Null)) OR
(((RECEIPT.TRANSDATE) Between [Beginning Date] And [Ending Date]) AND
((RECEIPT.PAYMENT)="3" Or (RECEIPT.PAYMENT)="R") AND
(([RECEIPT]![CATEGORY])="TRAD"));


This works fine except in a case where two adjacent records are
duplicated field for field. I'm assuming this is caused by the "Group
By" statement. In Design View, access won't let me ignore the "Group
By" feature. Is there a change I can make that will include the
duplicated records?

Thanks
Bill Lentz
 
Dear Bill:

Ignoring duplicates is exactly what your GROUP BY is supposed to do.

I think what you want is simply to not have the GROUP BY at all, just
a simple query.

In the Query Design Grid, click the SIGMA to turn off totals. That
probably should fix it right there.

Or, change the SQL:

INSERT INTO tblCommission ( EmpInit, TransDate, CusFname, CusLname,
ReceiptNo, Category, Sku, InvDesc, SellPrice, CloseOut )
SELECT RECEIPT.SALEPER, RECEIPT.TRANSDATE, RECEIPT.FIRSTNAME,
RECEIPT.LASTNAME, RECEIPT.RECEIPTNO, RECEIPT.CATEGORY,
RECEIPT.STOCKNUM, RECEIPT.DESCS, RECEIPT.PRICE, RECEIPT.SHOWCASE
FROM RECEIPT
WHERE (((RECEIPT.TRANSDATE) Between [Beginning Date] And [Ending
Date]) AND ((RECEIPT.VOIDTRANS)<1) AND ((RECEIPT.PAYMENT)="3" Or
(RECEIPT.PAYMENT)="R") AND ((RECEIPT.SLUSH) Is Null)) OR
(((RECEIPT.TRANSDATE) Between [Beginning Date] And [Ending Date]) AND
((RECEIPT.PAYMENT)="3" Or (RECEIPT.PAYMENT)="R") AND
(([RECEIPT]![CATEGORY])="TRAD"));

All I did was erase the GROUP BY and change HAVING to WHERE.

The key for me to seeing this was that I did not see where you had
used any aggregate functions.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


I've written a set of queries to pull data from a linked .dbf file
into Access tables. I'm relatively new to Access and SQL, and have
run in to the following problem:

The first query in the set pulls data for a date range (and excludes
some data based on various criteria) from the linked table and puts it
in an access table. The SQL language is:

INSERT INTO tblCommission ( EmpInit, TransDate, CusFname, CusLname,
ReceiptNo, Category, Sku, InvDesc, SellPrice, CloseOut )
SELECT RECEIPT.SALEPER, RECEIPT.TRANSDATE, RECEIPT.FIRSTNAME,
RECEIPT.LASTNAME, RECEIPT.RECEIPTNO, RECEIPT.CATEGORY,
RECEIPT.STOCKNUM, RECEIPT.DESCS, RECEIPT.PRICE, RECEIPT.SHOWCASE
FROM RECEIPT
GROUP BY RECEIPT.SALEPER, RECEIPT.TRANSDATE, RECEIPT.FIRSTNAME,
RECEIPT.LASTNAME, RECEIPT.RECEIPTNO, RECEIPT.CATEGORY,
RECEIPT.STOCKNUM, RECEIPT.DESCS, RECEIPT.PRICE, RECEIPT.SHOWCASE,
RECEIPT.VOIDTRANS, RECEIPT.PAYMENT, RECEIPT.SLUSH
HAVING (((RECEIPT.TRANSDATE) Between [Beginning Date] And [Ending
Date]) AND ((RECEIPT.VOIDTRANS)<1) AND ((RECEIPT.PAYMENT)="3" Or
(RECEIPT.PAYMENT)="R") AND ((RECEIPT.SLUSH) Is Null)) OR
(((RECEIPT.TRANSDATE) Between [Beginning Date] And [Ending Date]) AND
((RECEIPT.PAYMENT)="3" Or (RECEIPT.PAYMENT)="R") AND
(([RECEIPT]![CATEGORY])="TRAD"));


This works fine except in a case where two adjacent records are
duplicated field for field. I'm assuming this is caused by the "Group
By" statement. In Design View, access won't let me ignore the "Group
By" feature. Is there a change I can make that will include the
duplicated records?

Thanks
Bill Lentz
Click to expand...
 
Back
Top