Returning the first result in a query

[R.Gunn]

Hi.

I am having trouble returning just 1 result from each item in a query.

The query is based on a few tables. One of which contains many rows of data
which is in a sense duplicated.

E.g.
Destination Product Qty
0001 A 5
0001 B 5
0001 C 5
0001 D 5
0001 E 5
0001 F 5
0002 A 5
0002 B 5
0002 C 5
0002 D 5
0002 E 5
0002 F 5
etc..

I wish to use this query to produce box labels. I end up with 6 returns for
"0001" and the same for "0002" etc... as the destination number is repeated.

How can I fix it so I just get the "first" result for each destination
number please?

I am not interested in using the Qty or even Product field on these labels.

I have tried using "FIRST([tblpickinglist].[Destination]" but I got an error
back:

Cannot have aggregate function in WHERE clause
(tblpickinglist.Destination=First([tblpickinglist].[Destination]) And
tblpickinglist.type like [Which format do you require?] & "*").

This means nothing to me

Removal of the prompting field produces the same error except does not
mention it any more:
Cannot have aggregate function in WHERE clause
(tblpickinglist.Destination=First([tblpickinglist].[Destination]).

Can somebody please point me the right way or assist with this problem? I
have 2 days left to complete the job and it's driving me mad!

Richard

 

[Michel Walsh]

Hi,


You don't supply the whole statement, but generally, if you are not
interested in the later two fields, you can try:



SELECT DISTINCT Destination
FROM ....
WHERE ...


Hoping it may help,
Vanderghast, Access MVP

 

[R.Gunn]

Michel,

Many thanks for the reply.

Unfortunately that does not appear to have worked. Heres my current
complete SQL statement:

SELECT DISTINCT tblPackingList.CentreNo, [tblRegions].[RegionNo] &
[tblDistricts].[DistrictNo] & [tblPackingList].[CentreNo] AS RDC,
tblRegions.RegionNo, tblRegions.Region, tblDistricts.DistrictNo,
tblDistricts.DistrictName, tblPackingList.CentreNo,
tblPackingList.CentreName, tblRegions.Label, tblPackingList.PaperCode,
tblRegions.RegionFull, tblPackingList.Type
FROM tblPackingList LEFT JOIN (tblRegions RIGHT JOIN tblDistricts ON
tblRegions.RegionNo = tblDistricts.Region) ON tblPackingList.CentreNo =
tblDistricts.CentreNo;

I was hoping the above would return just one 'PaperCode' for each 'CentreNo'
but it doesn't - it still returns all values.

Any more help would be much appreciated!

Thank you.
Richard

Hi,


You don't supply the whole statement, but generally, if you are not
interested in the later two fields, you can try:



SELECT DISTINCT Destination
FROM ....
WHERE ...


Hoping it may help,
Vanderghast, Access MVP

Hi.

I am having trouble returning just 1 result from each item in a query.

The query is based on a few tables. One of which contains many rows of data
which is in a sense duplicated.

E.g.
Destination Product Qty
0001 A 5
0001 B 5
0001 C 5
0001 D 5
0001 E 5
0001 F 5
0002 A 5
0002 B 5
0002 C 5
0002 D 5
0002 E 5
0002 F 5
etc..

I wish to use this query to produce box labels. I end up with 6 returns for
"0001" and the same for "0002" etc... as the destination number is repeated.

How can I fix it so I just get the "first" result for each destination
number please?

I am not interested in using the Qty or even Product field on these labels.

I have tried using "FIRST([tblpickinglist].[Destination]" but I got an error
back:

Cannot have aggregate function in WHERE clause
(tblpickinglist.Destination=First([tblpickinglist].[Destination]) And
tblpickinglist.type like [Which format do you require?] & "*").

This means nothing to me

Removal of the prompting field produces the same error except does not
mention it any more:
Cannot have aggregate function in WHERE clause
(tblpickinglist.Destination=First([tblpickinglist].[Destination]).

Can somebody please point me the right way or assist with this problem? I
have 2 days left to complete the job and it's driving me mad!

Richard

 

[Michel Walsh]

Hi,


Indeed, DISTINCT operates one the whole selection (, all the selected
fields, horizontally). You can try:


SELECT DISTINCT tblPackingList.CentreNo,
LAST( [tblRegions].[RegionNo] & [tblDistricts].[DistrictNo] &
[tblPackingList].[CentreNo] ) AS RDC,
LAST( tblRegions.RegionNo),
LAST(tblRegions.Region),
LAST(tblDistricts.DistrictNo),
LAST( tblDistricts.DistrictName),
LAST(tblPackingList.CentreNo),
LAST( tblPackingList.CentreName),
LAST( tblRegions.Label),
LAST(tblPackingList.PaperCode),
LAST( tblRegions.RegionFull),
LAST(tblPackingList.Type)

FROM tblPackingList LEFT JOIN (tblRegions RIGHT JOIN tblDistricts ON
tblRegions.RegionNo = tblDistricts.Region) ON tblPackingList.CentreNo =
tblDistricts.CentreNo

GROUP BY tblPackingList.CentreNo




Hoping it may help,
Vanderghast, Access MVP

Michel,

Many thanks for the reply.

Unfortunately that does not appear to have worked. Heres my current
complete SQL statement:

SELECT DISTINCT tblPackingList.CentreNo, [tblRegions].[RegionNo] &
[tblDistricts].[DistrictNo] & [tblPackingList].[CentreNo] AS RDC,
tblRegions.RegionNo, tblRegions.Region, tblDistricts.DistrictNo,
tblDistricts.DistrictName, tblPackingList.CentreNo,
tblPackingList.CentreName, tblRegions.Label, tblPackingList.PaperCode,
tblRegions.RegionFull, tblPackingList.Type
FROM tblPackingList LEFT JOIN (tblRegions RIGHT JOIN tblDistricts ON
tblRegions.RegionNo = tblDistricts.Region) ON tblPackingList.CentreNo =
tblDistricts.CentreNo;

I was hoping the above would return just one 'PaperCode' for each 'CentreNo'
but it doesn't - it still returns all values.

Any more help would be much appreciated!

Thank you.
Richard

Hi,


You don't supply the whole statement, but generally, if you are not
interested in the later two fields, you can try:



SELECT DISTINCT Destination
FROM ....
WHERE ...


Hoping it may help,
Vanderghast, Access MVP

Hi.

I am having trouble returning just 1 result from each item in a query.

The query is based on a few tables. One of which contains many rows

of
data

which is in a sense duplicated.

E.g.
Destination Product Qty
0001 A 5
0001 B 5
0001 C 5
0001 D 5
0001 E 5
0001 F 5
0002 A 5
0002 B 5
0002 C 5
0002 D 5
0002 E 5
0002 F 5
etc..

I wish to use this query to produce box labels. I end up with 6

returns
for

"0001" and the same for "0002" etc... as the destination number is repeated.

How can I fix it so I just get the "first" result for each destination
number please?

I am not interested in using the Qty or even Product field on these labels.

I have tried using "FIRST([tblpickinglist].[Destination]" but I got an error
back:

Cannot have aggregate function in WHERE clause
(tblpickinglist.Destination=First([tblpickinglist].[Destination]) And
tblpickinglist.type like [Which format do you require?] & "*").

This means nothing to me

Removal of the prompting field produces the same error except does not
mention it any more:
Cannot have aggregate function in WHERE clause
(tblpickinglist.Destination=First([tblpickinglist].[Destination]).

Can somebody please point me the right way or assist with this

problem?

I