Welcome to SPN

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

Sign Up Now!

Access Query / Report

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

  1. DanMc

    DanMc
    Expand Collapse
    Guest

    I am using Access 2000 and have a database that has 2 tables linked by an ID
    Number on a one to many basis. In the first table, the ID Number field is
    unique for each record, but there are several fields in each record that
    contain dates. The second table has the following fields: ID Number, Text,
    Date. There can be multiple records in the second table that have the same
    ID Number with different values in the Text and Date fields.

    I want the user to enter a start and end date and then be able to print a
    report that would look similar to the following:

    ID Number
    Field Name Date (From the first table)
    Field Name Date (From the first table)
    Field Name Date (From the first table)
    Text Date (From the second table)
    Text Date (From the second table)

    Each ID Number would appear in the report only if there were dates in it’s
    fields in table one that had dates equal to or between the start and end
    dates entered and/or dates in table two that met the same criteria. If a
    field from table one or from table two did not meet the criteria entered, it
    should not appear in the report.

    Is this possible? If so, how? Any help will be greatly appreciated.
     
  2. Loading...


  3. tbleecker@gmail.com

    tbleecker@gmail.com
    Expand Collapse
    Guest

    I would think that this would rest on a query.

    Do you know how to create a query that gives you the data you want? I
    would think you'd use parameter criteria (e.g., Between [Date1] and
    [Date2] as criteria, with the user entering in the dates).

    If you can create the query correctly, then use that as the data source
    for the report. You could then create a user-friendly form that would
    activate the query. The Access Cookbook has several examples of how to
    create such queries, if you need some guidance on that end.


    DanMc wrote:
    > I am using Access 2000 and have a database that has 2 tables linked by an ID
    > Number on a one to many basis. In the first table, the ID Number field is
    > unique for each record, but there are several fields in each record that
    > contain dates. The second table has the following fields: ID Number, Text,
    > Date. There can be multiple records in the second table that have the same
    > ID Number with different values in the Text and Date fields.
    >
    > I want the user to enter a start and end date and then be able to print a
    > report that would look similar to the following:
    >
    > ID Number
    > Field Name Date (From the first table)
    > Field Name Date (From the first table)
    > Field Name Date (From the first table)
    > Text Date (From the second table)
    > Text Date (From the second table)
    >
    > Each ID Number would appear in the report only if there were dates in it's
    > fields in table one that had dates equal to or between the start and end
    > dates entered and/or dates in table two that met the same criteria. If a
    > field from table one or from table two did not meet the criteria entered, it
    > should not appear in the report.
    >
    > Is this possible? If so, how? Any help will be greatly appreciated.
     
  4. DanMc

    DanMc
    Expand Collapse
    Guest

    The query I have does use the "Between [Date1] and [Date2]" criteria. The
    first table in my database has ID Number field and 10 date fields for each
    record. The query returns the entire record if any one of the 10 date fields
    meets the criteria. In the report, I only want to show the fields from each
    record that meet the criteria. So if I have a record that has 3 of the 10
    dates that meet the criteria, only those 3 fields would print in the report
    and the other 7 would not appear.

    "tbleecker@gmail.com" wrote:

    > I would think that this would rest on a query.
    >
    > Do you know how to create a query that gives you the data you want? I
    > would think you'd use parameter criteria (e.g., Between [Date1] and
    > [Date2] as criteria, with the user entering in the dates).
    >
    > If you can create the query correctly, then use that as the data source
    > for the report. You could then create a user-friendly form that would
    > activate the query. The Access Cookbook has several examples of how to
    > create such queries, if you need some guidance on that end.
    >
    >
    > DanMc wrote:
    > > I am using Access 2000 and have a database that has 2 tables linked by an ID
    > > Number on a one to many basis. In the first table, the ID Number field is
    > > unique for each record, but there are several fields in each record that
    > > contain dates. The second table has the following fields: ID Number, Text,
    > > Date. There can be multiple records in the second table that have the same
    > > ID Number with different values in the Text and Date fields.
    > >
    > > I want the user to enter a start and end date and then be able to print a
    > > report that would look similar to the following:
    > >
    > > ID Number
    > > Field Name Date (From the first table)
    > > Field Name Date (From the first table)
    > > Field Name Date (From the first table)
    > > Text Date (From the second table)
    > > Text Date (From the second table)
    > >
    > > Each ID Number would appear in the report only if there were dates in it's
    > > fields in table one that had dates equal to or between the start and end
    > > dates entered and/or dates in table two that met the same criteria. If a
    > > field from table one or from table two did not meet the criteria entered, it
    > > should not appear in the report.
    > >
    > > Is this possible? If so, how? Any help will be greatly appreciated.

    >
    >
     
  5. Larry Linson

    Larry Linson
    Expand Collapse
    Guest

    An un-normalized database design such as you describe will make it difficult
    (as you are finding) to Query. Your best bet would be to ask some questions
    about designing your tables, and the result may be that the Query you want
    to do is trivial instead of a "real pain."

    Larry Linson
    Microsoft Access MVP


    "DanMc" <DanMc@discussions.microsoft.com> wrote in message
    news:53947C78-EB17-42D1-A710-01E694A6B996@microsoft.com...
    > The query I have does use the "Between [Date1] and [Date2]" criteria. The
    > first table in my database has ID Number field and 10 date fields for each
    > record. The query returns the entire record if any one of the 10 date
    > fields
    > meets the criteria. In the report, I only want to show the fields from
    > each
    > record that meet the criteria. So if I have a record that has 3 of the 10
    > dates that meet the criteria, only those 3 fields would print in the
    > report
    > and the other 7 would not appear.
    >
    > "tbleecker@gmail.com" wrote:
    >
    >> I would think that this would rest on a query.
    >>
    >> Do you know how to create a query that gives you the data you want? I
    >> would think you'd use parameter criteria (e.g., Between [Date1] and
    >> [Date2] as criteria, with the user entering in the dates).
    >>
    >> If you can create the query correctly, then use that as the data source
    >> for the report. You could then create a user-friendly form that would
    >> activate the query. The Access Cookbook has several examples of how to
    >> create such queries, if you need some guidance on that end.
    >>
    >>
    >> DanMc wrote:
    >> > I am using Access 2000 and have a database that has 2 tables linked by
    >> > an ID
    >> > Number on a one to many basis. In the first table, the ID Number field
    >> > is
    >> > unique for each record, but there are several fields in each record
    >> > that
    >> > contain dates. The second table has the following fields: ID Number,
    >> > Text,
    >> > Date. There can be multiple records in the second table that have the
    >> > same
    >> > ID Number with different values in the Text and Date fields.
    >> >
    >> > I want the user to enter a start and end date and then be able to print
    >> > a
    >> > report that would look similar to the following:
    >> >
    >> > ID Number
    >> > Field Name Date (From the first table)
    >> > Field Name Date (From the first table)
    >> > Field Name Date (From the first table)
    >> > Text Date (From the second table)
    >> > Text Date (From the second table)
    >> >
    >> > Each ID Number would appear in the report only if there were dates in
    >> > it's
    >> > fields in table one that had dates equal to or between the start and
    >> > end
    >> > dates entered and/or dates in table two that met the same criteria. If
    >> > a
    >> > field from table one or from table two did not meet the criteria
    >> > entered, it
    >> > should not appear in the report.
    >> >
    >> > Is this possible? If so, how? Any help will be greatly appreciated.

    >>
    >>
     

Share This Page