Query with multiple searches on same field

S

Suzanne K

I would like to incorporate an additional piece to the following query - if
there was an invoice for the same BAN and the tblInvoice.[GL Month] = Feb
2005, give the value of tblInvoice.[Payment Due Date] in a new field called
Process Due Date. I'm trying to indicate what the due date would be in the
current month.

SELECT tblInvoice.ID, tblBan.BAN, tblBan.Vendor, tblBan.State,
tblBan.[Invoice Expected Date], tblBan.[Bill Cycle], tblBANStatus.BANSTATUS,
tblAnalyst.AnalystName, tblInvoice.[GL Month], tblInvoice.Status,
tblInvoice.[Payment Due Date], tblInvoice.[Invoice Received Date],
tblInvoice.[Invoice Number], tblInvoice.[Invoice Date]
FROM ((tblBan LEFT JOIN tblBANStatus ON tblBan.BANStatusID =
tblBANStatus.BANSTATUSID) LEFT JOIN tblAnalyst ON tblBan.AnalystID =
tblAnalyst.AnalystID) LEFT JOIN tblInvoice ON tblBan.BanID = tblInvoice.BanID
WHERE (((tblBan.[Bill Cycle]) Between 1 And 7) AND ((tblAnalyst.AnalystName)
Like "susan*") AND ((tblInvoice.[GL Month])="Mar 2005") AND
((tblInvoice.[Invoice Received Date]) Is not Null) AND ((tblInvoice.[Invoice
Number]) Is not Null) AND ((tblInvoice.[Invoice Date]) Is not Null) and
((tblInvoice.Status)="Unprocessed"))

Any help would be appreciated.
 
M

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Perhaps using a subquery in the SELECT clause (untested). The subquery
is a correlated subquery 'cuz it uses data supplied from the main query
(B1.BAN = B.Ban). I prettied it up to make it easier to read.

SELECT I.ID,
B.BAN,
B.Vendor,
B.State,
B.[Invoice Expected Date],
B.[Bill Cycle],
BS.BANSTATUS,
A.AnalystName,
I.[GL Month],
I.Status,
I.[Payment Due Date],
I.[Invoice Received Date],
I.[Invoice Number],
I.[Invoice Date],

(SELECT I1.[Payment Due Date]
FROM tblBan As B1 INNER JOIN tblInvoice AS I1
ON B1.BanID = I1.BanID
WHERE B1.Ban = B.Ban AND I1.[GL Month] = 'Feb 2005')
As [Process Due Date]

FROM ((tblBan As B
LEFT JOIN tblBANStatus As BS
ON B.BANStatusID = BS.BANSTATUSID)
LEFT JOIN tblAnalyst As A
ON B.AnalystID = A.AnalystID)
LEFT JOIN tblInvoice As I
ON B.BanID = I.BanID

WHERE B.[Bill Cycle] Between 1 And 7
AND A.AnalystName Like "susan*"
AND I.[GL Month]="Mar 2005"
AND I.[Invoice Received Date] Is not Null
AND I.[Invoice Number] Is not Null
AND I.[Invoice Date] Is not Null
AND I.Status="Unprocessed"

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQj5D+oechKqOuFEgEQKaAQCgxckBx7tVc6IAz1OjjQqUd+6gmwYAoLjz
z7bRL3cVWzKC2Z8hu1APxBc0
=ZrmK
-----END PGP SIGNATURE-----
 
S

Suzanne K

Thanks for the assistance - I'll give it a try.

Have a good week,

Suzanne

MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Perhaps using a subquery in the SELECT clause (untested). The subquery
is a correlated subquery 'cuz it uses data supplied from the main query
(B1.BAN = B.Ban). I prettied it up to make it easier to read.

SELECT I.ID,
B.BAN,
B.Vendor,
B.State,
B.[Invoice Expected Date],
B.[Bill Cycle],
BS.BANSTATUS,
A.AnalystName,
I.[GL Month],
I.Status,
I.[Payment Due Date],
I.[Invoice Received Date],
I.[Invoice Number],
I.[Invoice Date],

(SELECT I1.[Payment Due Date]
FROM tblBan As B1 INNER JOIN tblInvoice AS I1
ON B1.BanID = I1.BanID
WHERE B1.Ban = B.Ban AND I1.[GL Month] = 'Feb 2005')
As [Process Due Date]

FROM ((tblBan As B
LEFT JOIN tblBANStatus As BS
ON B.BANStatusID = BS.BANSTATUSID)
LEFT JOIN tblAnalyst As A
ON B.AnalystID = A.AnalystID)
LEFT JOIN tblInvoice As I
ON B.BanID = I.BanID

WHERE B.[Bill Cycle] Between 1 And 7
AND A.AnalystName Like "susan*"
AND I.[GL Month]="Mar 2005"
AND I.[Invoice Received Date] Is not Null
AND I.[Invoice Number] Is not Null
AND I.[Invoice Date] Is not Null
AND I.Status="Unprocessed"

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQj5D+oechKqOuFEgEQKaAQCgxckBx7tVc6IAz1OjjQqUd+6gmwYAoLjz
z7bRL3cVWzKC2Z8hu1APxBc0
=ZrmK
-----END PGP SIGNATURE-----


Suzanne said:
I would like to incorporate an additional piece to the following query - if
there was an invoice for the same BAN and the tblInvoice.[GL Month] = Feb
2005, give the value of tblInvoice.[Payment Due Date] in a new field called
Process Due Date. I'm trying to indicate what the due date would be in the
current month.

SELECT tblInvoice.ID, tblBan.BAN, tblBan.Vendor, tblBan.State,
tblBan.[Invoice Expected Date], tblBan.[Bill Cycle], tblBANStatus.BANSTATUS,
tblAnalyst.AnalystName, tblInvoice.[GL Month], tblInvoice.Status,
tblInvoice.[Payment Due Date], tblInvoice.[Invoice Received Date],
tblInvoice.[Invoice Number], tblInvoice.[Invoice Date]
FROM ((tblBan LEFT JOIN tblBANStatus ON tblBan.BANStatusID =
tblBANStatus.BANSTATUSID) LEFT JOIN tblAnalyst ON tblBan.AnalystID =
tblAnalyst.AnalystID) LEFT JOIN tblInvoice ON tblBan.BanID = tblInvoice.BanID
WHERE (((tblBan.[Bill Cycle]) Between 1 And 7) AND ((tblAnalyst.AnalystName)
Like "susan*") AND ((tblInvoice.[GL Month])="Mar 2005") AND
((tblInvoice.[Invoice Received Date]) Is not Null) AND ((tblInvoice.[Invoice
Number]) Is not Null) AND ((tblInvoice.[Invoice Date]) Is not Null) and
((tblInvoice.Status)="Unprocessed"))

Any help would be appreciated.
Click to expand...
Click to expand...
 
Top