Report based on selections on a form

Discussion in 'Microsoft Access Reports' started by Guest, May 27, 2005.

  1. Guest

    Guest Guest

    I have a form that has about 80 items each one has an option group for yes,
    no, and N/A. This updates a table for each itme for each person. Now I have
    a form for the user to create a report, but gives the user options to narrow
    the criteria for the report, like date, section, etc.

    I could create a report for every possiblity the user can select, but I
    would rather have one report and build in a statement to populate the report.

    The report shows these 80 items in a column and 3 boxes next to each item to
    display the count of yes, no, and N/A. I am trying to get an overall count
    for every record base on the criteria the user selected to create the report.
    I can't use the recordsource because it gives me a report for each record.
    I tried using a crosstab query, but that didn't seem to help. So for each of
    these boxes I created a formula to count each item. However, since I want to
    use one report to display the information based on the user selection from
    the form I am stuck on how to do this. I thought about creating a temp table
    and delete the items before previewing the report then populate it with all
    records with the possibility of narrow it down by the user selection.

    I hope I am making sense here.

    How can I excute a query "append query" based on a strSQL string? This will
    populate my temp table. I can then base the formula for each box on each
    itme in this table.

    Any help or other ideas on how to do this would be appreciated,
     
    Guest, May 27, 2005
    #1
    1. Advertisements

  2. Guest

    David Lloyd Guest

    I am not sure I understand all the details of your report, but one
    suggestion I would make would be to look at using the DCount function. This
    function allows you to count the number of items in a table or query
    separate from the report Record Source. The DCount function also allows you
    to specify criteria to limit the records included in the count.

    If the user inputs from the form are not shown visibly on the form, then you
    can consider using hidden textboxes to reference there values on the report
    and in the DCount function.

    --
    David Lloyd
    MCSD .NET
    http://LemingtonConsulting.com

    This response is supplied "as is" without any representations or warranties.


    "TT" <> wrote in message
    news:...
    I have a form that has about 80 items each one has an option group for yes,
    no, and N/A. This updates a table for each itme for each person. Now I
    have
    a form for the user to create a report, but gives the user options to narrow
    the criteria for the report, like date, section, etc.

    I could create a report for every possiblity the user can select, but I
    would rather have one report and build in a statement to populate the
    report.

    The report shows these 80 items in a column and 3 boxes next to each item to
    display the count of yes, no, and N/A. I am trying to get an overall count
    for every record base on the criteria the user selected to create the
    report.
    I can't use the recordsource because it gives me a report for each record.
    I tried using a crosstab query, but that didn't seem to help. So for each
    of
    these boxes I created a formula to count each item. However, since I want
    to
    use one report to display the information based on the user selection from
    the form I am stuck on how to do this. I thought about creating a temp
    table
    and delete the items before previewing the report then populate it with all
    records with the possibility of narrow it down by the user selection.

    I hope I am making sense here.

    How can I excute a query "append query" based on a strSQL string? This will
    populate my temp table. I can then base the formula for each box on each
    itme in this table.

    Any help or other ideas on how to do this would be appreciated,
     
    David Lloyd, May 29, 2005
    #2
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Phil Matish, MCSE

    Customize Report based on User Selections in a Form

    Phil Matish, MCSE, May 5, 2004, in forum: Microsoft Access Reports
    Replies:
    1
    Views:
    135
    Chris Sergent
    May 6, 2004
  2. Guest

    Filtering Report via form selections

    Guest, Aug 22, 2006, in forum: Microsoft Access Reports
    Replies:
    2
    Views:
    180
    Guest
    Jun 8, 2007
  3. Guest

    Create a report based on table field selections

    Guest, Sep 13, 2007, in forum: Microsoft Access Reports
    Replies:
    3
    Views:
    189
    Guest
    Sep 14, 2007
  4. vanlanjl
    Replies:
    4
    Views:
    306
    Duane Hookom
    Mar 3, 2009
  5. drewship
    Replies:
    0
    Views:
    353
    drewship
    Jul 17, 2009
Loading...

Share This Page