Importing data from a "matrix" type table

[Guest]

I need to import data from a table of General Ledger data for multiple
locations, the fields in the table are:
Location, Fiscal Year,GL Account Number,Beg Cr Bal,Beg Db Bal,Period1 cr amt,
Period2 cr amt
etc to Period 12 cr amt,Period 1 db amt,Period 2 db amt,etc to Period 12 db
amt.
I need to create a table or query that contains a record for each Location
Balance for each month (12 records per location per year)... and append
records for the each month going forward

Are there any "best practices" for handling this type of data? I set up an
expression to calculate a YTD ending balance in a query. However to calculate
a balance for each month I need a seperate query for each month and then
create a union query to create a seperate record for each location for each
period. It is not that difficult to do this...but terribly cumbersome.

Would it be better to import the transaction detail and calculate total
debits and credits and balances within my application?

 

[John Vinson]

I need to import data from a table of General Ledger data for multiple
locations, the fields in the table are:
Location, Fiscal Year,GL Account Number,Beg Cr Bal,Beg Db Bal,Period1 cr amt,
Period2 cr amt
etc to Period 12 cr amt,Period 1 db amt,Period 2 db amt,etc to Period 12 db
amt.
I need to create a table or query that contains a record for each Location
Balance for each month (12 records per location per year)... and append
records for the each month going forward

A "Normalizing Union Query" works nicely for this. First create a
query by opening the SQL window and editing:

SELECT [GL Account Number],[Period1 cr amt],
DateSerial([FiscalYear], 1,1) AS PeriodDate
FROM yourtable
WHERE [Period 1 cr amt] IS NOT NULL
UNION ALL
SELECT [GL Account Number],[Period2 cr amt],
DateSerial([FiscalYear], 2,1) AS PeriodDate
FROM yourtable
WHERE [Period2 cr amt] IS NOT NULL
UNION ALL
SELECT [GL Account Number],[Period3 cr amt],
DateSerial([FiscalYear], 3,1) AS PeriodDate
FROM yourtable
WHERE [Period3 cr amt] IS NOT NULL

<etc etc>

You may need some other algorithm (using DateAdd perhaps?) if the
fiscal year doesn't start on January 1.

A separate Append query should be used to create a new table of
accounts, handling the location, fiscal year, etc.; the UNION query
above can then be used as the basis of an Append query to populate a
transactions table related one-to-many to the accounts table.

John W. Vinson[MVP]

 

[Guest]

The Fiscal Year is Dec to Nov. So December is period 1. In the union query
for the first period I changed the function to DateSerial([FiscalYear]-1,
1,1) and it set the calender year back to the prior calender year. It seemed
to work for what I need.
Also, does it matter that records group by PeriodDate month and day?
1/1/05
1/1/06
2/1/05,
2/1/06
Thanks

I need to import data from a table of General Ledger data for multiple
locations, the fields in the table are:
Location, Fiscal Year,GL Account Number,Beg Cr Bal,Beg Db Bal,Period1 cr amt,
Period2 cr amt
etc to Period 12 cr amt,Period 1 db amt,Period 2 db amt,etc to Period 12 db
amt.
I need to create a table or query that contains a record for each Location
Balance for each month (12 records per location per year)... and append
records for the each month going forward

A "Normalizing Union Query" works nicely for this. First create a
query by opening the SQL window and editing:

SELECT [GL Account Number],[Period1 cr amt],
DateSerial([FiscalYear], 1,1) AS PeriodDate
FROM yourtable
WHERE [Period 1 cr amt] IS NOT NULL
UNION ALL
SELECT [GL Account Number],[Period2 cr amt],
DateSerial([FiscalYear], 2,1) AS PeriodDate
FROM yourtable
WHERE [Period2 cr amt] IS NOT NULL
UNION ALL
SELECT [GL Account Number],[Period3 cr amt],
DateSerial([FiscalYear], 3,1) AS PeriodDate
FROM yourtable
WHERE [Period3 cr amt] IS NOT NULL

<etc etc>

You may need some other algorithm (using DateAdd perhaps?) if the
fiscal year doesn't start on January 1.

A separate Append query should be used to create a new table of
accounts, handling the location, fiscal year, etc.; the UNION query
above can then be used as the basis of an Append query to populate a
transactions table related one-to-many to the accounts table.

John W. Vinson[MVP]

 

[John Vinson]

The Fiscal Year is Dec to Nov. So December is period 1. In the union query
for the first period I changed the function to DateSerial([FiscalYear]-1,
1,1) and it set the calender year back to the prior calender year. It seemed
to work for what I need.

Just be sure to check that it's getting the correct month and year.

Also, does it matter that records group by PeriodDate month and day?

Not a bit, if you're loading the data into a Table; the order of
records in the table is arbitrary and unimportant, you'll just use a
Query to sort them anyway.

John W. Vinson[MVP]