Qry header table records based on form table field without duplicates

[brharrii]

This sql statement is returning the results that I want, but it is returning each record multiple times based on how many records there are in the subTable. How can i set this up so it only returns only one instance of each record?

SELECT tblReportHeader.ReportID, tblReportHeader.ReportInvoiceNumber, tblReportHeader.ReportTestDate, tblReportHeader.ReportSampleDate, tblReportHeader.ReportSampleTime, tblReportResults.ReportResultsType
FROM tblReportHeader LEFT JOIN tblReportResults ON tblReportHeader.ReportInvoiceNumber = tblReportResults.ReportResultsInvoice
WHERE (((tblReportResults.ReportResultsType)=1));

 

[plog]

Can you provide some sample data from that query and then what record's data you want returned?

 

[brharrii]

This is what was returned. Essentially each invoice number is reported for every line item associated with it. Invoice 48595 for example is returned 6 times. I only want each invoice instace returned once.​

Query1
ReportID
ReportInvoiceNumber
ReportTestDate
ReportSampleDate
ReportSampleTime
ReportResultsType


65---48595---1/29/2012----1/20/2012-----6:30:00 AM---1
65---48595---1/29/2012----1/20/2012-----6:30:00 AM---1
65---48595---1/29/2012----1/20/2012-----6:30:00 AM---1
65---48595---1/29/2012----1/20/2012-----6:30:00 AM---1
65---48595---1/29/2012----1/20/2012-----6:30:00 AM---1
65---48595---1/29/2012----1/20/2012-----6:30:00 AM---1
79---48776---2/23/2012----2/22/2012-----6:30:00 AM---1
79---48776---2/23/2012----2/22/2012-----6:30:00 AM---1
79---48776---2/23/2012----2/22/2012-----6:30:00 AM---1
67---49075---3/24/2012----3/19/2012-----6:15:00 AM---1
67---49075---3/24/2012----3/19/2012-----6:15:00 AM---1
67---49075---3/24/2012----3/19/2012-----6:15:00 AM---1
67---49075---3/24/2012----3/19/2012-----6:15:00 AM---1
68---49643---6/15/2012----5/29/2012-----6:15:00 AM---1
68---49643---6/15/2012----5/29/2012-----6:15:00 AM---1
68---49643---6/15/2012----5/29/2012-----6:15:00 AM---1
68---49643---6/15/2012----5/29/2012-----6:15:00 AM---1
69---50287---8/5/2012------7/27/2012-----7:30:00 AM---1
69---50287---8/5/2012------7/27/2012-----7:30:00 AM---1
69---50287---8/5/2012------7/27/2012-----7:30:00 AM---1
69---50287---8/5/2012------7/27/2012-----7:30:00 AM---1
69---50287---8/5/2012------7/27/2012-----7:30:00 AM---1
69---50287---8/5/2012------7/27/2012-----7:30:00 AM---1
70---51089---10/29/2012---10/18/2012---8:30:00 AM---1
70---51089---10/29/2012---10/18/2012---8:30:00 AM---1
70---51089---10/29/2012---10/18/2012---8:30:00 AM---1
70---51089---10/29/2012---10/18/2012---8:30:00 AM---1

 

[liddlem]

This usually indicates that query is pointing to the invoice DETAIL, rather than to the invoice HEADER record.
Check that the query is looking at the correct source.

 

[plog]

The simple answer is to make it an aggregate query and GROUP BY every field.

 

[brharrii]

This usually indicates that query is pointing to the invoice DETAIL, rather than to the invoice HEADER record.
Check that the query is looking at the correct source.

The query is referencing the detail information to verify the [ReportResultsType]. This is important because headers may be associated with detail entries of more than 1 type. I believe that this is what is causing the problem for me. I need to be able to sort through the report headers one at a time so that each record has its associated detail items of a specific type, whilest ignoring potential other types.

For example

Invoice 22456 - has two sub-reports on it:

8 lines of allergen report

and

4 lines of microbial testing report

In the query I've provided, the [ReportResultsType] must = 1 so that only the 8 lines of allergen report are displayed under the report header. Moving to the next record pulls up the next invoice with all of its associated allergen lines. There will be an option on another form to change the [ReportResultsType] to look at all of the microbial testing, etc.

 

[brharrii]

The simple answer is to make it an aggregate query and GROUP BY every field.

This had the desired effect, but now since I use that form as the Record Source for my form I'm unable to create new records. Do you know if there is a way around this?

 

[plog]

You shouldn't be basing a form off of a query. Use the underlying table.

 

[brharrii]

Hey plog,

thanks for the suggestion. I was using the query to prevent the Invoices that don't have a corresponding detail lines classified as report type 1, from showing up. types 2, 3, 4, and 5 will all have their own forms since the fields vary between each of them.

In other words, my header is the same for all subforms. The subforms are all slightly to significantly different than each other. The way I envisioned handling this was to have a form for each subform type and filter the report headers based on whether or not they are assigned to a subform detail line of the corresponding report detail type. If they are not, then they wouldn't show up on the given form.

Maybe that isn't the best way to handle it though?