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
|
|||
|
|||
Query Help.
Hi, I am new to access and am learning through various methods. I am
creating a database for someone else to help tally sales on a daily, monthly and yearly basis. We use access 2002. I created a query to calculate daily total and that work fine. When I try to create a query for a montly basis I run into the problem that when I try to run the query it wants me to enter the daily total amount. The function that I am entering is Montly Total:Sum([DailyTotal]) and have the totals set at expression. Under the Date criteria sections I have Between [EnterStartDate] And [EnterEndDate]. Any help would be great and as I said I am in the process of teaching myself access so I know that I could have gotten mixed up on how I should be running the queries. Thanks a bunch. |
#2
|
|||
|
|||
For your monthly and yearly queries you can forget about the daily totals;
just sum the individual amounts. For monthly total you need to Group By both the year and the month as if your date range spans more than one year you'd end up with the sum for the same month in both years rather than having separate sums for that month in each year. So for the monthly query create two computed columns in the query by entering something like the following in the 'field' row of two blank columns in query design view: AccountYear: Year([TransactionDate]) AccountMonth: Month[TransactionDate]) where TransactionDate is the name of the date field in the table. Group by these two columns. Then Sum the Amount column whose values you want to total for each month. For the yearly query do exactly the same but omit the AccountMonth computed column. You can call your computed columns whatever you wish of course. For your date range criteria beware of one thing when using the BETWEEN….AND operator. If the dates include unseen times of day (which usually happens when people use the Now function rather than the Date function to set the default value of a field) you'll miss out any rows for the final day in the range as it will be looking for date/time values no later than midnight at the start of the day (there is no such thing in Access as a date without a time or vice versa; what you see is just a formatted value of the underlying date/time value, which is actually a floating point number as an offset form midnight at the start of 30 December 1899, the integers representing days, the fractional part the times of day). A better way to specify a date range is to look for dates on or after the first date and les than the day following the last date, in SQL this would read: WHERE TransactionDate = [EnterStartDate] AND TransactionDate [EnterStartDate] + 1 In query design view you'd do this by putting the following in the criteria row of the TransactionDate column: = [EnterStartDate] AND TransactionDate [EnterStartDate] + 1 You'll find that after you save the query Access will rearrange things but the result will be the same. "Becks" wrote: Hi, I am new to access and am learning through various methods. I am creating a database for someone else to help tally sales on a daily, monthly and yearly basis. We use access 2002. I created a query to calculate daily total and that work fine. When I try to create a query for a montly basis I run into the problem that when I try to run the query it wants me to enter the daily total amount. The function that I am entering is Montly Total:Sum([DailyTotal]) and have the totals set at expression. Under the Date criteria sections I have Between [EnterStartDate] And [EnterEndDate]. Any help would be great and as I said I am in the process of teaching myself access so I know that I could have gotten mixed up on how I should be running the queries. Thanks a bunch. |
#3
|
|||
|
|||
Thanks for the help Ken. The monthly query worked no problem for me, but
when I try to do the yearly total query and run it, nothing comes up. It does ask me to enter the date three different times. I was wondering that the statement to be place in the criteria row should be [EnterEndDate] +1, instead of another [EnterStartDate] +1. Thanks Again! "Ken Sheridan" wrote: For your monthly and yearly queries you can forget about the daily totals; just sum the individual amounts. For monthly total you need to Group By both the year and the month as if your date range spans more than one year you'd end up with the sum for the same month in both years rather than having separate sums for that month in each year. So for the monthly query create two computed columns in the query by entering something like the following in the 'field' row of two blank columns in query design view: AccountYear: Year([TransactionDate]) AccountMonth: Month[TransactionDate]) where TransactionDate is the name of the date field in the table. Group by these two columns. Then Sum the Amount column whose values you want to total for each month. For the yearly query do exactly the same but omit the AccountMonth computed column. You can call your computed columns whatever you wish of course. For your date range criteria beware of one thing when using the BETWEEN….AND operator. If the dates include unseen times of day (which usually happens when people use the Now function rather than the Date function to set the default value of a field) you'll miss out any rows for the final day in the range as it will be looking for date/time values no later than midnight at the start of the day (there is no such thing in Access as a date without a time or vice versa; what you see is just a formatted value of the underlying date/time value, which is actually a floating point number as an offset form midnight at the start of 30 December 1899, the integers representing days, the fractional part the times of day). A better way to specify a date range is to look for dates on or after the first date and les than the day following the last date, in SQL this would read: WHERE TransactionDate = [EnterStartDate] AND TransactionDate [EnterStartDate] + 1 In query design view you'd do this by putting the following in the criteria row of the TransactionDate column: = [EnterStartDate] AND TransactionDate [EnterStartDate] + 1 You'll find that after you save the query Access will rearrange things but the result will be the same. "Becks" wrote: Hi, I am new to access and am learning through various methods. I am creating a database for someone else to help tally sales on a daily, monthly and yearly basis. We use access 2002. I created a query to calculate daily total and that work fine. When I try to create a query for a montly basis I run into the problem that when I try to run the query it wants me to enter the daily total amount. The function that I am entering is Montly Total:Sum([DailyTotal]) and have the totals set at expression. Under the Date criteria sections I have Between [EnterStartDate] And [EnterEndDate]. Any help would be great and as I said I am in the process of teaching myself access so I know that I could have gotten mixed up on how I should be running the queries. Thanks a bunch. |
#4
|
|||
|
|||
Doh! You're absolutely right about the parameters of course. I meant to say:
= [EnterStartDate] AND TransactionDate [EnterEndDate] + 1 I don't know why its prompting you three times, however. Post back with the SQL for the offending query. "Becks" wrote: Thanks for the help Ken. The monthly query worked no problem for me, but when I try to do the yearly total query and run it, nothing comes up. It does ask me to enter the date three different times. I was wondering that the statement to be place in the criteria row should be [EnterEndDate] +1, instead of another [EnterStartDate] +1. Thanks Again! |
#5
|
|||
|
|||
I seem to have gotten the query to work, but am not sure that it would be the
best way to work it. I just started over and what I did was have one of the colums with the sales year and the second with the product amount I want totaled. Then I used the sum function under product amount and when I ran the query it worked. Not sure why this wouldn't work out before, but I wanted to see if I should still have something in which there were parameter values. You know sometimes just because it is easy doesn't mean that it is the right way to do things. Thanks for all the help. "Ken Sheridan" wrote: Doh! You're absolutely right about the parameters of course. I meant to say: = [EnterStartDate] AND TransactionDate [EnterEndDate] + 1 I don't know why its prompting you three times, however. Post back with the SQL for the offending query. "Becks" wrote: Thanks for the help Ken. The monthly query worked no problem for me, but when I try to do the yearly total query and run it, nothing comes up. It does ask me to enter the date three different times. I was wondering that the statement to be place in the criteria row should be [EnterEndDate] +1, instead of another [EnterStartDate] +1. Thanks Again! |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
SQL query showing diff between actual and budget | Bon | Running & Setting Up Queries | 3 | August 25th, 2005 12:07 PM |
Nested in-line Query | laura | Running & Setting Up Queries | 0 | February 11th, 2005 12:17 AM |
AVG Function in a Query | JohnL | Running & Setting Up Queries | 5 | December 18th, 2004 05:52 AM |
Big number gives error! | Sara Mellen | Running & Setting Up Queries | 8 | October 11th, 2004 02:48 AM |
Display Parameter from Form on Report | sara | Setting Up & Running Reports | 10 | July 19th, 2004 04:54 PM |