Do I use Concatenate? help!

[bob1]

I'm looking for some help with creating a query.

The fields are

user, case, location, units

I need a total count of locations, but whenever if the fields case and
location are the same multiple times, I need for it to count as one. But I
need the total sum of units. ex.


User Case Location Units

bob 01 a1 1
bob 01 a1 1
bob 01 a1 1
bob 01 a2 1

The results would be

User location units
bob 2 4

Location would show 2 because the units came from two different locations.

Thank you!

 

[Allen Browne]

So you want the number of distinct locations for each User + Case
combination, and also the number of distinct Location + Unit combinations
for each User + Case.

This query generates a record for each combination of User + Case +
Location:
SELECT DISTINCT [User], Case, Location
FROM Table1;
Substitute your table name, and save as (say) qryUserCaseLocation.

This query generates a record for each combination of User + Case + Location
+ Unit:
SELECT DISTINCT [User], Case, Location, Units
FROM Table1;
Save as (say) qryUserCaseLocationUnits.

You can then create a query that groups by User and Case, and counts the
locations:
SELECT [User], Case, Count([Location]) AS CountOfLocation,
FROM qryUserCaseLocation
GROUP BY [User], Case;

Now you want to modify that query so it also gets the count of units too, so
perhaps something like this:
SELECT [User], Case, Count([Location]) AS CountOfLocation,
(SELECT Count(Units) AS CountOfUnits
FROM qryUserCaseLocationUnits
WHERE qryUserCaseLocationUnits.[User] = qryUserCaseLocation.[User]
AND qryUserCaseLocationUnits.Case = qryUserCaseLocation.Case)
AS CountOfLocationUnits
FROM qryUserCaseLocation
GROUP BY [User], Case;

The final query uses a subquery. If that's a new concept, see:
http://allenbrowne.com/subquery-01.html

Note that USER is a reserved word, so not a good choice for a field name.
When designing your tables, you might want to refer to this list to avoid
problem names:
http://allenbrowne.com/AppIssueBadWord.html