On Aug 17, 1:07*am, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
> On Tue, 16 Aug 2011 06:28:59 -0700 (PDT), Fev <hgos...@worldonline.co.za>
> wrote:
>
>
>
>
>
> >Hi
> >I am busy setting up a database for my market research company, and
> >have a query about setting up a table to store the Project Detail
> >information for each research project. *While I do have some
> >understanding of the normalisation process, I also am not sure how to
> >store this information. *The database will store information about the
> >demographics each research project will target and how many interviews
> >will be done in each demographic area. *Project 1 may only require
> >interviews of females only in the following age groups 16 - 22; 23 -
> >30 and 31 - 39 in Regions 1, 2 and 3 for LSM 7, 8, 9 and 10, while
> >Project 2 may require interviews for both males and females in age
> >groups 18 - 24; 25 - 35, 36 - 49 and 50 - 65, in Regions 1, 4, 7, 8
> >and 9 for LSM 6 - 10. For each demographic group I will need to store
> >how many interviews are required and how many interviews have been
> >completed.
>
> >My first instinct would be to set up my Project Details table with the
> >following fields:
> >ProjectDetailID, ProjectID, Gender, AgeGroup, Region, LSM,
> >NoRequiredInterviews, NoSuccessfulInterviews
>
> >Would a crosstab query and report be able to compare the number of
> >required interviews versus the number of successfull interviews per
> >demographic group?
>
> >I would appreciate any input from more experienced database designers.
>
> >Thanks
> >Heather
>
> I've got a few suggestions. One would be that you store the age range as a
> pair of numbers (low and high) rather than a single hyphenated text field;
> this will make it easier to create a non-equi join Query selecting only ages
> within the range.
>
> Another would be that you not store the NoSuccessfulInterviews at all;
> instead, have a table of interviews with fields for the ProjectNo, a linkto
> the identity of the interviewee (if you keep that information) and the results
> of the inteview. You could then do a Totals query to count the number of
> records in this table, rather than typing in a count.
>
> Finally, the ProjectDetails table needs to be split into three (or more)
> tables - you have one to many relationships from Projects to AgeGroups and
> (independently) to Regions. I'm not sure what LSM means but that might be
> another.
> --
>
> * * * * * * *John W. Vinson [MVP]
> *Microsoft's replacements for these newsgroups:
> *http://social.msdn.microsoft.com/For...-US/accessdev/
> *http://social.answers.microsoft.com/.../en-US/addbuz/
> *and see alsohttp://www.utteraccess.com- Hide quoted text -
>
> - Show quoted text -
Hi John
Thanks so much for this advice. LSM (Living Standards Measure - It is
a means of segmenting the South African market that cuts across race,
gender, age or any other variable used to categorise people. Instead,
it groups people according to their living standards.), and in my post
I forgot to mention an ethnic grouping as well. One of the problems is
that most of the data is currently being managed in Excel, and input
and reports are largely in "cross-tab" like formats, however all
manually created. I am trying to provide a more automated and
efficient approach using Access 2007. I would also like to ease the
transition to the new system by trying to aproximate (and translate)
our current look and feel of the Excel Input and Reports into the
Access forms and reports.
Your response has provided me with a whole new approach, that will
also help solve some of the data input issues that I was confronting.
I will test this format and see how it performs on my sample data.
Thanks
Heather
Similar Threads
