How do I limit Report/WSubReport Data? 1

[MikeJess]

Hi All,

I am fairly new at this, so please hang with me. Details first -

2 tables:
Table1 has employee name and ssn

Table2 has emplyee ssn and event dates (Month, Day, & Year), and the event details.

The base report has the employee name, etc.; while the sub-report has the event details. This subreport could have any number of items from 0 to ???.

I am trying to creat reports that would:
(1) limit the report to an individual ~or~
(2) limit the search to a particular month ~or~
(3) a combination of both the above.

To meet criteria 1, I know I have to pass a parameter to the base report for the individual I am looking for. This I can do.
However to meet criteria 2, how do I pass the parameter to the subreport to limit the data?
And to meet criteria 3, how do I pass BOTH parameters - one to the base report and one to the subreport?

This maybe more of a form question, but I thought since the end product is a report I would try here first.

~ Mike ~
"Being second best, I try harder"

 

[randysmid]

Hi Mike,

FIRST, you don't need a subreport to get the information you need. Access reports provides for some great tools to sort and group. So,......

I wrote two FAQ's on how to use a form to send criteria to a report. The first one uses a form and a query to send criteria. This is probably the best one for you to use.
I understand that you are already using filter.

See the following FAQ: faq703-2696

In your case, you will want to go to the query painter, and create ONE query. This will get your master information (from your employee table), AND the detail information. You will use the SSN from your form as the criteria for the query. Your query should also have the table containing the detail information you want, including the date fields. The criteria for your date field will include the two dates from your form. The criteria for that will look like this: Between [Forms]![frmInput]![txtStart] And [Forms]![frmInput]![txtEnd]

Your report will use this query as it's recordsource. HTH,
Randy Smith
California Teachers Association

 

[MikeJess]

Randy,

This will work on a single level report, but I am using a base_report with a sub-report. The sub-report is the one I am having trouble limiting the information from.

When I tried using a single query for both the base_report and the sub-report, I ended up with duplicate information being printed. i.e. I would get five records for 'Smith,Mike' and would have that name repeated five times with all five records under it each time.

What I currently have is a query that gives me the employee info for the base_report and a second query that gives me the data for the sub-report.

The problem - getting the start_date and end_date parameters to the sub-report.

THANKS ~ Mike ~
"Being second best, I try harder"

 

[randysmid]

Hi Mike,
I have also had trouble sending additional criteria to a subreport, and that is why I have mostly switched over to using the query method as I explained in the previous post. With using the date criteria and the "between" statement, I am able to get the reports to come out exactly as I want. HTH,
Randy Smith
California Teachers Association