Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Specify date for report with subs (2000)

    Hi,
    Hope someone can help me here.
    I've got a report(1) that has three sub reports(A,B,C) within it.
    Essentially report 1 is literally a blank report with solely the sub reports in.
    I need to produce A,B & C seperately on a monthly basis. This is all fine.
    Report (1) needs to be able to produce reports for previous months. I normally do this through combo boxes which details each month & year. How would I get around the fact that (1) has to be able to display A,B & C through a selection of months.

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

    Re: Specify date for report with subs (2000)

    Sorry, I don't understand. Can you try to explain more clearly what you want to accomplish? Do you mean that you want to apply different criteria to each of the subreports, or something else?

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Specify date for report with subs (2000)

    Sorry, I'll explain.
    Basically I produce monthly reports detailing various information & statistics. Those reports, for arguments sake, are called RptA,RptB,RptC. These are fine and all work okay.
    I also produce a report(RptAll) that displays all three(A,B,C) on one page.This get emailed to management as a complete overview. The problem lays that I have been asked to produce reports for previous months statistics.
    What I'd like to be able to do is to still produce Reports A,B & C seperately, but also produce RptAll for various months. The problem lays that the criteria in the queries for RptA,B & C does not refer to comboboxes. So do I need to duplicate & rename A,B & C to be able to produce RptAll for various months, or is there a way to bypass this and specify critera for RptAll, although the sub reports do not have the criteria?

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

    Re: Specify date for report with subs (2000)

    You could create a query that returns all dates that are in the table(s) behind the subreports, and criteria that limit the dates to those selected in the combo boxes on a form.

    Set the Record Source of the main report rptAll to this query, and link each of the subreports to the main report on the date field (by setting the Link Child Fields and Link Master Fields properties).

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Specify date for report with subs (2000)

    Thanks Hans, that makes perfect sense. The only problem is that the reports are based on crosstab queries that calculate certain data fields and return totals. Heres an SQL view:

    TRANSFORM Count(QryMainCaseType.URN) AS CountOfURN
    SELECT QryMainCaseType.Ethnicity, QryMainCaseType.Gender, Count(QryMainCaseType.URN) AS [Total Of URN]
    FROM QryMainCaseType
    GROUP BY QryMainCaseType.Ethnicity, QryMainCaseType.Gender
    PIVOT QryMainCaseType.QryMain.[Case Type];

    Currently as these do not have the Date field in them, as soon as I add it it displays multiple lines. For instance, two fields Male/Female. I want to count how many males & how many females, and return a total. Currently this work fine, producing just two lines of data. As soon as I add a date field, the crosstab naturally splits this as well so that I then get 3 pages. Is there anyway around this?

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

    Re: Specify date for report with subs (2000)

    In that case, the only thing you can do (I think) is add criteria referring to the year and month combo boxes to the crosstab queries or to the queries they are based on. You may want to create duplicates for the queries and subreports for this, in order to keep the original version.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Specify date for report with subs (2000)

    Sorry Hans, reviving this one again.

    Just been working on the queries to specify a date for them to run. Currently I have a main query, from this I have queries to pick out specific data and set a date for the records returned dependant upon what is recorded in CboMonth & CboYear in FrmRptMenu. All fine up until now!

    When I then run a crosstab query to calculate ethnicities, gender, etc I get the following message.

    I'm really quite stumped as too how to work around this one!

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

    Re: Specify date for report with subs (2000)

    You will probably have to declare the parameters in each of the queries. Start with the query having [Forms]![frmRptMenu]![cboMonth] in its criteria.
    - Open it in design view.
    - Select Query | Parameters...
    - Enter [Forms]![frmRptMenu]![cboMonth] under Parameter.
    - Select Long Integer under Data Type.
    - Repeat for [Forms]![frmRptMenu]![cboYear]
    - Click OK.
    - Save and close the query.

    Repeat this for each query based on this query.

  9. #9
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Specify date for report with subs (2000)

    Wow, works a treat, I wouldn't have got there in a million years.
    Thanks Hans <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

  10. #10
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Specify date for report with subs (2000)

    Oops, can I specify what the parameter relates to?
    I have several date fields but need it to match the values in the comboboxes with the values in the field [DateOut] in each of the queries.

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

    Re: Specify date for report with subs (2000)

    The Parameters dialog is only used to tell Access which parameters you are using, not where you are using them. You only specify the conditions in the Criteria row(s) of the "first" query; there is no need to repeat them in the queries built upon the first one.

    So: specify the criteria in the first query only, but declare the parameters in the first query and in all queries built upon it.

  12. #12
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Specify date for report with subs (2000)

    So, just to reitterate:
    MainQuery(1) has the criteria that specifies what field is to be checked against the values selected in the comboboxes, and the Parameters.
    3 queries(A,B,C) from based on the main query(1) do not have the criteria, but do have the parameters.
    Does this also apply for each cross tab queries based seperately on A,B and C?
    Just wanted to clarify, as I have started from scratch with regards to the queries as I designed the database sometime ago and it looks a bit muddled.

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

    Re: Specify date for report with subs (2000)

    Yes, that sums it up correctly.

  14. #14
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Specify date for report with subs (2000)

    Hans,
    I still seem to be having problems with this one. I've attached a slimmed version of the access database.
    I have placed the criteria in QryMain as well as the parameters. Following this by the parameters in the remaining queries.............still throws up the same amount of records regardless of what is selected in CboMonth and CboYear.
    Any ideas as to where I've gone wrong?

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

    Re: Specify date for report with subs (2000)

    The database you attached doesn't work, since it expects a query QryMain. There is no such query in the database. Moreover, DSQryMain filters on Archive = False, and all records you included have Archive = True. <img src=/S/burnup.gif border=0 alt=burnup width=31 height=31>

    If I correct that, the queries display different results if I select a different month in the combo box.

Page 1 of 2 12 LastLast

Posting Permissions

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