Convert Columnar Data To A Linear View In A New Table

[BrianP]

I have an annual transaction history table that contains columnar billing
transactions by date such as:

Acct Inv InvDate Amt
123 987 1/15/08 10.00
456 988 1/15/08 15.00
123 994 2/15/08 10.00
456 995 2/15/08 15.00
123 998 3/15/08 10.00
456 999 3/15/08 15.00

What I need is to append/write the data to a new table that shows the
transactions in a linear format such as:

Acct JanInv JaDate JaAmt FebInv FeDate FeAmt MaInv MaDate MaAmt
123 987 1/15/08 10.00 994 2/15/08 10.00 998 3/15/08
10.00
456 988 1/15/08 15.00 995 2/15/08 15.00 999 3/15/08
15.00

Any suggestions on the best way to convert this data to a linear view in a
new table would be greatly appreciated.
Thanks.
BP

 

[pietlinden]

I have an annual transaction history table that contains columnar billing
transactions by date such as:

Acct   Inv   InvDate   Amt
123    987  1/15/08  10.00
456    988  1/15/08  15.00
123    994  2/15/08  10.00
456    995  2/15/08  15.00
123    998  3/15/08  10.00
456    999  3/15/08  15.00

What I need is to append/write the data to a new table that shows the
transactions in a linear format such as:

Acct  JanInv   JaDate   JaAmt  FebInv   FeDate   FeAmt  MaInv   MaDate   MaAmt
123    987     1/15/08  10.00    994     2/15/08   10..00    998     3/15/08  
 10.00
456    988     1/15/08  15.00    995     2/15/08   15..00    999     3/15/08  
 15.00  

Any suggestions on the best way to convert this data to a linear view in a
new table would be greatly appreciated.
Thanks.
BP

it's called a crosstab query.

 

[BrianP]

Yes, I have used crosstab queries as a recordset for reports. However, what I
am looking for is a way to make a new table or append to an existing table
with the new linear table structure not just report in a crosstab view.

 

[BrianP]

Okay, this is in no way a standard access database. What we are trying to
accomplish is to create a small scale data warehouse in access in a linear
format so that our financial analysts can easily export into excel for their
statistical analysis. The original set of data is part of a large oracle
database and is being queried in pl/sql and exported into access with the
thought that it could be more easily converted into a linear format in access
with monthly bill segment detail (not a summary/total) per account. We need
the line item detail but in a linear format.

Now with that being said, any ideas?

 

[Clif McIrvin]

Okay, this is in no way a standard access database. What we are trying
to
accomplish is to create a small scale data warehouse in access in a
linear
format so that our financial analysts can easily export into excel for
their
statistical analysis. The original set of data is part of a large
oracle
database and is being queried in pl/sql and exported into access with
the
thought that it could be more easily converted into a linear format in
access
with monthly bill segment detail (not a summary/total) per account. We
need
the line item detail but in a linear format.

Now with that being said, any ideas?

Why can't you just do a TransferSpreadsheet off of your crosstab query?

 

[BrianP]

A crosstab requires the summation of data at a column and row intersection.
That would give me a monthly count, sum, first, last, min or max in a linear
view exported to excel. The problem with that is that I would lose the
necessary monthly line item detail formatted linearly which is the desired
result.

 

[Clif McIrvin]

A crosstab requires the summation of data at a column and row
intersection.
That would give me a monthly count, sum, first, last, min or max in a
linear
view exported to excel. The problem with that is that I would lose the
necessary monthly line item detail formatted linearly which is the
desired
result.

I've lost track of your OP, so don't have a clear recall of your table
structure.

I would imagine that you can construct a query (or a set of nested
queries) to create the necessary datasheet view. (In fact, were you to
actually create the table you are requesting, queries are THE tool you
would use.)

When you have the view the way you want it, you can use the
TransferSpreadsheet action or method to export the data to Excel.

I can think of no advantage to be gained from taking the intermediate
step of actually creating the linear table you are describing. IIRC, in
the rest of the RDBMS world what Access calls "Queries" are known as
"Views". In point of fact, you NEVER look at your data (unless you are
really, really good with tools like the old MSDOS Debug) ... you only
look at Views.

That being said, for many processes there is no practical difference
between using a table (vs.) a query for your recordsource ... and
Queries give you orders of magnitude greater power and flexibility in
what you can do and how you can do it.

 

[BrianP]

Makes sense. Thanks.

I've lost track of your OP, so don't have a clear recall of your table
structure.

I would imagine that you can construct a query (or a set of nested
queries) to create the necessary datasheet view. (In fact, were you to
actually create the table you are requesting, queries are THE tool you
would use.)

When you have the view the way you want it, you can use the
TransferSpreadsheet action or method to export the data to Excel.

I can think of no advantage to be gained from taking the intermediate
step of actually creating the linear table you are describing. IIRC, in
the rest of the RDBMS world what Access calls "Queries" are known as
"Views". In point of fact, you NEVER look at your data (unless you are
really, really good with tools like the old MSDOS Debug) ... you only
look at Views.

That being said, for many processes there is no practical difference
between using a table (vs.) a query for your recordsource ... and
Queries give you orders of magnitude greater power and flexibility in
what you can do and how you can do it.