Viewing multiple calculated fields (1 Viewer)

Hi guys,

I know I have done some research on this before and I was advised it was not best database practice.

However, I have a form that calculates a profit field. It has stored values from tables in my db that do a series of calculations on the form, and the profit box displays the profit for the particular job.

My total expenses figure is something similar, it is a calculated field comprising of many expenses, which is then deducted from the capital to leave the profit.

My question is this, if I have 20 jobs, I want to view all profits for each job in one query (or store the profit figure in a table for analysis?), I would just like to compare and contrast profit numbers against one another in access, without having to revert back to excel. I am sure this can be done.

All help will be greatly appreciated
Best,
Michael

Can you demonstrate what you want with data? I don't care about your forms or your reports. Just give me tablular data as it is in your database. Provide me 2 sets:

A. Starting sample data. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show me what data you expect to end up with when you feed in the data from A.

Again, data. No need for an explanation, show me what you want with data.

And, most likely with how you started your post, I am going to yell at you to normalize your tables.

For the purposes of a data explanation, here it is:
tblJobs - Contains Job Capital figure
tblJobCosts - Contains the JobID (PK of tblJobs) along with all expenses (rates, drawings, heating etc.)

Capital - total expenses = profit, which is done on a form.

I want to see this profit, along with the 19 others, in one area without having to load each form to view its respective profit.

The tables are alot more complex than simply that but for this particular inquiry, I have made it as simple as possible.

That's my fault for repeatedly stating I wanted data not an explanation. Luckily, as predicted, what you did provide is enough for me to yell at you to normalize your data.

...tblJobCosts - Contains the JobID (PK of tblJobs) along with all expenses (rates, drawings, heating etc.)

Click to expand...

You say don't want to move back to Excel, but you really haven't moved to Access. In a database you accomodate data with more rows, not more columns. You are storing values in field names when you name fields after cost types. Instead your tblJobCosts should look like this:

tblJobCosts
JobCostID, autonumber, primary key
JobID, number, foreign key to tblJobs
JobCostType, text, this will hold the value that is now stored in your field names
JobCost, number, the actual cost of the Job Type

That's it 4 fields now accomodate all your expense data. That's how a database works, not with adding more columns.

Better yet, this does address your initial issue. Because now, when you want total expenses for a job, the query is trivial:


Fix your tables and this and all the other issues you are going to encounter will be avoided.