Cross Tab Queries

  • Thread starter Thread starter James
  • Start date Start date
J

James

I am having some serious difficulties understanding
these. I need to create a tabular report and was told
this would be the best way but I can seem to create what
seems to be a report which is based on two cross tab
queries please see below for an example of what i am
trying to create...

Drawing ID ¦ 12.03.03 ¦ 13.03.03 ¦ 14.03.03 ¦ more dates..¦
-----------------------------------------------------------
a16413 ¦ a ¦ ¦ b ¦
a16414 ¦ b ¦ c ¦ d ¦
a16415 ¦ f ¦ g ¦ ¦

-----------------------------------------------------------
sent to ¦
-----------------------------------------------------------
P Smith ¦ 1 ¦ 1 ¦ 2 ¦
M Green ¦ 1 ¦ ¦ ¦
G Johnson ¦ 1 ¦ ¦ 1 ¦

----------------------------------------------------------

.....the letters stand for the revision of the drawing
sent
and the names are the people who recevied the drawings
listed above them and how many sets they received.

James
 
P smith received 1 set of the the three drawings with the
revision letter liested above the "1"
 
Your sample display doesn't seem to suggest which drawings were sent to
which person. How would I know which 4 drawings P Smith received?

--
Duane Hookom
MS Access MVP


I am having some serious difficulties understanding
these. I need to create a tabular report and was told
this would be the best way but I can seem to create what
seems to be a report which is based on two cross tab
queries please see below for an example of what i am
trying to create...

Drawing ID ¦ 12.03.03 ¦ 13.03.03 ¦ 14.03.03 ¦ more dates..¦
-----------------------------------------------------------
a16413 ¦ a ¦ ¦ b ¦
a16414 ¦ b ¦ c ¦ d ¦
a16415 ¦ f ¦ g ¦ ¦

-----------------------------------------------------------
sent to ¦
-----------------------------------------------------------
P Smith ¦ 1 ¦ 1 ¦ 2 ¦
M Green ¦ 1 ¦ ¦ ¦
G Johnson ¦ 1 ¦ ¦ 1 ¦

----------------------------------------------------------

.....the letters stand for the revision of the drawing
sent
and the names are the people who recevied the drawings
listed above them and how many sets they received.

James
 
So, in the days displayed, P Smith actually received 9 sets of drawings?
Also, what is the structure of your original tables. We can't tell you how
to get someplace if we don't know where you are starting from.

--
Duane Hookom
MS Access MVP


P smith received 1 set of the the three drawings with the
revision letter liested above the "1"
 
Sorry, youll have to forgive my lack of knwoledge I am
only working on this part-time over the summer.

In answer to your last question... P Smith got nine
speartae drawings on 3 on one date, 2 on one date and 2 on
the other. One set on one date.

I have the tables, "Issue Sheet", "Recipients",
and "Drawings".

I have a form Issue Sheet in which there are the
Subforms "Recpients" and "Drawings".

The idea is that people record what drawings are issued
from our office, to whom and on which date.

In the form Issue Sheet, users, in the main form, will
select the project for which they are issuing and the date
of issue.

They then go to the first sub form and enter all the
recipients for the drawings. In the second subform they
will then enter all the individual drawings that are to be
issued to these recipients. Collectively these drawings
then are a set.

A report is then create based on a certain date range from
this data, so i mite wish the look at all the drawings
issued for one specific project over one month.

I have created many reports in what i consider the
standard fashion but i have been specifically asked to
create a report based on the information in a tabular
format as described below.


Hope that is of some help.

james
 
I still don't have an idea of your table structure. Can you create a query
that has these fields:
IssueDate
Recipient
DrawingID
Revision
NumOfCopies

This would have one record per person per issue date per drawing.
You could then create a form (frmRptSlct) with a text box (txtEndDate). Lets
assume you want 5 dates as headings with the last date being the date from
the text box.

Create a query that has the above mentioned fields. Make it into a crosstab
by selecting this from the menu. Set the DrawingID as the Row Heading, "Day"
& DateDiff("d",IssueDate, Forms!frmRpSlct!txtEndDate) as the Column Heading,
and First of Revision for the Value. Menu Select Query|Parameters and enter:
Forms!frmRpSlct!txtEndDate Date/Time
Set the Column Headings property of the crosstab to:
"Day0","Day1",..."Day4"
This will create your first part of the report with Day0 being the EndDate
and Day4 being 4 days prior.
Create a report from this crosstab.

Then create another similar crosstab only use Recipient as the Row Heading
and Sum of NumOfCopies as the Value.
Create a report from this crosstab.

Create a new report and add each report as subreports.

--
Duane Hookom
MS Access MVP


Sorry, youll have to forgive my lack of knwoledge I am
only working on this part-time over the summer.

In answer to your last question... P Smith got nine
speartae drawings on 3 on one date, 2 on one date and 2 on
the other. One set on one date.

I have the tables, "Issue Sheet", "Recipients",
and "Drawings".

I have a form Issue Sheet in which there are the
Subforms "Recpients" and "Drawings".

The idea is that people record what drawings are issued
from our office, to whom and on which date.

In the form Issue Sheet, users, in the main form, will
select the project for which they are issuing and the date
of issue.

They then go to the first sub form and enter all the
recipients for the drawings. In the second subform they
will then enter all the individual drawings that are to be
issued to these recipients. Collectively these drawings
then are a set.

A report is then create based on a certain date range from
this data, so i mite wish the look at all the drawings
issued for one specific project over one month.

I have created many reports in what i consider the
standard fashion but i have been specifically asked to
create a report based on the information in a tabular
format as described below.


Hope that is of some help.

james
 
Back
Top