This would not let me post new threads for some reason, but heres my problem:
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_Ordere d (numeric)
Due-Date (date)
Order_Completed (Yes/No)
Finished_Produc ts_Table:
SKU_ID (Unique Key)
Product_Name
Product_Descrit ption
Diamond_Table:
Diamond_ID (Primary Key)
Diamond_Shape
Diamond_Size
Diamond_Quantit y_In_Stock
Products_Diamon d_Components_Ta ble:
Diamond_Compone nt_Key (Primary Key)
SKU_ID (foreign key)
Diamond_ID
Diamond_Quantit y (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
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_Ordere d (numeric)
Due-Date (date)
Order_Completed (Yes/No)
Finished_Produc ts_Table:
SKU_ID (Unique Key)
Product_Name
Product_Descrit ption
Diamond_Table:
Diamond_ID (Primary Key)
Diamond_Shape
Diamond_Size
Diamond_Quantit y_In_Stock
Products_Diamon d_Components_Ta ble:
Diamond_Compone nt_Key (Primary Key)
SKU_ID (foreign key)
Diamond_ID
Diamond_Quantit y (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
Comment