How to combine data with separate row count

[choochoowinn2009]

Hi,

Would someone help me write the codes for this? I am not sure how to do the row count as it looks at each row. Would I need to do a sub-report? Any suggestions would help me a lot.

Here's my data:

ID Assistance
5 Medicaid
5 Medicare
5 Food Stamps
24 Medicaid
49 Food Stamps
119 Medicare
151 Food Stamps
151 Medicare
If an ID has both Medicaid and Food Stamps, then it should be combined as Food Stamps/Medicaid, otherwise, it will only show what it has. So the results should look like this:
ID Assistance
5 Medicare
5 Food Stamps/Medicaid
24 Medicaid
49 Food Stamps
119 Medicare
151 Food Stamps
151 Medicare

Thank you so muc!

 

[SkipVought]

hi,

SELECT ID, IIF(Assistance='Medicaid' OR Assistance='Food Stamps','Food Stamps/Medicaid',Assistance) As Asst
, Count(*)
FROM [YourTable]

Group By
ID, IIF(Assistance='Medicaid' OR Assistance='Food Stamps','Food Stamps/Medicaid',Assistance)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dhookom]

I prefer to keep data in tables rather than code and expressions. You should have a table with values that group Medicaid and Food Stamps together. Without seeing your actual table structures it's difficult to suggest a solution.

I assume these types of groupings will change in the future and I don't want to go back to expressions in queries or control sources for maintenance.


Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

Yes, i notice that my solution falls short.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}