Crosstab Column Sum - Impossible??

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello

I made a crosstab query which has dates for the column headings, users for the row headings, and a value for the ij-entry. I used Allen Browne's tip to automatically re-size my column headings when the report is requeried for a new month. In my crosstab query, I have the totals for each user (row sum), however, on the report I would also like a column sum. Is this possible? I tried just summing the text box in the footer section, however, since my controls are adjusted automatically (thus, controls have generic names which are pulled from the query via code), this is not possible, according to Access. Does anyone know how I can fix this problem? - I am not adverse to using code, but I could use some pointers to get me started

Thanks
Carrie
 
Use "relative" dates rather than absolute dates in you column headings
expression. This solution requires no code.
If your report will contain 5 days beginning today.
ColHead:"Day" & DateDiff("d",Date(),[DateField])
Set the Column Headings property to
"Day0","Day1",..."Day4"
Day0 will be today and Day4 will be four days out. Your report and query
will always have the same columns/fields. In your report, you can use text
boxes as column labels:
=DateAdd("d",0,Date())
=DateAdd("d",1,Date())
=DateAdd("d",2,Date())
=DateAdd("d",3,Date())
=DateAdd("d",4,Date())
You can also substitute a reference to a control on a form for Date() if you
want your columns to reference a different ending date. If you want more
than five columns, then add more values to your column headings property.
--
Duane Hookom
MS Access MVP


Carrie said:
Hello,

I made a crosstab query which has dates for the column headings, users
Click to expand...
for the row headings, and a value for the ij-entry. I used Allen Browne's
tip to automatically re-size my column headings when the report is requeried
for a new month. In my crosstab query, I have the totals for each user (row
sum), however, on the report I would also like a column sum. Is this
possible? I tried just summing the text box in the footer section, however,
since my controls are adjusted automatically (thus, controls have generic
names which are pulled from the query via code), this is not possible,
according to Access. Does anyone know how I can fix this problem? - I am
not adverse to using code, but I could use some pointers to get me started.
 
Back
Top