Multiple Rows Return from VBA Query

psulions83

Board Regular
Joined
Nov 16, 2015
Messages
126
Hi,

I have the below query in Access and it seems I am getting a line for each line in the tables. I only want a sum for the entire table which in return would give me one line back. Can anyone help? I have tried different variations in the group by section.

Code:
SELECT     SUM(A.QUANTITY) AS QUANTITY,
     (SUM(A.QUANTITY) / SUM(B.QUANTITY)) * B.AMOUNT AS AMOUNT,
     "2_SALES" AS COMPONENT,
     "ACCESS" AS SOURCE
FROM Tbl4 A,
   Tbl7 B
WHERE A.PRODUCT = B.PRODUCT
GROUP BY A.PRODUCT=B.PRODUCT, A.QUANTITY, B.QUANTITY, B.AMOUNT
 
Last edited:
Code:
SELECT 
  SUM(A.QUANTITY) AS QUANTITY,
  (SUM(A.QUANTITY) / SUM(B.QUANTITY)) * B.AMOUNT AS AMOUNT,
  "2_SALES" AS COMPONENT,
  "ACCESS" AS SOURCE
FROM 
  Tbl4 A 
    inner join 
      Tbl7 B
    on 
      A.PRODUCT = B.PRODUCT
GROUP BY 
  A.PRODUCT


This gives me the same error....when i add the fields to the group by I get over 600k lines back when I only want the one.
 
Upvote 0

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
To only get one line then you shouldn't group by anything that has different values:

Code:
SELECT Sum(A.Quantity) AS Quantity, Sum([A].[QUANTITY]/[B].[QUANTITY]*[B].[AMOUNT]) AS AMOUNT, 
"2_SALES" AS COMPONENT, "ACCESS" AS SOURCE
FROM Tbl4 A INNER JOIN Tbl7 AS B
ON A.Product = B.Product
 
Upvote 0
Another try:

Code:
select 
	sum(T.Quantity) as SumOfQuantity,
	sum(T.Amount) as SumOfAmount, 
	T.Component, 
	T.Source
from
(
	select
	  A.QUANTITY as QUANTITY,
	  (A.QUANTITY / B.QUANTITY) * B.AMOUNT as AMOUNT,
	  "2_SALES" as COMPONENT,
	  "ACCESS" as SOURCE
	from 
	  Tbl4 A 
	  inner join Tbl7 B
	  on A.PRODUCT = B.PRODUCT
) T
group by T.Component, T.Source

Note that if B.Quantity is ever zero you will get a divide by zero error, and if any quantity or amounts are null (even one record) you may get Null as the result.
 
Upvote 0
The last two queries run but give me a Division by Zero error. If there error handling in access for this similar to excel?
 
Upvote 0
Division by zero should be excluded from the query if it is possible (edit: I mean if division by zero is possible - i.e., if B.Quantity can be zero):

Code:
select 
	sum(T.Quantity) as SumOfQuantity,
	sum(T.Amount) as SumOfAmount, 
	T.Component, 
	T.Source
from
(
	select
	  A.QUANTITY as QUANTITY,
	  (A.QUANTITY / B.QUANTITY) * B.AMOUNT as AMOUNT,
	  "2_SALES" as COMPONENT,
	  "ACCESS" as SOURCE
	from 
	  Tbl4 A 
	  inner join Tbl7 B
	  on A.PRODUCT = B.PRODUCT
	 [B][COLOR="#FF0000"]where B.QUANTITY <> 0[/COLOR][/B]
) T
group by T.Component, T.Source
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top