Demographic Tracking

[leetx]

Hello - I'm fairly new to Access and I'm stuck on how to structure my demographic table.

I currently have one table for demographics that tracks the following:

Racial Background
Gender
Age Range
Household Income
Education Level

For all categories except Racial Background, there is only one answer. Our clients often select more than one race and I'm not sure how to capture that. Originally I had a drop down box on the Form that would allow more than one race to be selected, however I found it difficult to pull any meaningful reports. I now have it set up so that only one race can be selected, but that is not very accurate either.

Does anyone have some suggestions or work arounds? I would like my reporting to be as accurate as possible based on the clients' response.

Thanks!

 

[jdraw]

Have you tried to mock up the reports that will be required(expected)?
Have you worked out what fields/data must be available to produce each of the reports?
That will give you a good basis for the overall data required.

There is an excellent tutorial here to help you with getting your tables structured properly.
http://www.databaseanswers.org/approach2db_design.htm

 

[leetx]

Yes - I have all that. The problem is so much with the table as it is with figuring out how to set up that particular field. As I said, if it's a drop down box that allows multiple options, you can grab those items in a report.

You would have to run a report for all the different combination (pacific islander and african american, or native american and pacific islander). I don't want to run that many reports. I want all the combinations to show up on one report and grouped.

If I don't allow multiple choices in the drop down list, I'm not getting a very accurate picture.

 

[MMaynard]

You could make another table to hold the race(s) of the people and do a one-to-many relationship for each record.

 

[jdraw]

leetx,

Here is a tutorial on Dynamic Reporting http://www.fontstuff.com/access/acctut19.htm

I think it may provide some options of interest to you.

 

[DCrake]

In the UK we have a standard list of Ethnic Origins and when completed it is the response from the person askd that is entered even if they are Black and they say White.

What you could do is to have a sub groups to place several expressions into one category and report by category.

 

[Mr Smin]

Just a word about age range - if at all possible, store a date of birth. This allows you to analyse age relative to the current (or any arbitrary) date.
If you have a record that says "Bob is in the 35-40 age group" this will become wrong in 5 years or less (when Bob has his 41st birthday).

 

[leetx]

Good point about the age range. I'll have to review that and determine what exactly we'll be using the data for.

I had thought about creating a table to store all the races, but would I add a new record for each time a person checks more than one box?

For example, someone checks Asian and African American - is that now a new record in my new table?

 

[Mr Smin]

Race/ethnicity categorisation is (I find) a big headache before you even get to setting up a database.
You could have a people table, a race/ethnicity table and a junction table between them which would allow a person to define themselves by any mixture they require.
This means when Bob wants to select Asian and African American, that's two rows in the junction table.

Junction Table
1 Bob Asian
2 Bob African American

Ethnicity/Race table
Asian
South Asian
White American
African American
White European

In fact, what is common where I work is that there is a standard set of about 70 options (including "Not Stated") and people have to pick whatever is closest to them. Therefore no junction table.

btw, you should probably wait in case anyone has a better suggestion on this thread

 

[leetx]

Yes - headache is a good word for this. I've gone through a few different options with none of them being exactly what I need.

I don't know if this matters in terms of design - but my demographic form is anonymous. We don't ask them to put a name. It's really more about capturing data.

Thank you to everyone for all the advice. I'm thinking a separate table might have to be the option I use. If there are any other ideas though, I'm open to them. My main goal is just to be sure that if I ever leave this job, the database is structured as properly as possible so anyone can just come in and use it...

Thanks! - Lisa