Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report design (Access 2k)

    Greetings,

    I have a main form called HouseHold and a subform called Applicants.
    In my subform (Applicants) I have a field called AppType with drop down PREGNANT, CHILD, POST PARTUM. And another field in (Applicants) subform called AppStatus, with drop down NEW, DSS, REDETERM.
    I

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

    Re: Report design (Access 2k)

    For 5,6 and 7 you could create a query based on the Applicants table (probably).

    Activate the Queries section of the database window.
    Click New, then Crosstab Query Wizard, and click OK.
    Select the Applicants table, and click Next.
    Select AppType as Row Header, then click Next.
    Select the date field you want to group on as Column Header, and click Next.
    Specify Month as grouping interval, then click Next.
    Select an arbitrary field for calculations, and Count as function.
    Make sure that Row Totals is checked, and click Next.
    Modify the name of the query if you wish, and select the option to edit the query's design. Then click Finish.
    In the design window, add AppStatus to the query grid.
    Change the Totals option to Where, leave the Crosstab option empty and enter "DSS" in the criteria line.
    Close and save the query.
    Use this query as record source for a (sub) report.

  3. #3
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report design (Access 2k)

    Hans,
    Thanks for your reply. I made a sample report (bogus) to show you how the report should look like and e-mail it to you as a word attachment.
    Thanks,
    DD
    Attached Files Attached Files

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

    Re: Report design (Access 2k)

    If I understand what you want correctly, you can create a report that looks more or less like the one in the Word document by creating two crosstab queries and tworeports (a main report and a subreport)

    The first query calculates counts per AppType by month, plus a yearly total. The second query does the same, but only for cases with AppStatus DSS (this is the one described in my previous reply.) The queries are (more or less) identical except for the condition.

    The first query acts as record source for the main report. I let the Report Wizard create it, then tweaked it by adding text boxes for the percentages, plus some minor changes. When it was finished, I duplicated it, set the record source of the copy to the second query and stripped all header and footer sections out of it. After saving it, I put the copy as a subreport in the report footer section of the main report. The advantage of making a copy was that I could be sure that the Detail section would have the same layout.

    I have attached a demo database (with some dummy data) in Access 97 format (zipped). There is no code in this very simple demo, only three tables, two crosstab queries and two reports (main and sub). I hope that studying it will make things clearer than my short description above.
    Attached Files Attached Files

Posting Permissions

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