Query - Summing dates....

  • Thread starter Thread starter Al Mackay
  • Start date Start date
A

Al Mackay

Wondered if anyone could help with the following.

I have an access database that I want to query the following on

Need to be able to sum up the amount of entries by month/year from a
column name 'Received'.

e.g (output to be something like this)

Jan-01 24
Feb-01 33
Apr-01 88

Then, if possible - I have other tables the same that I want to run
this on. Would it then be possible to combine all the queries
together into something that would produce something similar to below:

Table: Example1 Example 2
Jan-01 24 36
Feb-01 33
Apr-01 88 78
Sep-02 101

Hope you can help - please let me know if you need any further info'.

Many Thanks, al.
 
Hi,

Question 1.


SELECT Month(Received) As mt, Year(Received) As yr, SUM(amount)
FROM myTable
GROUP BY Month(Received), Year(Received)


Question 2

Make the required queries as in question 1, one per table. Then, make
the following query

SELECT a.*, b.*
FROM previousQueryForOneTable As a INNER JOIN
previousQueryForAnotherTable As b
ON a.mt=b.mt AND a.yr=b.yr



where previousQueryForOneTable and previousQueryForAnotherTable are the
names of the query you used to save them.



Hoping it may help,
Vanderghast, Access MVP
 
Thanks for your help on this Michel.

The Question 1 answer worked perfectly, but for some reason the second
answer doesn't work.

Getting the following error "The Microsoft Jet engine cannot find the
input table or query 'query_test_2'. Make sure it exists and that its
name is spelled correctly".

I have checked this and it is all spelt correctly. Any ideas?

Have had to tweak the original SQL to do a count instead of a sum as
per below:

SELECT Month([Received]) AS mt, Year([Received]) AS yr,
Count([Received])
FROM [Change Panel Related Info (CRs)]
GROUP BY Month([Received]), Year([Received]);

the other table name is "Amendments (CRs)" and the query is exactly
the same.

Many thanks for your help on this one, Al.

Many Thanks, Al.
 
Hi,

Try graphically. In the query designer, bring the two previous queries
(as if they were table) in a new query. Drag and drop mt field of one
query(table) over the other. Do the same for the yr field. Bring, in the
grid, the wanted fields. That should do job.


Hoping it may help,
Vanderghast, Access MVP


Al Mackay said:
Thanks for your help on this Michel.

The Question 1 answer worked perfectly, but for some reason the second
answer doesn't work.

Getting the following error "The Microsoft Jet engine cannot find the
input table or query 'query_test_2'. Make sure it exists and that its
name is spelled correctly".

I have checked this and it is all spelt correctly. Any ideas?

Have had to tweak the original SQL to do a count instead of a sum as
per below:

SELECT Month([Received]) AS mt, Year([Received]) AS yr,
Count([Received])
FROM [Change Panel Related Info (CRs)]
GROUP BY Month([Received]), Year([Received]);

the other table name is "Amendments (CRs)" and the query is exactly
the same.

Many thanks for your help on this one, Al.

Many Thanks, Al.

"Michel Walsh" <vanderghast@VirusAreFunnierThanSpam> wrote in message
Hi,

Question 1.


SELECT Month(Received) As mt, Year(Received) As yr, SUM(amount)
FROM myTable
GROUP BY Month(Received), Year(Received)


Question 2

Make the required queries as in question 1, one per table. Then, make
the following query

SELECT a.*, b.*
FROM previousQueryForOneTable As a INNER JOIN
previousQueryForAnotherTable As b
ON a.mt=b.mt AND a.yr=b.yr



where previousQueryForOneTable and previousQueryForAnotherTable are the
names of the query you used to save them.



Hoping it may help,
Vanderghast, Access MVP
Click to expand...
Click to expand...
 
Back
Top