By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,222 Members | 1,123 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,222 IT Pros & Developers. It's quick & easy.

Problems with MAX and Group By

P: n/a
Hi,
i need to create a query on Access 2000 for select some records.
Here is an example with records inserted on my two tables.

TABLE ACQ:

ID ARTICLE PERIOD DATE QTY RESERVE
1 0001 R02-1 13/01/2003 3 5
2 0001 SOO1-A 11/02/2003 6 5
3 0002 33D4-R 07/03/2003 8 10
4 0001 001-3A 15/03/2003 1 5
5 0001 ZY9-K 25/03/2003 7 5
6 0002 R99-1 30/04/2003 10 10
7 0002 ZZ11I-A1 01/05/2003 9 10
8 0002 ZZY9 19/05/2003 13 10
TABLE ART:

CODE DESCRIPTION OBS
0001 ARTICLE0001 N
0002 ARTICLE0002 N
I NEED THIS RESULT:
ALL ARTICLES (WITH DESCRIPTION) FROM TABLE ACQ (NOT DUPLICATED),
WITH MAX ACQ.ID AND MAX ACQ.DATE,
ACQ.PERIOD (NOT MAX),
WHERE ACQ.QTY<ACQ.RESERVE AND ART.OBS="N"
THE RESULT OF THE EXAMPLE MUST BE THIS:
ID ARTICLE DESCRIPTION PERIOD DATE QTY RESERVE
4 0001 ARTICLE0001 001-3A 15/03/2003 1 5
7 0002 ARTICLE0002 ZZ11I-A1 01/05/2003 9 10
I have created this query, but i have a result with duplicated
articles, because it consider the ACQ.PERIOD:
SELECT Max(ACQ.ID) AS MaxDiID, Max(ACQ.DATE), ACQ.ARTICLE, ACQ.PERIOD,
ACQ.QTY, ART.DESCRIPTION, ART.OBS
FROM ACQ LEFT JOIN ART ON ACQ.ARTICLE = ART.CODE
GROUP BY ACQ.ARTICLE, ACQ.PERIOD, ACQ.QTY, ART.DESCRIPTION, ART.OBS
HAVING (ACQ.QTY<ACQ.RESERVE) AND (ART.OBS="N")

Can you tell me how can i modify my query, please ?

Thanks,
Elvis.
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
el*****@libero.it (Elvis) wrote in message news:<26*************************@posting.google.c om>...
Hi,
i need to create a query on Access 2000 for select some records.
Here is an example with records inserted on my two tables.
<Snip well described problem>
Can you tell me how can i modify my query, please ?

Thanks,
Elvis.


My first reaction would be to base one query on another along these lines:

qry1
~~~~
SELECT Max(ACQ.ID) AS MaxOfID, ACQ.ARTICLE, ART.DESCRIPTION
FROM ACQ INNER JOIN ART ON ACQ.ARTICLE = ART.CODE
WHERE (((ART.OBS)="N") AND ((ACQ.QTY)<[ACQ].[RESERVE]))
GROUP BY ACQ.ARTICLE, ART.DESCRIPTION;
Next, create qry2 and use qry1 as the basis for it like this:

qry2
~~~~
SELECT ACQ.ID, ACQ.ARTICLE, ACQ.PERIOD, ACQ.DATE, ACQ.QTY, ACQ.RESERVE
FROM qry1 INNER JOIN ACQ ON qry1.MaxOfID = ACQ.ID;

~~~~~~~~~~~~~~~~~~~~~~~~
Hope that helps
Regards
Stuart
Nov 12 '05 #2

P: n/a
Try this simple solution, include the DISTINCT keyword after select

select DISTINCT max (acq.id) ...

and see whether it returns the result you want.

"Elvis" <el*****@libero.it> wrote in message
news:26*************************@posting.google.co m...
Hi,
i need to create a query on Access 2000 for select some records.
Here is an example with records inserted on my two tables.

TABLE ACQ:

ID ARTICLE PERIOD DATE QTY RESERVE
1 0001 R02-1 13/01/2003 3 5
2 0001 SOO1-A 11/02/2003 6 5
3 0002 33D4-R 07/03/2003 8 10
4 0001 001-3A 15/03/2003 1 5
5 0001 ZY9-K 25/03/2003 7 5
6 0002 R99-1 30/04/2003 10 10
7 0002 ZZ11I-A1 01/05/2003 9 10
8 0002 ZZY9 19/05/2003 13 10
TABLE ART:

CODE DESCRIPTION OBS
0001 ARTICLE0001 N
0002 ARTICLE0002 N
I NEED THIS RESULT:
ALL ARTICLES (WITH DESCRIPTION) FROM TABLE ACQ (NOT DUPLICATED),
WITH MAX ACQ.ID AND MAX ACQ.DATE,
ACQ.PERIOD (NOT MAX),
WHERE ACQ.QTY<ACQ.RESERVE AND ART.OBS="N"
THE RESULT OF THE EXAMPLE MUST BE THIS:
ID ARTICLE DESCRIPTION PERIOD DATE QTY RESERVE
4 0001 ARTICLE0001 001-3A 15/03/2003 1 5
7 0002 ARTICLE0002 ZZ11I-A1 01/05/2003 9 10
I have created this query, but i have a result with duplicated
articles, because it consider the ACQ.PERIOD:
SELECT Max(ACQ.ID) AS MaxDiID, Max(ACQ.DATE), ACQ.ARTICLE, ACQ.PERIOD,
ACQ.QTY, ART.DESCRIPTION, ART.OBS
FROM ACQ LEFT JOIN ART ON ACQ.ARTICLE = ART.CODE
GROUP BY ACQ.ARTICLE, ACQ.PERIOD, ACQ.QTY, ART.DESCRIPTION, ART.OBS
HAVING (ACQ.QTY<ACQ.RESERVE) AND (ART.OBS="N")

Can you tell me how can i modify my query, please ?

Thanks,
Elvis.

Nov 12 '05 #3

P: n/a
If i include the distinct keyword, i have always a result with
duplicated articles, one for every field ACQ.PERIOD (see group by).

Regards,
Elvis.
"Tony" <ri*********@hotmail.com> wrote in message news:<3f******@news.comindico.com.au>...
Try this simple solution, include the DISTINCT keyword after select

select DISTINCT max (acq.id) ...

and see whether it returns the result you want.

"Elvis" <el*****@libero.it> wrote in message
news:26*************************@posting.google.co m...
Hi,
i need to create a query on Access 2000 for select some records.
Here is an example with records inserted on my two tables.

TABLE ACQ:

ID ARTICLE PERIOD DATE QTY RESERVE
1 0001 R02-1 13/01/2003 3 5
2 0001 SOO1-A 11/02/2003 6 5
3 0002 33D4-R 07/03/2003 8 10
4 0001 001-3A 15/03/2003 1 5
5 0001 ZY9-K 25/03/2003 7 5
6 0002 R99-1 30/04/2003 10 10
7 0002 ZZ11I-A1 01/05/2003 9 10
8 0002 ZZY9 19/05/2003 13 10
TABLE ART:

CODE DESCRIPTION OBS
0001 ARTICLE0001 N
0002 ARTICLE0002 N
I NEED THIS RESULT:
ALL ARTICLES (WITH DESCRIPTION) FROM TABLE ACQ (NOT DUPLICATED),
WITH MAX ACQ.ID AND MAX ACQ.DATE,
ACQ.PERIOD (NOT MAX),
WHERE ACQ.QTY<ACQ.RESERVE AND ART.OBS="N"
THE RESULT OF THE EXAMPLE MUST BE THIS:
ID ARTICLE DESCRIPTION PERIOD DATE QTY RESERVE
4 0001 ARTICLE0001 001-3A 15/03/2003 1 5
7 0002 ARTICLE0002 ZZ11I-A1 01/05/2003 9 10
I have created this query, but i have a result with duplicated
articles, because it consider the ACQ.PERIOD:
SELECT Max(ACQ.ID) AS MaxDiID, Max(ACQ.DATE), ACQ.ARTICLE, ACQ.PERIOD,
ACQ.QTY, ART.DESCRIPTION, ART.OBS
FROM ACQ LEFT JOIN ART ON ACQ.ARTICLE = ART.CODE
GROUP BY ACQ.ARTICLE, ACQ.PERIOD, ACQ.QTY, ART.DESCRIPTION, ART.OBS
HAVING (ACQ.QTY<ACQ.RESERVE) AND (ART.OBS="N")

Can you tell me how can i modify my query, please ?

Thanks,
Elvis.

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.