[AccessD] Multiple Criteria Query

Keith Williamson Kwilliamson at RTKL.com
Fri Mar 20 17:16:51 CDT 2009


Here is the SQL Criteria that gives me the 287 records.


SELECT dbo_LedgerAR.WBS1, dbo_LedgerAR.Invoice, dbo_LedgerAR.TransType,
Sum(IIf([account]="261.02",-[amountbillingcurrency],[amountbillingcurren
cy])) AS Amount,
IIf([account]="120.09","Retainage",IIf(Left([desc2],4)="0708","Write-Off
","Regular")) AS Type
FROM qryAR200812 RIGHT JOIN dbo_LedgerAR ON (qryAR200812.[Project#] =
dbo_LedgerAR.WBS1) AND (qryAR200812.[Invoice#] = dbo_LedgerAR.Invoice)
WHERE (((Mid([Invoice],4,4))="8888" Or (Mid([Invoice],4,4))="9999" Or
(Mid([Invoice],4,4))="0999") AND ((dbo_LedgerAR.Period)>[Enter Begin
Period?] And (dbo_LedgerAR.Period)<=[Enter End Period?]) AND
((qryAR200812.[Invoice#]) Is Null))
GROUP BY dbo_LedgerAR.WBS1, dbo_LedgerAR.Invoice,
dbo_LedgerAR.TransType,
IIf([account]="120.09","Retainage",IIf(Left([desc2],4)="0708","Write-Off
","Regular")), Left([wbs1],2)
HAVING (((dbo_LedgerAR.TransType)="CR") AND
((Sum(IIf([account]="261.02",-[amountbillingcurrency],[amountbillingcurr
ency])))<>0) AND
((IIf([account]="120.09","Retainage",IIf(Left([desc2],4)="0708","Write-O
ff","Regular")))="regular") AND ((Left([wbs1],2))<>"RI" And
(Left([wbs1],2))<>"SH" And (Left([wbs1],2))<>"TK" And
(Left([wbs1],2))<>"UK" And (Left([wbs1],2))<>"AS" And
(Left([wbs1],2))<>"IN" And (Left([wbs1],2))<>"MA"));


And the statement that returns only one record:

SELECT dbo_LedgerAR.WBS1, dbo_LedgerAR.Invoice, dbo_LedgerAR.TransType,
Sum(IIf([account]="261.02",-[amountbillingcurrency],[amountbillingcurren
cy])) AS Amount,
IIf([account]="120.09","Retainage",IIf(Left([desc2],4)="0708","Write-Off
","Regular")) AS Type
FROM qryAR200812 RIGHT JOIN dbo_LedgerAR ON (qryAR200812.[Project#] =
dbo_LedgerAR.WBS1) AND (qryAR200812.[Invoice#] = dbo_LedgerAR.Invoice)
WHERE (((dbo_LedgerAR.Period)>[Enter Begin Period?] And
(dbo_LedgerAR.Period)<=[Enter End Period?]) AND
((qryAR200812.[Invoice#]) Is Null))
GROUP BY dbo_LedgerAR.WBS1, dbo_LedgerAR.Invoice,
dbo_LedgerAR.TransType,
IIf([account]="120.09","Retainage",IIf(Left([desc2],4)="0708","Write-Off
","Regular")), Left([wbs1],2)
HAVING (((dbo_LedgerAR.WBS1)="88-99999.09") AND
((dbo_LedgerAR.TransType)="CR") AND
((Sum(IIf([account]="261.02",-[amountbillingcurrency],[amountbillingcurr
ency])))<>0) AND
((IIf([account]="120.09","Retainage",IIf(Left([desc2],4)="0708","Write-O
ff","Regular")))="regular") AND ((Left([wbs1],2))<>"RI" And
(Left([wbs1],2))<>"SH" And (Left([wbs1],2))<>"TK" And
(Left([wbs1],2))<>"UK" And (Left([wbs1],2))<>"AS" And
(Left([wbs1],2))<>"IN" And (Left([wbs1],2))<>"MA"));

>>>>> You have to make sure everything from line one is also on line
two.<<<<<

That's my dilemma.....the first criteria is for ALL projects, who's
invoices meet certain criteris.  The second set is for ONE project, all
invoices.

Am I barking up a wrong tree?  Should I make another query and join it?

Thanks,


Keith Williamson | Associate, Asst. Controller | kwilliamson at rtkl.com 

RTKL Associates Inc. | 901 South Bond St. | Baltimore, MD  21231

410.537.6098 Direct | 410.276.4232 Fax | www.rtkl.com


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David McAfee
Sent: Friday, March 20, 2009 6:05 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Multiple Criteria Query

I take it by "line two" that you are talking about the QBE grid?

Can you copy the SQL statement for the query with just one line and also
copy the SQL statement when you have both lines of creieria?

Across the QBE grid is "AND"
Down the rows is "OR"

You have to make sure everything from line one is also on line two.

On Fri, Mar 20, 2009 at 2:53 PM, Keith Williamson
<Kwilliamson at rtkl.com>wrote:

> Okay...am I being an absolute idiot.....or what am I doing wrong
here??
>
> I have a query that I want to return all records that meet criteria on
> line ONE (in msquery) ...OR....all the records that meet criteria on
> line TWO.
>
> As long as I have the criteria (about 6 fields with criteria filters)
on
> only one line....I get either set of criteria to work.  BUT...once I
add
> the second set of criteria, on line two......I wind up with a TON of
> records....much more than either set alone.
>
> To try to elaborate.....I have this query where the first line of
> criteria is for all AR activity where TransType="CR", AccountType =
> "Regular",Amount <>0, Period is >2007....Invoice Number is All,
Project#
> is All.
>
> It gives me 287 records.  Then...I want to add a second line of
criteria
> where I am wanting it to return all TransType="CR", for Project#
> ="88-99999.09"
>
> When I add that second criteria....I get back 13,000 records.
>
> ????? Am I just being stupid....or shouldn't I be able to get the
> original 287 records...AND....the ONE record that satisfies the second
> line of criteria...for a total of 288 records???
>
> This is driving me crazy.
>
> Thanks,
>
> Keith Williamson | Associate, Asst. Controller | kwilliamson at rtkl.com
>
> RTKL Associates Inc. | 901 South Bond St. | Baltimore, MD  21231
>
> 410.537.6098 Direct | 410.276.4232 Fax | www.rtkl.com
>
>
------------------------------------------------------------------------
--------
> The information contained in this communication is confidential, may
be
> privileged and is intended for the exclusive use of the above named
> addressee(s). If you are not the intended recipient(s), you are
expressly
> prohibited from copying, distributing, disseminating, or in any other
way
> using any of the information contained within this communication. If
you
> have received this communication in error, please contact the sender
by
> telephone at (410) 537-6000 or by response via e-mail and permanently
delete
> the original email and any copies.
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com





More information about the AccessD mailing list