drawing amounts from two tables--please--i am beyond desperate

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

Guest

My SQL statement looks like the following:

SELECT Sum([Journal.Amount]+[Rent.Amount]) AS [WSpfld Rent]
FROM Journal, Rent
WHERE (((Journal.Type)="4") AND ((Journal.LocationID)=1) AND ((Journal.Date)
Between [enter beginning date] And [enter ending date]) AND
((Rent.Location)="1") AND ((Rent.Date) Between [enter beginning date] And
[enter ending date]));

the number i am getting is way too big. I have 2 tables---Journal & Rent.
Both are the same except journal has a field, Type, ich has to = 4. Both have
amount fields and both have location fields, which have to equal 1 for this
query (I will apply this logic to the other 6 locations). Both have date
fields, for which the parameters will be "between [enter beginning date] and
[enter ending date]. What i want to do ultimately is total the whole amount
adding the amount fields in both tables for certain dates from locations
1---the only catch is Journal.type=4.
any help would be tremendously appreciated! thanks

If you would like, my name is Josh and work phone is 413-733-4540 and
switchboard operator could get me.
 
Your query is structured as a cartesian query and as such you'll get
numerous duplicates of records returned. What you want is a joined query,
something like this:

SELECT Sum([Journal.Amount]+[Rent.Amount]) AS [WSpfld Rent]
FROM Journal INNER JOIN Rent ON Journal.LocationID = Rent.LocationID
WHERE (((Journal.Type)="4") AND ((Journal.LocationID)=1) AND ((Journal.Date)
Between [enter beginning date] And [enter ending date]) AND
((Rent.Location)="1") AND ((Rent.Date) Between [enter beginning date] And
[enter ending date]));
 
Back
Top