Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

subreport problem

Discussion in 'Information Technology' started by deltauser2006, Jul 28, 2006.

  1. deltauser2006

    deltauser2006
    Expand Collapse
    Guest

    Hey, I currently have multiple reports which pull data from one set of
    tables. Every quarter the tables are updated and the old data is
    stored in a different set of historical tables and is tagged with the
    appropriate quarter and year that they are from. I am trying to create
    the same reports except have them reflect the historical data. In
    otherwords, when the report is opened, I want the user to pick a
    quarter and year and then have the report produce information for that
    quarter and year from the historical tables. The problem is, when I do
    this, for every subreport in the report I am prompted to enter the
    quarter and year over again. This means entering quarter and year 25
    different times. Instead I would like to have to do this only once.
    Please let me know if you have any ideas about how to fix it so quarter
    and year only have to be entered once for both report and all
    subreports. Thanks!
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News Sikhs Face Problems While Carrying Kirpans In Khyber-Pakhtunkhwa - SikhSiyasat.Net Breaking News Jul 29, 2016
    "Dasam" Granth - A Look At The Core Problems Dasam Granth Oct 21, 2015
    Problems In Life Sikh Sikhi Sikhism Apr 13, 2015
    How can I make decent and useful friends and from where ?? most people have a problem with me Sikh Youth Oct 23, 2013
    The Problem with Taking Too Many Vitamins Health & Nutrition Oct 18, 2013

  3. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    DeltaUser,

    Instead of a parameter query where the user is prompted to enter the
    quarter and year criteria, consider putting a couple of unbound
    textboxes or comboboxes on a form where the user can enter the criteria.
    Then, in the criteria of your queries, refer to these controls using
    syntax such as [Forms]![NameOfForm]![NameOfControl]

    Do you mean you have a report with 25 subreports? Gosh!

    --
    Steve Schapel, Microsoft Access MVP

    deltauser2006 wrote:
    > Hey, I currently have multiple reports which pull data from one set of
    > tables. Every quarter the tables are updated and the old data is
    > stored in a different set of historical tables and is tagged with the
    > appropriate quarter and year that they are from. I am trying to create
    > the same reports except have them reflect the historical data. In
    > otherwords, when the report is opened, I want the user to pick a
    > quarter and year and then have the report produce information for that
    > quarter and year from the historical tables. The problem is, when I do
    > this, for every subreport in the report I am prompted to enter the
    > quarter and year over again. This means entering quarter and year 25
    > different times. Instead I would like to have to do this only once.
    > Please let me know if you have any ideas about how to fix it so quarter
    > and year only have to be entered once for both report and all
    > subreports. Thanks!
    >
     
  4. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    The simplest way to do this would be to leave the data in the original
    tables, and use some kind of flag (yes/no field? date field?) to indicate if
    it is an archive record or should be considered as belonging to another
    period. That solves the problem completely. It is easy enough to create a
    query that filters out all the archive records, and use that query where
    ever you are currently using your table.

    Another option would be to create a form with some unbound controls that let
    you specify the criteria for the queries of the main report and subreport.
    The criteria would look like this:
    [Forms].[Form1].[Text0]
    Since the queries are reading the criteria from the form, they don't have to
    keep asking you for the same value.

    If the data must be collected from multiple tables, you might need to read
    up on UNION queries.

    If the query statement for the subreport needs to be re-written, it is
    possible to do so in code. Before you OpenReport:
    Dim strSql As String
    strSql = "SELECT ...
    dbEngine(0)(0).QueryDefs("Query1").SQL = strSql

    Hope that gets you out of trouble. The first suggestion is the simplest,
    most powerful, most flexible, and easiest to maintain.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "deltauser2006" <David.Rycyna@gmail.com> wrote in message
    news:1152060442.429349.186280@p79g2000cwp.googlegroups.com...
    > Hey, I currently have multiple reports which pull data from one set of
    > tables. Every quarter the tables are updated and the old data is
    > stored in a different set of historical tables and is tagged with the
    > appropriate quarter and year that they are from. I am trying to create
    > the same reports except have them reflect the historical data. In
    > otherwords, when the report is opened, I want the user to pick a
    > quarter and year and then have the report produce information for that
    > quarter and year from the historical tables. The problem is, when I do
    > this, for every subreport in the report I am prompted to enter the
    > quarter and year over again. This means entering quarter and year 25
    > different times. Instead I would like to have to do this only once.
    > Please let me know if you have any ideas about how to fix it so quarter
    > and year only have to be entered once for both report and all
    > subreports. Thanks!
     

Share This Page