Simple Query - At first glance

[Shawn]

Thought I would create a query that at first sounded
simple, but has given me problems. Very simple data is
involved: I have data broken down into Divisions, then
broken into Sub-divisions, and then broken down into
Gender. So if I have 10 people in Div X, Sub-division Y,
what percent is Female (designated by 1 or 2)?
I am using the QBE grid in Access XP. I do not know SQL,
so if you know how to show a percentage of a total in
Access without coding, and by using only the grid, please
let me know.
Thanks very much in advance for any help.
Shawn

 

[Chris Kennedy]

Hi Shawn,

Thank you for using the Microsoft Access Newsgroups.

Your question:
"...if I have 10 people in Div X, Sub-division Y, what percent is Female
(designated by 1 or 2)?..."

Have you tried this:

QBE Grid #1

- Fields
----------------
qryDivision: Division
qrySubdivision: Sub-division
qryGender: Gender
qryGenderCount: Gender
qryPerct: qryGenderCount/DCount ("[ID]", "[table name]", "[Division] =' " &
qryDivision & " ' [Sub-division] =' " & qrySubDivision & " ' ")

- Select the menu option View > Totals

- For the columns "qryDivision", "qrySubdivision", "qryGender" have the
following in the QBE grid

Field: qryDivisionivision qrySubdivision: Sub-division
qryGender: Gender
Total: GroupBy GroupBy
GroupBy

- For the column "qryGenderCount" have the following in the QBE grid

Field: qryGenderCount: Gender
Total: Count

- For the column "qryGenderCount" have the following in the QBE grid
Field: qryPerct: qryGenderCount/DCount ("[ID]", "[table name]",
"[Division] =' " & qryDivision & " ' [Sub-division] =' " & qrySubDivision &
" ' ")

Total: Expression


If needing to have the detail information plus the above Grouping then you
would need to use a Report based on your table.


I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support

"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."

 

[Dale Fye]

Shawn,

Strongly recommend you learn how to write SQL as well. Best method is
to build queries in the query grid, then select View-SQL View to look
at what is actually generated when you build your queries. Modify my
query by substituting the name of your table where I have entered
'yourTable'. Then copy the SQL and paste it into the SQL view.

I prefer another technique, which involves a couple of subqueries.
This will normally run faster than the previous method you received.
Using DCOUNT to get a value once is very quick. Doing it for every
record in a result set can be excruciatingly slow. Creating the
subqueries that give you the total number of people in each
subdivision and in each subdivision by gender will be quicker

SELECT T.Division
, T.SubDivision
, T.Gender
, T.GenderCount
, T.GenderCount/T1.SubTotals as GenderPct
FROM
(SELECT Division
, SubDivision
, Gender
, Count(ID) as GenderCount
FROM yourTable
GROUP BY Division
, SubDivision
, Gender) as T
INNER JOIN
(SELECT Division
, SubDivision
, Count(ID) as SubTotals
FROM yourTable
GROUP BY Division, SubDivision) as T1
ON T.Division = T1.Division
AND T.SubDivision = T1.SubDivision

--
HTH

Dale Fye


Thought I would create a query that at first sounded
simple, but has given me problems. Very simple data is
involved: I have data broken down into Divisions, then
broken into Sub-divisions, and then broken down into
Gender. So if I have 10 people in Div X, Sub-division Y,
what percent is Female (designated by 1 or 2)?
I am using the QBE grid in Access XP. I do not know SQL,
so if you know how to show a percentage of a total in
Access without coding, and by using only the grid, please
let me know.
Thanks very much in advance for any help.
Shawn