Pivot Query Non-Numeric reporting

[Dan Woods]

I have the "many side" of a one-to-many relationship (i.e. multiple rows) and
I want to flatten this to make these rows into columns. I am trying the
pivot query with the transform statement, but I don't believe it is going to
work. Can I write a non-numeric cross-tab query to a make-table query and
try to report this new columnized data from that table instead of the
original, "many" table?

 

[KARL DEWEY]

How is this?

TRANSFORM First([StatusDate] & " - " & [Remarks]) AS Expr1
SELECT [PO-Table].PO
FROM [PO-Table]
GROUP BY [PO-Table].PO
PIVOT [PO-Table].Status;

 

[Dan Woods]

Karl,

I have Purchase Order statuses in a many relationship, i.e.:
P/O number (Bridge between P/O header and P/O statuses)
P/O Status Description (~7 possible statuses)
P/O Status Date
P/O Status Remarks

right now:
PO Stat Desc Date Remarks
123 Approved 8/29/05 N/A
123 Rejected 8/30/05 Blah Blah
123 At Hub 8/31/05 Ready to Go

I have a pivot query that has PO as the row group and Description as the
column grouping and I want to make this data columized as:

PO Approved Rejected At Hub
123 8/29/05 N/A 8/30/05 Blah Blah 8/31/05 Ready to Go

This would change the "many" data into a one-to-one relationship to match up
against P/O header data with PO Number, Supplier, Country, etc. so I can put
this new view of the data in a report and export the report to excel for
further analysis and calculations.

Thanks,
Dan

 

[J Shrimps, Jr.]

I just posted this on 8/28
Does this answer apply also?

Prev. post:
"I have imported a table from
a log output with a sample like this:

fluxcapacitor .80
widgit in stock
flibertygidit 3.2
(blank record)
fluxcapacitor 1.10
widgit ordered
flibertygidit 2.3
(blank record)
Fuxcapacitor 4.01
widgit pending
flibertygidit .734
(blank record)

-There are about a dozen repeating metrics from
the log file separated by a blank record.

I'm trying to build a table like this:"

Record# fluxcapacitor widgit flibertygidit
1 .80 in stock 3.2
2 1.1 ordered 2.3
3 4.01 pending 7.34

Post ends.

How is this?

TRANSFORM First([StatusDate] & " - " & [Remarks]) AS Expr1
SELECT [PO-Table].PO
FROM [PO-Table]
GROUP BY [PO-Table].PO
PIVOT [PO-Table].Status;

Karl,

I have Purchase Order statuses in a many relationship, i.e.:
P/O number (Bridge between P/O header and P/O statuses)
P/O Status Description (~7 possible statuses)
P/O Status Date
P/O Status Remarks

right now:
PO Stat Desc Date Remarks
123 Approved 8/29/05 N/A
123 Rejected 8/30/05 Blah Blah
123 At Hub 8/31/05 Ready to Go

I have a pivot query that has PO as the row group and Description as the
column grouping and I want to make this data columized as:

PO Approved Rejected At Hub
123 8/29/05 N/A 8/30/05 Blah Blah 8/31/05 Ready to Go

This would change the "many" data into a one-to-one relationship to match up
against P/O header data with PO Number, Supplier, Country, etc. so I can put
this new view of the data in a report and export the report to excel for
further analysis and calculations.

Thanks,
Dan

 

[KARL DEWEY]

Yours is different. I am making the aussumption that your data alway is in
groups of four.
When you import do so in a table that has an autonumber that will begin with
1. Put the other data in Field1 and Field2.
Use these two queries. The second query has the first as data source.

SELECT IIf([num]/4=Int([num]/4),([num]/4),Int([num]/4)+1) AS Expr1,
ImportTable.Num,
[num]-(IIf([num]/4=Int([num]/4),([num]/4),Int([num]/4)+1)-1)*4 AS Expr2,
ImportTable.Field1, ImportTable.Field2
FROM ImportTable
WHERE ((([num]-(IIf([num]/4=Int([num]/4),([num]/4),Int([num]/4)+1)-1)*4)<>4));

TRANSFORM First(ConsolidateQuery.Field2) AS [The Value]
SELECT ConsolidateQuery.Expr1
FROM ConsolidateQuery
GROUP BY ConsolidateQuery.Expr1
PIVOT ConsolidateQuery.Field1;

I just posted this on 8/28
Does this answer apply also?

Prev. post:
"I have imported a table from
a log output with a sample like this:

fluxcapacitor .80
widgit in stock
flibertygidit 3.2
(blank record)
fluxcapacitor 1.10
widgit ordered
flibertygidit 2.3
(blank record)
Fuxcapacitor 4.01
widgit pending
flibertygidit .734
(blank record)

-There are about a dozen repeating metrics from
the log file separated by a blank record.

I'm trying to build a table like this:"

Record# fluxcapacitor widgit flibertygidit
1 .80 in stock 3.2
2 1.1 ordered 2.3
3 4.01 pending 7.34

Post ends.

How is this?

TRANSFORM First([StatusDate] & " - " & [Remarks]) AS Expr1
SELECT [PO-Table].PO
FROM [PO-Table]
GROUP BY [PO-Table].PO
PIVOT [PO-Table].Status;

Karl,

I have Purchase Order statuses in a many relationship, i.e.:
P/O number (Bridge between P/O header and P/O statuses)
P/O Status Description (~7 possible statuses)
P/O Status Date
P/O Status Remarks

right now:
PO Stat Desc Date Remarks
123 Approved 8/29/05 N/A
123 Rejected 8/30/05 Blah Blah
123 At Hub 8/31/05 Ready to Go

I have a pivot query that has PO as the row group and Description as the
column grouping and I want to make this data columized as:

PO Approved Rejected At Hub
123 8/29/05 N/A 8/30/05 Blah Blah 8/31/05 Ready to Go

This would change the "many" data into a one-to-one relationship to match up
against P/O header data with PO Number, Supplier, Country, etc. so I can put
this new view of the data in a report and export the report to excel for
further analysis and calculations.

Thanks,
Dan

:

Post an example of your data and how you would like it to be.

:

I have the "many side" of a one-to-many relationship (i.e. multiple rows) and
I want to flatten this to make these rows into columns. I am trying the
pivot query with the transform statement, but I don't believe it is going to
work. Can I write a non-numeric cross-tab query to a make-table query and
try to report this new columnized data from that table instead of the
original, "many" table?

 

[J Shrimps, Jr.]

I will try tommarrow @ work (Aug 31st).
May need some tweaking, if you can stay tuned.
Many thanx.

Yours is different. I am making the aussumption that your data alway is in
groups of four.
When you import do so in a table that has an autonumber that will begin with
1. Put the other data in Field1 and Field2.
Use these two queries. The second query has the first as data source.

SELECT IIf([num]/4=Int([num]/4),([num]/4),Int([num]/4)+1) AS Expr1,
ImportTable.Num,
[num]-(IIf([num]/4=Int([num]/4),([num]/4),Int([num]/4)+1)-1)*4 AS Expr2,
ImportTable.Field1, ImportTable.Field2
FROM ImportTable
WHERE

TRANSFORM First(ConsolidateQuery.Field2) AS [The Value]
SELECT ConsolidateQuery.Expr1
FROM ConsolidateQuery
GROUP BY ConsolidateQuery.Expr1
PIVOT ConsolidateQuery.Field1;

I just posted this on 8/28
Does this answer apply also?

Prev. post:
"I have imported a table from
a log output with a sample like this:

fluxcapacitor .80
widgit in stock
flibertygidit 3.2
(blank record)
fluxcapacitor 1.10
widgit ordered
flibertygidit 2.3
(blank record)
Fuxcapacitor 4.01
widgit pending
flibertygidit .734
(blank record)

-There are about a dozen repeating metrics from
the log file separated by a blank record.

I'm trying to build a table like this:"

Record# fluxcapacitor widgit flibertygidit
1 .80 in stock 3.2
2 1.1 ordered 2.3
3 4.01 pending 7.34

Post ends.

How is this?

TRANSFORM First([StatusDate] & " - " & [Remarks]) AS Expr1
SELECT [PO-Table].PO
FROM [PO-Table]
GROUP BY [PO-Table].PO
PIVOT [PO-Table].Status;


:

Karl,

I have Purchase Order statuses in a many relationship, i.e.:
P/O number (Bridge between P/O header and P/O statuses)
P/O Status Description (~7 possible statuses)
P/O Status Date
P/O Status Remarks

right now:
PO Stat Desc Date Remarks
123 Approved 8/29/05 N/A
123 Rejected 8/30/05 Blah Blah
123 At Hub 8/31/05 Ready to Go

I have a pivot query that has PO as the row group and Description as the
column grouping and I want to make this data columized as:

PO Approved Rejected At Hub
123 8/29/05 N/A 8/30/05 Blah Blah 8/31/05 Ready to Go

This would change the "many" data into a one-to-one relationship to match up
against P/O header data with PO Number, Supplier, Country, etc. so

I
can put

this new view of the data in a report and export the report to excel for
further analysis and calculations.

Thanks,
Dan

:

Post an example of your data and how you would like it to be.

:

I have the "many side" of a one-to-many relationship (i.e.

multiple
rows) and

I want to flatten this to make these rows into columns. I am

trying
the

pivot query with the transform statement, but I don't believe it

is
going to

work. Can I write a non-numeric cross-tab query to a make-table query and
try to report this new columnized data from that table instead

of
the

original, "many" table?

 

[J Shrimps, Jr.]

You are a genius!

Now, in the one log file I used,
there were 23 distinct fields, separated
by a blank or null record.
Is it necessary to do the Int([num]/4 bit
if a null record is the start of a new set of data?
ie. when a null record is found, the first value
of 23 has been found...

I will try tommarrow @ work (Aug 31st).
May need some tweaking, if you can stay tuned.
Many thanx.

Yours is different. I am making the aussumption that your data alway is in
groups of four.
When you import do so in a table that has an autonumber that will begin with
1. Put the other data in Field1 and Field2.
Use these two queries. The second query has the first as data source.

SELECT IIf([num]/4=Int([num]/4),([num]/4),Int([num]/4)+1) AS Expr1,
ImportTable.Num,
[num]-(IIf([num]/4=Int([num]/4),([num]/4),Int([num]/4)+1)-1)*4 AS Expr2,
ImportTable.Field1, ImportTable.Field2
FROM ImportTable
WHERE

TRANSFORM First(ConsolidateQuery.Field2) AS [The Value]
SELECT ConsolidateQuery.Expr1
FROM ConsolidateQuery
GROUP BY ConsolidateQuery.Expr1
PIVOT ConsolidateQuery.Field1;

I just posted this on 8/28
Does this answer apply also?

Prev. post:
"I have imported a table from
a log output with a sample like this:

fluxcapacitor .80
widgit in stock
flibertygidit 3.2
(blank record)
fluxcapacitor 1.10
widgit ordered
flibertygidit 2.3
(blank record)
Fuxcapacitor 4.01
widgit pending
flibertygidit .734
(blank record)

-There are about a dozen repeating metrics from
the log file separated by a blank record.

I'm trying to build a table like this:"

Record# fluxcapacitor widgit flibertygidit
1 .80 in stock 3.2
2 1.1 ordered 2.3
3 4.01 pending 7.34

Post ends.


How is this?

TRANSFORM First([StatusDate] & " - " & [Remarks]) AS Expr1
SELECT [PO-Table].PO
FROM [PO-Table]
GROUP BY [PO-Table].PO
PIVOT [PO-Table].Status;


:

Karl,

I have Purchase Order statuses in a many relationship, i.e.:
P/O number (Bridge between P/O header and P/O statuses)
P/O Status Description (~7 possible statuses)
P/O Status Date
P/O Status Remarks

right now:
PO Stat Desc Date Remarks
123 Approved 8/29/05 N/A
123 Rejected 8/30/05 Blah Blah
123 At Hub 8/31/05 Ready to Go

I have a pivot query that has PO as the row group and Description

as

the

to

Go

so

I

excel

for

it

is

 

[KARL DEWEY]

Your example had the data in groups of four so that is how I developed the
query.

If your data has 23 plus blank then you will need to tweak the query.

It will not work if some of the data varies and has less than the standard
number.

You are a genius!

Now, in the one log file I used,
there were 23 distinct fields, separated
by a blank or null record.
Is it necessary to do the Int([num]/4 bit
if a null record is the start of a new set of data?
ie. when a null record is found, the first value
of 23 has been found...

I will try tommarrow @ work (Aug 31st).
May need some tweaking, if you can stay tuned.
Many thanx.

Yours is different. I am making the aussumption that your data alway is in
groups of four.
When you import do so in a table that has an autonumber that will begin with
1. Put the other data in Field1 and Field2.
Use these two queries. The second query has the first as data source.

SELECT IIf([num]/4=Int([num]/4),([num]/4),Int([num]/4)+1) AS Expr1,
ImportTable.Num,
[num]-(IIf([num]/4=Int([num]/4),([num]/4),Int([num]/4)+1)-1)*4 AS Expr2,
ImportTable.Field1, ImportTable.Field2
FROM ImportTable
WHERE

TRANSFORM First(ConsolidateQuery.Field2) AS [The Value]
SELECT ConsolidateQuery.Expr1
FROM ConsolidateQuery
GROUP BY ConsolidateQuery.Expr1
PIVOT ConsolidateQuery.Field1;


:

I just posted this on 8/28
Does this answer apply also?

Prev. post:
"I have imported a table from
a log output with a sample like this:

fluxcapacitor .80
widgit in stock
flibertygidit 3.2
(blank record)
fluxcapacitor 1.10
widgit ordered
flibertygidit 2.3
(blank record)
Fuxcapacitor 4.01
widgit pending
flibertygidit .734
(blank record)

-There are about a dozen repeating metrics from
the log file separated by a blank record.

I'm trying to build a table like this:"

Record# fluxcapacitor widgit flibertygidit
1 .80 in stock 3.2
2 1.1 ordered 2.3
3 4.01 pending 7.34

Post ends.


How is this?

TRANSFORM First([StatusDate] & " - " & [Remarks]) AS Expr1
SELECT [PO-Table].PO
FROM [PO-Table]
GROUP BY [PO-Table].PO
PIVOT [PO-Table].Status;


:

Karl,

I have Purchase Order statuses in a many relationship, i.e.:
P/O number (Bridge between P/O header and P/O statuses)
P/O Status Description (~7 possible statuses)
P/O Status Date
P/O Status Remarks

right now:
PO Stat Desc Date Remarks
123 Approved 8/29/05 N/A
123 Rejected 8/30/05 Blah Blah
123 At Hub 8/31/05 Ready to Go

I have a pivot query that has PO as the row group and Description

as

the
column grouping and I want to make this data columized as:

PO Approved Rejected At Hub
123 8/29/05 N/A 8/30/05 Blah Blah 8/31/05 Ready

to

Go

This would change the "many" data into a one-to-one relationship to
match up
against P/O header data with PO Number, Supplier, Country, etc.

so

I
can put
this new view of the data in a report and export the report to

excel

for
further analysis and calculations.

Thanks,
Dan

:

Post an example of your data and how you would like it to be.

:

I have the "many side" of a one-to-many relationship (i.e. multiple
rows) and
I want to flatten this to make these rows into columns. I am trying
the
pivot query with the transform statement, but I don't believe

it

is
going to
work. Can I write a non-numeric cross-tab query to a make-table
query and
try to report this new columnized data from that table instead of
the
original, "many" table?

 

[J Shrimps, Jr.]

The group of four was an example for illustration purposes.
It was easy to port the code over to the log file of 23 separate
metrics.
Each set of 23 separate metrics has a blank record after it.
Is there a way to run this same query based on a blank record
being before every new instance of 23 (or 53 or whatever),
instead of the ([num]/4=Int([num]/4),([num]/4),Int([num]/4)+1?

Your example had the data in groups of four so that is how I developed the
query.

If your data has 23 plus blank then you will need to tweak the query.

It will not work if some of the data varies and has less than the standard
number.

You are a genius!

Now, in the one log file I used,
there were 23 distinct fields, separated
by a blank or null record.
Is it necessary to do the Int([num]/4 bit
if a null record is the start of a new set of data?
ie. when a null record is found, the first value
of 23 has been found...

I will try tommarrow @ work (Aug 31st).
May need some tweaking, if you can stay tuned.
Many thanx.



Yours is different. I am making the aussumption that your data alway is
in
groups of four.
When you import do so in a table that has an autonumber that will begin
with
1. Put the other data in Field1 and Field2.
Use these two queries. The second query has the first as data source.

SELECT IIf([num]/4=Int([num]/4),([num]/4),Int([num]/4)+1) AS Expr1,
ImportTable.Num,
[num]-(IIf([num]/4=Int([num]/4),([num]/4),Int([num]/4)+1)-1)*4 AS Expr2,
ImportTable.Field1, ImportTable.Field2
FROM ImportTable
WHERE

TRANSFORM First(ConsolidateQuery.Field2) AS [The Value]
SELECT ConsolidateQuery.Expr1
FROM ConsolidateQuery
GROUP BY ConsolidateQuery.Expr1
PIVOT ConsolidateQuery.Field1;


:

I just posted this on 8/28
Does this answer apply also?

Prev. post:
"I have imported a table from
a log output with a sample like this:

fluxcapacitor .80
widgit in stock
flibertygidit 3.2
(blank record)
fluxcapacitor 1.10
widgit ordered
flibertygidit 2.3
(blank record)
Fuxcapacitor 4.01
widgit pending
flibertygidit .734
(blank record)

-There are about a dozen repeating metrics from
the log file separated by a blank record.

I'm trying to build a table like this:"

Record# fluxcapacitor widgit flibertygidit
1 .80 in stock
3.2
2 1.1 ordered
2.3
3 4.01 pending
7.34

Post ends.


How is this?

TRANSFORM First([StatusDate] & " - " & [Remarks]) AS Expr1
SELECT [PO-Table].PO
FROM [PO-Table]
GROUP BY [PO-Table].PO
PIVOT [PO-Table].Status;


:

Karl,

I have Purchase Order statuses in a many relationship, i.e.:
P/O number (Bridge between P/O header and P/O statuses)
P/O Status Description (~7 possible statuses)
P/O Status Date
P/O Status Remarks

right now:
PO Stat Desc Date Remarks
123 Approved 8/29/05 N/A
123 Rejected 8/30/05 Blah Blah
123 At Hub 8/31/05 Ready to Go

I have a pivot query that has PO as the row group and

Description
as

the
column grouping and I want to make this data columized as:

PO Approved Rejected At Hub
123 8/29/05 N/A 8/30/05 Blah Blah 8/31/05

Ready
to

Go

This would change the "many" data into a one-to-one

relationship
to

match up
against P/O header data with PO Number, Supplier, Country,

etc.
so

I
can put
this new view of the data in a report and export the report to excel
for
further analysis and calculations.

Thanks,
Dan

:

Post an example of your data and how you would like it to be.

:

I have the "many side" of a one-to-many relationship (i.e.
multiple
rows) and
I want to flatten this to make these rows into columns. I am
trying
the
pivot query with the transform statement, but I don't

believe
it

is
going to
work. Can I write a non-numeric cross-tab query to a make-table
query and
try to report this new columnized data from that table instead
of
the
original, "many" table?