Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Nov 2003
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Define Ages (Access 2)

    Hi hope you can help a beginner - I have a table which shows locations, year of contact, ages and gender. Ages are to be defined in about 20 bands (<1, 1-4 etc.). In one query, how do I get the database to show my locations (grouped ascending), year of contact (grouped ascending), and a count of the males in each individual band?? Eg

    Location Year Count Band 1 Count Band 2 etc.
    ------------ ------ ------------------ -------------------

    Basingstoke 1999 3 5
    Basingstoke 2000 3 0
    Bradford 1999 2 1

    Thanks! Blitzy

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

    Re: Define Ages (Access 2)

    Try a crosstab query, there is a wizard that will take you thru it step by step.

  3. #3
    Lounger
    Join Date
    Nov 2003
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Define Ages (Access 2)

    Hi Pat, thanks I have done this. However, I need the crosstab to show the age bands down the side (row heading), even when there is no value - the age bands are defined in a separate table and linked in the underlying query. Is there any way I can do this?

    Many thanks!

    Blitzy

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

    Re: Define Ages (Access 2)

    Double click the join between the separate age band table and the other table in the query design. Specify that you want to include ALL records from the age band table.

  5. #5
    Lounger
    Join Date
    Nov 2003
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Define Ages (Access 2)

    Hi Hans -IMany thanks. have tried this and it doesnt make any difference. I have the Age Band as a Row Heading and a count as the value. Any other ideas?B

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

    Re: Define Ages (Access 2)

    Could you post a stripped down version of your database?
    <UL><LI>Make a copy of the database and work with that.
    <LI>Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
    <LI>In the remaining table(s), remove most records - leave only the minimum number necessary to demonstrate the problem.
    <LI>Remove or modify data of a confidential nature.
    <LI>Do a compact and repair (Tools/Database Utilities).
    <LI>Make a zip file containing the database; it should be below 100KB.
    <LI>Attach the zip file to a reply.[/list]This will enable Loungers to try and find the problem.

  7. #7
    Lounger
    Join Date
    Nov 2003
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Define Ages (Access 2)

    Hi Hans, unfortunately I am not allowed to do this. I will try and resolve it - thanks for your help. It is a very old version of Access (Access 2) - perhaps it is something to do with that. Regards/

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Define Ages (Access 2)

    Can you at least post the SQL for the query? That would allow us to see what was actually going on.
    Charlotte

Posting Permissions

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