Returning the first result in a query

  • Thread starter Thread starter R.Gunn
  • Start date Start date
R

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
 
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
 
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

Michel Walsh said:
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 said:
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
Click to expand...
Click to expand...
 
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


R.Gunn said:
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

Michel Walsh said:
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 said:
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
Click to expand...
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
Click to expand...
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
Click to expand...
Click to expand...
problem?
I
Click to expand...
Click to expand...
 
Back
Top