Order total in queries

[e51lrrp]

I need to create an order total where there are many items in one order. How do I tell Access that if the OrderID is the same then sum the item totals.

Thanks so much for any assistance that anyone might offer. Obviously new at this...THANKS
Sharon

[orange]

The key is to get your database t6ables and relationships set up to match your business.
Review these videos
http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

Here are some other links that may be helpful to you.
Database Design Principles http://forums.aspfree.com/attachment...2&d=1201055452
Entity Relationship Diagramming http://www.rogersaccesslibrary.com/T...lationship.zip

Good luck.

[e51lrrp]

Orange...thanks for the websites. I have figured out how to total a report but have a few questions about my report. First when you open rptSalesOrders it is asking for a ID parameter....I believe it is because I have not identified an ID but for the life of me I can't see what is missing. Also why does the totals keeping going away as you scroll down the report. I have attached my database so that you can take a look at it.

Sure appreciate your assistance.
SharonzipSylvester12-30-13.zip

[orange]

A few things:
It seems you deal with 2 item types (apparel, trophy/engraving)
Things like S,M,L,XL,2XL... are attributes of an Item(Apparel) and shouldn't be in OrderDetails
Things like Placement1, placement2, placement3 would normally be in a separate table say Placement(whatever that is) and be related to Item
You would normally have a Price for an Item. That would be currentPrice, you can change it when necessary.
In the OrderDetail you would have AgreedToPrice or similar that indicates the Price for that item for that Customer on that date - this could include salePrice, Discount, Loyalty Price etc. AgreedToPrice and Quantity are recorded within OrderDetail.
You never store Totals in a table -- you calculate them when needed.
I think you have a little more normalizing (additional tables) to do.

Same comments relate to your EngravingTrophyDetails - many fields are attributes of Trophy (I think). Things like Product Description,TrophyBase, MaterialUsed,
ColumnStyle...ColumnPrice belong in other tables. You may need to further identify the components that make up a trophy eg base,columns, topper, cup, trim and then relate these to Trophy or CompletedTrophy. Such a structure may allow you to have several combinations of components that together make various FinalTrophies. I'm not sure, but it's not my business. Some of these things may make sense or nonsense to you.

In general make the ID (usually the PK of the table) a more meaningful name eg OrderID, CustomerID,..just makes things clearer.
Anyway, that's my 2 cents based on looking at your relationships diagram.
Good luck with your project.