Database design question using one form to many tables that are not related.

[hjnash]

I am building a budget tracking database, I will have as many as 12 different spending tables
to use for tracking my expenses; example, Groceries, Autos, Recreation, so one. I however want
to use one entry form and be able to select from the form the table (category) I will be updating.
Any suggestions how I can do this?

[June7]

Why do you have a table for each expense? This means if you add a new expense category have to modify design of tables, queries, forms, reports.

Try a form with a tab control. Put a subform for each expense on pages of the tab control.

Have you considered off-the-shelf software instead of reinventing the wheel? I have used Quickbooks. It is a relatively inexpensive program and will track expenses, although I don't remember if it has budget tracking component.

[hjnash]

I am learning Access for personal interest, I have made some small simple database systems now I want to expand to something more complicated.

Why do you have a table for each expense? This means if you add a new expense category have to modify design of tables, queries, forms, reports.

Try a form with a tab control. Put a subform for each expense on pages of the tab control.

Have you considered off-the-shelf software instead of reinventing the wheel? I have used Quickbooks. It is a relatively inexpensive program and will track expenses, although I don't remember if it has budget tracking component.

[June7]

So why do you feel a separate table for each expense is needed?

[hjnash]

I figured that would be cleaner to do reports and query's on.
I originally was going to use one table and have a field for the
Category I don't remember why I change my mind

So why do you feel a separate table for each expense is needed?

[June7]

You might find that separating the expenses actually complicates data entry and output design.

[Missinglinq]

I think most experienced developers are going to agree with June7, here! An expense Table, with a 'category' Field, is the practical way to go. To make sure that the correct term is entered, for the expense category, I'd use a Combobox that is Bound to the Field in the underlying Table. This will prevent things like entering Autos, one time, and Auto another time, or even Cars. Having the correct category, for each Record, and a correct date, is all that you should need to pull Reports accurately.

Linq ;0)>

[hjnash]

I went back to using a field with Category and will experiment with that. I like the idea of using the combobox too.
Thank You !!!

[hjnash]

Just ran into the reason I stopped using the Category field and went to separate tables. The
database is a budget spending database and I have to put a deposit into each Category
so the spending in one category wont affect the balance of money in another category. I
still prefer one table idea though! I will quit for now and think of what to try next.

[June7]

So you need a table of category budgets and a transactions table for payments.

[hjnash]

Thanks June7, that is a good idea! I looked into linking two tables using many to many but I
was not able to figure out what I had to do to make the relationship between the two tables
I have a book I can see if there is more written than the short explanation. I have to figure how
to make sure this wont lead to an error where a spending or deposit goes to the wrong place.

[June7]

Many-to-many requires a third 'junction' table.

[orange]

PMFJI,

I think there is a tutorial concerning business facts,table design, normalization and Entity Relationship Diagramming that could help you put the pieces together, before getting into comboboxes etc.

see http://www.rogersaccesslibrary.com/T...lationship.zip

[hjnash]

Thanks for the input, I will check out the link and see what I can learn!

[orange]

Good luck.