Grouping multiple fields in Access and VB

I have a table that has 3 fileds that relate to 3/8" pipe and 3 fields that relate to 1/2" pipe. Here is some sample data:

Cust ID    3/8x10   3/8x20   3/8x30   1/2x10   1/2x20   1/2x30
   4              2          1           1            6          0            0
   4              0          2           1            1          2            2
   5              1          0           0            2          0            3
   5              2          2           1            2          3            0

I have an SQL statement that sums each field and groups by custIDand gives the totals below.

Cust ID    3/8x10   3/8x20   3/8x30   1/2x10   1/2x20   1/2x30
   4              2          3           2            7          2            2
   5              3          2           1            4          3            3

What I want to do now, is to add all the 3/8 and all the 1/2 into 2 totals labeled 3/8 Pipe and 1/2 Pipe so it would look like the data below:

Cust ID    3/8 Pipe    1/2 Pipe
   4              7              11
   5              6              10

Do I have to have 2 SQL statements to do that or can it be in 1? Or can it be done at all? Thanks.

J_K_M_A_N
LVL 3
J_K_M_A_NAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
Hi J_K_M_A_N,

SELECT CustID, (Sum([3/8x10])+Sum([3/8x20])+Sum([3/8x30])) AS ThreeEighths, (Sum([1/2x10])+Sum([1/2x20])+Sum([1/2x30])) AS HalfInch
FROM YourTable
GROUP BY CustID

BTW, this is a bad way to organize the data; a better way would be:

CustID, PipeType, Quantity

Regards,

Patrick
0
Patrick MatthewsCommented:
J_K_M_A_N,

Or:

SELECT CustID, (Sum([3/8x10]+[3/8x20]+[3/8x30])) AS ThreeEighths, (Sum([1/2x10]+[1/2x20]+[1/2x30])) AS HalfInch
FROM YourTable
GROUP BY CustID


Regards,

Patrick
0
Patrick MatthewsCommented:
J_K_M_A_N,

That all assumes, of course, that there are no NULLs in the quantity fields...

Regards,

Patrick
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

J_K_M_A_NAuthor Commented:
Well, this is an order table and we need the data seperated this way for other reports and orders so that is how it needs to be stored.

So what you are saying is that I need to run a second SQL statement to sum them together. I can do that. Thanks. I will give it a shot.

J_K_M_A_N
0
Patrick MatthewsCommented:
> Well, this is an order table and we need the data seperated this way for other reports and orders
> so that is how it needs to be stored.

I respectfully disagree.  So what happens if you start selling a new product?  Add another field?
Set up your database in a proper, normalized fashion, and you can always cover your reporting
requirements.

> So what you are saying is that I need to run a second SQL statement to sum them together. I can
> do that. Thanks. I will give it a shot.

Depends on what you need.  You could very easily do:

SELECT CustID, Sum([3/8x10]) AS p1, Sum([3/8x20]) AS p2, Sum([3/8x30]) AS p3,
(Sum([3/8x10]+[3/8x20]+[3/8x30])) AS ThreeEighths, Sum([1/2x10]) AS p4, Sum([1/2x20]) AS p5,
Sum([1/2x30]) AS p6, (Sum([1/2x10]+[1/2x20]+[1/2x30])) AS HalfInch
FROM YourTable
GROUP BY CustID

That would give all of them in one statement.  (Change the field aliases as needed.)

Patrick
0
J_K_M_A_NAuthor Commented:
Well, this is a set number of sizes but I guess I can see what you mean. I am pretty new to databases (as you probably have guessed). I can say with much certainty that there will be no more sizes added though.

But let me ask you this, there are actually 28 fields for the pipe. I do have them laid out as 28 seperate fields. Then I have a Date field and a Customer ID field. I also have a value field for the dollar value of the order. I will be having the user click a date and pick a customer and then add the amount of each pipe and the dollar value on a form. How would you set up the table based on that? I figured it would be easiest to have them all seperate. Do you recommend having a date field and customer ID field and then a desc field and value field? I thought that would be a little too much if there were many sizes of pipe entered. I was going with the one entry per order per date. Would you go with many entries?

Just to sumerize. My table is as follows:

Date - ChildID - 28 fields for the pipe - Value

I will increase points for the extra questions. I would just like to know the best way in your opinion.

J_K_M_A_N
0
Patrick MatthewsCommented:
I'm pleased to do it :)

The typical way to handle this is to use at least four tables:


tblCustomers
----------------------------------------------------------
CustID (PK)
CustName
<other customer attributes>

tblProducts
----------------------------------------------------------
ProdID (PK)
ProdName
<other product attributes; may include unit price or that may be handled by other tables, especially
if you want to use effective dating and store prices over time>

tblOrders
----------------------------------------------------------
OrderID (PK)
CustID (FK) -- relates to tblCustomers
OrderDate
<other order details but usually not total $$ amount--that can be calculated by query.  Could include
shipping costs, though>

tblOrderDetails
----------------------------------------------------------
OrderDetailID (PK)
OrderID (FK) --relates to tblOrders
ProdID (FK) -- relates to tblProducts
Quantity
<maybe other line-item attributes>



So, when there is an order, one record is created for tblOrders; if the order has N discrete products in
it (in varying quantities, or not), there are N related records in tblOrderDetails.  This is usually handled
by having a form for Orders that has a subform for the details.

How you handle where the price and $$ goes depends on other decisions about how to determine the price.


To see this in action, make a new dummy database using Access's Order Entry template, or take a peek
in the Northwind db that MS uses as a sample.

Regards,

Patrick
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
J_K_M_A_NAuthor Commented:
Well, after using my brain (which hurt by the way) I came up with the following. Please tell me what you think.

Table 1: 'Parent'
   ParentID - Name

Table 2: 'Child'
   ChildID - ParentID - Name

Table 3: 'Pipe'
   SizeID - PipeSize

Table 4: 'OrderID'
   OrderID - ChildID - Date - Value

Table 5: 'Orders'
   KeyID - OrderID - SizeID - Amount

I use the 'parent' table to group companies together. It is like a 'Sold To' table.

I use the 'child' table to group companies together. It is like a 'Ship To' table.

I will use the 'OrderID' table when a new order is entered. That way each order will have its own unique key with the child ID and the value and date right in it.

I will use the 'Orders' table to keep track of what pipe was ordered.

So if there is an order for a customer that has three kinds of pipe on it, there will be three entries in the 'Orders' table. Each one will have the pipe size and amount of pipe sold.

Does that look better? Like I said, I am new to databases. I wish I had tried to learn this a long time ago.

J_K_M_A_N
0
J_K_M_A_NAuthor Commented:
Well, we have the same timing. I think I was pretty close to what you had. Not too bad for a person set in his ways. :) I should tell you that this is for tracking pipe sold each week and month by month and year to date so it isn't the typical Order entry type of thing. So the price of the pipe isn't actually used. We get a value of the order from some place else. That is why I used a total value in the OrderID table. I couldn't get a total by a query. But it should work out all the same. (I hope)

I REALLY appreciate your help. I can do ok with a little help.

J_K_M_A_N
0
Patrick MatthewsCommented:
Your revised schema is good--nearly identical to mine, the main difference being that you have a
structure above "customer", which makes a lot of sense if you sell to/ship to lots of different individuals/
locations for the same client company.

Long-term, I think you would be better off moving to something like it.

Regards,

Patrick
0
J_K_M_A_NAuthor Commented:
The one question I have is about how to come up with all the figures. I noticed that you answer questions in VB and Excel and whatnot so...

The way I am working this is with VB6. That is about all I use for programming. I make a report every week that shows the date, customer name, 3/8" pipe, 1/2" pipe, etc, etc, value. I am guessing I would have to have several SQL statements to come up with all the totals I need. I am trying to wrap my head around all of that. Is there an easy way to do something like that? I am having trouble coming up with a GROUP BY statement that would give me all that info in one table. Is there a way to do that or will I need a SQL statement for each size?

J_K_M_A_N
0
Patrick MatthewsCommented:
You might want to think about either using a crosstab query itself, or basing a report off of a crosstab
query.  The crosstab spits out output similar to your original structure, with separate columns for each
pipe size.

Patrick
0
J_K_M_A_NAuthor Commented:
Thanks for all your help Matthew. I will do some checking into the crosstab query. Hopefully it is something I can do using VB. If not, not a big deal. Thanks again.

J_K_M_A_N
0
Patrick MatthewsCommented:
Glad to help :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.