A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

complex duplicate queries



 
 
Thread Tools Display Modes
  #1  
Old May 23rd, 2004, 12:19 PM
Gary Walter
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:39 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.