Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Reports / Printing / October 2007

Tip: Looking for answers? Try searching our database.

Crosstab report

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Pammy - 10 Oct 2007 14:09 GMT
The problem is, none of the fields are available for me me to select to
create the report ... it's blank. When I try to create a report using design
view, the pop-up box for parameter I set keeps coming up everytime I try to
move a field onto the report.
The Crosstab query has parameters set up for the Start Date and End Date, I
read that you have to set up column headings, does this mean that I have to
type 1/1/2007, 1/2/2007, .........until I reach 12/31/2007?  I want the dates
to be column headings, the parameter would only be for 15 days, start:  
01/01/2007 end:01/15/2007.
Allen Browne - 10 Oct 2007 14:54 GMT
With a crosstab, the column names come from the values in a field. Therefore
Access has to *run* the query in order to figure out what the field names
are, so you can design the report. To run the query, it has to ask for the
parameters.

Here's an alternative. Assuming your date/time field is named OrderDate,
open the crosstab in design view, and replace the OrderDate field that is
the Column Heading with this:
   Day([OrderDate])
This will cause the column headings to be the numbers 1 to 15 instead of 15
dates. Consequently you don't have to redesign the crosstab every month to
get consistent field names.

Now in query design view open the Properties box. Viewing the properties of
the query (not of a field), enter the Column Headings property like this:
   1,2,3,4,5, ...
This defines the column names. Benefits:
a) It ensures that there is a column for all values, even if there was not
field for that date. Consequently the report does not fail if there was no
data for a date.
b) Access now knows the names of the fields for the report *without* needing
to run the query. This makes designing the report much easier.

You wanted to use a parameter as well. Declare this parameter (Parameters on
Query menu.) If you always want just the first 15 days of the month, you can
do it with one parameter instead of two. Your criteria will be something
like this:
   Between [Start Date] And ([Start Date] + 14)

For some other examples, see Duan Hookom's suggestions:
   Dynamic Monthly Crosstab Reports
here:
   http://www.access.hookom.net/DynamicMthlyCrosstabRpt.htm

Signature

Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> The problem is, none of the fields are available for me me to select to
> create the report ... it's blank. When I try to create a report using
[quoted text clipped - 10 lines]
> to be column headings, the parameter would only be for 15 days, start:
> 01/01/2007 end:01/15/2007.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.