How to do calculations between different data tables

Question

How to do calculations between different data tables

Anonymous

I'm stuck.

I have data table 1 and data table 2.

both tables have the same column and row headers.

how can I get access to look at the data in table 1 and subtract that data from table 2.

for example:

table 1, column header = supply, row header = Bob, data value = 100

table 2, column header = supply, row header = Bob, data value = 75

I want access to subtract 100-75 = 25.

any help is appreciated.

jcm1239

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

5 answers

Answer 1

Yes, there is a better approach.

Excel is a spreadsheet, best of breed. Access is a relational database. They are very different. One sheet per week is very normal for a spreadsheet, but it's simply WRONG for a relational database!

Instead you should (probably, your data structure is still a complete mystery to us, especially the "primary keys will change" bit) have one big table with a date field; or probably several tables with appropriate one-to-many relationships. It will then be relatively easy to create Queries joining the tables, or joining the master table to itself, with date criteria to allow you to compare the data from one week to that from another week.

To make productive use of Access you really need to design a relational database structure for your information, rather than trying to treat Access as if it were "Excel on steroids". The data may originate in Excel, but you are certainly NOT limited to working with it in that data structure; you can instead move the data from the 52 sheets into one relational database, and work with it there.

Answer 2

ScottGem 68,820 Volunteer Moderator

You've explained your situation, but not the data structures. This you need to explain. what the data represents. Only then can we help.

0 comments

Answer 3

I apologize for my first stab at database design. maybe you have a better idea for the design?

I will do my best to paint a picture of what I want to accomplish.

here are the parameters:

1. data is downloaded into Access 1 time a week from an excel spreadsheet (52 downloads a year).

2. every download will have the same column and row headers, however the data will change

what I want is to have access run mathematical expressions between tables to show how the data is changing over time.

as I only have authority to manipulate data that comes in the form of an excel download my creative problem solving seems limited.

also the kicker is the primary keys will change over time due to a dynamic processing environment.

my idea was if I had a primary key that existed for weeks 2-10, then access could relate those tables that had the same primary keys.

Do you have a better approach?

Thanks so much for the help

Answer 4

ScottGem 68,820 Volunteer Moderator

As Tom said, duplicate table is not normally good design. But what I don't see are key fields that relate the two tables and that is mandatory.

As Tom said, this should be easy with key fields. You just join both tables on the key fields, add the columns you need and a calculated column to subtract them.

0 comments

Answer 5

That smells like bad database design. It is highly unusual to have two tables with the same fields. If you are the exception, let us know why you think it should be this way.

That said, you can create a new select query, put both tables on the design surface and drag-n-drop the primary key field from the one to the other. Then you can create a column in the output grid like this:

Result: [table 1].supply - [table 2].supply

Share via

How to do calculations between different data tables

5 answers