I think I am confuse about how to do this report.

  • Thread starter Thread starter Robert Raley
  • Start date Start date
R

Robert Raley

Access 2003



I need help building a query. These are the tables.



wkly_trans_dates

[date_id]

[data_start_date]



wkly_con_stats

[con_stats_id]

[date_id]

[stage_name]

[pvt_min]

[open_min]

[tot_min]

[conv]

[comments]

[suggestions]





wkly_pvt

[con_stats_id]

[seconds]



I would like to create a quiery that I then can use to create a report
showing the following items



this would be selected by [data_start_date]

Sorted and grouped by

[stage_name]

[pvt_min] [open_min] [tot_min] [conv] [pvt_ave, from table
wkly_pvt average( [seconds]/60] [comments] [suggestions]

Past conversions

current-6wk current-5wk current-4wk
current-3wk current-2wk current-1wk current-1wk

[conv] [conv] [conv]
[conv] [conv] [conv] [conv]



Please note that the heading has to change with [conv]



I have steps one and two done works great. I just have not been able to
figure out how to do the history part.



Thanks bob.
 
Not sure what you want. You may have attempted to post HTML or something.

It looks like you can "re-purpose" the following to work for weeks rather
than months:
=======================
Try not to use "absolute" column headings for dates. You could possibly use
"relative" dates. For instance:
Form: frmA
Text Box: txtEndDate
Table: tblSales
Field: SaleDate
You want to show 12 months of sales in columns of a crosstab report.
Set the with menuing: Query|Parameter
Forms!frmA!txtEndDate Date/Time

Use this expression for your Column Headings:
ColHead:"Mth" & DateDiff("m",[SaleDate],Forms!frmA!txtEndDate)

This will produce Mth0, Mth1, Mth2, Mth3,... where Mth0 is sales from the
same month as the ending date on your form. Mth1 is the previous month etc.

Set your queries Column Headings property to:
"Mth0", "Mth1", "Mth2", "Mth3",.., "Mth11"
Build your report based on these "relative" months. If you need column
labels in your report, use text boxes with control sources of:
=DateAdd("m",0,Forms!frmA!txtEndDate)
=DateAdd("m",-1,Forms!frmA!txtEndDate)
=DateAdd("m",-2,Forms!frmA!txtEndDate)
=DateAdd("m",-3,Forms!frmA!txtEndDate)
...
This solution requires no code and will run fairly quickly.
 
Back
Top