Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington, Cheshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Complex Reports (Access 2003)

    The local hospice where my wife and I volunteer is required to submit data to a Government Health body concerning the number of patients it treats as a result of referrals from the National Health System. They have a current system, which no longer does everything that is required, written to use Excel and Visual Basic exclusively i.e. all the output data is calculated and written using VB procedures, which write to an Excel spreadsheet. The author of this system has now died and the hospice has no VB expertise. Consequently, they would like to convert to an Access based system and produce the output using Access reports. They have asked us if we can do this.

    They have a database which collects straightforward data, such as name, address, Date of birth, diagnosis, location before admission, location after discharge etc. etc. but the government body requires composite data; examples are shown in the attached spreadsheet.

    Q1 : The output has to detail the numbers of people in particular age ranges e.g. between 21 and 30, 31 and 40 etc by sex and total. The database only captures Date of Birth.
    How do we design a report to produce the number of male, female and the combined total in each of the age ranges, please? Surely it
    Silverback

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

    Re: Complex Reports (Access 2003)

    Q1: You can create a custom VBA function to calculate the age group from the date of birth and the report date. (We can help with this function)
    You can then create a crosstab query to display the numbers of patients by age group vs gender.

    Q2: You'll have to obtain or create a table with two columns, one listing the detailed diagnoses, the other the corresponding categories.
    You can then use this translation table together with the diagnoses table in a crosstab query to display the numbers of patients by diagnosis category vs gender.

  3. #3
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Complex Reports (Access 2003)

    As a followup to Hans comments for Q1:
    If you only need to be reasonably accurate (as in Year of Birth math) with the report, you can do something as simple as the following: age: Year(Date())-Year(DoB) to calculate the age in years where DoB is the Date of Birth field. Save this in a query which also has your gender stats as well. Then create a crosstab based on this first query where gender is the row heading and use the switch function in the column heading. Has did a demo for me in <post#=554028>post 554028</post#> of how to implement this. It was using sales statistics, but the principal is the same. You need to remember to list your column headings in the properties for the crosstab and make sure they are the same as the columns you use in switch or the query won't display results properly.

    If you need an accurate to the day age, you will need a custom function as Hans indicated.

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington, Cheshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Complex Reports (Access 2003)

    Edited by HansV to replace Word doc with the picture it contained

    Thanks to Peter and Hans
    I have the crosstab working - see attached Word doc. for picture. Many thanks - I would never have found switch without your help.
    Now, another dumb question - sorry.
    Producing the required data is going to need a number of crosstab queries. Having tried to build a report using more than one crosstab, I get an error which says that I am trying to take data from places that access cannot join.
    Does this mean that I have to have 'n' reports, each based on a crosstab or is there a way to build a report using data from all the crosstab queries, please?
    Thanks again for your help.
    Silverback
    Attached Images Attached Images
    • File Type: png x.png (4.5 KB, 0 views)
    Silverback

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

    Re: Complex Reports (Access 2003)

    If the various crosstab queries can be joined on a field (or fields), you could create a query based on the crosstab queries, and use that as record source for the report.
    Otherwise, you could create a separate report for each crosstab query, and place those reports as subreports on a main report.

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington, Cheshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Complex Reports (Access 2003)

    Hans
    I now find there is a requirement to total the columns of the crosstab pictured in my last posting i.e. the totals for each of the All, Female and Male columns.
    How can this be achieved please?
    Thanks
    Silverback
    Silverback

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

    Re: Complex Reports (Access 2003)

    Queries don't display row totals.
    But you can easily add total in a report based on the query: place text boxes in the report footer with a control source such as

    =Sum([Female])

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington, Cheshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Complex Reports (Access 2003)

    Hans
    Thanks again for your help. I've got it going now, albeit after getting an #Error on the =sum() calculation because I put the text box in the page footer and not the report footer. Doh!

    One last two part question about this topic, please.

    Is there a way of a) making the cross tab return 0 instead of blank when there is no data for a row/column and [img]/forums/images/smilies/cool.gif[/img] can the cross tab be made to include a row if there is no data (i.e. if there are no males or females in a particular age group)?

    Thanks again.
    Silverback
    Silverback

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

    Re: Complex Reports (Access 2003)

    To include rows with no data, you must have a table (or query) that lists all age groups (i.e. each age group is a separate record).
    Create a new query based on this table (or query) and the crosstab query.
    Join them on the age group field.
    Double click the join line, and select the option to return all records from the table, and only related records from the crosstab.
    Add the age group field from the table (*not* from the crosstab query) to the query grid.
    Add expressions like this:

    All: Nz([NameOfCrosstab].[All],0)

    where NameOfCrosstab is the name of the crosstab query.
    Similar for Male and Female.

Posting Permissions

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