Adding values in field

[askajan]

I have a field named Supplies Expense. As an analyst inputs a business's expenses, it may require multiple entries into this one field. What is the best way to accomplish this?

For example:

Supplies Expense (Field Name) [$0.00]

The analyst has the following data:

2009 Financial Statement
Paper $50
Depreciation Expense N/A
Ink $100

So, going down the list, the analyst goes into access and enters $50 for paper then later needs to enter $100 for ink. What is the best way to combine these multiple entries into a single Supplies Expense field and allow for a quality check of the individual numbers at the end of the project?

 

[plog]

I'm not entirely following you, but I think you have an improperly structured database.

What you should have is an Expense field that has at least these two fields: ExpenseDescription, ExpenseAmount. Data in this would look like this:

ExpenseDescription, ExpenseAmount
Ink, 50.00
Paper, 100.00
Paint, 46.00
Ink, 50.00
Lawn Mower, 525.00

Then to categorize all the values in ExpenseDescription you would have another table called ExpenseCategories that would look like this:

ExpenseDescription, ExpenseCategory
Ink, Supplies
Paper, Supplies
Paint, Maintenance
Lawn Mower, Maintenance

Doing that would allow you to create a query that gives you this data:

ExpenseCategory, Total
Maintenance, 571.00
Supplies, 200.00

What does your Expense table currently look like and could you provide some sample data (use the format I used to present your data).

 

[askajan]

Ultimately, I am trying to enter an income statement (revenue items and expense items) for each year, for each client. Then classify each expense according to the category used in industry publications (for example, what a client calls supplies, I would like to classify as either administrative supplies or clinical supplies, etc). So, I have thought of doing either:

1) ID, ClientNumber, Year, Revenue Item 1, ...Revenue Item X, Expense Item 1, ...Expense item X

In this format, each item would be the actual benchmark category.

Problem: Each benchmark field would have to be revisited each time that expense appears on the financial statement or the analyst would have to manually add all supplies expenses before entering them into a single field.

2) ID, ClientNumber, Year, RevenueItem1Name, RevenueItem$, RevenueItemBenchmarkCategory, etc....

Problem: The expenses which revenue and expense items which appear under each descriptive field would be different. Perhaps this is ok, not sure yet.

 

[plog]

Numerated field names (i.e. 'Revenue Item 1', 'RevenueItem1Name') are never the answer. You should not store relevant data in a field's name. You're using a relational database, so use relationships like I suggested--have an additional table that categorizes each Expense Description.

 

[askajan]

SO...

Year ClientName RevenueDescriptionNumber Revenue$ RevenueBenchmarkCat
2012 Acme 3 $30k 5

??

The only problem is that I won't know the description for the RevenueDescriptionNumber field until each client sends me their financial statements. Each of them may call their revenue item something different. This is the problem I was running into and this is why I need to use the benchmark category to classify them into comparable buckets.

 

[plog]

I don't think RevenueDescriptionNumber and RevenueBenchmarkCat should be in the same table. Most likely you will have another table (as I've discussed) that ties RevenueDescriptionNumber to a RevenueBenchmarkCat.

 

[askajan]

So make very expense a separate record

i.e. ID, ClientName, Year, RevenueOrExpense

?

 

[plog]

Again, if you can provide sample data I can more specifically help you. Listing field names doesn't lend the insight I need.

 

[askajan]

Ok. I will work on this tonight.