Perform calculation from two tables

[Zandra]

Hi,
I have 2 tables, one is called Main table and the other one is called secondary table. Both tables have the following fields: 200701, 200702, 200703, 200704, 200801, 200802, 200803, 200804, 200901, 200902, 200903, 200904 and so on&. Both tables have always equal fields meaning if the Main table has data from 2007 up to 2011 then the secondary table will have the same fields too. The list always starts from 200701 and go up as shown above. So all what I need a VBA code that does the following calculation:
Take the field 200701 from the Main table and divide the same field in the Secondary table and so on. In other words, take every field from the Main table and divide to the corresponding fields in the Secondary table. Thank you so much for your help.

 

[Scooterbug]

Sounds to me like you really should re-examine your tables. I'm guessing that each field is the fiscal quarter of a set year. Also having two identical tables is not good practice either.

What you are asking can be done...but a good database is founded on a good table structure.


You might want to do some research into Normalization. A simple search of the forums here will provide you with an abundance of information on the topic.

 

[boblarson]

1. I hope you realize that the database design is actually pretty horrible. You should not have repeating fields like that. It makes getting meaningful data out really painful.

2. You can do what you want with a query but you'll have to build it manually.

3. If this were a normalized database, all you would need would be a simple query to do it.

 

[Zandra]

Thanks for your quick response. But what i am trying to achieve is different. Here is what i want to do in a simple way:
I want all the value in 200701 from the Main Table to be devided 2007Q1 in the Secondary table and the same thing goes on,
I want all the value in 200702 from the Main Table to be devided 2007Q2 in the Secondary table and so on.. See attached for more details.

For example:
Master Table Secondary Table
200701 / 2007Q1
200702 / 2007Q2
200703 / 2007Q3
200704 / 2007Q4
200801 / 2008Q1

 

[Zandra]

I have a attached a sample database in my previous post. Pls. feel free to do anything as long as i get the desired results then i don't mind. thanks so much for your time.

 

[Scooterbug]

Creating the tables differently would make the calculation much easier. Give me a few minutes and I will repost your database with a few changes.

From what I see, you are treating Access like it was Excel. To get a database to work properly, you have to get out of that mindset.

 

[Scooterbug]

Here is something I did up quickly. I'm sure that there are other ways to do it..but this way works. Notice the table structure.

Open the query called: qselDivideCalculationAmount

that will show you what you need. Take a look at it to see how it was done.

 

[Zandra]

Scooterbug,
there is no query at all in the file you attached.

 

[Scooterbug]

Oops...wrong one. Try this