trying sum large amount of info in query

[cmartin1986]

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.

[MMcCarthy]

Not sure if I understand you fully but something like the following:

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
FROM TableName
GROUP BY ProductLine
 

[cmartin1986]

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,

[MMcCarthy]

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

 Field; Type; IndexInfo
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

[cmartin1986]

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,

[cmartin1986]

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.

[MMcCarthy]

OK this is going to be a long one ...

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
 

[cmartin1986]

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,

[MMcCarthy]

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.

[cmartin1986]

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,

[MMcCarthy]

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;

[cmartin1986]

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

Thanks much for all trouble your going to for me!

[MMcCarthy]

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?

[cmartin1986]

It just moves the cursor below the last line of code and displays the error message

[FishVal]

It just moves the cursor below the last line of code and displays the error message

Hi, there.

Square bracket is missed in

..........
Sum(IIf([LINE UNIT 9997="to go",1,0)) As [ToGo]
.........
 

Have you thought about db normalization?

[MMcCarthy]

Hi, there.

Square bracket is missed in

..........
Sum(IIf([LINE UNIT 9997="to go",1,0)) As [ToGo]
.........
 

Have you thought about db normalization?

Good catch. I've edited the code accordingly.

[cmartin1986]

Hot Damn!!!! You guys are awesome it worked. Thanks a million times for all the help you gave me!

[cmartin1986]

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.