Access query HELP!

[talliaman64]

I have a table, which I simplified below, that I need to pull the Max 2 dates for each client, return the division, client, status and the Max 2 dates. I am a novice so appreciate any assistance. In the example below, I would want the underlined lines below.


DIVISION Client Frequency Status MaxOfDATE
703 ABC Weekly Active 26-Jan-16
703 ABC Weekly Active 24-Jan-16
703 ABC Weekly Active 21-Jan-16
703 ABC Weekly Active 18-Jan-16
703 ABC Weekly Active 16-Jan-16
703 ABC Weekly Active 13-Jan-16
731 DEF Weekly Active 28-Jan-16
731 DEF Weekly Active 26-Jan-16
731 DEF Weekly Active 23-Jan-16
731 DEF Weekly Active 20-Jan-16
731 DEF Weekly Active 18-Jan-16
731 DEF Weekly Active 15-Jan-16
713 HIJ Weekly Active 29-Jan-16
713 HIJ Weekly Active 27-Jan-16
713 HIJ Weekly Active 24-Jan-16
713 HIJ Weekly Active 21-Jan-16
713 HIJ Weekly Active 19-Jan-16
713 HIJ Weekly Active 16-Jan-16

[CJ_London]

you have underlined 4 rows for division 731/Client DEF - is this an error?

Assuming it is, your sql will be something like

SELECT *
FROM myTable
WHERE MaxofDate in (SELECT TOP 2 MaxofDate FROM myTable AS T WHERE Client=myTable.Client ORDER BY MaxofDate Desc)

[talliaman64]

My bad, yes, that was an error. Will give this a shot.

[talliaman64]

When I try this I get a popup to enter MaxofDate, Client, and MyTalbe.Client. My query then takes quite some time to run and returns one row
division, status, occ-class, products, and counts are blank, the date 1/25/2016 is populated.

[CJ_London]

well - you didn't tell me the name of your table and I used the field names you provided. You just need to change them to your real names. Also, it is a good idea to copy and paste the actual code you have used since you may not have copied what I provided correctly or applied it correctly.

[talliaman64]

I've done that, should have told you that. Table name and fields match the above

[CJ_London]

OK - so post your real code - I cannot debug something that I provided and works for me - suggest also you provide details of your table since the popups you are getting implies a spelling issue - or are you saying you've solved those but not getting any returned records?

[talliaman64]

SELECT *
FROM [Total Record Count]
WHERE MaxofDate in (SELECT TOP 2 MaxofDate FROM [Total Record Count] AS T WHERE Client=[Total Record Count].Client ORDER BY MaxofDate Desc);

[CJ_London]

Code looks OK to me. You said Total Record Count is a table? Roughly how many records does it have? I would ensure both Client and MaxofDate fields are indexed. That should solve your speed issue. With regards it not returning data, the query should return 2 records per client (unless of course there is only one) per the data in your initial post.

Another outside possibility is that Count is a reserved word - I know you have used square brackets but that does not always solve the issue - recommend removing the spaces from your table name.

[talliaman64]

BINGO!!! Thank you so much, greatly appreciated!

[CJ_London]

BINGO? - is that the spaces/count or adding indexing?

[talliaman64]

It was the spaces.