Caculating Totals by groups with a number field

[calpolymis]

Hi. I have a tblorder and tbldetail, 1 to many relationship. tblorder has order A, B, C... and each order has detailed items in tbldetail for example CD1, CD2, CD3...
Each item has a number field named Qty for order quantity.

I'm wondering if I can add up the Qty field by items or get a total by a term(monthly, etc..) Something like monthly sales by item, or total monthly sales, etc..

I'm in the logical design phase for a fairly challenging project to me, and this is something I have to know in advance to set up the tables and relationships. Thanks.

 

[fneily]

Some reading:
Fundamentals of Relational Database Design

http://r937.com/relational.html

I'm going to go out on a limb and say that one order can have many items, AND one item can belong to many orders. So you have a many-to-many relationship, not a one-to-many.
So three tables:
tblItem
ItemID Primary Key
Description
Price
Other item fields

tblOrder
OrderID Primary Key
Date
Salesperson
other order fields

tblOrderDetails (this is known as a junction table)
OrderDetailID Primary Key
ItemID Foreign Key
OrderID Foreign Key
Quantity

Using queries you can get the total price for an order, or find the total sales of a specific item, etc.
The best way to do summaries is with Pivot Tables. eg how many were sold in each month, week, year, who bought the most, etc.

If you have a customer table, then you'd add the custID to the junction table.

 

[calpolymis]

Hi, fneily.

Thanks for your comment. Pivot table approach is something that I've never tried, but sounds very intriguing. I too have to go out now for a work. I'll be responding probably tomorrow morning time. Thanks and have a great weekend!

 

[PHV]

If you have a customer table, then you'd add the custID to the junction table
Usually an order belong to only one customer, so I'd add the custID FK in the order table, not the junction table.

 

[fneily]

Duh, I stand corrected. Thnak you.

 

[calpolymis]

Hello. So I redesigned the relationship as suggested. Attached image shows the original relashionship and the modified one. As I said, I've never tried this design, so I'm just wondering how I shoule set up queries, etc..

For example, a query with the original design to get the total per item looked like this:

SELECT [tblDetail].ITEM, Count(tblOrder.CustOrderID) AS [Total Sold including Replacement]
FROM tblOrder INNER JOIN [tblDetail] ON tblOrder.CustOrderID = [tblDetail].CustOrderID
WHERE ((([tblDetail].[Shipped Date]) Between [Start] And [End]))
GROUP BY [tblDetail].ITEM;

How can I change join part of this query with the new design? And also, the above DB didn't have a number field with number of orders per an order. It just calculated total rows per item in tblDetail table. What would be a typical SQL to get the total # of orders per item with the new design, for just quick glance? Many thanks..

 

[fneily]

Tried your link and get a bizarre website.

 

[calpolymis]

Hi, fneily.

Here's the link, directly copied from the address bar on the browser:

http://sweatmaster.com/images/Before_and_After.jpg

Try copy and paste this link on your address bar.

 

[fneily]

That worked but it was hard to read. However, I did see that you have CustOrderID in both tblOrder and tblDetail. There should be NO duplicate fields in these tables.

Also, in tblOrder, I saw fields with names like Termcode1, Termcode3, etc. Violates first normal form. Drop the number you have Termcode, Termcode, etc. Duplicate column headings. Each Termcode should be in a separate record.

What I could see, which wasn't much, is that your tables need redesigning.

 

[calpolymis]

Hi. fneily.

I removed duplicates based on the new table design. The fields are actually from one of my previous projects, and I'm not going to use all the fields, especially something like Termcode1,2..

Now I remember your ID I believe I got great help from you a few years ago when I developed my first fairly big- scale project for my company! Well, nice to talk to you again! And thank you again.

As I said, I've seen this pivot table approach before, but I've never used it. I modified tables and re-uploaded the image. It's in

http://sweatmaster.com/images/New_Table_Design.jpg

Please copy and paste again. For unknown reason, link opens a hosting home page if you click it.
(If you can't see it clearly, try downloading it and open it with your graphic software. It should work.)

How do you write(or drag in Access)SQL in join part in this design? Do you know some examples that I can learn from?

 

[fneily]

Oh my.
In tblCustomer, since they have CustCode, why do you need CustID? You can use CustCode as the Primary Key.
Take CustOrderID out of tblCustomer.
Take CustCode out of tblOrder.
If CustPO, CustPODate, etc belong to the customer, then they should not be in tblOrder.
There's an ItemCode in tblOrder. Do you have an Item table?
Do not store values like TotalAmt, TotalQty. they are calculated fields.
TblDetail has Custwhatever. No customer info should be in tblDetail.

Print your image, take a pencil and cross out all fields that are not related to the tables name. So everything in tblCustomer has to do with the customer - no orders, no detail, no food groups, etc.
Etc.

tblDetail, I guess, has to do with an item. So everything is about the item. No customer stuff, no orders, no movies, etc.

Can't proceed until these tables are cleaned up.

 

[calpolymis]

OK. CustCode is a value used in our SBT accounting system, therefore has totally different usages and has nothing to do with this DB. CustPO/CustPODate are order-specific, so I think they should stay in tblOrder. Let me know if I got it wrong.

Sorry for not cleaning up the tables. These are from a very complicated old DB that an order and actual item shippings were seperate. I was going to build a new DB out of this DB by modifying it alot, but using many core elements.

I've cleaned up the tables a bit(there's gonna be tens of more fields eventually) and here's the link:

http://sweatmaster.com/images/New_Table_Design.jpg

 

[calpolymis]

Oops. the link was wrong. Here's the correct one.

http://sweatmaster.com/images/New_Table_Design3.jpg

 

[fneily]

In tblOrderDetail, you have Qry. Is this Qty?
In tblDetail, take out OrderQty. Or is this how many on hand of that item? Then change the name, it's confusing.
Quantity should be in tblOrderDetail.

Now, working with those tables, create the queries to do the analysis.

 

[calpolymis]

Ok. That's exactly what I'm confused with this new pivot table approach. Qty or OrderQty is going to be "quantity ordered" of an item in an order. I removed it from tblDetail. Now here it is:

http://sweatmaster.com/images/New_Table_Design4.jpg

 

[fneily]

First work on regular queries.
Obviously, this is not a classroom to spend a couple of hours on teaching pivot tables. Do you have any books?

 

[calpolymis]

Wow.. Pivot table is pretty amazing. I could have saved a lot of time if I had known this before. Now I understand I can make a query to include all the necessary fields and make a pivot table out of that query to come up with a report like this:

http://sweatmaster.com/images/New_Table_Design4.jpg

I now became to know that Access automatically sets up the relationship either. Still I have to know how to set up a form with this new junction table approach. In my previous design, tblCustomer had CustOrderID to connect to CustOrderID field in tblOrder, but you advised me to remove it from tblCustomer.

Now, if I make a form, starting from tblCustomer, and make a subform with tblOrder, the form shows all the orders of a customer, instead of a specific order detail.

If I may ask, how do you set up a form with this junction table approach? Many thanks..

 

[calpolymis]

Oops, sorry again. Here's the correct link:

http://sweatmaster.com/images/PivotTable.jpg

 

[fneily]

tblCustomer is connected to tblOrder through CustID. It is a one-to-many relationship, one customer can have many orders.

To see what details a customer has, you connect tblcustomer to tblorders to tblorderdetails to tbldetails. Just follow the path.

To see a specific order detail, you connect tblorder to tblorderdetail to tbldetail. Just follow the path.

Your form would be based on queries connecting tables. In other words, instead of a form having a table as its source, it would have a query.

Pivot tables are awesome. And you just saw the perverbial tip of the iceberg of them.

 

[calpolymis]

Thank you very much for your help, fneily. I have more to learn, but I think I gotta close this post for now due to my sudden business trip to another state. Thanks again.