FROM (tblUnit INNER JOIN tblReExam ON tblUnit.UnitID = tblReExam.UnitID) INNER JOIN tblHousehold ON tblReExam.ReExamID = tblHousehold.ReExamID
GROUP BY tblUnit.UnitCode, tblHousehold.MemberType;
Sent: Monday, August 14, 2017 5:05 PM
To: Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]
Subject: Re: RE: [MS_AccessPros] Count query not returning correct numbers
With Warm Regards,
Sent: Monday, August 14, 2017 3:29 PM
To: 'Graham Mandeno' graham@mandeno.com [MS_Access_Professionals]
Subject: Re: RE: [MS_AccessPros] Count query not returning correct numbers
FROM (tblUnit INNER JOIN tblReExam ON tblUnit.UnitID = tblReExam.UnitID) INNER JOIN tblHousehold ON tblReExam.ReExamID = tblHousehold.ReExamID
GROUP BY tblUnit.UnitCode;
With Warm Regards,
Hi Art
Can you please describe the relationships between your three tables? Also, what is the purpose of tblReExam?
Best wishes,
Graham
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Tuesday, 15 August 2017 06:42
To: Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Subject: Re: [MS_AccessPros] Count query not returning correct numbers
This query is still only returning the adult count for the unit. In the first record returned there is only 1 adult and 3 dependents but on the 1 adult is being returned.
With Warm Regards,
Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265 Ext. 130
Fax (605)964-1070
"Valar Dohaeris"
On Monday, August 14, 2017 12:52:47 PM, Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Art,
How do you expect to see the counts: different columns in the same row or a row for Adult and a row for Dependent?
I would expect to see MemberType in the SELECT. I would also change the HAVING clause to a WHERE clause and use IN ().
SELECT tblUnit.UnitCode, MemberType, Count(tblHousehold.HouseholdID) AS CountOfHouseholdID
FROM (tblUnit INNER JOIN tblReExam ON tblUnit.UnitID = tblReExam.UnitID) INNER JOIN tblHousehold ON tblReExam.ReExamID = tblHousehold.ReExamID
WHERE tblHousehold.MemberType IN ("1","2")
GROUP BY tblUnit.UnitCode, tblHousehold.MemberType;
If you want a column for Adult and a column for Dependent you could create a crosstab query.
Duane Hookom
From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Monday, August 14, 2017 12:17 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Count query not returning correct numbers
I have the following query:
SELECT tblUnit.UnitCode, Count(tblHousehold.HouseholdID) AS CountOfHouseholdID
FROM (tblUnit INNER JOIN tblReExam ON tblUnit.UnitID = tblReExam.UnitID) INNER JOIN tblHousehold ON tblReExam.ReExamID = tblHousehold.ReExamID
GROUP BY tblUnit.UnitCode, tblHousehold.MemberType
HAVING (((tblHousehold.MemberType)="1" Or (tblHousehold.MemberType)="2"));
What I am trying to get is a total count of household members per Unit Code. Member Type = 1=Adult and 2 = Dependent. But this query is only return the adult count per unit. What am I missing?
Thank you,
Art Lorenzini
Sioux Falls, SD
Posted by: Duane Hookom <duanehookom@hotmail.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (12) |
Tidak ada komentar:
Posting Komentar