Report Populated by a crosstab query

  • Thread starter Thread starter Michael Noblet
  • Start date Start date
M

Michael Noblet

I have a report that is poulated by a cross tab query.
The querry runs off a linked table that is actually an
excel spreadsheet that updates automatically every month.

The report shows system log ins for the current month and
the previous month. The query and report layout has the
user's group as the row heading, the month as the column
heading and the # of log ins as the sum value.

so the output fileds in the report are Group, April, March
for a data set of march and april when the table is update
for april and may the query works fine but the report is
looking for a data field of march that no longer exists.

I need the output to look like a crosstab report. Any
thoughts.
 
I would approach this much differently by using "relative" months rather
than semi-hardcoding months. If you want the previous 12 months, create a
column heading expression of ColHead:"Mth" &
DateDiff("m",[DateField],Date())
This will create columns in the crosstab of "Mth0" to "MthN" where Mth0 is
this month and MthN is the oldest previous month. If you set the column
headings property to
"Mth0","Mth1","Mth2",..."Mth11"
you will always get the same number of columns with the same names and they
will always display the current and previous 11 months.

In your report, you can use text boxes as your column headings with control
sources of
=DateAdd("m",0,Date())
=DateAdd("m",-1,Date())
=DateAdd("m",-2,Date())
etc

This solution results in no coding.
 
Duane,

That is a solid solution but I would have to change the
way most of my data is presented in my queries to make
that work. But I am sure the answer lies along the same
lines.

In this cross tab query I have 3 data fields. The Group
Name, The Log In hour sum and the Month (ie:march).

There are only vere 2 months worth of Data. this month
and prvious. Each month the data table is overwritten so
as of today there would be april and May so the output has
only 2 columns that are basically a text field.

Any way to make this work without changing this and a
couple other queries?
-----Original Message-----
I would approach this much differently by using "relative" months rather
than semi-hardcoding months. If you want the previous 12 months, create a
column heading expression of ColHead:"Mth" &
DateDiff("m",[DateField],Date())
This will create columns in the crosstab of "Mth0" to "MthN" where Mth0 is
this month and MthN is the oldest previous month. If you set the column
headings property to
"Mth0","Mth1","Mth2",..."Mth11"
you will always get the same number of columns with the same names and they
will always display the current and previous 11 months.

In your report, you can use text boxes as your column headings with control
sources of
=DateAdd("m",0,Date())
=DateAdd("m",-1,Date())
=DateAdd("m",-2,Date())
etc

This solution results in no coding.


--
Duane Hookom
MS Access MVP


I have a report that is poulated by a cross tab query.
The querry runs off a linked table that is actually an
excel spreadsheet that updates automatically every month.

The report shows system log ins for the current month and
the previous month. The query and report layout has the
user's group as the row heading, the month as the column
heading and the # of log ins as the sum value.

so the output fileds in the report are Group, April, March
for a data set of march and april when the table is update
for april and may the query works fine but the report is
looking for a data field of march that no longer exists.

I need the output to look like a crosstab report. Any
thoughts.
Click to expand...


.
Click to expand...
 
Are you suggesting your data value of the Month is a text value of the month
name? If so, I would take the time to get this changed to a month number or
date.

Possibly someone else might have a work-around that works better with your
data and current queries. I have a tendency to find a flexible, efficient
solution and consistently stick with it.

--
Duane Hookom
MS Access MVP


Michael Noblet said:
Duane,

That is a solid solution but I would have to change the
way most of my data is presented in my queries to make
that work. But I am sure the answer lies along the same
lines.

In this cross tab query I have 3 data fields. The Group
Name, The Log In hour sum and the Month (ie:march).

There are only vere 2 months worth of Data. this month
and prvious. Each month the data table is overwritten so
as of today there would be april and May so the output has
only 2 columns that are basically a text field.

Any way to make this work without changing this and a
couple other queries?
-----Original Message-----
I would approach this much differently by using "relative" months rather
than semi-hardcoding months. If you want the previous 12 months, create a
column heading expression of ColHead:"Mth" &
DateDiff("m",[DateField],Date())
This will create columns in the crosstab of "Mth0" to "MthN" where Mth0 is
this month and MthN is the oldest previous month. If you set the column
headings property to
"Mth0","Mth1","Mth2",..."Mth11"
you will always get the same number of columns with the same names and they
will always display the current and previous 11 months.

In your report, you can use text boxes as your column headings with control
sources of
=DateAdd("m",0,Date())
=DateAdd("m",-1,Date())
=DateAdd("m",-2,Date())
etc

This solution results in no coding.


--
Duane Hookom
MS Access MVP


I have a report that is poulated by a cross tab query.
The querry runs off a linked table that is actually an
excel spreadsheet that updates automatically every month.

The report shows system log ins for the current month and
the previous month. The query and report layout has the
user's group as the row heading, the month as the column
heading and the # of log ins as the sum value.

so the output fileds in the report are Group, April, March
for a data set of march and april when the table is update
for april and may the query works fine but the report is
looking for a data field of march that no longer exists.

I need the output to look like a crosstab report. Any
thoughts.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top