Troublesome Reporting Glitches

[Fstetson]

I am having some trouble figuring out why my report (with multiple sub reports) is repeating whenever I exceed mor that one item entered into the associated form. Specifically, I am using an internal use only form to input five key components to a contract; Work Description, Misc Job Expenses, Job Fees, Job Material and Job Time. I can enter multiple lines into the Work Descriptions, and all is fine (See attach Screen_01), my report prints a single summary of the descriptions. Same is true as long as I only enter one line into any of the remaining four categories. The trouble comes when I enter more than one line in any of those four categories (see attach Screen_02), the report glitches and repeats all of the sub reports for as many lines I may add...ex: add two repeats everything twice, three, thrice, so on and son on! (See attach Screen_03).



Not sure what could be doing this and seeking your help in investigation this. If you need more information or screenshots, please ask and I will post accordingly. Any help in focusing my efforts would be greatly appreciated.

[orange]

fstetson,

Is this something new? Did the report or part of it work previously?
Did this logic and reporting work in your development/testing?
Sounds like sorting/grouping issue, but readers will need to see more of your design to offer focused advice.

[Gicu]

Open the report's record source and see if you get duplicates when the report itself shows them; if yes than that is your cause. Usually with your setup you use a one table record source for the main report (lets say tblJobs) and link the subreports via the PK (JobID)\FKs (in tblJobDescription, tblJobExpenses...).
Cheers,

[Fstetson]

Is this something new? No

Did the report or part of it work previously? No, this has been an ongoing issue. I am just now narrowed it to impacts I listed in original post

Did this logic and reporting work in your development/testing? I did not test for it

Sounds like sorting/grouping issue, but readers will need to see more of your design to offer focused advice. What would be the best to see? I do not want to bombard with too much.

[Fstetson]

All of the sub reports, and their relative queries display as desired.

[Welshgasman]

Cartesian join?

[Gicu]

Not the subreports but the main report. Can you show us the report in design view and its record source? How do you link the report\subreport(s)?

[Fstetson]

@Vlad

They are linked by JobID

[Fstetson]

Here is the relationship table, all the sub queries are using option two of Join Properties

[Fstetson]

Here is the report SQL Statement, hope these answers all!


SELECT IIf(IsNull([CustomerFirstName2]),[Tbl_WorkOrders]![JobCustomer],[Tbl_WorkOrders]![JobCustomer] & " & " & [CustomerFirstName2] & " " & [CustomerLastName2]) AS JobCustomer, tbl_Workorders.JobID, [Tbl_Customers]![Customer] AS Customer, IIf(IsNull([JobCustomer]),[BillingContactCompanyName],[BillingContactFirstName] & " " & [BillingContactLastName]) AS BillingContactOld, Len([BillingContact]) AS BCLength, Trim(IIf(IsNull([BillingContactCompanyName]),[BillingContactFirstName] & " " & [BillingContactLastName],[BillingContactCompanyName])) AS BillingContact, tbl_Workorders.JobContact, [CustomerFirstName2] & " " & [CustomerLastName2] AS 2ndContact, tbl_Customers.CustomerFirstName2, tbl_Customers.CustomerLastName2, IIf(IsNull([customer]),"",[customer] & " " & [customerlastname]) AS Cust, tbl_Customers.EmailOne, tbl_Customers.EmailTwo, tbl_Customers.BillingHouseNumber, tbl_Customers.BillingStreet, tbl_Customers.BillingAddress, tbl_Customers.BillingCity, tbl_Customers.BillingState, tbl_Customers.BillingZip, tbl_Customers.BillingPhone1Type, tbl_Customers.BillingPhone1, tbl_Customers.BillingExt1, tbl_Customers.BillingPhone2Type, tbl_Customers.BillingPhone2, tbl_Customers.BillingExt2, tbl_Customers.BillingPhone3Type, tbl_Customers.BillingPhone3, tbl_Customers.BillingExt3, tbl_Customers.BillingPhone4Type, tbl_Customers.BillingExt4, tbl_Customers.BillingFax, "Thank you, " & [estimator] AS Thanks, tbl_Customers.CustomerLastName, tbl_Workorders.JobHouseNumber, tbl_Workorders.JobStreet, tbl_Workorders.JobSt, tbl_Workorders.JobAddress, tbl_Workorders.JobCity, tbl_Workorders.JobZip, tbl_Workorders.JobPhone1Type, tbl_Workorders.JobPhone2Type, tbl_Workorders.JobPhone3Type, tbl_Workorders.JobPhone4Type, tbl_Workorders.JobContactPhone4, tbl_Workorders.JobContactPhone1, tbl_Workorders.JobContactPhone2, tbl_Workorders.JobContactPhone3, tbl_Workorders.JobStart, tbl_Workorders.CreateDate, tbl_Workorders.Estimator, tbl_Workorders.JobSiteEmployee, tbl_Workorders.JobReference, tbl_Workorders.Notes, Q_113_d_SumPreInvoiceJobMisc.JobMiscCost, Q_113_a_SumPreInvoiceJobTime.JobTimeRate, Q_113_b_SumPreInvoiceJobFees.JobFeePrice, Q_113_c_SumPreInvoiceJobMaterial.JobMaterialTotal, Q_113_e_SumPreInvoiceJobContract.JobContractAmount , tbl_Workorders.JobPaymentDown, tbl_Workorders.JobPaymentDownNote, tbl_Workorders.JobPaymentDownNoteExt, tbl_Workorders.JobPayment1, tbl_Workorders.JobPayment1Note, tbl_Workorders.JobPayment1NoteExt, tbl_Workorders.JobPayment2, tbl_Workorders.JobPayment2Note, tbl_Workorders.JobPayment2NoteExt, tbl_Workorders.JobPaymentFinal, tbl_Workorders.JobPaymentFinalNote, tbl_Workorders.JobPaymentFinalNoteExt
FROM tbl_Customers INNER JOIN (((((tbl_Workorders LEFT JOIN Q_113_a_SumPreInvoiceJobTime ON tbl_Workorders.JobID = Q_113_a_SumPreInvoiceJobTime.JobID) LEFT JOIN Q_113_b_SumPreInvoiceJobFees ON tbl_Workorders.JobID = Q_113_b_SumPreInvoiceJobFees.JobID) LEFT JOIN Q_113_c_SumPreInvoiceJobMaterial ON tbl_Workorders.JobID = Q_113_c_SumPreInvoiceJobMaterial.JobID) LEFT JOIN Q_113_e_SumPreInvoiceJobContract ON tbl_Workorders.JobID = Q_113_e_SumPreInvoiceJobContract.JobID) LEFT JOIN Q_113_d_SumPreInvoiceJobMisc ON tbl_Workorders.JobID = Q_113_d_SumPreInvoiceJobMisc.JobID) ON tbl_Customers.CustomerID = tbl_Workorders.CustomerID
WHERE (((tbl_Workorders.JobID)=[Forms]![Frm_Customer_WorkOrders]![JobID]));

WHERE (((tbl_Workorders.JobID)=[Forms]![Frm_Customer_WorkOrders]![JobID]));

[orange]

Did this logic and reporting work in your development/testing? I did not test for it

Do you have a test database that you can add a few sample records and post same for readers to review?
All that is needed is a few test records----Porky Pig, CapitalCity, JobOne etc nothing personal/confidential. Readers will want to see the structures, recordsources and any ancillary logic/code.

[Gicu]

So if you open Q_113_g_JobSheetMain do you get two records if you have two entries in one of the 4 troublesome components\tables? My initial point was that you do not need all this info in the record source for the main report, you only need the fields in the report header and footer. Remove all the left-joined queries and use them individually as record sources for their corresponding subreport.

Cheers,

[orange]

Formatted sqlvia Poor SQL)

SELECT IIf(IsNull([CustomerFirstName2]), [Tbl_WorkOrders] ! [JobCustomer], [Tbl_WorkOrders] ! [JobCustomer] & " & " & [CustomerFirstName2] & " " & [CustomerLastName2]) AS JobCustomer
	,tbl_Workorders.JobID
	,[Tbl_Customers] ! [Customer] AS Customer
	,IIf(IsNull([JobCustomer]), [BillingContactCompanyName], [BillingContactFirstName] & " " & [BillingContactLastName]) AS BillingContactOld
	,Len([BillingContact]) AS BCLength
	,Trim(IIf(IsNull([BillingContactCompanyName]), [BillingContactFirstName] & " " & [BillingContactLastName], [BillingContactCompanyName])) AS BillingContact
	,tbl_Workorders.JobContact
	,[CustomerFirstName2] & " " & [CustomerLastName2] AS 2 ndContact
	,tbl_Customers.CustomerFirstName2
	,tbl_Customers.CustomerLastName2
	,IIf(IsNull([customer]), "", [customer] & " " & [customerlastname]) AS Cust
	,tbl_Customers.EmailOne
	,tbl_Customers.EmailTwo
	,tbl_Customers.BillingHouseNumber
	,tbl_Customers.BillingStreet
	,tbl_Customers.BillingAddress
	,tbl_Customers.BillingCity
	,tbl_Customers.BillingState
	,tbl_Customers.BillingZip
	,tbl_Customers.BillingPhone1Type
	,tbl_Customers.BillingPhone1
	,tbl_Customers.BillingExt1
	,tbl_Customers.BillingPhone2Type
	,tbl_Customers.BillingPhone2
	,tbl_Customers.BillingExt2
	,tbl_Customers.BillingPhone3Type
	,tbl_Customers.BillingPhone3
	,tbl_Customers.BillingExt3
	,tbl_Customers.BillingPhone4Type
	,tbl_Customers.BillingExt4
	,tbl_Customers.BillingFax
	,"Thank you, " & [estimator] AS Thanks
	,"6820 South Ave. Saint Paul, MN 55344" AS MHStreet
	,"612-328-4789 -- 952-345-0124 -- 651-366-4785" AS MHPhone
	,tbl_Customers.CustomerLastName
	,tbl_Workorders.JobHouseNumber
	,tbl_Workorders.JobStreet
	,tbl_Workorders.JobSt
	,tbl_Workorders.JobAddress
	,tbl_Workorders.JobCity
	,tbl_Workorders.JobZip
	,tbl_Workorders.JobPhone1Type
	,tbl_Workorders.JobPhone2Type
	,tbl_Workorders.JobPhone3Type
	,tbl_Workorders.JobPhone4Type
	,tbl_Workorders.JobContactPhone4
	,tbl_Workorders.JobContactPhone1
	,tbl_Workorders.JobContactPhone2
	,tbl_Workorders.JobContactPhone3
	,tbl_Workorders.JobStart
	,tbl_Workorders.CreateDate
	,tbl_Workorders.Estimator
	,tbl_Workorders.JobSiteEmployee
	,tbl_Workorders.JobReference
	,tbl_Workorders.Notes
	,Q_113_d_SumPreInvoiceJobMisc.JobMiscCost
	,Q_113_a_SumPreInvoiceJobTime.JobTimeRate
	,Q_113_b_SumPreInvoiceJobFees.JobFeePrice
	,Q_113_c_SumPreInvoiceJobMaterial.JobMaterialTotal
	,Q_113_e_SumPreInvoiceJobContract.JobContractAmount
	,tbl_Workorders.JobPaymentDown
	,tbl_Workorders.JobPaymentDownNote
	,tbl_Workorders.JobPaymentDownNoteExt
	,tbl_Workorders.JobPayment1
	,tbl_Workorders.JobPayment1Note
	,tbl_Workorders.JobPayment1NoteExt
	,tbl_Workorders.JobPayment2
	,tbl_Workorders.JobPayment2Note
	,tbl_Workorders.JobPayment2NoteExt
	,tbl_Workorders.JobPaymentFinal
	,tbl_Workorders.JobPaymentFinalNote
	,tbl_Workorders.JobPaymentFinalNoteExt
FROM tbl_Customers
INNER JOIN (
	(
		(
			(
				(
					tbl_Workorders LEFT JOIN Q_113_a_SumPreInvoiceJobTime ON tbl_Workorders.JobID = Q_113_a_SumPreInvoiceJobTime.JobID
					) LEFT JOIN Q_113_b_SumPreInvoiceJobFees ON tbl_Workorders.JobID = Q_113_b_SumPreInvoiceJobFees.JobID
				) LEFT JOIN Q_113_c_SumPreInvoiceJobMaterial ON tbl_Workorders.JobID = Q_113_c_SumPreInvoiceJobMaterial.JobID
			) LEFT JOIN Q_113_e_SumPreInvoiceJobContract ON tbl_Workorders.JobID = Q_113_e_SumPreInvoiceJobContract.JobID
		) LEFT JOIN Q_113_d_SumPreInvoiceJobMisc ON tbl_Workorders.JobID = Q_113_d_SumPreInvoiceJobMisc.JobID
	) ON tbl_Customers.CustomerID = tbl_Workorders.CustomerID
WHERE (((tbl_Workorders.JobID) = [Forms] ! [Frm_Customer_WorkOrders] ! [JobID]));

[Fstetson]

I have to correct myself, the query return for the SQL above does show three records, not one.

[Gicu]

So there you have it, the report will repeat the details section three times... Please follow the suggestions in post #12 and "report" back the results.

Cheers,