UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Getting Counts Of A Particular Field, Access 2013    
 
   
msnarayanan
post Dec 3 2019, 08:01 AM
Post#1



Posts: 282
Joined: 28-May 13



dear forum members

it is a quite long time since i posted queries.

this time i want your help in solving the following problem:

i have created a questionnair for the development of my native village. the questionnair includes fields like gender, age group, nature of job, educational qualifications, etc.

i want to consolidate the questionnair - total number of females - based on age groups, age, etc.

should i create query for each criteria? how to arrive at total counts based on multiple fields.

your suggestion will be greatly appreciated.

m s narayanan
Go to the top of the page
 
Jeff B.
post Dec 3 2019, 08:05 AM
Post#2


UtterAccess VIP
Posts: 10,336
Joined: 30-April 10
From: Pacific NorthWet


How will depend on what -- what data structure are you starting with? That isn't the questionnaire, but the underlying table structure.

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
cheekybuddha
post Dec 3 2019, 08:06 AM
Post#3


UtterAccess Moderator
Posts: 11,906
Joined: 6-December 03
From: Telegraph Hill


I don't think you necessarily need to create separate queries.

Post your table structure(s) and outline more explicitly what data you wish to see.

d

--------------------


Regards,

David Marten
Go to the top of the page
 
projecttoday
post Dec 3 2019, 08:11 AM
Post#4


UtterAccess VIP
Posts: 11,283
Joined: 10-February 04
From: South Charleston, WV


You do need separate queries if you want to know the result by gender and age group and also by gender and age. You might also want the result only by gender. Or only by age. Or only by age group.

Better to make a pivot table or a report so as to avoid every possible combination of demographics.

--------------------
Robert Crouser
Go to the top of the page
 
cheekybuddha
post Dec 3 2019, 08:45 AM
Post#5


UtterAccess Moderator
Posts: 11,906
Joined: 6-December 03
From: Telegraph Hill


>> You do need separate queries if you want to know the result by gender and age group and also by gender and age. <<

I don't think that's true, but it all depends on the tables and their structure

--------------------


Regards,

David Marten
Go to the top of the page
 
projecttoday
post Dec 3 2019, 09:10 AM
Post#6


UtterAccess VIP
Posts: 11,283
Joined: 10-February 04
From: South Charleston, WV


Well, if we're doing aggregate queries then it's going to be

... GROUP BY GENDER, AGEGROUP

or

... GROUP BY GENDER, AGE

and not both. Not the exact same query on a single execution, at least. That's why I suggested something less rigid. Of course, it depends on what you want. More information will help.

--------------------
Robert Crouser
Go to the top of the page
 
cheekybuddha
post Dec 3 2019, 09:33 AM
Post#7


UtterAccess Moderator
Posts: 11,906
Joined: 6-December 03
From: Telegraph Hill


We can also do:
CODE
SELECT
  Gender
  SUM(IIf([Age]  = 30, 1, 0) AS 30YearOlds,
  SUM(IIf([Age] > 20 And [Age] < 30, 1, 0) AS TwentySomethings
FROM YourTable
GROUP BY
  Gender

--------------------


Regards,

David Marten
Go to the top of the page
 
projecttoday
post Dec 3 2019, 09:42 AM
Post#8


UtterAccess VIP
Posts: 11,283
Joined: 10-February 04
From: South Charleston, WV


Yes, that might work, depending.

Just let me point out that aggregate queries don't show you the details the way pivot tables and reports can.

--------------------
Robert Crouser
Go to the top of the page
 
ADezii
post Dec 3 2019, 10:05 AM
Post#9



Posts: 2,708
Joined: 4-February 07
From: USA, Florida, Delray Beach


IMHO, one possible solution may be a little known and rarely used Function named PARTITION. It is extremely handy when you want to calculate how many times something occurs within a calculated series of Ranges. I'll go into the syntax and details of its use below, then you can decide if it would be appropriate in your case.
  1. Partition(number, start, stop, interval) Function syntax:
    • number - whole number to be evaluated against the Ranges ([Age] Field).
    • start - whole number that is the start of the overall Range (20 for Demo).
    • stop - whole number that is the end of the overall Range (80 for Demo)
    • interval - difference between Range elements (10 for Demo).
  2. Sample Data:
    ID1GenderAge
    0Male20
    1Male34
    2Male47
    3Female32
    4Female66
    5Female71
    6Female79
    7Female27
    8Male28
    9Female39
    10Male31
    11Female55
    12Male59
    13Male51
    14Female77
    15Female70
    16Male32
    17Male34
    18Female80
    19Female48
    20Female53
    21Female62
    22Female41
    23Male69
    24Male49
    25Female47
  3. Query Definition:
    SQL
    SELECT DISTINCTROW Partition([Age],20,80,10) AS Age_Range, tblData.Gender, Count(tblData.Age) AS Count_In_Range
    FROM tblData GROUP BY Partition([Age],20,80,10), tblData.Gender
    ORDER BY Partition([Age],20,80,10), tblData.Gender;
  4. OUTPUT (Total Count by of [Age] by Gender within Interval of 10 years):
    Age_RangeGenderCount_In_Range
    20:29Female1
    20:29Male2
    30:39Female2
    30:39Male4
    40:49Female3
    40:49Male2
    50:59Female2
    50:59Male2
    60:69Female2
    60:69Male1
    70:79Female4
    80:80Female1
  5. Obviously, you can change any of the Arguments to the PARTITION Function.

This post has been edited by ADezii: Dec 3 2019, 10:09 AM
Go to the top of the page
 
msnarayanan
post Dec 4 2019, 02:24 AM
Post#10



Posts: 282
Joined: 28-May 13



dear sirs
thank for many suggestions offered by forum members.

there are many fields, like gender (male/female), age groups (15-20, 21-30, 31-40 etc), status(student, employed, agriculture etc.)
i want to tabulate the data in the following manner:

Gender age group status education
M F M F student employed illerate upto 8th upto 12th

M F M F M F M F M Fh

and similar details for all the headings.

hope my above statement is clear to you.

i am also attaching the table which may help in solving my problem.


thanks

m s narayanan

Attached File(s)
Attached File  MFF_QUESTIONNAIRE.zip ( 135.34K )Number of downloads: 4
 
Go to the top of the page
 
cheekybuddha
post Dec 4 2019, 08:23 AM
Post#11


UtterAccess Moderator
Posts: 11,906
Joined: 6-December 03
From: Telegraph Hill


Hi m s,

I had a (very brief) look at your db.

I'm afraid my advice is to a step back and look at your table structure, and apply the principles of Normalisation.

Your tables are essentially designed like spreadsheets and contain many 'repeating fields' (any time you have a field with the same name suffixed with 1, 2, 3 ...).

Whilst this might make sense in how you would like to visualise your data it doesn't lend itself well to data analysis. Once the table structure is sorted it is still possible to view the data in the layout you have.

See here for some posts on Normalisation

Surveys/questionnaires are also difficult to implement with regards to normalising your data. There is a (quite complex) example in the UA Code Archive here which you can study.

Think about what data you are trying to record, and then come back here to ask for assistance in getting your tables designed properly - our members can offer great advice/assistance.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
msnarayanan
post Dec 4 2019, 11:40 AM
Post#12



Posts: 282
Joined: 28-May 13



Dear cheekybudha
I am very thankful for your suggestions. I pretty well know the table is unwieldy and it is all the more difficult to enter data.
Based on the advice I propose to create four tables as follows:
1 members information. Name, address, contact number, age group, nature of job, educational qualification
2 current problems faced by residents: 17 problems are identified and member should select 10 problems out of 17. (drop down menu)


3 support table we Have selected about 15 items. Members should select their 4 most preferred ones. (drop down menu)
4 a table to choose their interest to participate in various projects (drop down menu)

Will this be helpful to analyse data. How many of each gender, agegrups etc are identifying the areas,

How these tables could be related. Is it necessar to add names in all the tables for linking.

I would request to give your valued suggestion.

M S Narayanan
Go to the top of the page
 
cheekybuddha
post Dec 4 2019, 12:42 PM
Post#13


UtterAccess Moderator
Posts: 11,906
Joined: 6-December 03
From: Telegraph Hill


You seem to be on the right track.

In Members, if you record DateOfBirth then you can calculate which age group they belong to (though I understand DOB might not always be available data)

You will need junction tables (M:M) to tie it all together.

MemberProblems:
ID
MemberID
ProblemID
(limit to 10 per MemberID)

MemberSupport:
ID
MemberID
SupportID
(limit to 4 per MemberID)

MemberInterests:
ID
MemberID
InterestID

Whether you need a MemberProjects or InterestProjects junction table will depend how they relate.

Then the trick is to query the junction tables for your aggregated data.


hth,


d

--------------------


Regards,

David Marten
Go to the top of the page
 
msnarayanan
post Dec 5 2019, 07:20 AM
Post#14



Posts: 282
Joined: 28-May 13



dear sir

as suggested by you i have created 5 tables and forms.

while i can enter data in the first form, i could not enter data in other forms. the dropdown menu does not work. i tried many ways but in vain.
i do not figure out any mistake in creating the form. but in tables, i can fill in'

i am attaching the file for your information and for correcting ihem. i am sory for giving you trouble.
please also guide me for the further steps - i.e. creating junction table, queries, etc.

thank you

m s narayananAttached File  MFF_QUESTIONNAIRE.zip ( 149.59K )Number of downloads: 0

Attached File(s)
Attached File  MFF_QUESTIONNAIRE.zip ( 149.59K )Number of downloads: 1
 
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    11th December 2019 - 08:39 AM