Tables

[Guest]

Hello,

I am a new Access user and am trying to set up a financial database. I have
set up my basic tables, but I am having trouble figuring out how to set up
time periods. My time frame is January 2003 through December 2011 (for
forecast).

I want to be able to import data from Excel into a table that, for example,
has Account Number and then all the periods as columns (see below).

Jan03 Feb03 Mar03 Apr03 Etc.
Account 1000 $222 $121 $345 $465 $$

Any help is much appreciated.

John M

 

[Guest]

Hi John M,

I would avoid any table structure that would require you to change the table
each time you need an extra month.

How about something like:

AccountBalanceID (autonumber)
AccountID (maps to the Accounts Table)
AccountBalanceDate (datetime)
AccountBalanceAmount (currency)

Then there's one record for every date you need in there, and you can format
the dates to just show month and year if you need.

Hope that helps.

Damian.

 

[John Vinson]

Hello,

I am a new Access user and am trying to set up a financial database. I have
set up my basic tables, but I am having trouble figuring out how to set up
time periods. My time frame is January 2003 through December 2011 (for
forecast).

I want to be able to import data from Excel into a table that, for example,
has Account Number and then all the periods as columns (see below).

Jan03 Feb03 Mar03 Apr03 Etc.
Account 1000 $222 $121 $345 $465 $$

Any help is much appreciated.

John M

This is a good spreadsheet design... and an utterly incorrect table
design.

"Fields are expensive, records are cheap". You have a one to many
relationship between Accounts and values as of a date; a better
structure would have two tables:

Accounts
AccountNo
Description
<maybe links to a table of account owners, I don't know what an
Account is for you>

AccountValue
AccountNo
AsOfDate
Value

This table would have records like

1000; #1/1/2003#; $222
1000; #2/1/2003#; $121
1000; #3/1/2003#; $345
....
1001; #1/1/2003#; $185
1001; #2/1/2003#; $192
....

You can create a Crosstab Query to DISPLAY the data in the wide-flat
orientation you describe, if that's needed; but that's not how the
data should be stored.

John W. Vinson[MVP]

 

[Guest]

Is there a way I can create one record that contains all the months of data
(Jan03 - Dec12)? The model I am creating is a ten-year monthly model, so to
have one record for each month would require 120 records for one account.
There are hundreds of accounts so it will be difficult to load the data this
way. The data already exists in spreadsheet form in Excel (see below), and I
was hoping to find a fast, easy way to "dump" all the data into Access. This
is not a database that will need to be changed since I will simply change the
date headers when I want to reuse the model.

Thanks.

 

[John Vinson]

Is there a way I can create one record that contains all the months of data
(Jan03 - Dec12)?

Sure. It's bad design and a bad idea, but you can do it. You'll need
to run a Crosstab query on the properly normalized data and a
MakeTable query based on the result.

The model I am creating is a ten-year monthly model, so to
have one record for each month would require 120 records for one account.
There are hundreds of accounts so it will be difficult to load the data this
way.

12,000 - or 120,000 - records is NO problem for Access, and is no
reason not to use properly normalized tables.

The data already exists in spreadsheet form in Excel (see below), and I
was hoping to find a fast, easy way to "dump" all the data into Access. This
is not a database that will need to be changed since I will simply change the
date headers when I want to reuse the model.

A "Normalizing Union Query" can move the data from your linked
spreadsheet into the normalized table, and a dynamic Crosstab query
can recast it into the spreadsheet appearance. The UNION query would
resemble something like

SELECT [Account], #1/1/2003# AS TransDate, [JAN03] AS TransAmount FROM
[yourspreadsheet] WHERE [JAN03] IS NOT NULL
UNION
SELECT [Account], #2/1/2003# AS TransDate, [FEB03] AS TransAmount FROM
[yourspreadsheet] WHERE [FEB03] IS NOT NULL
UNION
SELECT [Account], #3/1/2003# AS TransDate, [MAR03] AS TransAmount FROM
[yourspreadsheet] WHERE [MAR03] IS NOT NULL

<etc etc through all the columns>

Base an Append query on this saved query to populate your normalized
table.

John W. Vinson[MVP]