Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    query help (Access2003)

    It is time again for the quarterly United Way reports. I am able to obtain the needed information but there has to be a better way. [am currently running 15 or more queries to accomplish what I need] The demographics part of the report requires all the participants of each program to be fitted into a grid. Each participant is divided by race and within each of those groups we must additionally show each person by gender and then in age groups 0-17, 18-44, 45-64, 65+ and then in another part of the grid, show each person bye the town within the county. The data is kept in three tables-- The family table, the person table and then each program had data by program. These are related by the personal ID and the family ID. I assume we have to have several queries made separately and then joined. Any suggestions will be appreciated.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: query help (Access2003)

    I asssume that you already have a query that calculates the age of each person; if not, create it now.

    Create a new query based on this query, and select Query | Crosstab Query

    Add the Race/Ethnicity field, leave its Total option as Group By, and set its Crosstab option to Row Header.
    Add the Gender field, leave its Total option as Group By, and set its Crosstab option to Row Header.
    Create a calculated column:

    AgeGroup: Switch([Age]<18, "0-17", [Age]<45, "18-44", [Age]<65, "45-64", [Age]>64, "65+")

    where Age is the age field. This column displays the age groups. Leave its Total option as Group By, and set its Crosstab option to Column Header.
    Add a calculated column:

    Cnt: Count(*)

    Set its Total option to Expression, and set its Crosstab option to Value.
    This query should result in the first part that you want.

    For the second part, you also need a crosstab query but I don't know where the estimated number for next year comes from.

  3. #3
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query help (Access2003)

    Yes, I understand-- how to guess how many people will need service next year is always a difficult one since I do not tell fortunes but I usually just add ten per cent and they pass it through. I see what you are telling me but it does not separate out each program-- and when I have tried to add the program I just have mush for data-- here is one of the many queries I use now. I will try your sugggestion to cut the number of queries down-- I guess I can just do one for each program and then another for each area-- my inexperience with cross tab is causing me the trouble. Here is one of what I am doing now.
    SELECT Count(tblPerson.[Personal ID]) AS [CountOfPersonal ID], tblPerson.Race, tblPerson.Sex
    FROM tblPerson INNER JOIN tblAttendance ON tblPerson.[Personal ID] = tblAttendance.[Personal ID]
    WHERE (((tblAttendance.Date) Between #1/1/2007# And #12/31/2007#) AND ((Year(Now())-Year([tblPerson]![DOB]))>17 And (Year(Now())-Year([tblPerson]![DOB]))<44))
    GROUP BY tblPerson.Race, tblPerson.Sex
    ORDER BY tblPerson.Race, tblPerson.Sex;

    You are so much help to me-- I am the director of our non profit and all the computer and data falls to me to work out. The town is rural and have never found anyone close to me who knows more than I do [which you can see is such a small amount].

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: query help (Access2003)

    You could add the program field as the first column in the crosstab query, with its Crosstab property set to Row Header.

  5. #5
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query help (Access2003)

    I hate to be a bother--but I am having no luck getting the column set up as you suggested and I am wondering if the age column is done correctly in the example I sent. (if I just leave out the >17 and less than 44) If you could show me an example, I will remember how to do this from now on. I was not sure what to put in the place of (age). Thank you

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: query help (Access2003)

    In the SQL that you posted, age is Year(Now())-Year([tblPerson]![DOB]). You can use that instead of [Age].

  7. #7
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query help (Access2003)

    I tried to use that in the column as I thought you suggested but am not referencing it correctly since I get an error when I try to do it. If you could help me with that column, I can make all the other queries.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: query help (Access2003)

    Could you post a stripped down and zipped copy of the database?

  9. #9
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query help (Access2003)

    we have tried that but I cant get it small enough--I will try again but I dont have much hope that I will succeed.

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: query help (Access2003)

    Post your query.

  11. #11
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query help (Access2003)

    I have tried to copy and paste the suggestion Hans made -- but I can not exit the column (so cant send it in) -- I am told there is a syntax error-- no chance to replace [age] with the formula. I will take extra time tonight to re read cross tab queries so that I may get it right. I am totally self taught through trial and error and LOTS of reading. I have been successful in getting the data out of the database in order to make the United Way report each quarter but it is sooo time consumming to have 15 to 20 queries for each report and then still have hand counting from the query.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: query help (Access2003)

    Have you read my PM? If not, click on PostBox in the Lounge menu bar.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •