Budget database design with many "autofill" fields

[chelonidae]

Please bear with me, I'm a beginner.


I'm asked to create a simple budget database design. My boss wants a single form to put in items that are purchased. Each item will fall in to a category with a specific code. Each unique numeric code will always have a unique title and different kind of categories (e.g. supplies and services, professional development, travel, communication). Categories will apply to more than 1 code. My boss wants to enter the date, item and select the code and

Using the database:

1. Data entry using single form (probably tabular)
2. Wants to enter date, item, select item title and have code and category fields automatically populate (**this is where I am most stuck)
3. Create reports for different categories of spending or spending over a certain period of time

So, how do I organize this database?
My idea:

1. Create a table with the codes, titles and corresponding categories. In the form, I would use a combo box to help autofill by creating event? (haven't tried this before).
2. I also would have fields in the form like biennium (which would be great if it was automatically selected by the date entry year)
3. For certain categories, a field would need to select an employee name or vendor name. Do these via look-ups and list boxes?

I've tried some of this but it is one big mess right now.

Any advice, links, code, you can provide is greatly appreciated!! Thank you!!

[aytee111]

Post # 3 of this thread - https://www.accessforums.net/showthread.php?t=68603 - contains information to get you started. We can assist you with questions you have but we cannot do it for you. Learn how to design a table structure that contains every piece of data that you need to create the reports somewhere, on subject per table, with no repeats.

For instance:
- a table of categories
- a table of codes
- a table of items
- a table of employees
- a table of vendors

Those are the "master" tables which contain static data. Then you need at least one transaction table which stores the data on the form that your boss is entering, and ties all of the values together. You would have a main form for the master values - category, item, code - and a subform for the transaction table. In the relationships window you would link the tables together in a logical way. The best way to do this design is to do it on paper and don't move into Access until you have it laid out correctly.

[chelonidae]

We can assist you with questions you have but we cannot do it for you. Learn how to design a table structure that contains every piece of data that you need to create the reports somewhere, on subject per table, with no repeats.

Thanks for the link to the resources. I reviewed them and thought I followed the principles of normalization. Also, I didn't ask you to create it for me, but wanted feedback on my approach. Maybe I wasn't clear enough. I'll be clearer in the future.

My table structure isn't what you suggest, but doesn't have repeats and is complete. I'm confused how the subform you mentioned helps us automatically populate from one of the fields (like code or title).

Database has:
luBiennium
luEmployee
luSubproject
luVendors
tblCodes: code (primary key) , title, category
tblBudgetData (where form data goes): ID (Auto# - key), Date, Biennium, Item description (typed value), Vendor, Cost, Code (linked 1 to many), Title (*want autofill based on code), Category (*want autofill), Employee

I have 1 columnar form for the tblBudgetData.

Question 1: Is this "bad"? Employee fields may be empty if not HR related cost, but I can add an "NA" value
Question 2: How do I populate Title and Category from the Code selected? Is code a list button (drop down) and the others a combo box with an event? I tried this unsuccessfully.

Thank you for your time.

[pbaldy]

A couple of methods:

BaldyWeb - Autofill

As noted in the link, normally you wouldn't store the other fields from other tables, just the key field. For example in your budget table we would not typically have the title and category fields, just the code.

[aytee111]

That's fine, although category is a repeating field. As Paul says, you don't want to carry those fields on the budget data table, just the ID pointing to the applicable record. Then display the values to the user as he says.

Not knowing your process, you may want to consider making the entry form tabular, depending on how the user is going to be entering, it may be quicker for them.