Solved

# Grouping multiple fields in Access and VB

Posted on 2006-03-27
272 Views
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
0
Question by:J_K_M_A_N

LVL 92

Expert Comment

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

LVL 92

Expert Comment

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

LVL 92

Expert Comment

J_K_M_A_N,

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

Regards,

Patrick
0

LVL 3

Author Comment

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

LVL 92

Expert Comment

> 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

LVL 3

Author Comment

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

LVL 92

Accepted Solution

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

LVL 3

Author Comment

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

LVL 3

Author Comment

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

LVL 92

Expert Comment

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

LVL 3

Author Comment

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

LVL 92

Expert Comment

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

LVL 3

Author Comment

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

LVL 92

Expert Comment

0