Multi-table Calculations

[dwcrockford]

I need to do calculations across multiple tables and am not certain coding regarding this, and if I need to make a separate table that collects the data (and calculates it), and then a report based on that table so it displays on a form, so here goes.

3 tables (table 1, table 2, table 3)
3 columns (fields) in each table - all with number as data type
Each of these columns are totalled using the SUM total function in ACCESS.

I need to add the total of column 1 (table 1), column 1 (table 2) and column 1 (table 3) together for a "grand total).

I then need to somewhere have a if/then/else statement based on that calculated total: ie., if the sum of (the above) is less than 6, then return "0", if = to 6, then return 1, if = to 7, then return 2, if greater than 7 then return 3.

I will be using this same similar calculation to return different values that will ultimately all be added together as well.

I see no place when choosing fields in a query for selecting the "SUM" value of the table columns (fields). Or is that entered into the coding?

 

[Machada2015]

In Access, click create query, then double click on the tables you want to include. Once the tables are in, double click on each field you need in the calculation. Then at the top right of your screen, you should see the 'totals' symbol which looks like an 'E'. Click that. When you've done that, at the bottom pane where all your fields are listed you'll notice a 'total' field. Click on that box for each field and from the drop down menu select 'sum'. Once this is complete save your query (must save it or the next step will not be available). After you've saved it go to the bottom pane where all your query fields are listed, find the first empty field and right click in it, then select 'build' from here you can now do arithmetic manipulations to the existing fields in your query (i.e. you can add together the sums of field1, field2 etc). I've recorded a youtube tutorial just now to address this issue for you. As soon as it is finished uploading I'll post it for guidance.

 

[Machada2015]

I've posted the video of how to do this at Youtube https://youtu.be/wHtGxov1-XM

 

[dwcrockford]

For some reason, my question on the above did not post... so I'm re-posting it.

Machada - I tried the above instructions and followed the video you posted to YouTube.

When I add the first table and field from it to the query, and do the sum portion, the resulting calculation shows up in the query with no issues. When I add the second table, and tell it to return the sum, the resulting query gives me blank results in both spots (empty fields). When I continue on with the calculation to add the two results together, and then try to run the query, Access asks me for the parameters for each of the fields chosen.

Do you know of the reason behind this? Note: the two tables have the same Field Name in these instances... Access renames the second one to Mon1 (Mon is the original field name in both tables)

Daryl