Q: crosstab query with expression for values, not just counts

[Guest]

I created a calendar type crosstab query. The row header shows the "week
of:", the column header shows the day (Mon, Tue, Wed, ...) and I'm getting
the count of records as the value.

This works just fine, but to make things easier to read, I want to show the
day concatenated with the count, ie. instead of just showing 200, I want to
show "12/1/2005: 200"

Can this be done in a crosstab?

Thanks,
-Mark

 

[Tom Ellison]

Dear Mark:

A crosstab query is a special feature of Access Jet, and has not typically
been produced in the way it is done in Jet if you look at the common
database engines that are available.

Now, this is good news for you.

The Access implementation of the crosstab is not the "only way to skin the
cat."

There is al alternative to the crosstab query that is not as limited as the
Jet implementation of the crosstab.

Two of these limitations are having a "hierarchy" of column headings, and
having multiple cells for each "intersection".

Now, and "intersection" (my own personal term) means the place where a row
heading "intersects" with a column heading.

If you learn how to create a crosstab without the limited Jet crosstab
feature, then you can quickly learn how to add those additional features not
available in crosstabs.

This is not entirely a simple exercise. You must first write a query that
provides the variable column headings. In VBA code, you step through these
column heading values and generate subquery code for each, building the
overall SQL for this query as you go. Because this is such a general
approach, it provides the ability to generate multiple values (columns) for
each intersection, which is what I understand you want to do.

If you're thinking of tackling this, please let me give you some advice.

If you're a moderately advanced SQL programmer who can handle the VBA as
well, then this is a rather excellent exercise that will teach you something
new and valuable. If not, it may become a frustration and waste of time.

While you're contemplating that, may I add another advantage or "rolling
your own crosstab."

I said you can also have a hierarchy of column headings. With a Jet
crosstab, you can have only one set of column headings. Why have a two (or
more) level hierarch of column headings?

Actually, you often see this kind of hierarchy for column headings. First,
let me point out that you can have a hierarch of row headings in a crosstab.
This is not uncommon. The crosstab can have several columns on the left
that create a hierarchy of groups. If your reporting needs the same thing
in both dimensions, then you have a hierarchy of column headings as well.
The custom code generating method does this as well!

Another reason for having a coded method instead of crosstabs is the column
restriction. Of course, there is a 255 column limit, but typically the
width of a piece of paper brings this down to 20 or so. With the coding I
propose, you can continue the crosstab effect horizontally as well as
vertically. If your data creates a crosstab with, say, 100 rows and 80
columns, you can put this on 16 pieces of paper, each having 50 rows and 20
columns. You can create (with code) the 4 different queries that have all
the effect of crosstabing in sets of 20 columns each, with the row headings
repeated on each. This is a third reason to learn to roll your own
crosstabs.

Have I whetted anyone's appetite? Let me know.

As for you, Mark, I'll work with you if you post all details of what you
have and what you need. How about the crosstab you have that doesn't have
the 12/1/2005 part to start with. Does this date change within the query,
or is it just based on the date reported?

Tom Ellison

 

[Duane Hookom]

You haven't shared your sql view so a WAG suggests that you can use a value
of:
Field: theValue: First([DateField]) & ": " & Count([UnnamedField])
Crosstab: Value
Total: Expression

 

[Guest]

WAG - "wild ass guess"?

In any case, your solution works. I didn't put the aggregation function on
the date field, so I guess that's where Access had a problem. In hindsight,
this being a group by query, I should have figured that out. But at least it
makes sense..

Thanks!
-Mark

You haven't shared your sql view so a WAG suggests that you can use a value
of:
Field: theValue: First([DateField]) & ": " & Count([UnnamedField])
Crosstab: Value
Total: Expression

--
Duane Hookom
MS Access MVP

I created a calendar type crosstab query. The row header shows the "week
of:", the column header shows the day (Mon, Tue, Wed, ...) and I'm getting
the count of records as the value.

This works just fine, but to make things easier to read, I want to show
the
day concatenated with the count, ie. instead of just showing 200, I want
to
show "12/1/2005: 200"

Can this be done in a crosstab?

Thanks,
-Mark

 

[Duane Hookom]

Your WAG about WAG seems to be than just a WAG!

--
Duane Hookom
MS Access MVP

WAG - "wild ass guess"?

In any case, your solution works. I didn't put the aggregation function on
the date field, so I guess that's where Access had a problem. In
hindsight,
this being a group by query, I should have figured that out. But at least
it
makes sense..

Thanks!
-Mark

You haven't shared your sql view so a WAG suggests that you can use a
value
of:
Field: theValue: First([DateField]) & ": " & Count([UnnamedField])
Crosstab: Value
Total: Expression

--
Duane Hookom
MS Access MVP

I created a calendar type crosstab query. The row header shows the "week
of:", the column header shows the day (Mon, Tue, Wed, ...) and I'm
getting
the count of records as the value.

This works just fine, but to make things easier to read, I want to show
the
day concatenated with the count, ie. instead of just showing 200, I
want
to
show "12/1/2005: 200"

Can this be done in a crosstab?

Thanks,
-Mark