Total Query to Group & Count Some Records Twice (1 Viewer)

[julia55]

I know this is something that is probably pretty simple, but I am still new to Access and can't figure it out.

I am trying to create a totals query that simply groups all the unit types into 2 categories (FL & TR) and gives me a total count for each type. However, the column that contains the unit type data will sometimes have both unit types input into one record, and I need that number to count for both totals. I tried using an IIF function, but that of course only allowed the total to count towards one of my unit types not both. I included a scalled down version of the DB and the query I started working with that doesn't work. Any help is greatly appreciated!

 

[plog]

You need to restructure your data source so that distinct data is stored distinctly. You should not have records that have 'TR & FL' as a value.

For the mean time, the easy solution just to get a value would be to run this query:

SELECT [Unit Type], COUNT([Unit Type]) AS UnitTypeTotal
FROM [Copy of Chronic Units]
GROUP BY [Unit Type];

And manualy add the 'TR & FL' values to both distinict types.

 

[julia55]

Thanks! Wish I could restructure the data, but it comes from a SharePoint list, so I'm just stuck with what I have, was hoping I could find some way to automate around manually adding the numbers together.