Counting mulitple groups

[imad hammad]

My database stores Facilities, the units that belong to each facility and
then the Devices that belong to each unit in that facility. Each device was
used to test patients as well.

What I have been trying to accomplish for about a week now, is a count!

The result I am looking for is basically the number of units,devices and
patients tested PER facility.

So something like this:

# Units # devices # patients tested
Facility 1 - - -
Facility 2 - - -
Facility 3 - - -

Now what is making this really difficult for me is that I do NOT want to
count duplicates. In my database only the patients would be duplicates as the
same patient is usually tested many times on a single device.

I have been using the Group By predicate to get a count per facility, and it
works fine to find the # of units or # of devices seperately but when i try
to accomplish this in one query i get the wrong count...for instance:

SELECT LOCATION.FACILITY, Count(LOCATION.UNIT) AS CountOfUNIT,
Count(COMPUTER.COMP_ID) AS CountOfCOMP_ID
FROM LOCATION INNER JOIN COMPUTER ON LOCATION.LOC_ID = COMPUTER.LOC_ID
GROUP BY LOCATION.FACILITY;

is returning the wrong answer.

I was hoping i could solve this problem first, and then worry about counting
the patients per device as I can tell that will be even more difficult.





Some more information about my tables

Location Table Device Table
Loc_ID -----| Comp_ID
Facility |-------Loc_ID
Building Type
Unit Ram
Floor IP_Address
Zone

 

[MGFoster]

My database stores Facilities, the units that belong to each facility and
then the Devices that belong to each unit in that facility. Each device was
used to test patients as well.

What I have been trying to accomplish for about a week now, is a count!

The result I am looking for is basically the number of units,devices and
patients tested PER facility.

So something like this:

# Units # devices # patients tested
Facility 1 - - -
Facility 2 - - -
Facility 3 - - -

Now what is making this really difficult for me is that I do NOT want to
count duplicates. In my database only the patients would be duplicates as the
same patient is usually tested many times on a single device.

I have been using the Group By predicate to get a count per facility, and it
works fine to find the # of units or # of devices seperately but when i try
to accomplish this in one query i get the wrong count...for instance:

SELECT LOCATION.FACILITY, Count(LOCATION.UNIT) AS CountOfUNIT,
Count(COMPUTER.COMP_ID) AS CountOfCOMP_ID
FROM LOCATION INNER JOIN COMPUTER ON LOCATION.LOC_ID = COMPUTER.LOC_ID
GROUP BY LOCATION.FACILITY;

is returning the wrong answer.

I was hoping i could solve this problem first, and then worry about counting
the patients per device as I can tell that will be even more difficult.





Some more information about my tables

Location Table Device Table
Loc_ID -----| Comp_ID
Facility |-------Loc_ID
Building Type
Unit Ram
Floor IP_Address
Zone

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

What you want is the COUNT(DISTINCT <column>) function. That function
is not in Access. You have to individually count the values in the
columns like this (just a suggestion, there are probably other ways to
do this):

SELECT L.Facility,
Count((SELECT DISTINCT Unit FROM Location WHERE L1.LOC_ID = L.Loc_ID AND
Facility=L.Facility)) As UnitCount,
Count((SELECT DISTINCT C.Comp_ID FROM Location As L1 INNER JOIN Computer
As C ON L1.Loc_id = C.Loc_ID WHERE L1.LOC_ID = L.Loc_ID AND
L1.Facility=L.Facility)) As Comp_IDCount
FROM Location As L
GROUP BY L.Facility

Make sure Location.Facility is indexed.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQwzU/4echKqOuFEgEQI6VQCgukJt0VJtWwMN2A0d7bvo5mrOSv8An0S/
ue/Skx3FII5RMejos6LPrg/0
=h+sh
-----END PGP SIGNATURE-----

 

[imad hammad]

Thank you for your fast response. I have tried the query you left me and it
is throwing some errors that I can't solve.

First I got : LOC_ID is not part of an aggregate function. So I included it
as part of the Group by.

Then i would get : enter parameter value for L1.LOC_ID. I believe this is
because the select in the first count is disjoined from that in the second
count and therefore you cannot reference the L1 alias.

I don't understand what the query is attempting to accomplish and i,
therefore, was not able to trouble shoot it further. I gather Count
(distinct.....) is to remove the problem of counting duplicates, but
currently i can't even get a normal count to count properly.

Any feedback is appreciated, thank you.

 

[imad hammad]

In my most recent reply i said the Count (distinct....); ignore that I meant
to say Count( Select distinct...) as you have pointed out the Count
(distinct....) is not present in Access, and i see why you need a distinct in
there to get the proper count. So if we can just get the query you wrote to
run without the errors i think i'll be in good shape

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Sorry for the errors. I just typed it in off the top of my head. Try
this:

SELECT L.Facility,

Count((SELECT DISTINCT Unit
FROM Location
WHERE LOC_ID = L.Loc_ID AND Facility=L.Facility)) As UnitCount,

Count((SELECT DISTINCT C.Comp_ID
FROM Location As L1 INNER JOIN Computer As C
ON L1.Loc_id = C.Loc_ID
WHERE L1.LOC_ID = L.Loc_ID AND L1.Facility=L.Facility))
As Comp_IDCount

FROM Location As L

GROUP BY L.Facility

Each SELECT statement retrieves the DISTINCT column values "Unit" and
"Comp_ID." These are correlated subqueries. IOW, the Unit subquery
gets each distinct Unit for the main query's current Loc_ID and
Facility. The Comp_ID subquery does the same, but for Comp_IDs.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQwzkpIechKqOuFEgEQJ4zQCgtq58QW8ZLM6I850Od9yOD4Qd+NAAoN/H
tRKk4enNCPCinmZneB9vEalX
=tC7x
-----END PGP SIGNATURE-----

 

[imad hammad]

This now makes perfect sense to me! It is unfortunately still not working
though and i can't understand why.

I get the error message: query does not include the specified 'LOC_ID'
expression as part of an aggregate function.

From my experience this is a problem with the Group BY, so I added LOC_ID to
the group by clause but then I get the error "At most one record can be
returned by this subquery"


Why is this happening, especially since the select statment doesn't select
LOC_ID!

Thank you very much

 

[imad hammad]

After playing around with it a little bit i figured it out!

This is working just fine:

SELECT L.Facility,

(SELECT DISTINCT Count(Unit)
FROM Location
WHERE Facility=L.Facility) As UnitCount,

(SELECT DISTINCT Count(C.Comp_ID)
FROM Location As L1 INNER JOIN Computer As C
ON L1.Loc_id = C.Loc_ID
WHERE L1.Facility=L.Facility) As Comp_IDCount

FROM Location As L

GROUP BY L.Facility;


thank you for showing me the light MGFoster! My task will get a lot more
complicated very shortly but hopefully with this new technique i'll be able
to power through. Thank you soo very much for your help!

 

[imad hammad]

As you can already tell i tend to speak to soon!

The query i wrote will count duplicate values......So I guess i need the
query you wrote in your second reply to function properly to make this work.

Basically my second step is to count the number of patients per facility,
but the way my tables are linked:

Location Computer Run_ID Patient (TABLES)
Loc_ID----| Comp_ID----| Run_ID |--PAT_ID
Facility |- Loc_ID |-- Comp_ID | PIN
Building Type PAT_ID----| FIRST_NAME

is making this difficult for me to do with the query i wrote because i can't
identify UNIQUE patients per facility (my way).

I think if the query you wrote works then this should not be too difficult.

I apologies for my "spam" and will try to cut down on my posts

 

[MGFoster]

As you can already tell i tend to speak to soon!

The query i wrote will count duplicate values......So I guess i need the
query you wrote in your second reply to function properly to make this work.

Basically my second step is to count the number of patients per facility,
but the way my tables are linked:

Location Computer Run_ID Patient (TABLES)
Loc_ID----| Comp_ID----| Run_ID |--PAT_ID
Facility |- Loc_ID |-- Comp_ID | PIN
Building Type PAT_ID----| FIRST_NAME

is making this difficult for me to do with the query i wrote because i can't
identify UNIQUE patients per facility (my way).

I think if the query you wrote works then this should not be too difficult.

I apologies for my "spam" and will try to cut down on my posts

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Don't know if this will work. It's always a PITA to get Count Distinct
to work in Access, but, let's try this:

SELECT L.Facility, Count(F.Unit) As UnitCount,
Count(C.Comp_ID) As Comp_IDCount

FROM (Location As L
INNER JOIN (SELECT Loc_ID, Facility, Unit
FROM Location
GROUP BY Loc_ID, Facility, Unit) As F
ON L.Loc_ID = F.Loc_ID AND L.Facility = F.Facility)
INNER JOIN (SELECT L1.Loc_ID, L1.Facility, C1.Comp_ID
FROM Location As L1 INNER JOIN Computer As C1
ON L1.Loc_id = C1.Loc_ID) As C
ON L.Loc_ID = C.Loc_ID AND L.Facility = C.Facility

GROUP BY L.Facility

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQw02qIechKqOuFEgEQLMbgCggAxizrmWqF+oPHj4LoolCNDsCVcAn1yb
09CpNozD7lQfezkr9u082B2W
=RJP/
-----END PGP SIGNATURE-----