Help With Sum in Query (1 Viewer)

jw195

New member
Local time
Today, 00:52
Joined
Feb 20, 2019
Messages
8
I am building a database to track and balance incoming and outgoing orders of crops.

I have 3 tables right now.

"Products" is a list of products in the database

"Incoming_Orders" is a list of incoming orders with the following fields;

"Order#", "Supplier", "Order_Date", "Ship_Date"

"Incoming_Order_Information" contains the details of each order with the following fields;

"Order#" (linked to "Order#" in "Incoming_Orders"), "Crop_Description" (Linked to "Crop_Description", the only field in the "Products" Table) and "Quantity_Ordered")

One crop may be ordered multiple times on different orders and from different suppliers, I want to see the total amount ordered for each crop across all orders.

I think the next step is to use a query that will consolidate the orders to show the quantities ordered for each crop, however I am not sure how to achieve this or if it's even the correct way to go about it.

Thanks.
 

Attachments

  • DatabaseCapture.PNG
    DatabaseCapture.PNG
    10.9 KB · Views: 91

June7

AWF VIP
Local time
Yesterday, 23:52
Joined
Mar 9, 2014
Messages
5,424
That would be an aggregate (GROUP BY) query.

Or build a report and use its Sorting & Grouping features with aggregate calcs in group section. Report allows display of raw data as well as summary info.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:52
Joined
Feb 19, 2002
Messages
42,981
I don't understand why order is broken into two tables. If the supplier, order date, and ship date are all the same. Why is quantity in a separate table?

Also,
1. It is best practice to avoid the use of embedded spaces and special characters in object names. Although "Access" allows it, VBA does not and at a minimum, it is a PITA to always have to enclose the offending name in square brackets.
2. Rather than using descriptions as the primary key, it might be better to use an autonumber and then keep the description as a text field. That will let you separate the description into discrete data elements if you are currently mushing things together.
 

Users who are viewing this thread

Top Bottom