drawing from 2 different tables with a certain criteria---for year

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

Guest

In two tables ([Rent] & [Journal]), I have 7 locations (1-7) an [amount]
field, and a [date] field. In the [journal] table, there is another field
that it must look for specifics on: [type] and it must be equal to 4. In
[Rent], there is no such field because all amounts "4"s.

I want to be able to for each location (1-7), have a query that totals the
amount for a given date (Between [enter start date] and [enter ending date]).

If you could help me out, you would be doing me a huge favor because i need
to assimilate this info for years end. thanks you. I tried SQL and I must
have made an error somewhere.
 
Hi,




SELECT location, SUM(Amount )
FROM myTable
WHERE dateTimeFieldName BETWEEN startingDate AND endingDate
GROUP BY location


should SUM the amounts, by location, for records involved, through their
dateTiemFieldName, between the two dates supplies as parameter,
startingDate and endingDate.


I assumed your table design is like:

location Amount dateTimeFieldname ' fields
1 10.10$ 2004.01.01
1 99.45$ 2004.01.02
....
7 16.15$ 2004.12.01 ' data sample






Hoping it may help,
Vanderghast, Access MVP
 
Hi Michel,
I am trying to sum the amount fields from both tables, each with specific
criteria---the criteria is almost the same, except for in [journal], type=4
and [rent], there is no such field. thanks for your earlier response. I would
really like to figure this out. If it would help, my work# is 413-733-4540
and the operator could get me---I am JOsh. thanks for your help already.

Michel Walsh said:
Hi,




SELECT location, SUM(Amount )
FROM myTable
WHERE dateTimeFieldName BETWEEN startingDate AND endingDate
GROUP BY location


should SUM the amounts, by location, for records involved, through their
dateTiemFieldName, between the two dates supplies as parameter,
startingDate and endingDate.


I assumed your table design is like:

location Amount dateTimeFieldname ' fields
1 10.10$ 2004.01.01
1 99.45$ 2004.01.02
....
7 16.15$ 2004.12.01 ' data sample






Hoping it may help,
Vanderghast, Access MVP


Josh Henry said:
In two tables ([Rent] & [Journal]), I have 7 locations (1-7) an [amount]
field, and a [date] field. In the [journal] table, there is another field
that it must look for specifics on: [type] and it must be equal to 4. In
[Rent], there is no such field because all amounts "4"s.

I want to be able to for each location (1-7), have a query that totals the
amount for a given date (Between [enter start date] and [enter ending
date]).

If you could help me out, you would be doing me a huge favor because i
need
to assimilate this info for years end. thanks you. I tried SQL and I must
have made an error somewhere.
Click to expand...
Click to expand...
 
Hi,


Can you make a first query that will merge (weld) the two tables, for
the fields we have to sum. That may look to:


SELECT location, Amount FROM firstTable WHERE dateTime BETWEEN this AND that
UNION ALL
SELECT fieldName1, fieldName2 FROM secondTable WHERE type=4



the goal of that query is to bring all the data under one "entity" (the name
of the query, once it is saved). If you open such a query, you should be
able to see that this query would then use the same name than the one
supplied in the first SELECT defining the query.


Once that query is working, what is left is to do a second query like:


SELECT location, SUM(Amount)
FROM savedQueryNameHere
GROUP BY location




Hoping it may help
Vanderghast, Access MVP


Josh Henry said:
Hi Michel,
I am trying to sum the amount fields from both tables, each with specific
criteria---the criteria is almost the same, except for in [journal],
type=4
and [rent], there is no such field. thanks for your earlier response. I
would
really like to figure this out. If it would help, my work# is 413-733-4540
and the operator could get me---I am JOsh. thanks for your help already.

Michel Walsh said:
Hi,




SELECT location, SUM(Amount )
FROM myTable
WHERE dateTimeFieldName BETWEEN startingDate AND endingDate
GROUP BY location


should SUM the amounts, by location, for records involved, through their
dateTiemFieldName, between the two dates supplies as parameter,
startingDate and endingDate.


I assumed your table design is like:

location Amount dateTimeFieldname ' fields
1 10.10$ 2004.01.01
1 99.45$ 2004.01.02
....
7 16.15$ 2004.12.01 ' data sample






Hoping it may help,
Vanderghast, Access MVP


Josh Henry said:
In two tables ([Rent] & [Journal]), I have 7 locations (1-7) an
[amount]
field, and a [date] field. In the [journal] table, there is another
field
that it must look for specifics on: [type] and it must be equal to 4.
In
[Rent], there is no such field because all amounts "4"s.

I want to be able to for each location (1-7), have a query that totals
the
amount for a given date (Between [enter start date] and [enter ending
date]).

If you could help me out, you would be doing me a huge favor because i
need
to assimilate this info for years end. thanks you. I tried SQL and I
must
have made an error somewhere.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top