Crosstab multivalue and column order

G

Guest

Hi folks. I need some help. I'm using Access 2002, and I like the way the
pivottables work with groupings, but the drilldown buttons and pulldown lists
that are offered to the user take up too much real estate for what I just
want to be a printable report. I was therefore considering a crosstab.

I basically have three values that I want to cross: Mature, Juvenile and
Seedling for each line number, which I wanted as a column, the interval being
the row. For example

Line 1 Line 2
Line 3 ...etc
Interval Mature Juvenile Seedling Mature Juvenile Seedling
0 to 10 0 2 1 1 4 7
10 to 20 3 0 19 0 0 13
20 to 20 1 0 9 0 0 8

I tried three separate queries, concatenating "Mature" and the Line Number
to each column, but when bring them together, the sort order for the columns
doesn't order in the way that I want; for something that will be dynamic,
with variable number of lines, I don't think I can just input the Column
headers in the property window. If anyone has any thoughts on this, please
let me know. Below is my table structure.

accesskastle

tblINTRESID
INTRESID-PK Counter
LINECOVERID-FK from tblLINECOVERID
LINKINTSTID-FK from tblLINKINTSTID
MATURE- Long
JUVENILE- Long
SEEDLING- Long

tblLINKLINEID
LINKLINEID-PK Counter
LINKWGBID-Long
LINENOID-Long

tblLINKINTSTID
LINKINTSTID-PK Counter
LINKLINEID-FK from tblLINKLINEID
INTSTID- FK from tblINTSTID

tblINTSTID
INTSTID-PK Counter
INTCALLED-Text
INTST-Long
INTEND-Long

tblLINECOVERID
LINECOVERID-PK Counter
WGBRESID-FK From tblWGBRESID
LINKLINEID-FK from tblLINKLINEID

tblWGBRESID
WGBRESID-PK Counter
WGBDATE-Date
LINKWGBID-Long
 
D

Duane Hookom

I think one issue is that your table is not normalized. Having separate
fields for Mature, Juvenile, Seedling isn't a good structure for creating
reports like this. If you can't change your table structure, you can use a
union query:
SELECT INTRESID, LINECOVERID, LINKINTSTID, "MATURE" as Age, [MATURE] as
NumOf
FROM tblINTRESID
UNION ALL
SELECT INTRESID, LINECOVERID, LINKINTSTID, "JUVENILE", [JUVENILE]
FROM tblINTRESID
UNION ALL
SELECT INTRESID, LINECOVERID, LINKINTSTID, "SEEDLING", [SEEDLING]
FROM tblINTRESID;

You can then think about creating a crosstab with the Line Number and Age as
the Column Heading.
 
G

Guest

Thanks for the reply, Duane. I kind of got around it by changing mature to
adult ("A") and using the suggestion from another website to concatenate line
number and age, with the following SQL which uses a partial Cartesian, and a
table FldName with only one field.

tblFldName
FldName-Text (A, J, or S)

TRANSFORM Sum(IIf([FldName]="A",[M],IIf([FldName]="J",[J],))) AS DaVal
SELECT qryForCross.METERS, qryForCross.INTSTID
FROM qryForCross, tblXtabColumns
GROUP BY qryForCross.METERS, qryForCross.INTSTID
ORDER BY qryForCross.INTSTID, IIf(Len([LINE])=2,[LINE],"0" & [LINE]) &
[FldName]
PIVOT IIf(Len([LINE])=2,[LINE],"0" & [LINE]) & [FldName];

But I am still designing this thing, and I want to get it right. How do you
think I should modify my tables to better suit this?

accesskastle

Duane Hookom said:
I think one issue is that your table is not normalized. Having separate
fields for Mature, Juvenile, Seedling isn't a good structure for creating
reports like this. If you can't change your table structure, you can use a
union query:
SELECT INTRESID, LINECOVERID, LINKINTSTID, "MATURE" as Age, [MATURE] as
NumOf
FROM tblINTRESID
UNION ALL
SELECT INTRESID, LINECOVERID, LINKINTSTID, "JUVENILE", [JUVENILE]
FROM tblINTRESID
UNION ALL
SELECT INTRESID, LINECOVERID, LINKINTSTID, "SEEDLING", [SEEDLING]
FROM tblINTRESID;

You can then think about creating a crosstab with the Line Number and Age as
the Column Heading.

--
Duane Hookom
MS Access MVP
--

accesskastle said:
Hi folks. I need some help. I'm using Access 2002, and I like the way
the
pivottables work with groupings, but the drilldown buttons and pulldown
lists
that are offered to the user take up too much real estate for what I just
want to be a printable report. I was therefore considering a crosstab.

I basically have three values that I want to cross: Mature, Juvenile and
Seedling for each line number, which I wanted as a column, the interval
being
the row. For example

Line 1 Line 2
Line 3 ...etc
Interval Mature Juvenile Seedling Mature Juvenile Seedling
0 to 10 0 2 1 1 4 7
10 to 20 3 0 19 0 0 13
20 to 20 1 0 9 0 0 8

I tried three separate queries, concatenating "Mature" and the Line Number
to each column, but when bring them together, the sort order for the
columns
doesn't order in the way that I want; for something that will be dynamic,
with variable number of lines, I don't think I can just input the Column
headers in the property window. If anyone has any thoughts on this,
please
let me know. Below is my table structure.

accesskastle

tblINTRESID
INTRESID-PK Counter
LINECOVERID-FK from tblLINECOVERID
LINKINTSTID-FK from tblLINKINTSTID
MATURE- Long
JUVENILE- Long
SEEDLING- Long

tblLINKLINEID
LINKLINEID-PK Counter
LINKWGBID-Long
LINENOID-Long

tblLINKINTSTID
LINKINTSTID-PK Counter
LINKLINEID-FK from tblLINKLINEID
INTSTID- FK from tblINTSTID

tblINTSTID
INTSTID-PK Counter
INTCALLED-Text
INTST-Long
INTEND-Long

tblLINECOVERID
LINECOVERID-PK Counter
WGBRESID-FK From tblWGBRESID
LINKLINEID-FK from tblLINKLINEID

tblWGBRESID
WGBRESID-PK Counter
WGBDATE-Date
LINKWGBID-Long
Click to expand...
Click to expand...
 
D

Duane Hookom

Your "suggestion from another website" looks somewhat familiar
http://msaccessadvisor.com/doc/13107.

As I suggested, try to not use values (ages) as field names. I have several
more crosstab samples at a previous employer's web site
http://www.invisibleinc.com/divFiles.cfm?divDivID=4;

--
Duane Hookom
MS Access MVP


accesskastle said:
Thanks for the reply, Duane. I kind of got around it by changing mature
to
adult ("A") and using the suggestion from another website to concatenate
line
number and age, with the following SQL which uses a partial Cartesian, and
a
table FldName with only one field.

tblFldName
FldName-Text (A, J, or S)

TRANSFORM Sum(IIf([FldName]="A",[M],IIf([FldName]="J",[J],))) AS DaVal
SELECT qryForCross.METERS, qryForCross.INTSTID
FROM qryForCross, tblXtabColumns
GROUP BY qryForCross.METERS, qryForCross.INTSTID
ORDER BY qryForCross.INTSTID, IIf(Len([LINE])=2,[LINE],"0" & [LINE]) &
[FldName]
PIVOT IIf(Len([LINE])=2,[LINE],"0" & [LINE]) & [FldName];

But I am still designing this thing, and I want to get it right. How do
you
think I should modify my tables to better suit this?

accesskastle

Duane Hookom said:
I think one issue is that your table is not normalized. Having separate
fields for Mature, Juvenile, Seedling isn't a good structure for creating
reports like this. If you can't change your table structure, you can use
a
union query:
SELECT INTRESID, LINECOVERID, LINKINTSTID, "MATURE" as Age, [MATURE] as
NumOf
FROM tblINTRESID
UNION ALL
SELECT INTRESID, LINECOVERID, LINKINTSTID, "JUVENILE", [JUVENILE]
FROM tblINTRESID
UNION ALL
SELECT INTRESID, LINECOVERID, LINKINTSTID, "SEEDLING", [SEEDLING]
FROM tblINTRESID;

You can then think about creating a crosstab with the Line Number and Age
as
the Column Heading.

--
Duane Hookom
MS Access MVP
--

accesskastle said:
Hi folks. I need some help. I'm using Access 2002, and I like the
way
the
pivottables work with groupings, but the drilldown buttons and pulldown
lists
that are offered to the user take up too much real estate for what I
just
want to be a printable report. I was therefore considering a crosstab.

I basically have three values that I want to cross: Mature, Juvenile
and
Seedling for each line number, which I wanted as a column, the interval
being
the row. For example

Line 1 Line 2
Line 3 ...etc
Interval Mature Juvenile Seedling Mature Juvenile Seedling
0 to 10 0 2 1 1 4
7
10 to 20 3 0 19 0 0
13
20 to 20 1 0 9 0 0
8

I tried three separate queries, concatenating "Mature" and the Line
Number
to each column, but when bring them together, the sort order for the
columns
doesn't order in the way that I want; for something that will be
dynamic,
with variable number of lines, I don't think I can just input the
Column
headers in the property window. If anyone has any thoughts on this,
please
let me know. Below is my table structure.

accesskastle

tblINTRESID
INTRESID-PK Counter
LINECOVERID-FK from tblLINECOVERID
LINKINTSTID-FK from tblLINKINTSTID
MATURE- Long
JUVENILE- Long
SEEDLING- Long

tblLINKLINEID
LINKLINEID-PK Counter
LINKWGBID-Long
LINENOID-Long

tblLINKINTSTID
LINKINTSTID-PK Counter
LINKLINEID-FK from tblLINKLINEID
INTSTID- FK from tblINTSTID

tblINTSTID
INTSTID-PK Counter
INTCALLED-Text
INTST-Long
INTEND-Long

tblLINECOVERID
LINECOVERID-PK Counter
WGBRESID-FK From tblWGBRESID
LINKLINEID-FK from tblLINKLINEID

tblWGBRESID
WGBRESID-PK Counter
WGBDATE-Date
LINKWGBID-Long
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Combobox Not style 1
Refreshing subform 5

Top