How to merge to tables (

[Mikael Lindqvist]

Good morning everyone!

My financial system's database (general ledger) keeps seperate tables for
each year. Each table (GL2007, GL2008 etc) has 14 fields:

* Account_number, Cost_account, January_balance,
February_balance,...,December_balance

Now, I have not encountered this before but I did make some calculations and
then merged this two tables into 1 table with 26 fields (12 + 12 months +
Account_number and Cost_account).

My question:
HOW do change this summary table into one table with 5 fields
(account_number, cost_account, year, month, balance).

For example:
3010, 10, 2007, January, 35000
3010, 10, 2008, January, 12000

I believe the solution is not too complicated but I just can't get my head
around on how to make it (I have done the same thing in Excel, using
Walkenbach's tip, see post:

http://www.microsoft.com/office/com...=en-us-excel&lang=en&cr=US&sloc=en-us&m=1&p=1

Kindly,
Mikael
Sweden

 

[scubadiver]

Seperate tables for each year? Why? It seems to me you are trying to use
Access as a spreadsheet.

One table:

ID Field
Field for year
Field for month

Second table:

ID Field
Field for account number

Third (junction) table:

ID field from table1
ID field from table2
field for balance

that is how I would do it

 

[Mikael Lindqvist]

Maybe I wasn't clear enough - it's the financial system that setup the
database (that I'm using for queries) and for the balance (in general ledger)
it has 1 table per year (i.e. GL2007 and GL2008 in my case).

Each table (e.g. GL2007) has 14 fields (see below).

I want to merge GL2007 and GL2008 and bascially getting all the months into
two fields (month_name, month_balance).

Example:
Originally setup, table GL2007

3010, 10, 0, 5000, 2000, 800
3010, 11, 500, 200, 600, 100
..
..

This represent (Account number, cost account number, january, february,
march, april balance).

Same structure in GL2008.

Now, I want to merge this tables into 1 table with 5 fields (see below).

There's a way as you suggest to make 1 table for each month and then merge
this tables - but that would render 24 (temporary) tables, maybe there's a
smarter solution?

Kindly,
Mikael

 

[John Spencer]

Build a table with your five fields and then populate it using append
queries. I would not name fields Year and Month since they are reserved
words in Access and could cause errors.
Fields:
Account_Number (Text)
Cost_Account (Text)
Acct_Year (Number, Long)
Acct_Month (Number, Integer)
Balance (Currency)

Add an Compound primary key index on the first four fields to prevent
duplicates. Select all four fields and select Edit: Primary Key from the
menu.

Then use a query that looks something like the following to insert the data
into the table

INSERT INTO NewTable
(account_number, cost_account, Acct_year, Acct_month, balance)
SELECT Account_Number
, Cost_Account
, 2007 as TheYear
, 1 as the Month
, January_Balance
FROM YourSummaryTable

That will be tedious, in that you will have the change the select statement
to import each month.

You might be able to make this a little simpler by using a UNION ALL query
as the source. You probably cannot get all 24 of the balances in one Union
query. Union queries cannot be built using Design View, but must be built
in the SQL view.

SELECT Account_Number, Cost_Account, 2007 as TheYear
, 1 as the Month, January_Balance
FROM YourSummaryTable
UNION ALL
SELECT Account_Number, Cost_Account, 2007 as TheYear
, 2 as the Month, February_Balance
FROM YourSummaryTable
UNION ALL
SELECT Account_Number, Cost_Account, 2007 as TheYear
, 3 as the Month, March_Balance
FROM YourSummaryTable
UNION ALL
SELECT Account_Number, Cost_Account, 2007 as TheYear
, 4 as the Month, April_Balance
FROM YourSummaryTable
UNION ALL
SELECT Account_Number, Cost_Account, 2007 as TheYear
, 5 as the Month, May_Balance
FROM YourSummaryTable
UNION ALL
SELECT Account_Number, Cost_Account, 2007 as TheYear
, 6 as the Month, June_Balance
FROM YourSummaryTable

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Mikael Lindqvist]

This works well, but just a small question, I tried to enter a "condition" in
the query but I receive (mixed type error):

Specifically, I want Account_Number to be greater than 3000, but when I put

3000 I receive error (mixed type).

Like this:
SELECT Account_Number, Cost_Account, 2007 as TheYear
, 1 as the Month, January_Balance
FROM YourSummaryTable
WHERE (((Account_Number)=0));

Ideas?

Kindly,
Mikael

 

[Mikael Lindqvist]

....solved by putting "" around the 0.

Cheers,
Mikael

 

[John Spencer]

If Account Number is a text field then the criteria would be

WHERE Account_Number > "3000"

If ALL your Account_Number values consist of only number characters you can
use the following as long as Account_number is never NULL.

WHERE Val(Account_Number) > 3000

If Account_Number is ever null, you will get an error.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..