Query - Is this possible?

[Guest]

I have a query question I hope someone can help with.

I have a table that contains an order date and an amount on each line among
other items. The table is basically a list of sales, so there are many
entries for each salesman and dealer.

From that table I need to gather the following information. I need one
column to contain the amount summed YTD per dealer. The other column I need
to contain the amount summed for the month per dealer. The date on each line
is in the format 20050101.

I can get this to work individually just fine, e.g. the total for the month
OR the total YTD, but not both in the same query.

I have tried several methods to try to do this including using the criteria
field and trying to customize the SQL statements. From what I can tell Access
only allows one SELECT statement per query, so I don’t know how to write it
to get both.

The end goal is to create a report showing sales comparisons against last
year for the month and YTD. If it is not possible to do this with the query
is it a better idea to try to use more than one query in the report?

Any help would be greatly appreciated.

Ken

 

[Guest]

Is your YTD amount stored in a table field or is it calculated? If it is
calculated, how do you calculate it and where?
Based on you answers, I may have a function that will solve your problem.

 

[Guest]

The YTD is calculated in the Query by adding an amount column to the query
and setting total to sum, basically by not giving that column any criteria I
get a total for the whole year. When i try to have that column and one that
just sums one month, I can not get it to work.

The table I am trying to query is just a list of every sale made and that is
what I am trying to total based on the date.

I hope I am explaining well enough.

Thank You

Ken

 

[Guest]

Here is a function I use for just that purpose.
My query is a totals query where the months have Sum as their Domain Aggrate
function. The YTD column (zActualYTD) is Expression in the Total: row. The
first argument is the last month to include in the YTD calculation. The
other arguments are all the months of the year. Note the reference to the
combo box cblPeriod. It has the current month number and is the last month
to include in the calculation.

This is the Field: Column for the calculation:

zActualYTD:
Sum(Calc_Ytd(Forms!frmSCCBrpt!cboPeriod,[jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec]))

This is the function:

Public Function Calc_Ytd(MoNo, January, February, march, april, may, june,
july, August, September, October, November, December)
' Dave Hargis 8/04
' Calculates Year To Date Totals
'MoNo is the last month to include in the calculation
'For example, to include January through August, it should be 8


Select Case MoNo
Case 1
Calc_Ytd = January
Case 2
Calc_Ytd = January + February
Case 3
Calc_Ytd = January + February + march
Case 4
Calc_Ytd = January + February + march + april
Case 5
Calc_Ytd = January + February + march + april + may
Case 6
Calc_Ytd = January + February + march + april + may + june
Case 7
Calc_Ytd = January + February + march + april + may + june + july
Case 8
Calc_Ytd = January + February + march + april + may + june +
july + August
Case 9
Calc_Ytd = January + February + march + april + may + june +
july + August + September
Case 10
Calc_Ytd = January + February + march + april + may + june +
july + August + September + October
Case 11
Calc_Ytd = January + February + march + april + may + june +
july + August + September + October + November
Case 12
Calc_Ytd = January + February + march + april + may + june +
july + August + September + October + November + December

End Select


End Function

Let me know if it works for you or if you have any other questions.

 

[Guest]

Thank you for your response.

I think using VB and a function is more than I bargained for. While I have a
little experience using VB. net and a little experience using Access, I
honestly thought there would be a more simple route to what I was trying to
accomplish.

Thanks Again,

Ken

Here is a function I use for just that purpose.
My query is a totals query where the months have Sum as their Domain Aggrate
function. The YTD column (zActualYTD) is Expression in the Total: row. The
first argument is the last month to include in the YTD calculation. The
other arguments are all the months of the year. Note the reference to the
combo box cblPeriod. It has the current month number and is the last month
to include in the calculation.

This is the Field: Column for the calculation:

zActualYTD:
Sum(Calc_Ytd(Forms!frmSCCBrpt!cboPeriod,[jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec]))

This is the function:

Public Function Calc_Ytd(MoNo, January, February, march, april, may, june,
july, August, September, October, November, December)
' Dave Hargis 8/04
' Calculates Year To Date Totals
'MoNo is the last month to include in the calculation
'For example, to include January through August, it should be 8


Select Case MoNo
Case 1
Calc_Ytd = January
Case 2
Calc_Ytd = January + February
Case 3
Calc_Ytd = January + February + march
Case 4
Calc_Ytd = January + February + march + april
Case 5
Calc_Ytd = January + February + march + april + may
Case 6
Calc_Ytd = January + February + march + april + may + june
Case 7
Calc_Ytd = January + February + march + april + may + june + july
Case 8
Calc_Ytd = January + February + march + april + may + june +
july + August
Case 9
Calc_Ytd = January + February + march + april + may + june +
july + August + September
Case 10
Calc_Ytd = January + February + march + april + may + june +
july + August + September + October
Case 11
Calc_Ytd = January + February + march + april + may + june +
july + August + September + October + November
Case 12
Calc_Ytd = January + February + march + april + may + june +
july + August + September + October + November + December

End Select


End Function

Let me know if it works for you or if you have any other questions.

The YTD is calculated in the Query by adding an amount column to the query
and setting total to sum, basically by not giving that column any criteria I
get a total for the whole year. When i try to have that column and one that
just sums one month, I can not get it to work.

The table I am trying to query is just a list of every sale made and that is
what I am trying to total based on the date.

I hope I am explaining well enough.

Thank You

Ken