Help with crosstab query

[flanagan]

Hello,

Using an access 2002 database in Access 2007, I have a table called JOBS
with [rate], [billtype], [start_date], [end_date], [numwords] as fields among
others.

I would like to use a single (crosstab?) query to return some calculated
values for each month (Month([start_date])).

Here is how I have been calculating values either in a select query or using
Dsum.

If billtype = 1 Then total = [rate] * (1+([end_date]-[start_date]))
If billtype = 2 or 4 or 6 Then total = [rate] * [numwords]
If billtype = 3 or 5 Then total = [rate]


I have successfully created three (clumsy) queries that return a month's
total when I feed them a month and a year as criteria, based on the records'
[start_date] field. I have however run into difficulties when trying to pass
wildcards (*) as criteria, but that is besides the point since what I would
really like to do is create a single query.

Ideally, the query would also total the recordcount for each category of
billtype as per above.

And in an altogether Utopian world the query would return the number of days
worked for [billtype] = 1 or 5

Again, I have more or less managed to stitch together queries for each
scenario but I end up with 20 or so queries. And I resort to slow
dsums/lookups on forms querying each query.

Any help would be greatly appreciated,

Thanks

 

[Duane Hookom]

I would probably create a user-defined function in a module that accepts
arguments of Rate, BillType, Start_Date, End_Date, and NumWords. You could
return the appropriate value to sum.

Public Function GetBill( dblRate as Double, intBillType as Integer, _
datStart As Date, datEnd as Date, lngNumWords as Long) as Double
Select Case intBillType
Case 1
GetBill = dblRate * 1+datEnd - datStart
Case 2, 4, 6
GetBill = dblRate * lngNumWords
Case Else
GetBill = dblRate
End Select
End Function

You can then use this function in the Value of the crosstab.
I don't know what you are using for column or row headings so it is
difficult to provide any other expressions.

 

[flanagan]

Hi Duane,

Thanks for your answer! My crosstab would ideally have billtype as rows and
months as columns. I'll look into your solution over the next couple of weeks
as I learn how crosstabs work.

Thanks again
flanagan

I would probably create a user-defined function in a module that accepts
arguments of Rate, BillType, Start_Date, End_Date, and NumWords. You could
return the appropriate value to sum.

Public Function GetBill( dblRate as Double, intBillType as Integer, _
datStart As Date, datEnd as Date, lngNumWords as Long) as Double
Select Case intBillType
Case 1
GetBill = dblRate * 1+datEnd - datStart
Case 2, 4, 6
GetBill = dblRate * lngNumWords
Case Else
GetBill = dblRate
End Select
End Function

You can then use this function in the Value of the crosstab.
I don't know what you are using for column or row headings so it is
difficult to provide any other expressions.

--
Duane Hookom
Microsoft Access MVP

Hello,

Using an access 2002 database in Access 2007, I have a table called JOBS
with [rate], [billtype], [start_date], [end_date], [numwords] as fields among
others.

I would like to use a single (crosstab?) query to return some calculated
values for each month (Month([start_date])).

Here is how I have been calculating values either in a select query or using
Dsum.

If billtype = 1 Then total = [rate] * (1+([end_date]-[start_date]))
If billtype = 2 or 4 or 6 Then total = [rate] * [numwords]
If billtype = 3 or 5 Then total = [rate]


I have successfully created three (clumsy) queries that return a month's
total when I feed them a month and a year as criteria, based on the records'
[start_date] field. I have however run into difficulties when trying to pass
wildcards (*) as criteria, but that is besides the point since what I would
really like to do is create a single query.

Ideally, the query would also total the recordcount for each category of
billtype as per above.

And in an altogether Utopian world the query would return the number of days
worked for [billtype] = 1 or 5

Again, I have more or less managed to stitch together queries for each
scenario but I end up with 20 or so queries. And I resort to slow
dsums/lookups on forms querying each query.

Any help would be greatly appreciated,

Thanks