Excel/Access Question 1

[CCPKGUY]

I have a query that I wrote in Access and it givs me the following:

ACCOUNT_NO OCCUPANT_CODE NAME
10011250 12 JACQUELINE W TEST1
10011250 11 JAQUITA TEST2
10011250 10 LALISA A TEST3


Is there a way to query to only show the highest Occupant Code by Account No?

 

[Andrzejek]

Out of what you showed:

[tt]Select [blue]TOP 1[/blue] ...[/tt]

Have fun.

---- Andy

 

[CCPKGUY]

Select TOP 1?? Do I do that from my query in Access?

 

[Andrzejek]

The best way to know for sure is to.... try it there

Have fun.

---- Andy

 

[combo]

If names are unique for a pair (Occupant_Code, Account_No), choose maximum as an aggregation function for Occupant_Code field.
If no,create a subquery with two fields: Occupant_Code and Account_No, again with aggregation with maximum for Occupant_Code. Next add this subquery to your original query with links of subsequent fields.

combo

 

[CCPKGUY]

This is my SQL View

SELECT [CCPK SWU REPORT].account_no, [CCPK SWU REPORT].account_num, [CCPK SWU REPORT].SFU, [CCPK SWU REPORT].MaxOfoccupant_code, [CCPK SWU REPORT].name, [CCPK SWU REPORT].serv_street_no, [CCPK SWU REPORT].serv_street, [CCPK SWU REPORT].serv_region, [CCPK SWU REPORT].serv_city, [CCPK SWU REPORT].serv_province, [CCPK SWU REPORT].serv_postal_zip, [CCPK SWU REPORT].utility_type, [CCPK SWU REPORT].no_units, [CCPK SWU REPORT].bill_code, [CCPK SWU REPORT].rate_code, [CCPK SWU REPORT].descript, [CCPK SWU REPORT].Rate, [CCPK SWU REPORT].effective_date, [CCPK SWU REPORT].end_effective_date
FROM [CCPK SWU REPORT];


I want to select the top occupant code (Maxofoccupant_code) per account number (account_no)

 

[combo]

You have a plain input table.
Create a subquery first. select two fields: account_no and MaxOfoccupant_code. Select maximum as aggregation function for the second field. The sql for this subquery:
SELECT [account_no], Max([Maxofoccupant_code]) AS occupant_code
FROM [CCPK SWU REPORT]
GROUP BY [account_no];

Save this query. It will return account_no with maximal MaxOfoccupant_code for each account_no.

Next, in design mode, add this query to your original query design. Link fields between tables (drag one onto another): [account_no] with [account_no] and [occupant_code] with [Maxofoccupant_code]. Test.

combo

 

[CCPKGUY]

Thanks Combo...that worked just fine.