adding numbers from two tables

[thetrystero]

forgive my newbie question, but i'd like to know how to add numbers from two different queries.

for the sake of discussion, I have two different queries q1 and q2, that extract the number of oranges, apples and kiwis from two different source files. how do i aggregate the number of oranges, apples and kiwis, respectively, from q1 and q2 in to one table?

i want to end up with three rows like:

oranges: q1.oranges+q2.oranges
apples: q1.apples+q2.apples
kiwis: q1.kiwis+q2.kiwis

 

[rainman89]

You are best to use a query to do this instead of a table. It is a general rule that you do not want to store calculations into a db... unless absolutely necessary

 

[wiklendt]

i think what you mean to do is turn two db's into one? or to append the data from one into another?

 

[thetrystero]

@wiklendt: the two dbs/queries/tables are to be made one. since they both have the same fields, I don't want to append.

@rainman89: can you please explain how you mean to use queries instead of tables and how it explicitly applies to this case?

To clarify, here are my two queries q1 and q2:

q1:
apples 3
oranges 4
kiwis 5

q2:
fruit/count
apples 2
oranges 6
kiwis 3

I want to end up with one table/db/query that has these two numbers combined:

fruit/count
apples 5
oranges 10
kiwis 8

My conceptual SQL is as follows but it doesn't work:

select sum(count) from q1,q2
group by fruit

 

[rainman89]

@wiklendt: the two dbs/queries/tables are to be made one. since they both have the same fields, I don't want to append.

@rainman89: can you please explain how you mean to use queries instead of tables and how it explicitly applies to this case?

To clarify, here are my two queries q1 and q2:

q1:
apples 3
oranges 4
kiwis 5

q2:
fruit/count
apples 2
oranges 6
kiwis 3

I want to end up with one table/db/query that has these two numbers combined:

fruit/count
apples 5
oranges 10
kiwis 8

My conceptual SQL is as follows but it doesn't work:

select sum(count) from q1,q2
group by fruit

Ok if i understand you correctly you want to combine the 2 amounts to make one single table? Then are you going to get rid of the redundant information in the other tables?

 

[thetrystero]

Ok if i understand you correctly you want to combine the 2 amounts to make one single table? Then are you going to get rid of the redundant information in the other tables?

yes, i'm only interested in the sum.

 

[DCrake]

If you create a union query from q1 and q2 then create a new query that sums the numeric column grouped by fruit you will get the answer you require.

David

 

[rainman89]

If you create a union query from q1 and q2 then create a new query that sums the numeric column grouped by fruit you will get the answer you require.

David

Then you can turn it into a make table query and you are done!

 

[Chris RR]

Hi,
Could you please explain the difference between q1 and q2, and a little about the source data? What you are showing is two queries that have the same two fields (fruit and count). Do they represent counts of fruits for different dates or locations, and you are trying to get the overall total?

You may need a third query that joins q1 and q2, or you may be able to get your results directly from their source data. It's hard to tell without a little more detail.

Once you have a query that sums them up correctly, that's most of the battle. Rainman89 is right, you would generally not want to store these results in a table but could use the third query for your reporting. Occasionally there is good reason to store totals in a table, and then you could change the query to a make-table one. But if the only purpose is reporting, the best practice is to keep the calculations in a query.

 

[thetrystero]

But if the only purpose is reporting, the best practice is to keep the calculations in a query.

can you please provide a sample code on how to perform the addition in a query?

 

[thetrystero]

Hi,
Could you please explain the difference between q1 and q2, and a little about the source data? What you are showing is two queries that have the same two fields (fruit and count). Do they represent counts of fruits for different dates or locations, and you are trying to get the overall total?

I'm actually working off of an existing access mdb created by someone else.

I've identified one of the queries as a good jumping off point for what I want to do. Basically, that query takes multiple raw data of interest-rate durations from multiple portfolios and combines them in to one query. It looks something like:

SELECT currency, instrument, buckets, DV01 FROM [q00 JoinAll]

I'll refer to this above query as q01. Each instrument is attached to a particular currency. Within each instrument, there are about 10+ buckets and a corresponding DV01 for each bucket.

Instrument|Currency|Buckets|DV01
InstrumentA|USD|1year|0.599
InstrumentA|USD|2year|0.679
...
InstrumentA|USD|30year|0.345
InstrumentB|USD|1year|0.234
...
InstrumentB|USD|30year|0.789
InstrumentC|GBP|1year|0.349
InstrumentC|GBP|2year|0.449
...
InstrumentC|GBP|10year|0.555
InstrumentD|CNY|1year|0.114
...
InstrumentD|CNY|10year|0.229

etc. for the other instruments


The end results I'm looking for is the total DV01 by buckets (key rate duration for all you finance types out there) across all instruments, grouped by currency. So my end table/query should look like (first line is header):

currency|buckets|DV01
USD|1year|1.230
USD|2year|2.349
.....
USD|30year|4.56
GBP|1year|3.233
GBP|2year|5.346
.....
GBP|10year|8.59

etc. for the other currencies

For example for the above above, the last column (DV01) for bucket
"1year" is the sum of all the 1 year bucket DV01 for all USD instruments etc.

 

[thetrystero]

i managed to figure this out using the wizard. .. turns out it was pretty straight forward.

SELECT DISTINCTROW [q0032 mBuckets].mBucket, Sum([q0032 mBuckets].DV01) AS [Sum Of DV01]
FROM [q0032 mBuckets]
WHERE [q0032 mBuckets].[Result Currency]='MYR'
GROUP BY [q0032 mBuckets].mBucket;


thanks all for your help!