Create Table style report

[Simon]

I am wanting to create a table style report from a table containing the
following records; Machines (text), week No's (No's), m/c hrs (No's).

The report i want to create is a sum of the total m/c hrs for a machine for
that week as follows;

Week No 35 36 37 38 39 40
Machine
H9 8 10 4 6 34 5
VTL 9 12 9 3 12 10
GAN 14 8 2 0 16 5

I also want to select between two week no's for the range i require.
I have started using a crosstab query but it struggles with the between
function.
I am also unsure how to transfer this into a report in a table format.

 

[BruceM]

If your week numbers are fields it will be very difficult to do what you
want, particularly when it comes to selecting a date range. Also, it is not
sound database design. To name just one difficulty, you wioll need a new
table every year. There should be a separate MachineHours table related to
the Machine table.

tblMachine
MachineID (primary key)
MachineDescription
AcquiredDate
etc.

tblMachineHours
MH_ID (primary key)
MachineID (foreign key)
WeekStart (Date field)
Hours (Number - Double if you will use fractional hours)

Build a form based on tblMachine, with a subform on tblMachineHours. This
is for data entry.

For the report you could build a report based on qryMachine and
qryMachineHours (these are queries based on the similarly named tables). To
obtain the week number you could use the Format function in a blank column
in query design view:
WeekNumber: Format([WeekStart],"ww")

In the criteria for WeekNumber you could have:
Between [Enter Start Week ] and [Enter End Week]

There are lots of ways to do this, depending on the needs and the details.
Do you want to see reports for all machines for the range of weeks, or for
just one machine? Would you rather choose dates than week numbers?

 

[John Spencer]

First question
With a crosstab query you MUST declare your parameters and if any
other queries are used in the crosstab their parameters must also be declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

Second question
Quoting MVP Duane Hookom
If you want to allow any number of columns with extra columns "wrapping" under
the first group of columns, this can be done. It is a little beyond what I
would care to explain in a message. I have a demo at

http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=11

The Crosstab demo is in Access 97 but could easily accomodate later versions.


Duane Hookom
Microsoft Access MVP

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County