Help With Query Calculation

bphoward1

Registered User.
Local time
Today, 16:17
Joined
Aug 29, 2014
Messages
18
I'm an Access rookie, so please ignore my ignorance.


I'm using Access 2010. I need to calculate a score based on values selected in a table by looking up corresponding values in other tables. I have a "Project" form to create new entries into the Project table (see Table 1). When I create a new project record, I will select values for the Payback and Need fields by selecting options from a list. The Payback list is pointed at Table 2 and the Need list is pointed at Table 3. In the below example, I created the "ABC" project and selected "1 year" for the Payback field and "Repair" for the Need field. Pretty simple.


Now that I have the "ABC" project loaded to my Project table, I'd like to create a report that will show a "score" for this project. The score should be calculated as follows: Payback Impact + Need Impact. In this example, the score should be 30 (Payback Impact of 20 + Need Impact of 10). I hope this makes sense.


Can anybody assist me with creating a report that has this simple calculation?


Thanks in advance for any guidance!

Tables.png
 
you need a query along the following lines:

Code:
SELECT project.projectname, payback.impact+need.impact as score
FROM (project INNER JOIN payback ON project.payback=paypack.period) INNER JOIN need on project.need=need.item

Note that you have project both as a table name and the name of a field within that table - this can cause problems. Suggest you change the table name to the plural - i.e. Projects
 
A quick look indicates that your tables are not related to each other. In the three tables you listed, change the name of the ID field to ProjectID, PaybackID and NeedID; these are called primary keys. In design mode, they should have a yellow key next to them indicating they are unique and indexed and autonumber, if no key, fix this. Then add a field in the payback and need tables called ProjectID and put the number one in there. These fields are called a foreign key. This shows that record one in the two tables are related to project one (ABC) in the projects table. Make sure that all payback and need tables in the future get the corresponding ProjectID number so they are not "orphaned".

Now open a blank query and put all three tables in there. Access should link them through the ProjectID. Then pull the fields down that you need. Then create the Score field by typing in the following:

Score: tblPayback.Inpact + tblNeed.Impact

Just noticed what CJ wrote. An issue with normalization is to remove duplication, and you have the same field in two tables. If you relate the tables as described, you can delete the payback and need fields in Project, that information is stored in the other tables.

That should do it. You might want to read up on table design and "normalization". Most tables have a primary key and many have a foreign key so you can relate information from one to the other. Good Luck.
Privateer
 
I follow concept of using primary/foreign keys, but something Privateer suggested isn't clicking with me. If I add "ProjectID" as a foreign key in the Payback/Need tables, wouldn't I wind up with multiple rows that contain the same information, the only difference would be the ProjectID field, right? For example, if I have ten projects that each have a 1 year payback period, wouldn't the Payback table wind up containing 10 rows showing the same exact information but with different ProjectID's? That doesn't seem right to me.
 
You are right, but the goal here is that the information is stored in one place and not entered into a table like a spreadsheet. Remember, spreadsheets are horizontal, databases are vertical. In Excel you insert a column, in Access you add a new record.

If the value, weight and impact fields are fixed for the ABC project, then put those fields in the project table and delete the other two. In other words, if they are in a one to one relationship with the project, you don't need the other tables. You will have to decide if you want a table like this:

ProjectID ProjectName, PaybackValue, PaybackWeight, PaybackImpact, NeedValue, NeedWeight, NeedImpact

However, I was assuming from your ellipses that you might have many payback types, like five year or ten year where the value, weight and impact are different. By normalizing the tables, you have two advantages, size of the table and the ability to change data in one place and have it affect many records.

In the example above, you have eight fields times ten records or eighty storage "spaces". The Projects table, with have only four fields, has forty "spaces".

The other advantage is you can change one value in the payback table, and that new value will now be associated with all the related projects. So when you have those ten projects and seven are for one year, all seven projects (when opened in a query) will now have the new value.

You are the developer there and you know the situation, so pick whichever works best. Databases like to store information in its rawest, most basic form, and defining payback and need in separate tables does that. Imagine being asked how many payback options your project has in five years. You open the payback table and have the answer. Otherwise you have to do a query on the projects table where all three fields are unique. Ugly. Hope this helps and good luck.
Privateer
 
Thanks for taking the time to explain things in detail, Privateer.

I am totally on board with separating things into their own tables. I get the advantages. I think I have things worked out nicely at this point. I'm sure I'll encounter numerous challenges along the way. Glad to know this forum is here to offer support.

-- B
 

Users who are viewing this thread

Back
Top Bottom