By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,678 Members | 2,439 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,678 IT Pros & Developers. It's quick & easy.

SQL Totaling Diamonds by Date Crosstab in ACCESS

P: 4
I have several related tables. One table lists orders with due dates for SKUs while another table lists components of a SKU (diamonds). A third related table lists diamond IDs and diamond inventory. One diamond can be included in multiple SKUs and one SKU can contain different types of diamonds. The SKU ID links the SKU component table. The diamond ID links the SKU component table and the diamond type table. The diamond table lists unique types of diamonds plus their total inventory and Diamond ID.

My boss wants me to return the # of diamonds by diamond ID we need to order and the due dates (month totals) minus the inventory on hand. Except the only problem, is that when i create a query, the due date repeats, thus giving multiple columns for each due date per diamond type, even though I only need one. This results in a total inventory subtracted from the total # of diamonds we need in each month. I would like a query that subtracts out the diamonds on hand from the earliest months and leaves the later months for our totals.

Any ideas? Can this be done?

Here is my tables list, this should shed more light on it. But this is actually an ACCESS crosstab with the months going across in columns and the Diamond IDs in rows.

Purchase Orders: There is a one to many between products and purchase_orders
PO_ID (Primary Key)
SKU_ID (Foreign Key)
Quantity_Ordered (numeric)
Due-Date (date)
Order_Completed(Yes/No)

Finished_Products_Table:
SKU_ID (Unique Key)
Product_Name
Product_Descritption

Diamond_Table:
Diamond_ID (Primary Key)
Diamond_Shape
Diamond_Size
Diamond_Quantity_In_Stock

Products_Diamond_Components_Table:
Diamond_Component_Key (Primary Key)
SKU_ID (foreign key)
Diamond_ID
Diamond_Quantity (integer)
Relationships: there is a one to many between diamond table and this table
and a one to many beetween product/sku table and this table
May 21 '09 #1
Share this Question
Share on Google+
2 Replies


puppydogbuddy
Expert 100+
P: 1,923
two things you can due quickly:
1. Change the DueDate column to a row header.
2. Add another column named MonthDue which will be based on the expression Month(DueDate) & Year(DueDate) and make that the Column Header in your crosstab. This will give you column totals by month as compared to daily columns.
May 24 '09 #2

P: 4
I found the solution. I needed the keyword FIRST for the inventory column.
May 25 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.