Best way to handle

[Steven]

I need to make a budget database. I was thinking of making a field for each
month. The database fields would be:
Co, Acct, Dept, TranDescription, Year, M01Jan, M02Feb, M03Mar, ....
M12Dec

Now in my mind I would think what it sould be is:

In table1 AutoNumber, Co, Acct, Dept, TranDescription, Year and then in
table2 IdNumber, Month, Amount

where table2 field IdNumber is related to AutoNumber in table1. ie each
Autonumber in table1 could have 12 records with IdNumber = the reltated
AutoNumber in table1.

Now I kind of want to do the first method becuase it seems easy ....
although not normalized.

My question is that in theory terms this application should be split as in
the second example, correct? If yes, then when a user is updating a budget
item they will always see budget amounts per month vertically in a form.
There is not a way to spread table 2 horizontally accross the form to show
the Co, Acct, Dept, TranDescription, Year, Jan, Feb, March ...... and be
able to update the amount fields. ie you cannot relate the tables to each
other and then spread across horizonnillaly. Correct? The correct method
approach of this is you would have a form with table1 as the source and a
subform with table2 as the source and then see the months and amounts in the
subform in a vertical view for that particular item of table1. Correct?

I think I make this sound confusing but I hope it is somewhat understandable.

Thank you for your help,

Steven

 

[Michel Walsh]

Embedded comment

I need to make a budget database. I was thinking of making a field for
each
month. The database fields would be:
Co, Acct, Dept, TranDescription, Year, M01Jan, M02Feb, M03Mar,
....
M12Dec

Now in my mind I would think what it sould be is:

In table1 AutoNumber, Co, Acct, Dept, TranDescription, Year and then
in
table2 IdNumber, Month, Amount

where table2 field IdNumber is related to AutoNumber in table1. ie each
Autonumber in table1 could have 12 records with IdNumber = the reltated
AutoNumber in table1.

Now I kind of want to do the first method becuase it seems easy ....
although not normalized.

My question is that in theory terms this application should be split as in
the second example, correct?

NO. Well, at least, it sounds strange that a YEAR belong to a COMPANY, so
table1 should not have any YEAR field at all.

It seems better to have table2 like:

Autonumber, refAutonumberToTable1, Year_Month_1, Amount


where refAutonumberToTable1 refers to the autonumber of table1
Autonumber is its own primary key for table2
Year_month_1 is a date_time field with the right year, month and day =
1 (the first of the said month).

If yes, then when a user is updating a budget
item they will always see budget amounts per month vertically in a form.

You are using the tables as forms? Table are where data lives, while form
are what user see. That concept make Access different than Excel where data
is the view. In Access, both, data and view, are disjoint.

There is not a way to spread table 2 horizontally accross the form to show
the Co, Acct, Dept, TranDescription, Year, Jan, Feb, March ...... and be

If you just want to see the data spread horizontally, you can use a
CROSSTAB. On the other hand, crosstab are not updateable, directly, but you
can use VBA code to open a dialog form when the user click on a 'cell',
through the 'on click' event. The dialog can ask for a new value and, on
confirmation, update the data in the right table and requery the visual form
display.

able to update the amount fields. ie you cannot relate the tables to each
other and then spread across horizonnillaly. Correct?

You can with a join and a crosstab.

The correct method
approach of this is you would have a form with table1 as the source and a
subform with table2 as the source and then see the months and amounts in
the
subform in a vertical view for that particular item of table1. Correct?

That is another option, indeed. It requires less coding if you need to be
able to update values, since that approach is already updateable.

I think I make this sound confusing but I hope it is somewhat
understandable.

Thank you for your help,

Steven

Vanderghast, Access MVP