adjusting where clause in SQL statement

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My query in SQL looks like the following:
(right now the query makes sure it has a certain qualifier when it pulls
data from tblJournal & I would like to add that same qualifier for the data
it pulls from tblRent using the where clause.

this is what my SQLstatement looks like:

SELECT Sum(Journal.Amount) AS SumOfAmount,
Sum([Journal.Amount]+[Rent.Amount]) AS RentIncome
FROM Rent INNER JOIN Journal ON Rent.Tenant = Journal.Name
WHERE (((Journal.Type)="4") AND ((Journal.LocationID)=1) AND ((Journal.Date)
Between [Enter YTD beginning date] And [Enter ending date]));

As of now it makes sure it pulls the [Amount] field out of [tblJournal]
only if [LOCID] = 1 in the record in [tblJournal]. I want to add the same
qualifier for the [Amount] it
pulls from the [tblRent].

Any help would be greatly appreciated so I can apply the same principles to
other queries. Thanks
 
Josh said:
My query in SQL looks like the following:
(right now the query makes sure it has a certain qualifier when it pulls
data from tblJournal & I would like to add that same qualifier for the data
it pulls from tblRent using the where clause.

this is what my SQLstatement looks like:

SELECT Sum(Journal.Amount) AS SumOfAmount,
Sum([Journal.Amount]+[Rent.Amount]) AS RentIncome
FROM Rent INNER JOIN Journal ON Rent.Tenant = Journal.Name
WHERE (((Journal.Type)="4") AND ((Journal.LocationID)=1) AND ((Journal.Date)
Between [Enter YTD beginning date] And [Enter ending date]));

As of now it makes sure it pulls the [Amount] field out of [tblJournal]
only if [LOCID] = 1 in the record in [tblJournal]. I want to add the same
qualifier for the [Amount] it
pulls from the [tblRent].

Any help would be greatly appreciated so I can apply the same principles to
other queries. Thanks
Click to expand...

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

How about this:

WHERE Journal.Type="4"
AND Journal.LocationID=1
AND Rent.LocationID=1
AND Journal.Date Between [Enter YTD beginning date] And [Enter ending
date]

You could also use the LocationID as "joining" fields in the INNER JOIN:

FROM Rent INNER JOIN Journal ON Rent.Tenant = Journal.Name
AND Rent.LocationID = Journal.LocationID

WHERE Journal.LocationID = 1 ... etc. ...

This would mean the LocationID for both tables would be whatever number
was indicated in the WHERE clause for Journal.LocationID. Then you
could make the LocationID a parameter that gets entered each time the
query runs.

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

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

iQA/AwUBQbDdW4echKqOuFEgEQKfowCg+GXrQgAbwZc4fml2nMeLbQRVIicAniNg
jr4UcRXq0FsI2cFj+u9vr+By
=5Uyj
-----END PGP SIGNATURE-----
 
Back
Top