If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Date query in Acceess
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 |
#2
|
|||
|
|||
Date query in Acceess
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. "sarah" wrote: 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 |
#3
|
|||
|
|||
Date query in Acceess
On Nov 12, 6:28 pm, Jerry Whittle
wrote: 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. "sarah" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|