Sub Report Multiplying Issue

Question

Hi,

I have made a report that has a Sub Report showing my Items I have for client. But if I have more than 1 record, the Sub Form shows all the records in that Box, but creates the Same box over and over to the amount of Records from the Items plus showing all the records on each.

How do I make the Sub Form show all the records on the one Box without making a box for every record?

Answer 1

My very strong guess is that you are basing the Subreport on a query joining the Items table to the Clients table; or that you don't have the subreport's Master/Child Link Field set to the ClientID.

The subreport should be just on the child table, not on a query joining the two tables; and the Master and Child Link Fields should be the client ID.

Answer 2

Derp, I knew that *shifty look* :-P

When I try to get it to give me SQL, it wants me to make a Query rather just allow me to see the SQL... is it because I am only using certain fields in the Sub Report rather the full table?

Are you sure there isn't a simpler way to make the Main Report stop Duplicating the Sub Report for how many Records there are on the Items Table?

Answer 3

I couldn't right click in Field list on Report for the Sub Report, but I got the Reports SQL, which I hope has the info you want, if not, Let me know how to get it.

Open the report that constitutes the subreport in design view and copy the Record Source SQL.

Answer 4

the Sub Form shows all the records in that Box, but creates the Same box over and over to the amount of Records from the Items plus showing all the records on each.

I do not understand about a 'box'.   Please post how it looks and how you want it to be.

Post the SQL of the subreport query/Record Source statement.

The Sub Form looks like a Box... so when you read box, think Sub Form.

I couldn't right click in Field list on Report for the Sub Report, but I got the Reports SQL, which I hope has the info you want, if not, Let me know how to get it.

SELECT Customers.CustomerID, Customers.FirstName, Customers.LastName, Customers.Company, Enquiry.JobID, Enquiry.PaymentType, Enquiry.Cash, Enquiry.Card, [Item/Service].ItemCode, [Item/Service].Hours, [Item/Service].TotalNoGST, [Item/Service].GST, Enquiry.Discount, [Item/Service].Total, [Item/Service].Item, Enquiry.DateMade, Enquiry.DateDone

FROM (Customers INNER JOIN Enquiry ON Customers.[CustomerID] = Enquiry.[CustID]) INNER JOIN [Item/Service] ON Enquiry.[JobID] = [Item/Service].[JobID];

So I have my Main Form with a lot of the info in that SQL, Then in the Detail Area I made A Sub Form/Report and used the Wizard to select Some fields from My Items Table. I fixed it all up in design View how I wanted it, I resized the Sub Form to fit around the Texboxes in the Details Area of the Sub Form as it only shows 1 Textbox for each field, when I press Report, Layout Or Print View, I get 5 pages since my Sub Form has 8 Records. pages 2-5 stack up repeats of the Sub Form. The Sub Form is doing what I want in showing all the Records in it, But I ONLY want 1 Sub Form to Show all records inside and Grow if there are multiple Records, not a Sub Form for each Record. I was thinking I might need a OnLoad Macro for the Sub Form to show only once but show all records inside it.

Thanks heaps

Any more questions, don't hesitate to ask...

Answer 5

the Sub Form shows all the records in that Box, but creates the Same box over and over to the amount of Records from the Items plus showing all the records on each.

I do not understand about a 'box'.   Please post how it looks and how you want it to be.

Post the SQL of the subreport query/Record Source statement.