Query Help.

[Guest]

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.

 

[Guest]

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.

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.

 

[Guest]

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!

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.

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.

 

[Guest]

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.

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!

 

[Guest]

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.

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.

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!