Selecting several tables into different fields

[Robbie G]

I have a seperate table for each day of the month with2 fields;
intAccountNumber and dblBalance. Not all the Accounts feature in every table.
I want to create a single table with all the accounts and a sperate fieldfor
each day. e.g.

intAccountNum Day1 Day2 Day3
A 100 150 150
B 0 0 40
C 500 400 300

where if the Account doesn't feature in a particular dau, the balance from
the previous day is used.
I can get the complete list of unique Account numbers, but how do i select
the daily balances into the individual columns and also have an expression to
check if i should be using the previous day's balance?

Thanks.

 

[Graham Mandeno]

Hi Robbie

I'm sorry, but you are going about this *completely* the wrong way.

You should have only two tables - one for Accounts and one for Balances,
with a one-to-many relationship (one account - many balances).

Your Balances table needs only three fields:
AccountNum (foreign key linket to the Accounts table)
BalanceDate (a date/time field)
BalanceAmount (currency)

AccountNum and BalanceDate should together make the primary key.

This table will have many records - potentially one for each account for
every day. This is not a problem!

To create the "table" that you require with one column for each day, all you
need ia a crosstab query.

 

[John W. Vinson]

I have a seperate table for each day of the month with2 fields;

Then you have a VERY badly denormalized database. *One* table with a date/time
field would be vastly better.

intAccountNumber and dblBalance. Not all the Accounts feature in every table.
I want to create a single table with all the accounts and a sperate fieldfor
each day. e.g.

intAccountNum Day1 Day2 Day3
A 100 150 150
B 0 0 40
C 500 400 300

This could be constructed from a Crosstab query, if you normalize the data.
But this would be just as badly non-normalized as your current spreadsheets.

where if the Account doesn't feature in a particular dau, the balance from
the previous day is used.
I can get the complete list of unique Account numbers, but how do i select
the daily balances into the individual columns and also have an expression to
check if i should be using the previous day's balance?

With a whole mess of code and/or UNION queries, given your current design.

Is this data stored in an Access database, or linked from some other data
store? Do you have a naming convention for the tables? How many rows in a
typical table? Do you have a separate Accounts table?

 

[Robbie G]

Then you have a VERY badly denormalized database. *One* table with a date/time
field would be vastly better.


This could be constructed from a Crosstab query, if you normalize the data.
But this would be just as badly non-normalized as your current spreadsheets.


With a whole mess of code and/or UNION queries, given your current design.

Is this data stored in an Access database, or linked from some other data
store? Do you have a naming convention for the tables? How many rows in a
typical table? Do you have a separate Accounts table?

This is the format of the data that i have inherited. I can normalise it as
you have indicated, the problem then is how can i fill in missing records
with a calculated value. i.e. if an account has no balance on 3rd, i want to
give it the same balance as on 2nd. There will be approx 1 million records.

thanks.

 

[John W. Vinson]

This is the format of the data that i have inherited. I can normalise it as
you have indicated, the problem then is how can i fill in missing records
with a calculated value. i.e. if an account has no balance on 3rd, i want to
give it the same balance as on 2nd. There will be approx 1 million records.

A Self Join update query would do it... but why would you want to store this
derived, artificial "balance"? A query looking up the balance on the most
recent non-null date would get you the same value, without loading zillions of
duplicate records in the table.

 

[Robbie G]

So how do i do a query to look up the most recent non null value?