Hi guys i need help
i have a table1 and table2 in access
table1 structure is something like this

Months / Revenue / GP
Jan / 10000 / 5000
Feb / 20000 / 2000

and table2 structure is like this

Months / Revenue / GP
Jan / 2000 / 200
Feb / 50000 / 12000

my question is how can i sum the Jan of Table1 and Table2 and Feb.
so that the total January of my Table1 and Table2 is
12000 on Revenue and 7000 on GP
same goes to February

Recommended Answers

All 2 Replies

I'm going to make a suggestion or two without knowing the complete structure and intention of your table ("something like" does not help me be more precise).

Having two separate but equal tables is rarely a good idea as it makes aggregate queries more difficult. Also, you are limiting your tables to only 12 entries (one per month). You would be better off with a structure more like

StoreID
TheYear
TheMonth
Revenue
GP

in which case your query reduces to something like

SELECT TheMonth,SUM(Revenue) AS MonthlyRevenue
  FROM table3
 WHERE TheYear = 2013
 GROUP BY TheMonth

Another suggestion would be to store the month value as a small integer so that the results can be sorted in month order. Again, without knowing more about your requirements it is difficult to be more helpful.

you got a point their Sir Reverend thanks for the idea :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.