By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,911 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

trying sum large amount of info in query

P: 37
Ok I this one will be tough to explain so i'll try to give as much detail as possible.

What i need to do is build a stacked column chart in access off a set of information that has 13 different product lines for each product line there is about 2100 records of information. Each record is a status for example (abc stock, def stock, to go, installed, abc shipped, def shipped, na) what i need to do is make a "in stock" category that would include both abc, and def stock, a shipped that would include both abc and def shipped, and a "to go", "installed", and "na" category, I need to total the number of records in each categoy for each product line. and i need it in a useable manner so i can build the stacked column chart. I hope this is enough information and that someone can help me I have spent way to much time on something that seems like it should be so easy. any help is greatly appreciated.


Thanks again.
Sep 13 '07 #1
Share this Question
Share on Google+
17 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Not sure if I understand you fully but something like the following:

Expand|Select|Wrap|Line Numbers
  1. SELECT ProductLine, Sum([abc stock]+[def stock]) As [InStock], Sum([abc shipped]+[def shipped]) As [Shipped], Sum([to go]) As ToGo, Sum(installed) As Installed,  Sum(na) As NA
  2. FROM TableName
  3. GROUP BY ProductLine
  4.  
Sep 14 '07 #2

P: 37
Thanks for your reply! I think your pretty close in your understanding... each product line is a different column in my table so i need to sum 13 different product lines, there are 2100 rows and the values are the status that i need summed or counted I guess would be more accurate. And again any help is great.

Thanks,
Sep 14 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
You'll need to post the metadata for your table as I don't know if I fully understand what you are doing.

Here is an example of how to post table MetaData :
Table Name=tblBookings
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; Autonumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. LastAttendance; Date/Time
Sep 14 '07 #4

P: 37
tblname=buildchart

Part number; text
LINE UNIT 0001; text
LINE UNIT 9997; text
LINE UNIT 0002; text
LINE UNIT 9998; text
LINE UNIT 0003; text
LINE UNIT 0004; text
LINE UNIT 9901; text
LINE UNIT 0005; text
LINE UNIT 0006; text
LINE UNIT 0007; text
LINE UNIT 0008; text
LINE UNIT 0009; text
LINE UNIT 0010; text


thats what the design view of my table looks like. there are 2100 part numbers and for each part number there will be a status for each line unit, I want a count of every different status, grouped like I said above. I know I'm having a hard time communicating with you so thanks again for your help.

Thanks,
Sep 14 '07 #5

P: 37
One of the things i tried was grouping by Line Unit 0001, then add all the the line units including Line Unit 0001 and do a count on all of them. But when I do that it gives me the the count for Line Unit 0001 on all of them.
Sep 14 '07 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
OK this is going to be a long one ...

Expand|Select|Wrap|Line Numbers
  1. SELECT "LINE UNIT 0001" As LineNum, 
  2. Sum(IIf([LINE UNIT 0001]="abc stock" OR [LINE UNIT 0001]="def stock",1,0)) As [InStock], 
  3. Sum(IIf([LINE UNIT 0001]="abc shipped" OR [LINE UNIT 0001]="def shipped",1,0)) As [Shipped], 
  4. Sum(IIf([LINE UNIT 0001]="to go",1,0)) As [ToGo], 
  5. Sum(IIf([LINE UNIT 0001]="installed",1,0)) As [Installed], 
  6. Sum(IIf([LINE UNIT 0001]="na",1,0)) As [NA]
  7. FROM buildchart
  8. UNION
  9. SELECT "LINE UNIT 9997" As LineNum, 
  10. Sum(IIf([LINE UNIT 9997]="abc stock" OR [LINE UNIT 9997]="def stock",1,0)) As [InStock], 
  11. Sum(IIf([LINE UNIT 9997]="abc shipped" OR [LINE UNIT 9997]="def shipped",1,0)) As [Shipped], 
  12. Sum(IIf([LINE UNIT 9997]="to go",1,0)) As [ToGo], 
  13. Sum(IIf([LINE UNIT 9997]="installed",1,0)) As [Installed], 
  14. Sum(IIf([LINE UNIT 9997]="na",1,0)) As [NA]
  15. FROM buildchart
  16. UNION
  17.  SELECT "LINE UNIT 0002" As LineNum, 
  18.  Sum(IIf([LINE UNIT 0002]="abc stock" OR [LINE UNIT 0002]="def stock",1,0)) As [InStock], 
  19.  Sum(IIf([LINE UNIT 0002]="abc shipped" OR [LINE UNIT 0002]="def shipped",1,0)) As [Shipped], 
  20.  Sum(IIf([LINE UNIT 0002]="to go",1,0)) As [ToGo], 
  21.  Sum(IIf([LINE UNIT 0002]="installed",1,0)) As [Installed], 
  22.  Sum(IIf([LINE UNIT 0002]="na",1,0)) As [NA]
  23.  FROM buildchart
  24. UNION
  25.  SELECT "LINE UNIT 9998" As LineNum, 
  26.  Sum(IIf([LINE UNIT 9998]="abc stock" OR [LINE UNIT 9998]="def stock",1,0)) As [InStock], 
  27.  Sum(IIf([LINE UNIT 9998]="abc shipped" OR [LINE UNIT 9998]="def shipped",1,0)) As [Shipped], 
  28.  Sum(IIf([LINE UNIT 9998]="to go",1,0)) As [ToGo], 
  29.  Sum(IIf([LINE UNIT 9998]="installed",1,0)) As [Installed], 
  30.  Sum(IIf([LINE UNIT 9998]="na",1,0)) As [NA]
  31.  FROM buildchart
  32. UNION
  33.  SELECT "LINE UNIT 0003" As LineNum, 
  34.  Sum(IIf([LINE UNIT 0003]="abc stock" OR [LINE UNIT 0003]="def stock",1,0)) As [InStock], 
  35.  Sum(IIf([LINE UNIT 0003]="abc shipped" OR [LINE UNIT 0003]="def shipped",1,0)) As [Shipped], 
  36.  Sum(IIf([LINE UNIT 0003]="to go",1,0)) As [ToGo], 
  37.  Sum(IIf([LINE UNIT 0003]="installed",1,0)) As [Installed], 
  38.  Sum(IIf([LINE UNIT 0003]="na",1,0)) As [NA]
  39.  FROM buildchart
  40. UNION
  41.  SELECT "LINE UNIT 0004" As LineNum, 
  42.  Sum(IIf([LINE UNIT 0004]="abc stock" OR [LINE UNIT 0004]="def stock",1,0)) As [InStock], 
  43.  Sum(IIf([LINE UNIT 0004]="abc shipped" OR [LINE UNIT 0004]="def shipped",1,0)) As [Shipped], 
  44.  Sum(IIf([LINE UNIT 0004]="to go",1,0)) As [ToGo], 
  45.  Sum(IIf([LINE UNIT 0004]="installed",1,0)) As [Installed], 
  46.  Sum(IIf([LINE UNIT 0004]="na",1,0)) As [NA]
  47.  FROM buildchart
  48. UNION
  49.   SELECT "LINE UNIT 9901" As LineNum, 
  50.   Sum(IIf([LINE UNIT 9901]="abc stock" OR [LINE UNIT 9901]="def stock",1,0)) As [InStock], 
  51.   Sum(IIf([LINE UNIT 9901]="abc shipped" OR [LINE UNIT 9901]="def shipped",1,0)) As [Shipped], 
  52.   Sum(IIf([LINE UNIT 9901]="to go",1,0)) As [ToGo], 
  53.   Sum(IIf([LINE UNIT 9901]="installed",1,0)) As [Installed], 
  54.   Sum(IIf([LINE UNIT 9901]="na",1,0)) As [NA]
  55.   FROM buildchart
  56. UNION
  57.   SELECT "LINE UNIT 0005" As LineNum, 
  58.   Sum(IIf([LINE UNIT 0005]="abc stock" OR [LINE UNIT 0005]="def stock",1,0)) As [InStock], 
  59.   Sum(IIf([LINE UNIT 0005]="abc shipped" OR [LINE UNIT 0005]="def shipped",1,0)) As [Shipped], 
  60.   Sum(IIf([LINE UNIT 0005]="to go",1,0)) As [ToGo], 
  61.   Sum(IIf([LINE UNIT 0005]="installed",1,0)) As [Installed], 
  62.   Sum(IIf([LINE UNIT 0005]="na",1,0)) As [NA]
  63.   FROM buildchart
  64. UNION
  65.   SELECT "LINE UNIT 0006" As LineNum, 
  66.   Sum(IIf([LINE UNIT 0006]="abc stock" OR [LINE UNIT 0006]="def stock",1,0)) As [InStock], 
  67.   Sum(IIf([LINE UNIT 0006]="abc shipped" OR [LINE UNIT 0006]="def shipped",1,0)) As [Shipped], 
  68.   Sum(IIf([LINE UNIT 0006]="to go",1,0)) As [ToGo], 
  69.   Sum(IIf([LINE UNIT 0006]="installed",1,0)) As [Installed], 
  70.   Sum(IIf([LINE UNIT 0006]="na",1,0)) As [NA]
  71.   FROM buildchart
  72. UNION
  73.   SELECT "LINE UNIT 0007" As LineNum, 
  74.   Sum(IIf([LINE UNIT 0007]="abc stock" OR [LINE UNIT 0007]="def stock",1,0)) As [InStock], 
  75.   Sum(IIf([LINE UNIT 0007]="abc shipped" OR [LINE UNIT 0007]="def shipped",1,0)) As [Shipped], 
  76.   Sum(IIf([LINE UNIT 0007]="to go",1,0)) As [ToGo], 
  77.   Sum(IIf([LINE UNIT 0007]="installed",1,0)) As [Installed], 
  78.   Sum(IIf([LINE UNIT 0007]="na",1,0)) As [NA]
  79.   FROM buildchart
  80. UNION
  81.   SELECT "LINE UNIT 0008" As LineNum, 
  82.   Sum(IIf([LINE UNIT 0008]="abc stock" OR [LINE UNIT 0008]="def stock",1,0)) As [InStock], 
  83.   Sum(IIf([LINE UNIT 0008]="abc shipped" OR [LINE UNIT 0008]="def shipped",1,0)) As [Shipped], 
  84.   Sum(IIf([LINE UNIT 0008]="to go",1,0)) As [ToGo], 
  85.   Sum(IIf([LINE UNIT 0008]="installed",1,0)) As [Installed], 
  86.   Sum(IIf([LINE UNIT 0008]="na",1,0)) As [NA]
  87.   FROM buildchart
  88. UNION
  89.    SELECT "LINE UNIT 0009" As LineNum, 
  90.    Sum(IIf([LINE UNIT 0009]="abc stock" OR [LINE UNIT 0009]="def stock",1,0)) As [InStock], 
  91.    Sum(IIf([LINE UNIT 0009]="abc shipped" OR [LINE UNIT 0009]="def shipped",1,0)) As [Shipped], 
  92.    Sum(IIf([LINE UNIT 0009]="to go",1,0)) As [ToGo], 
  93.    Sum(IIf([LINE UNIT 0009]="installed",1,0)) As [Installed], 
  94.    Sum(IIf([LINE UNIT 0009]="na",1,0)) As [NA]
  95.    FROM buildchart
  96. UNION
  97.    SELECT "LINE UNIT 0010" As LineNum, 
  98.    Sum(IIf([LINE UNIT 0010]="abc stock" OR [LINE UNIT 0010]="def stock",1,0)) As [InStock], 
  99.    Sum(IIf([LINE UNIT 0010]="abc shipped" OR [LINE UNIT 0010]="def shipped",1,0)) As [Shipped], 
  100.    Sum(IIf([LINE UNIT 0010]="to go",1,0)) As [ToGo], 
  101.    Sum(IIf([LINE UNIT 0010]="installed",1,0)) As [Installed], 
  102.    Sum(IIf([LINE UNIT 0010]="na",1,0)) As [NA]
  103.    FROM buildchart
  104.  
Sep 14 '07 #7

P: 37
This code is not working when i entered it i got this error: syntax error in query expression SELECT "LINE UNIT 0001" AS LineNum,
Sum(IIf([LINE UNIT 0001]="abc stock" OR [LINE UNIT 0001]="def stock",1,0) AS [InStock],
Sum(IIf([LINE UNIT 0001]="abc shipped" OR [LINE UNIT 0001]="def shipped",1,0) AS [Shipped],
Sum(IIf([LINE UNIT 0001]="to go",1,0) AS [ToGo],
Sum(IIf([LINE UNIT 0001]="installed",1,0) AS [Installed],
Sum(IIf([LINE UNIT 0001]="na",1,0) AS [NA]
FROM buildchart

I thought that may have been because you forgot closing paraenthesis after the 0 in each line (I believe there should be two, one to close the iif one to close the sum) then it gave me this error: Syntax error in union query. Hopefully you can see a reason for this because I can't!

Thanks again,
Sep 14 '07 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
Sorry my fault. They were all missing closing brackets. I've edited the code to add them rather than repost the whole thing.

I have a bad habit of Copy and Paste which means when I make an error it just gets copied. I'll have to stop that.
Sep 14 '07 #9

P: 37
Ok still have an error, this time I get invalid sql statement: expected 'delete', 'insert', 'select', 'update' or 'procedure' Any ideas on this. Are the numbers supposed to be there?

Thanks,
Sep 17 '07 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
OK, I'm pasting the code below without code tags. Just copy and paste it exactly as it appears and let me know what happens.

SELECT "LINE UNIT 0001" As LineNum,
Sum(IIf([LINE UNIT 0001]="abc stock" OR [LINE UNIT 0001]="def stock",1,0)) As [InStock],
Sum(IIf([LINE UNIT 0001]="abc shipped" OR [LINE UNIT 0001]="def shipped",1,0)) As [Shipped],
Sum(IIf([LINE UNIT 0001]="to go",1,0)) As [ToGo],
Sum(IIf([LINE UNIT 0001]="installed",1,0)) As [Installed],
Sum(IIf([LINE UNIT 0001]="na",1,0)) As [NA]
FROM buildchart
UNION
SELECT "LINE UNIT 9997" As LineNum,
Sum(IIf([LINE UNIT 9997]="abc stock" OR [LINE UNIT 9997]="def stock",1,0)) As [InStock],
Sum(IIf([LINE UNIT 9997]="abc shipped" OR [LINE UNIT 9997]="def shipped",1,0)) As [Shipped],
Sum(IIf([LINE UNIT 9997]="to go",1,0)) As [ToGo],
Sum(IIf([LINE UNIT 9997]="installed",1,0)) As [Installed],
Sum(IIf([LINE UNIT 9997]="na",1,0)) As [NA]
FROM buildchart
UNION
SELECT "LINE UNIT 0002" As LineNum,
Sum(IIf([LINE UNIT 0002]="abc stock" OR [LINE UNIT 0002]="def stock",1,0)) As [InStock],
Sum(IIf([LINE UNIT 0002]="abc shipped" OR [LINE UNIT 0002]="def shipped",1,0)) As [Shipped],
Sum(IIf([LINE UNIT 0002]="to go",1,0)) As [ToGo],
Sum(IIf([LINE UNIT 0002]="installed",1,0)) As [Installed],
Sum(IIf([LINE UNIT 0002]="na",1,0)) As [NA]
FROM buildchart
UNION
SELECT "LINE UNIT 9998" As LineNum,
Sum(IIf([LINE UNIT 9998]="abc stock" OR [LINE UNIT 9998]="def stock",1,0)) As [InStock],
Sum(IIf([LINE UNIT 9998]="abc shipped" OR [LINE UNIT 9998]="def shipped",1,0)) As [Shipped],
Sum(IIf([LINE UNIT 9998]="to go",1,0)) As [ToGo],
Sum(IIf([LINE UNIT 9998]="installed",1,0)) As [Installed],
Sum(IIf([LINE UNIT 9998]="na",1,0)) As [NA]
FROM buildchart
UNION
SELECT "LINE UNIT 0003" As LineNum,
Sum(IIf([LINE UNIT 0003]="abc stock" OR [LINE UNIT 0003]="def stock",1,0)) As [InStock],
Sum(IIf([LINE UNIT 0003]="abc shipped" OR [LINE UNIT 0003]="def shipped",1,0)) As [Shipped],
Sum(IIf([LINE UNIT 0003]="to go",1,0)) As [ToGo],
Sum(IIf([LINE UNIT 0003]="installed",1,0)) As [Installed],
Sum(IIf([LINE UNIT 0003]="na",1,0)) As [NA]
FROM buildchart
UNION
SELECT "LINE UNIT 0004" As LineNum,
Sum(IIf([LINE UNIT 0004]="abc stock" OR [LINE UNIT 0004]="def stock",1,0)) As [InStock],
Sum(IIf([LINE UNIT 0004]="abc shipped" OR [LINE UNIT 0004]="def shipped",1,0)) As [Shipped],
Sum(IIf([LINE UNIT 0004]="to go",1,0)) As [ToGo],
Sum(IIf([LINE UNIT 0004]="installed",1,0)) As [Installed],
Sum(IIf([LINE UNIT 0004]="na",1,0)) As [NA]
FROM buildchart
UNION
SELECT "LINE UNIT 9901" As LineNum,
Sum(IIf([LINE UNIT 9901]="abc stock" OR [LINE UNIT 9901]="def stock",1,0)) As [InStock],
Sum(IIf([LINE UNIT 9901]="abc shipped" OR [LINE UNIT 9901]="def shipped",1,0)) As [Shipped],
Sum(IIf([LINE UNIT 9901]="to go",1,0)) As [ToGo],
Sum(IIf([LINE UNIT 9901]="installed",1,0)) As [Installed],
Sum(IIf([LINE UNIT 9901]="na",1,0)) As [NA]
FROM buildchart
UNION
SELECT "LINE UNIT 0005" As LineNum,
Sum(IIf([LINE UNIT 0005]="abc stock" OR [LINE UNIT 0005]="def stock",1,0)) As [InStock],
Sum(IIf([LINE UNIT 0005]="abc shipped" OR [LINE UNIT 0005]="def shipped",1,0)) As [Shipped],
Sum(IIf([LINE UNIT 0005]="to go",1,0)) As [ToGo],
Sum(IIf([LINE UNIT 0005]="installed",1,0)) As [Installed],
Sum(IIf([LINE UNIT 0005]="na",1,0)) As [NA]
FROM buildchart
UNION
SELECT "LINE UNIT 0006" As LineNum,
Sum(IIf([LINE UNIT 0006]="abc stock" OR [LINE UNIT 0006]="def stock",1,0)) As [InStock],
Sum(IIf([LINE UNIT 0006]="abc shipped" OR [LINE UNIT 0006]="def shipped",1,0)) As [Shipped],
Sum(IIf([LINE UNIT 0006]="to go",1,0)) As [ToGo],
Sum(IIf([LINE UNIT 0006]="installed",1,0)) As [Installed],
Sum(IIf([LINE UNIT 0006]="na",1,0)) As [NA]
FROM buildchart
UNION
SELECT "LINE UNIT 0007" As LineNum,
Sum(IIf([LINE UNIT 0007]="abc stock" OR [LINE UNIT 0007]="def stock",1,0)) As [InStock],
Sum(IIf([LINE UNIT 0007]="abc shipped" OR [LINE UNIT 0007]="def shipped",1,0)) As [Shipped],
Sum(IIf([LINE UNIT 0007]="to go",1,0)) As [ToGo],
Sum(IIf([LINE UNIT 0007]="installed",1,0)) As [Installed],
Sum(IIf([LINE UNIT 0007]="na",1,0)) As [NA]
FROM buildchart
UNION
SELECT "LINE UNIT 0008" As LineNum,
Sum(IIf([LINE UNIT 0008]="abc stock" OR [LINE UNIT 0008]="def stock",1,0)) As [InStock],
Sum(IIf([LINE UNIT 0008]="abc shipped" OR [LINE UNIT 0008]="def shipped",1,0)) As [Shipped],
Sum(IIf([LINE UNIT 0008]="to go",1,0)) As [ToGo],
Sum(IIf([LINE UNIT 0008]="installed",1,0)) As [Installed],
Sum(IIf([LINE UNIT 0008]="na",1,0)) As [NA]
FROM buildchart
UNION
SELECT "LINE UNIT 0009" As LineNum,
Sum(IIf([LINE UNIT 0009]="abc stock" OR [LINE UNIT 0009]="def stock",1,0)) As [InStock],
Sum(IIf([LINE UNIT 0009]="abc shipped" OR [LINE UNIT 0009]="def shipped",1,0)) As [Shipped],
Sum(IIf([LINE UNIT 0009]="to go",1,0)) As [ToGo],
Sum(IIf([LINE UNIT 0009]="installed",1,0)) As [Installed],
Sum(IIf([LINE UNIT 0009]="na",1,0)) As [NA]
FROM buildchart
UNION
SELECT "LINE UNIT 0010" As LineNum,
Sum(IIf([LINE UNIT 0010]="abc stock" OR [LINE UNIT 0010]="def stock",1,0)) As [InStock],
Sum(IIf([LINE UNIT 0010]="abc shipped" OR [LINE UNIT 0010]="def shipped",1,0)) As [Shipped],
Sum(IIf([LINE UNIT 0010]="to go",1,0)) As [ToGo],
Sum(IIf([LINE UNIT 0010]="installed",1,0)) As [Installed],
Sum(IIf([LINE UNIT 0010]="na",1,0)) As [NA]
FROM buildchart;
Sep 17 '07 #11

P: 37
Ok,
I am now getting:
syntax error in union query

Thanks much for all trouble your going to for me!
Sep 17 '07 #12

MMcCarthy
Expert Mod 10K+
P: 14,534
Ok,
I am now getting:
syntax error in union query

Thanks much for all trouble your going to for me!
When you get this it displays part of the query. Which part is displaid?
Sep 17 '07 #13

P: 37
It just moves the cursor below the last line of code and displays the error message
Sep 18 '07 #14

FishVal
Expert 2.5K+
P: 2,653
It just moves the cursor below the last line of code and displays the error message
Hi, there.

Square bracket is missed in
Expand|Select|Wrap|Line Numbers
  1. ..........
  2. Sum(IIf([LINE UNIT 9997="to go",1,0)) As [ToGo]
  3. .........
  4.  
Have you thought about db normalization?
Sep 18 '07 #15

MMcCarthy
Expert Mod 10K+
P: 14,534
Hi, there.

Square bracket is missed in
Expand|Select|Wrap|Line Numbers
  1. ..........
  2. Sum(IIf([LINE UNIT 9997="to go",1,0)) As [ToGo]
  3. .........
  4.  
Have you thought about db normalization?
Good catch. I've edited the code accordingly.
Sep 18 '07 #16

P: 37
Hot Damn!!!! You guys are awesome it worked. Thanks a million times for all the help you gave me!
Sep 19 '07 #17

P: 37
Ok, In my world of building access databases the management wants somehting different everyday... Now I need to add one thing to this database... I need the shipped to pull from a different field now. I have a field for every Line Number (1,9997,2,9998,3 etc.) that is called GKN Shipped1, GKN shipped9997 and so on. It is a yes/no field with check boxes. If the box is check yes I want it to count as shipped if you can help me out again that would be awesome. Thanks a million.
Sep 24 '07 #18

Post your reply

Sign in to post your reply or Sign up for a free account.