Date query in Acceess

[sarah]

Hello there.
I am a beginner with Access software. I have created a data base with
this software that is used to have information about the missions in
the office. This database has information about the name, cost, date
etc. In addition the data base is not in English.
I want to have a query that is calculating sum of the costs for each
person in a month.
I have tested 2 ways and couldn't get the result I am looking for.
First, I made a query that has 3 fields: name, date and costs. Then I
used Criteria for date like this: <#1386/08/01# which is our country
date and all the dates in the database has the same format. And I also
used Sum in total cell for costs. But when I run the query it's just
compatible with the date. I mean it shows the missions in the
determined date (before 1386/08/01) But dose not calculate the sum. I
mean it has more than 1 row for each person. for example it has 10
rows for Peter.
Then I changed my way and used Query wizard to make a query on a
determined month. In this way I had another problem, it calculated the
sum cost but the months were different from my country.
Can any one help me in this case?
Sincerely: Sarah Hai

 

[Jerry Whittle]

Hi Sarah,

Save the query. Next use it for the basis of a report. You can sort and
group reports based on dates, such as months and have totals show up.

Also watch out for your field names. Date and Name are reserved words and
could cause problems.

http://support.microsoft.com/kb/286335/

Below is a query that might do what you need. Just make sure that the table
and field names are correct. Notice that I split up the date into months and
years as you could have problems when the data is over a year old. You could
be getting both 1386/08 and 1385/08 grouped together. I also took the liberty
of using a parameter query so that you could type in what date you wanted
without redesigning the query.

PARAMETERS [Enter Date] DateTime;
SELECT [Name],
Year([date]) AS TheYear,
Month([date]) AS TheMonth,
Sum([Costs]) AS SumOfCosts
FROM YourTable
WHERE [date] < [Enter Date]
GROUP BY [Name],
Year([date]),
Month([date])
ORDER BY Year([date]), Month([date]);

 

[sarah]

Hi Sarah,

Save the query. Next use it for the basis of a report. You can sort and
group reports based on dates, such as months and have totals show up.

Also watch out for your field names. Date and Name are reserved words and
could cause problems.

http://support.microsoft.com/kb/286335/

Below is a query that might do what you need. Just make sure that the table
and field names are correct. Notice that I split up the date into months and
years as you could have problems when the data is over a year old. You could
be getting both 1386/08 and 1385/08 grouped together. I also took the liberty
of using a parameter query so that you could type in what date you wanted
without redesigning the query.

PARAMETERS [Enter Date] DateTime;
SELECT [Name],
Year([date]) AS TheYear,
Month([date]) AS TheMonth,
Sum([Costs]) AS SumOfCosts
FROM YourTable
WHERE [date] < [Enter Date]
GROUP BY [Name],
Year([date]),
Month([date])
ORDER BY Year([date]), Month([date]);
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hello there.
I am a beginner with Access software. I have created a data base with
this software that is used to have information about the missions in
the office. This database has information about the name, cost, date
etc. In addition the data base is not in English.
I want to have a query that is calculating sum of the costs for each
person in a month.
I have tested 2 ways and couldn't get the result I am looking for.
First, I made a query that has 3 fields: name, date and costs. Then I
used Criteria for date like this: <#1386/08/01# which is our country
date and all the dates in the database has the same format. And I also
used Sum in total cell for costs. But when I run the query it's just
compatible with the date. I mean it shows the missions in the
determined date (before 1386/08/01) But dose not calculate the sum. I
mean it has more than 1 row for each person. for example it has 10
rows for Peter.
Then I changed my way and used Query wizard to make a query on a
determined month. In this way I had another problem, it calculated the
sum cost but the months were different from my country.
Can any one help me in this case?
Sincerely: Sarah Hai- Hide quoted text -

- Show quoted text -

Dear Jerry Whittle
Thanks for your hint.
But I have a problem. I used some of them for example Grouped by in
Design view of my Query,but don't know where to use others? Should I
use Build on the Fields or some where else?
Best regards: Sarah Hai