How can I have multiple SUM's from different tables

  • Thread starter Thread starter jjturon
  • Start date Start date
J

jjturon

Hello,

I would like some insight on how to create a Select Query that will
run through an Access DB and give me totals for 15 particular products
for a specified date range.

Ex.

Bottle_Code Sum_Of_Off_Premise_Sales Sum_Of_On_Premise_Sales

Wine12 12btls
24btls
Wine13 6btls
32btls

I need to have the results displayed in one record for each Bottle_Code
not in multiple records for each Bottle_Code. The query needs to find
this information based on a date range. I am new to this and I'm
having problems.

This is what I get when I use a Select Query. Muliple Records. This
is what I don't want.

Bottle_Code Sum_Of_Off_Premise_Sales Sum_Of_On_Premise_Sales

Wine12 3btls
4btls
Wine12 3btls
4btls
Wine12 3btls
4btls
Wine13 6btls
32btls
Wine13 6btls
32btls
Wine13 6btls
32btls

Thanks for any help,
 
Hi,


We don't have the table from which you start.


SELECT Bottle_Code, SUM(Sum_Of_Off_Premise_Sales),
SUM(Sum_Of_On_Premise_Sales)
FROM yourQuery
GROUP BY Bottle_Code


applied to your existing query (yourQuery) should do.


Hoping it may help,
Vanderghast, Access MVP
 
I think I figured this out. I had to make multiple Select Queries to
sum up the values that I want and then use a Master Select query to
show all of the values from the multiple Select Queries.


Ex. qrySum_Of_Off_Premise_Sales gives me total number of btls sold
to Off_Premise customers
qrySum_Of_On_Premise_Sales gives me total number of btls sold
to ON_Premise customers
qrySum_Of_Receipts gives me total number of
Receipts.

Then I have a master Select Query that will call each of the above
queries and I use the results for each column in the Master Select
Query.
Then I can sort by Bottle_Code.
This gives me what I want, one record per Bottle_Code with all of the
mentioned sums


Is there a better way of doing this??

Thanks
 
I have another question

Is there any way of messing up the realtionship between existing
tables in a database if my query references different tables??
Ex.
qry_Sum_Of_Off_Premise_Sales references two tables, tblInventory, and
tblTransitReceipts. Then I create a relationship in the query (in
design view) by draggin the Bottle_Code field from tblInventory to
tblTransitReceipts.

Is this a problem?

Thanks for your help
 
Hi,


If you want the intermediate results, yes, that is the way to go, with Jet
( a "cube" or a rollup, in MS SQL Server is also possible). If you just want
the final result, you may just reduce the GROUP BY to the unique category
you need.


SELECT ... FROM mytable GROUP BY Country, Month

would make groups based on country and month


SELECT ... FROM previousQuery GROUP BY Country

would make groups just based on country, so, "summing" over the
month from the previous query, but, in that case, there is no reason to go
directly from the table to that "grouping" :

SELECT ... FROM myTable GROUP BY Country

bypassing the need of the first query, since (I assumed) there is no
need to get "by month", into that last query.



Hoping it may help,
Vanderghast, Access MVP
 
Hi,


Technically, this is a join. The difference between a join and a reference
relationship is that the latter always hold, while the join only hold for
the precise query into which it is defined.

Making a join, as you do, should be fine if the Bottle_Code appears just
once in the two joined table. Otherwise, you will have amplification which
will lead to problem (in your cases).


Assume the two tables:

t1.f1 t1.f2
a 1
a 2
b 3
c 4
c 5
d 6


and

t2.g1 t2.g2
a 10
a 20
b 30
b 40
c 50
d 60


then,


SELECT t1.f1, t1.f2, t2.g1, t2.g2
FROM t1, t2

returns 36 rows, each possible mix:

t1.f1 t1.f2 t2.g1 t2.g2
a 1 a 10
a 1 a 20
a 1 b 30
a 1 b 40
a 1 c 50
a 1 d 60
a 2 a 10
a 2 a 20
a 2 b 30
a 2 b 40
a 2 c 50
a 2 d 60
b 3 a 10
b 3 a 20
b 3 b 30
b 3 b 40
b 3 c 50
b 3 d 60
c 4 a 10
c 4 a 20
c 4 b 30
c 4 b 40
c 4 c 50
c 4 d 60
c 5 a 10
c 5 a 20
c 5 b 30
c 5 b 40
c 5 c 50
c 5 d 60
d 6 a 10
d 6 a 20
d 6 b 30
d 6 b 40
d 6 c 50
d 6 d 60



If you make an inner join, only these "rows" where the ON clause evaluates
to true are kept.

SELECT t1.f1, t1.f2, t2.g1, t2.g2
FROM t1 INNER JOIN t2 ON t1.f1=t2.g1

t1.f1 t1.f2 t2.g1 t2.g2
a 1 a 10
a 1 a 20
a 2 a 10
a 2 a 20
b 3 b 30
b 3 b 40
c 4 c 50
c 5 c 50
d 6 d 60


Note that many records have been "duplicated", except for the last one,
since only t1.f1=t2.g1='d' appears only once in both tables. So,


SELECT t1.f1, SUM(t1.f2)
FROM t1 INNER JOIN t2 ON t1.f1=t2.g1
GROUP BY t1.f1

would return

a 6
b 6
c 9
d 6

which is quite different than



SELECT t1.f1, SUM(t1.f2)
FROM t1
GROUP BY t1.f1


a 3
b 3
c 9
d 6


a totally (pun intended) different result.




Hoping it may help,
Vanderghast, Access MVP
 
Hi Michel,

I'm having a problem with my Master Select Query.

When I perform a small Select Query base on values that I want to sum
the individual queries work on their own.
EX.
Query1Quantity_of_btls_OnOrder
Supplier_ID Item_Code Quantity_of_btls_OnOrder
ABC Wine1 50
DEF Wine2 20
GHI Wine3 60

Query2Quantity_of_btls_Sold
Supplier_ID Item_Code Quantity_of_btls_Sold
ABC Wine1 70
DEF Wine2 60

Query3Quantity_of_btls_Broken
Supplier_ID Item_Code Quantity_of_btls_Broken
ABC Wine1 20
DEF Wine2 10


Query4Quantity_of_btls_SoldToRestaurants
Supplier_ID Item_Code Quantity_of_btls_SoldToRestaurants
ABC Wine1 50
DEF Wine2 40

Master Query - Contains one record with all of the information Per
Item_Code

Supplier_ID Item_Code QbtlsOnOr QbtlsS QbtlsBrkn QbtlsSTR
ABC Wine1 Missing 70 20
Missing
DEF Wine2 Missing 60 10
Missing

Notice that Wine3 is on order with Query1Quantity_of_btls_OnOrder but
does not show up in the master query. This is a problem. Access is
somehow only matching like fields.
I tried creating different joins between the small queries within the
master query design view, but Im not having any luck.

I also tried to create an updateQuery with hopes that I could call each
select query and have it insert each sum into a master table with all
of the Item_Codes as static fields in the table. This way I could
update the different fields with the values from the small Queries or
some kind of functions.

The update query will not let me use a select query in the update table
field. At least I couldn't get it to work. Is this possible??


Any suggestions? Also, is there any way we could talk about this on
the phone?

Here's my email address (e-mail address removed)

Thank You for your help,
 
Hi,



Have a table with all possible Item_Code values, call it, for illustration,
ItemCodes. Then, make you final query by first getting ItemCodes then, add
the other tables, one at a time, and add a LEFT JOIN from
ItemCodes.Item_Code toward the Item_Code of the table you just added (right
click on the bar symbol representing the join, and use the option to keep
all record from ItemCodes, which is option 2, or 3).

You need an outer join to keep "unmatched" records. Since ItemCodes have all
the possible values (for Item_Code) and since you always keep all of them,
no record should disappear.



Hoping it may help,
Vanderghast, Access MVP
 
Hello Michel,

Im having another problem with Dsum calculations. I can get the Dsum
calculations to run through one of my tables

tbl_Transit_Receipts which Sum's the amount of bottles sold for a
specified time period.
The problem is that it runs through the table sequentially and is
probably using too much memory to calculate.

Any Suggestions?
THanks
 
Hi,


In general, if DSum is too slow, you can try to replace it with a select
query:


SELECT ..., DSUM("x", "y", "where z=" & w.aaa), .... FROM w

can become

SELECT ..., (SELECT SUM(x) FROM y WHERE y.z=w.aaa ), ... FROM w


That could still be quite slow, on the other hand, since the sub select runs
for each row. Probably faster:


SELECT ..., y.mySum, ...
FROM w INNER JOIN (SELECT z, SUM(x) As mySum GROUP BY z) AS y
ON w.aaa = y.z


and even faster would be to make a table out of the new sub-select query,
with an index on the groups, z.



Hoping it may help, and not too generic :-)
Vanderghast, Access MVP
 
Back
Top