Show 12 months in one field to 12 fields

[DL]

I have one table with 4 fields: CostCenter, GL, Volume, Month. This table is
updated monthly and the value in the month is current month, ie for this
month it is 01/2006, next month it will be 02/2006.

Now I have to convert this table to 14 fields: CostCenter, GL, M1, M2, M3,
M4 ….. M12.
With M1…. M12 as Janurary to December.

What is the best way for me to convert the table and how to do it? I am
using Access 2003.

Thanks in advance for your help.

 

[Rick Brandt]

I have one table with 4 fields: CostCenter, GL, Volume, Month. This
table is updated monthly and the value in the month is current month,
ie for this month it is 01/2006, next month it will be 02/2006.

Now I have to convert this table to 14 fields: CostCenter, GL, M1,
M2, M3, M4 ... M12.
With M1.. M12 as Janurary to December.

What is the best way for me to convert the table and how to do it? I
am using Access 2003.

Thanks in advance for your help.

A crosstab query will do that. It's one of the choices for the new query
wizard.

 

[DL]

Thanks very much. It works.

Since my monthly volume table has only 1 month, so I have to append 12
records with month from M1 to M12 to get all 12 fields. The problem now is
only one field has value. All others are blank. I have to update the fields
12 times to fill the blank with zeros which is required format. How can I do
achieve this goal in a simplied way?

Thanks very much for your input. I really appreciate it.

DL

 

[Rick Brandt]

Thanks very much. It works.

Since my monthly volume table has only 1 month, so I have to append 12
records with month from M1 to M12 to get all 12 fields. The problem
now is only one field has value. All others are blank. I have to
update the fields 12 times to fill the blank with zeros which is
required format. How can I do achieve this goal in a simplied way?

Thanks very much for your input. I really appreciate it.

Go into design view of the crosstab query and open the property sheet
(View - Properties). Place the cursor into the column that has "Column
Heading" in the crosstab row of the designer. In the property sheet you
should then see a property named "Column Headings". In that property you
can enter all of the columns that you could possible get back from the query
as comma separated entries like...

"M1", "M2", "M3", etc..

This does two things. It allows you to control the order that the columns
are displayed and it forces the display of all columns listed even when
there is no data for a particular column.

For the columns with no data you can change the expression used for the
"Value" column adding the Nz() function so that Nulls are replaced with
zeros. In the "Value" column where you currently have "Sum", change that to
"Expression". Then in the top row labelled "Field" change the name of the
field to...

Nz(Sum(FieldName), 0)

That should cause fields in the output that otherwise would be null to
display zeros instead.

 

[DL]

Rick:

You are so great. Thanks very much.

DL

Go into design view of the crosstab query and open the property sheet
(View - Properties). Place the cursor into the column that has "Column
Heading" in the crosstab row of the designer. In the property sheet you
should then see a property named "Column Headings". In that property you
can enter all of the columns that you could possible get back from the query
as comma separated entries like...

"M1", "M2", "M3", etc..

This does two things. It allows you to control the order that the columns
are displayed and it forces the display of all columns listed even when
there is no data for a particular column.

For the columns with no data you can change the expression used for the
"Value" column adding the Nz() function so that Nulls are replaced with
zeros. In the "Value" column where you currently have "Sum", change that to
"Expression". Then in the top row labelled "Field" change the name of the
field to...

Nz(Sum(FieldName), 0)

That should cause fields in the output that otherwise would be null to
display zeros instead.