Sum or total !! 1

[Yardyy]

Hi, I need to put together a report that queries a DB through access. I have been able in the past to put together simple queries, but this one is a little over my head, been at it for a few days now and am not making any progress, i just hope someone can push me in the right direction.


All that i need to get is a single figure of the last months sales from the database, i have been able to get the transactions for the last month and single amounts in access, all i need to now is total them up so that i can get that figure in a report, not sure how i would get that total figure though.

code:
SELECT VETRANS.DATETIME, VESALES.RETAIL_NET
FROM (VEDEPTS INNER JOIN VESALES ON VEDEPTS.DEPTID = VESALES.DEPTID) INNER JOIN VETRANS ON VESALES.TRANSID = VETRANS.TRANSID
GROUP BY VETRANS.DATETIME, VESALES.RETAIL_NET, VEDEPTS.NAME
HAVING (((VETRANS.DATETIME)>Date()-7) AND ((VEDEPTS.NAME) Like "021*" Or (VEDEPTS.NAME) Like "023*" Or (VEDEPTS.NAME) Like "024*"));


any assistance most appreciated.

Many Thanks
Yurov Ardyy

 

[HarleyQuinn]

Just a quick point, at present your query returns values for the last week rather than month...

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[Yardyy]

Hi, I thought that i would start small, and then change the -7 to -30, after i sussed out out how to do the total bit.

Many Thanks
Yurov Ardyy

 

[r937]

SELECT vedepts.name
     , SUM(vesales.retail_net) AS total_sales
  FROM (
       vedepts 
INNER 
  JOIN vesales 
    ON vesales.deptid = vedepts.deptid
       ) 
INNER 
  JOIN vetrans 
    ON vetrans.transid = vesales.transid
 WHERE vetrans.datetime > date()-7  
   AND (
       vedepts.name LIKE "021*" 
    OR vedepts.name LIKE "023*" 
    OR vedepts.name LIKE "024*"
       )
GROUP 
    BY vedepts.name

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[HarleyQuinn]

As - 30 isn't the most reliable indicator, I'd go with DataAdd() to get it right.

How about something like (untested):

SELECT SUM(VESALES.RETAIL_NET)
FROM (VEDEPTS INNER JOIN VESALES ON VEDEPTS.DEPTID = VESALES.DEPTID) INNER JOIN VETRANS ON VESALES.TRANSID = VETRANS.TRANSID
WHERE(((VETRANS.DATETIME)>Date()-7) AND ((VEDEPTS.NAME) Like "021*" Or (VEDEPTS.NAME) Like "023*" Or (VEDEPTS.NAME) Like "024*"));

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[Yardyy]

r937, the code is good and works well, Thanks, but is it possible to get one figure for the depts 021, 023, and 024, so total just those figures and get one over all number, something like :
A,
021= 400
023= 200
024= 100 total 700

and then do another one like
B,
011= 34
012= 34
013= 3 total 71

i have about 13 different areas that need calculations for the month, so like A, B, C, etc. .. but i only need the total for each section so total for A, and total for B etc..

I hope that i have explained it right.

Many Thanks
Yurov Ardyy

 

[r937]

yes, that's possible, but it would be really helpful if you could provide a small table for the translation of depts into areas

CREATE TABLE dept_areas
( dept_prefix CHAR(3)
, area CHAR(1)
);
INSERT INTO dept_areas VALUES ( '021' , 'A' );
INSERT INTO dept_areas VALUES ( '023' , 'A' );
INSERT INTO dept_areas VALUES ( '024' , 'A' );
INSERT INTO dept_areas VALUES ( '011' , 'B' );
INSERT INTO dept_areas VALUES ( '012' , 'B' );
INSERT INTO dept_areas VALUES ( '013' , 'B' );

then your query would be like this --

SELECT dept_areas.area
     , SUM(vesales.retail_net) AS total_sales  
  FROM ((       
       vedepts 
INNER
  JOIN dept_areas
    ON dept_areas.dept_prefix = LEFT(vedepts.deptid,3)
       )
INNER 
  JOIN vesales
    ON vesales.deptid = vedepts.deptid 
      ) 
INNER 
  JOIN vetrans 
    ON vetrans.transid = vesales.transid
 WHERE vetrans.datetime > date()-7 
GROUP 
    BY dept_areas.area

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Yardyy]

Here goes then,

A=013, 015, 016
B=003, 004, 023, 038
C=005, 006, 007, 008
D=001, 031, 032, 035, 039
E=009, 010, 040, 042
F=020, 021, 022
G=014, 043
H=028, 029
I=033
J=002, 011, 012, 017, 030, 034
K=050

So all that is rquired is the totals of each a, b, c, etc and then a grand total of all that would be good too.

Many Thanks
Yurov Ardyy

 

[r937]

yes, that data will work with the table that i suggested

for the grand total, you'll need a separate query --

SELECT SUM(vesales.retail_net) AS grand_total
  FROM (
       vedepts 
INNER   
  JOIN vesales 
    ON vesales.deptid = vedepts.deptid 
       ) 
INNER
  JOIN vetrans 
    ON vetrans.transid = vesales.transid
 WHERE vetrans.datetime > date()-7

you can combine the two queries in a UNION if you wish

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Yardyy]

sorry i dont understand the table..

if i was wanting to group two lots together say A and B,

A=013, 015, 016
B=003, 004, 023, 038

and find the totals of these how would that look like in the table ??

Many Thanks
Yurov Ardyy

 

[r937]

the purpose of the table is to translate the departments into areas

for comparison, imagine your main table was data for cities, and now you wanted to get totals by state, well, you'd need a table to tell you which state the city was in, and you'd have to join to that table

similarly, if you want totals by area, you join the dept_area table to the department data


what does "group two lots together" mean? do you want separate A and B totals or just one total for both? and do you want to see totals for any other areas at the same time, or just A and B?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Yardyy]

I understand the way that you have explained it. it is good. what i dont understand is how and where i put the bits in the table that you provided.

i have a drawing here next to me, which is like this ..

A=013, 015, 016 Total
B=003, 004, 023, 038 Total
C=005, 006, 007, 008 Total
D=001, 031, 032, 035, 039 Total
E=009, 010, 040, 042 Total
F=020, 021, 022 Total
G=014, 043 Total
H=028, 029 Total
I=033 Total
J=002, 011, 012, 017, 030, 034 Total
K=050 Total
-------------------------------------------
Total
drawing is not good, but hope i dont confuse further.

could you give me a small example of how that would work in the code above please.

Many Thanks
Yurov Ardyy

 

[r937]

what i dont understand is how and where i put the bits in the table that you provided.

i guess maybe you did not understand the table?


please have another look at my CREATE TABLE statement and the INSERT statements that populate the data

it is a 2-column table which maps a dept to an area

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Yardyy]

Unfortunatley i sill dont fully understand the example.

I know that the create table command creates a table with 2 fields with charachter lenghts as shown, but where is the data from the insert command coming from.

I dont understand or cannot relate how my tables and fields fit into your insert and create table commands.

Many Thanks
Yurov Ardyy

 

[r937]

but where is the data from the insert command coming from.

from your little drawing


it's your table, you have to fill it with your data



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Yardyy]

Ok so from the example above i have taken the example and addedd to it, it seems to work ok, but how would i get the overa ll total.

SELECT vedepts.OLDCODE AS deptname, Sum(vesales.RETAIL_NET) AS total_sales
FROM ((vedepts INNER JOIN vesales ON vedepts.DEPTID = vesales.DEPTID) INNER JOIN vetrans ON vesales.TRANSID = vetrans.TRANSID) INNER JOIN VEBRANCHES ON vetrans.BRANCHID = VEBRANCHES.BRANCHID
WHERE (((vetrans.DATETIME)>Date()-30) AND ((vedepts.OLDCODE) Like "013" Or (vedepts.OLDCODE)="015" Or (vedepts.OLDCODE)="016" Or (vedepts.OLDCODE)="003" Or (vedepts.OLDCODE)="004" Or (vedepts.OLDCODE)="023" Or (vedepts.OLDCODE)="038" Or (vedepts.OLDCODE)="005" Or (vedepts.OLDCODE)="006" Or (vedepts.OLDCODE)="007" Or (vedepts.OLDCODE)="008" Or (vedepts.OLDCODE)="001" Or (vedepts.OLDCODE)="031" Or (vedepts.OLDCODE)="032" Or (vedepts.OLDCODE)="035" Or (vedepts.OLDCODE)="039" Or (vedepts.OLDCODE)="009" Or (vedepts.OLDCODE)="010" Or (vedepts.OLDCODE)="040" Or (vedepts.OLDCODE)="042" Or (vedepts.OLDCODE)="020" Or (vedepts.OLDCODE)="021" Or (vedepts.OLDCODE)="022" Or (vedepts.OLDCODE)="014" Or (vedepts.OLDCODE)="043" Or (vedepts.OLDCODE)="028" Or (vedepts.OLDCODE)="029" Or (vedepts.OLDCODE)="033" Or (vedepts.OLDCODE)="002" Or (vedepts.OLDCODE)="011" Or (vedepts.OLDCODE)="012" Or (vedepts.OLDCODE)="017" Or (vedepts.OLDCODE)="030" Or (vedepts.OLDCODE)="034" Or (vedepts.OLDCODE)="018" Or (vedepts.OLDCODE)="019" Or (vedepts.OLDCODE)="050"))
GROUP BY vedepts.OLDCODE, VEBRANCHES.BRANCHID
HAVING (((VEBRANCHES.BRANCHID)=1));

the result currently looks like this ..

deptname total_sales
001 24010.9
002 7794
003 30257.78
004 4354.58
005 31.61
006 41.28
007 315.75
008 1894.92
009 32973.12
010 1036.03
011 664.78
012 3484.31
013 2869.99
etc

to total this up, which bit of the previous code do i amend.
SELECT SUM(vesales.retail_net) AS grand_total FROM ( vedepts INNER JOIN vesales ON vesales.deptid = vedepts.deptid ) INNER JOIN vetrans ON vetrans.transid = vesales.transid WHERE vetrans.datetime > date()-7

Thanks



Many Thanks
Yurov Ardyy