syntax for query to bring only one result per AccountNo

[Susan]

query1 brings the following results:

AccountNo DocID CompanyName
1 5 Salvation Army
1 5 Salvation Army
1 6 Salvation Army
2 7 United Church
2 8 United Church

query2 will be selecting all fields from query1 but limited to only one
result per AccountNo - The AccountNo listing with the highest DocID value.

I would like query2 to bring results as follows:

AccountNo DocID CompanyName
1 6 Salvation Army
2 8 United Church

What would the syntax for this sql query be?

thanks!

 

[KARL DEWEY]

Try this ---
SELECT Query1.AccountNo, Query1.CompanyName, Max(Query1.DocID) AS MaxOfDocID
FROM Query1
GROUP BY Query1.AccountNo, Query1.CompanyName;

 

[Susan]

this syntax works fine to eliminate duplicates for the AccountNo field. With
the inclusion of more fields though (such as phonenumber etc), the query is
resulting in more than one listing of a given AccountNo which is what I need
to avoid.

 

[Susan]

And what if I add another field as follows:

AccountNo DocID CompanyName PostalCode
1 5 Salvation Army 12345
1 5 Salvation Army 12345
1 6 Salvation Army 12345
2 7 United Church 24567
2 8 United Church 24567

 

[KARL DEWEY]

SELECT Query1.AccountNo, Query1.CompanyName, Query1.PostalCode,
Max(Query1.DocID) AS MaxOfDocID
FROM Query1
GROUP BY Query1.AccountNo, Query1.CompanyName, Query1.PostalCode;

 

[Susan]

You're right but I phrased my question wrong. I now realized why I have still
been having dup results per AccountNo. The added field that causes the
trouble is the PartNumber because there are different values per row versus
the PostalCode which contains the same value all accross a given AccountNo:

AccountNo DocID CompanyName PartNumber
1 5 Salvation Army abc
1 5 Salvation Army efg
1 6 Salvation Army abc
2 7 United Church ghi
2 8 United Church fgh

I need to have just one result per AccountNo but include the corresponding
PartNumber in the query.

 

[John Spencer]

So if you have three records with the same maximum docid which one or ones
do you want returned? If your record set was like
1 4 Salvation Army abc
1 5 Salvation Army abc
1 5 Salvation Army efg
1 5 Salvation Army ghi
2 7 United Church ghi
2 8 United Church fgh

Do you want three records returned for Account No 1?
Or just one record? If just one record, which one and how do you identify
the one?

If an arbitrary one, then you could just use FIRST for those fields where it
doesn't make any difference

SELECT Query1.AccountNo
, Max(Query1.DocID) AS MaxOfDocID
, Query1.CompanyName
, First(Query1.PartNumber) as PartNum
FROM Query1
GROUP BY Query1.AccountNo, Query1.CompanyName;

That would return
1 5 Salvation Army xxx <<< this could be an any one
of the part numbers
2 8 United Church fgh
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Susan]

Thank you all for your assistance in aiding me to create my queries
successfully. I now run accross problems when pulling my individual queries
together to create a UNION query.

My goal with the following query is to form a union of all fax numbers from
3 different sources while avoiding any duplicate fax number listings.

In the first and second portions of the query, separated by UNION, when
there are duplicates of a given fax number, I need the one with the min ID to
show. All other fields don't matter which results are displayed (I have used
First by some fields in an attempt to create distinct records when there are
duplicates in fields other than the fax number field).

In the last portion of the query, separated by another UNION, there are no
duplicate fax numbers so an aggregate function is not necessary.

Each individual query does not result in duplicates. This issue I face is
that the UNION is not avoiding duplicate entries from one portion of the
UNION query to another.

I understand that recordset variances created with the inclusion of fields
other than the fax number fields, will cause duplicate listing. This is
because 2 records will no longer be identical as it would be if only the fax
numbers were selected. However, when viewing the UNION query results, I
notice a duplication of 2 records that are identical throughout all fields in
the record except for the ID field.

(SELECT DocumentHeaders.SoldToFax,First( DocumentHeaders.SoldToCompany) As
FCompany, First(DocumentHeaders.DataSource) As FDataSource
,DocumentHeaders.Remove, First(DocumentHeaders.catagoryID) As FCatID,
Min(DocumentHeaders.ID) As MID
FROM DocumentHeaders
GROUP BY DocumentHeaders.SoldToFax, DocumentHeaders.Remove
HAVING (((DocumentHeaders.SoldToFax)<>""))
UNION
SELECT DocumentHeaders.ShipToFax, First(DocumentHeaders.ShipToCompany) As
FCompany, First(DocumentHeaders.DataSource) As FDataSource,
DocumentHeaders.Remove, First( DocumentHeaders.catagoryID) As FCatID,
Min(DocumentHeaders.ID) As MID
FROM DocumentHeaders
GROUP BY DocumentHeaders.ShipToFax, DocumentHeaders.Remove
HAVING (((DocumentHeaders.ShipToFax)<>"")))
UNION (SELECT faxnumbers.SoldToFax, faxnumbers.MSoldToCompany,
faxnumbers.DataSource, faxnumbers.Remove, faxnumbers.CatagoryID, faxnumbers.ID
FROM faxnumbers);

 

[John Spencer]

The two ways I can see to handle this are to
DROP the ID field from the queries making up the union query
OR
Use the union query as the source for another totals query.

SELECT SoldToFax, FCompany, FDataSource
,.Remove, FCatID, Min(MID) as TheID
FROM TheUnionQuery
GROUP BY SoldToFax, FCompany
, FDataSource,.Remove

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..