Access Query / Report

[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.

 

[tbleecker]

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.

 

[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.

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.

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.

 

[Larry Linson]

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

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.

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.

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.