Database Design Question

[Ray_Rattler]

I am very new to database design, and kind of learning as I go. Thus I have
a question about the design of my database.

I am working on a budget database. I have a table that contains all the
Categories (Cat_ID, Description, Income/Expense), a table with transactions
(I have other tables doing other unrelated things. I have a form to input
transactions, and a form to add new categories (This is a multiple items
form). The categories is basically my chart of accounts.

My ultimate goal is to create a form where I can select a budget month and
year, and have it list all the categories so I can input budget amounts. I
want the form to be able to adjust to an added category entered through that
category form.

I don't know SQL so if we can only use that if necessary that would be
great. Thanks in advance for your help. I have tried numerous things, but
can't get my mind around the design to do it correctly.

 

[Arvin Meyer [MVP]]

I would create a form that has a subform for the monthly budget. Once you
choose the month, store that date (as a date, so you want 1/1/10, not 1/10,
you can format it to look like 1/10 after the data is input)

In the subform, link on the date as a foreign key, or use a autonumber as
Primary/Foreign keys in the Form/Subform Use a combo box to display the
categories, and a text box to enter the amount.

In use, you'll add a date on the main form then start choosing categories
and adding amounts in the subform.

 

[Ray_Rattler]

Thanks for your help.

So would I have a table that just has dates and is then linked to a second
table that keeps track of the date ID, category, and amount?

In the subform, could I have all the categories in the category table listed
with a box to add the amount for the month?

Thanks

 

[Arvin Meyer [MVP]]

I had assumed that there was other information in the first table. If it's
just dates on the main form, you can dispense with it and add the date to
the second table, using a single form, in continuous form view.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Wesley Silveira]

Thanks for your help.

So would I have a table that just has dates and is then linked to a second
table that keeps track of the date ID, category, and amount?

In the subform, could I have all the categories in the category table
listed
with a box to add the amount for the month?

Thanks

 

[Larry Linson]

Wesley, Just an idle question. At least a couple of your posts simply copy
and repost an existing post without either answering, or asking another
question, or adding anything to it? If you are doing this purposely, could
you explain why? It does, unnecessarily clutter the message thread.

Larry Linson
Microsoft Office Access MVP