If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
complex duplicate queries
Hi Tony,
I woke up this morning thinking about how you probably (might) want your queries to be updatable. One method is putting the group by in the Where clause (maybe you already know this). So one method (using the previous example) would be to save the group by query as (say) "qrySameSupNumInvAmt" SELECT t1.[SupplierNumber], t1.[InvoiceAmount] FROM [PaymentTable] AS t1 GROUP BY t1.[SupplierNumber], t1.[InvoiceAmount] HAVING (((Count(*))1) AND ((Min(t1.[InvoiceNumber]))Max([InvoiceNumber])) AND ((Min(t1.[InvoiceDate]))Max([InvoiceDate]))); Then your full (I believe then updatable) query would look similar to: SELECT * FROM [Payment Table] WHERE ((([Payment Table].SupplierNumber)= (SELECT [SupplierNumber] FROM qrySameSupNumInvAmt)) AND (([Payment Table].InvoiceAmount)= (SELECT [InvoiceAmount] FROM qrySameSupNumInvAmt))); I use "*" here for brevity, but it is not hard to bring all the fields into your query in the query designer by double-clicking on the table header (which highlights all fields), then drag-and-drop the selection down to a field row in the grid. Then under SupplierNumber, type in the simple Select wrapped in parentheses in the Criteria row: (SELECT [SupplierNumber] FROM qrySameSupNumInvAmt) and likewise under InvoiceAmount: (SELECT [InvoiceAmount] FROM qrySameSupNumInvAmt) I think you probably get the idea how you could expand this to create "query pairs" for all the 2-same/2-diff cases. Hopefully I have not overlooked something that might be particular to your data. Also....you might investigate the situation where you use "OR" in the group by (and whether that might be meaningful for you). qrySameSupNumInvAmtDiffInvNumORDiffInvDate SELECT t1.SupplierNumber, t1.InvoiceAmount FROM [Payment Table] AS t1 GROUP BY t1.SupplierNumber, t1.InvoiceAmount HAVING (((Count(*))1) AND ((Min(t1.InvoiceNumber))Max([InvoiceNumber]))) OR (((Count(*))1) AND ((Min(t1.InvoiceDate))Max([InvoiceDate]))); You know your data best. BTW...it is considered inappropriate to post messages with attachments...I don't know any helpers that will open these attachments, so besides causing longer downloads, they will be ignored anyway. No offense intended. Good luck, Gary Walter "Gary Walter" wrote "Tony" wrote Hi, I audit payment records for companies and use different tools to find different errors. I used to work for a large company which did these reports but am now on my own. I can find the records I want to find when all fields are the same SUPPLIER NUMBER, INVOICE NUMBER, INVOICE DATE and INVOICE AMOUNT. I have also found a method of grouping and using the count function to find when one field must be different which appears to work fine. What I have a problem with is when TWO fields MUST be the same and TWO fields MUST be different. An example would be where the SUPPLIER NUMBER and INVOICE AMOUNT are the same, but the INVOICE NUMBER and INVOICE DATE MUST be different. This would help to find where a possible purchase or work order was paid as well as the original invoice. I will include the sample data I am using, as well as a sheet that shows the possible duplicate queries I need to use and a sample of the queries that I now have for when there is one field different. Thank you if anyone can help. Hi Tony, Maybe this will help, maybe not.... If I group by "SupplierNumber" and "InvoiceAmount", and return groups whose count 1, then we take care of "those 2 fields the same." You already know this. SELECT t1.SupplierNumber, t1.InvoiceAmount FROM [PaymentTable] AS t1 GROUP BY t1.SupplierNumber, t1.InvoiceAmount HAVING (((Count(*))1)); To test within those groups for different InvoiceNumber and InvoiceDate, all we have to do is compare Min(field) to Max(field). SELECT t1.[SupplierNumber], t1.[InvoiceAmount] FROM [PaymentTable] AS t1 GROUP BY t1.[SupplierNumber], t1.[InvoiceAmount] HAVING (((Count(*))1) AND ((Min(t1.[InvoiceNumber]))Max([InvoiceNumber])) AND ((Min(t1.[InvoiceDate]))Max([InvoiceDate]))); I like "divide and conquer" so I might save this query then join it with original table in a select query (not groupby) to get all needed fields. Or you could join original table to this subquery all in one query. Please respond back if I have misunderstood. Good luck, Gary Walter |
Thread Tools | |
Display Modes | |
|
|