Query help needed

[seenfresh]

Hi,

I have a query that works fine and populates a list of records and related fields. From this list their is a specific field I am interested in, Line Item ID, which is stored as double. In the query of records I will have a combination of unique Line Item ID's and duplicate Line Item ID's. My goal is for each record to find out if the Line Item ID exists in a data table called "Table1" if it exists more than once I would like the query to exclude this record and all records with this Line Item ID from my query "Query1".

It is an unusual request, but I'm not sure how to accomplish this task. I think I may need to incorporate some VBA once my query populates and loop through the records and delete one's that do not hold true to my above criteria?

Does anyone have any suggestions?

Thanks in advance,

 

[Joe4]

Create another query between your original query and Table1 (joined on your Line Item ID field).

Then add the Line Item ID to the list of fields you are going to return and then add any other field from to the list of fields to return.

Then click on the Totals button (looks like a Sigma) to make this an Aggregate Query. This will add a Totals Row to the query under each field with the words "Group By" under these two fields. Leave "Group By" under the Line Item ID field, but change the Totals row value to "Count" under the other field.

Now, under this "counter" field you have created, add the criteria:
=1
This will only return Line Item IDs that appear exactly once in your query.

If you want to return all the other fields from Table1 for these records, create another query that links this Aggregate Query back to Table1, and simply return all the fields you want.

To learn more about Aggregate Queries, check out Access' built-in help. A simple web search may also return some tutorials and explanations as well.

 

[seenfresh]

Hi Joe

Sorry for the delayed response, I had actually figured out how to accomplish this after posting my issue on the board... I guess sometimes just writing out the problem helps me understand the situation better...

I performed the same concept as yours. I created another query to count the occurrence of each unique line item ID and used this query as a criteria in my main query by including the "count of same line item id's" field in my query setting the criteria = 1.

Thank you for your help and advice, much appreciated.