Embedded comment
"Steven" <(E-Mail Removed)> wrote in message
news:8D8213D4-7A1D-4A45-9F2F-(E-Mail Removed)...
>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
Similar Threads
