Query in Subreport help (1 Viewer)

[Jomat]

Hello. I hope someone can help.

I have a main report and a subreport. I'm having some problems with the query.
1. two tables - employees_tbl and employees_records_tbl
2. 1 query - QRY_employees_by_employees_Begindaterange
3. 2 reports - RPT_Employees_Main and RPT_Employees_records_Bydatarange(subreport)

The report works find with the main report that displays the subreport without any queries.
My problem is when I change the query on the subreport, i get a listing of each employees multiple times with each records of the employees records.
The query that I am using on the subreport is on a field called Exclusion_Begin_Date :

Between [Enter the start date search of the Exclusion Begin Date field?] And [Enter the end date search for the Exclusion Begin Date field?]

I linked the main report and the subreport by ID from the main report to LinkedID on the subreport.
I like to see just one employee general info records and below the each record, have the list of employee records from the records table that are between the user input dates.

I hope I'm making sense here. Thank you for any help.


The way it looks now.

Nick Joma Cage
Type Initiated Begin Date End Date
1 Excluded Supervisor 01/01/23 01/02/23
Nick Joma Cage
Type Initiated Begin Date End Date
2 Excluded Manager 01/02/23 01/04/23
Nick Joma Cage
Type Initiated Begin Date End Date
1 Excluded Supervisor 01/06/23 01/09/23
Amy G Edwards
Type Initiated Begin Date End Date
3 Excluded Supervisor 01/04/23 01/08/23
Amy G Edwards
Type Initiated Begin Date End Date
1 Excluded Manager 01/02/23 01/05/23

I like it to look like this.

Nick Joma Cage
Type Initiated Begin Date End Date
1 Excluded Supervisor 01/01/23 01/02/23
2 Excluded Manager 01/02/23 01/04/23
1 Excluded Supervisor 01/06/23 01/09/23
Amy G Edwards
Type Initiated Begin Date End Date
3 Excluded Supervisor 01/04/23 01/08/23
1 Excluded Manager 01/02/23 01/05/23

 

[CarlettoFed]

Without a sample file it will be difficult to help you.

 

[Jomat]

Hi I added a sample db. There was too much data in my original db.
Thanks for any help.

 

[plog]

No subreport necessary. You need to group your report properly.

First, make a backup copy of main. Then open main and create a grouping based on the ID field. In that groups header move all the data about the employee to it (everything but the subreport). The only thing left in the detail section should be the subreport--delete it. Open up the sub report in design view and copy everything, then paste it into the detail section of the main report. Your report is now as you like it.

On another note, why are you grouping the query? Why is employees_tbl LEFT JOINed to it? Is it possible to have recrods in the _records table but not in the employees table?

 

[CarlettoFed]

In the RPT_Employees_Main report you only need to set the employees_tbl table as the Record Source.

 

[Jomat]

IT WORKS!

plog ... My left join is probably in error. I thought it would attach my data from the two tables together.
I've tried linking the tables in the relationship, renamed the ID fields to unique names that both table had and a bunch other ideas.
I've followed your step exactly. It works now. It looks exactly what I wanted to accomplish

I was writing the following then went to check my other reports for an example. I've noticed that my other reports were all grouped differently then the report that was giving me issues.

>>>>What i've noticed was that this is the only report that I am having issues with. After converting it from the old access 2000 format and 32 bit over to the newer access 2019 64 bit version. I noticed that the old reports added different linking properties to my original linking properties. Ex. I normally just link ID from one table to LinkedID on another report. Now I noticed that for some reports it's ID to LinkedID and then it added other properties that linked between the two tables. ......<<<<<

I stopped typing when I notice the grouping on the other reports.

My main report pulls from the employee table and my subreport calls on the query thus asking for the date inputs when the main report opens.

Thank you so much.

On another thought, I don't remember exactly but I could of sworn that you can simply drag a subreport into a main report, link the fields and place your query or call it from a macro on open. I am probably wrong though.

For future references to others. I've included the working version of it. The new working report is called RPT_Employees_Main_Grouping .

Thank you so much again and have a wonderful day.
You are awesome and I am grateful. I humbly bow with head and arms stretched down and forward. )

 

[Jomat]

I need to make a correction.
On the working report. I used the query data for the main report. There is no longer a sub report.
In the previous post I stated that I pointed the main form to the table, that was incorrect.
Thanks.

 

[CarlettoFed]

Try like this

 

[Jomat]

Awsome. Thank you I will try that out.

 

[Pat Hartman]

My problem is when I change the query on the subreport, i get a listing of each employees multiple times with each records of the employees records.

You CANNOT include a join to the table/query that is the RecordSource for the subreport as the recordSource for the main report. That gives you one main report record for each subreport record. The RecordSource for the main report should only include the parent table (and possibly lookup tables). The RecordSource for the sub report should only include the child table (and possibly lookup tables)

 

[spaLOGICng]

The one mistake that I see often when dev's are creating reports, they seem to worry too much about the underlying record source and not enough about the design features of Access Reports.

First tip, save the Report SELECT Statement as a Query if you have not already done so. I makes managing an application a lot easier and renders the data source reusable for other needs and reducing duplicate objects.

As for the report, you need to invoke Grouping where you will add the field that populates "Nick Joma Cage". You do not want to place the Column Header Labels there. Leave them on the Page Header.

You can do all of this in a single report. No sus report is needed,

 

[Pat Hartman]

While it is true that you can use grouping to create a report by using a query that joins the parent table to the child table, you will have trouble with counts and summaries using that method. If you need accurate counts and summaries for the main report, use a mainreport with a subreport.

 

[spaLOGICng]

While it is true that you can use grouping to create a report by using a query that joins the parent table to the child table, you will have trouble with counts and summaries using that method. If you need accurate counts and summaries for the main report, use a mainreport with a subreport.

I think you are confused. Just use either the Group Header or Group Footer to SUM() or COUNT(). For example, the Control Source to a text box in either section expressed as =Sum([QuantityField]).

However, I do not see that you are summing or counting anything in the original post.

Nevertheless, you have a very simple report and it can all be done in a single report with no sub report.

 

[Pat Hartman]

I am not confused. Try it. Orders for example. Shipping is generally assigned at the Order level. So, you join Order and OrderDetails and every row contains a shipping amount. You can display the shipping amount for the order but you can't sum it. You also can't count the number of distinct orders.

I was also not suggesting that a single report with breaks would not work in this particular case. But since the case was ill defined, I posted a case when it wouldn't work.

Also, depending on the report, the headers for detail columns might work better in the group section rather than the page section. Use the section that looks best to the eye.

 

[spaLOGICng]

Here I used the Northwind Sample Database as an example.

I created a Query named "qryOrders_for_Report_Sample" with the following SELECT Statement. I added one Expression Column for Total Price.

SELECT
Orders.[Order ID]
, Orders.[Order Date]
, [Order Details].[Product ID]
, [Order Details].Quantity
, [Order Details].[Unit Price]
, [Quantity]*[Unit Price] AS Total_Price
FROM
Orders
INNER JOIN
[Order Details]
ON Orders.[Order ID] = [Order Details].[Order ID];

Next I created a Report named "rptOrders_Sample"

I added a Group for Order ID and set both Header and Foot to display.

I added the Order ID and Order Date to the Header Section and the =Sum([Total_Price]) for Total Price in the Footer Section.

Print Preview; so you can see that the Total Prices does sum as needed.

Hopefully this will help.

 

[Pat Hartman]

You didn't understand the point I made. Read my post again. The problem arises when the "parent" has data that you want to sum or count or average.

 

[Pat Hartman]

Sorry, I thought you were talking to me. You are conflating my comments with the OP's problem. I will go away now and leave you to it.

 

[spaLOGICng]

Sorry, I thought you were talking to me. You are conflating my comments with the OP's problem. I will go away now and leave you to it.

Sorry sir. Got twisted on who was replying.