Time based Report

  • Thread starter Thread starter Harold
  • Start date Start date
H

Harold

I am new to access, so I am hoping there is an easy answer to my question..

I have described the Table and report layouts below.
What I need help with is understanding how to build the quires to make
the report as it has been laid out possible.
I am having trouble building a report to the requested design because
the data does not naturally layout the way the report is laid out.

I need some idea on how to build the report.
I have the quires built the group and summire the data to the groups I need,
but they layout like the table with Time in a column rather than a row.

Base DataTable
Item ID Sale$ Sales Unts Time
PL1001 500 10 6/19/2004
PL1002 500 10 6/19/2004
PL1003 500 10 6/19/2004

Attribute Table
ItemID Attribute
PL1001 Green
PL1002 Red
PL1003 Green
PL1004 Red

Time Table
Time Theme
5/8/2004 Spr1
5/15/2004 Spr2
5/22/2004 Spr2
5/29/2004 Sum


Report Layout
Theme Spr1 Spr2
Sum

Attribute
Red
Sale$
SaleUnt

Green
Sales$
SalesUnt
 
Harold, the way this window displays I can not quite make
out what the intended layout is please show again in a
smaller area.
Fons
 
Report Layout
Theme
Spr1 Spr2 Sum
Attribute
Red
Sale$ 1 2 3
SaleUnt 1 1 1
Green
Sales$ 1 2 3
SalesUnt 1 1 1

The layout has the Spr1, Spr2, & Sum

accross the top of the report
with the Attributes and Metrics going down the page
with the break on each attribtue, and the metric repeated for each attribute.

Thanks
Harold
 
Harold.
First there is a difficulty with the tables
Base DataTable
Item ID Sale$ Sales Unts Time
PL1001 500 10 6/19/2004
PL1002 500 10 6/19/2004
PL1003 500 10 6/19/2004

Attribute Table
ItemID Attribute
PL1001 Green
PL1002 Red
PL1003 Green
PL1004 Red

Time Table
Time Theme
5/8/2004 Spr1
5/15/2004 Spr2
5/22/2004 Spr2
5/29/2004 Sum

The Base Data relates to the Attributes by item ID,
however, I do not see how the Time Table relates to the
other two.
Anyway, once you set the relationships for the tables you
need to create two crosstab queries:
Select each of you tables, Attribute, BaseData, and Time,
Set relations on ItemID from Attribute to BaseData All
from Atribute and matching from BaseData. than also from
BaseData to Time on whatever field you set the
relationship.
Now select the following fields from tables:
Attribute from Attribute Table set as row header
Theme from TimeTable set as clumn header
Sales from BaseData Table set as value.
Now for the second crosstab make it identical to the first
except:
SalesUnits from BaseData Table set as value.

Tahn in your report merge these two crosstab querries
merged on attribute, and now you can create the desired
layout of your report.

Hope this helps and gets you going in the right direction.
I am leaving for the next two weeks so if you need
additional help please start a new thread copying this
last message with you additional questions.
Fons
 
Back
Top