F
Functioning Mortal
I have a database which combines Access 2002 templates for inventory control
and order tracking. In it, I have a query based on three other queries that
provide a calculations from three of the template tables -- supplies received
- transactions and shrinkage -- to arrive at product quantities available.
When a new product is entered into the database with a form, the three
foundation queries pick up the new records. However, the query that is based
on these three will not reflect any new records when rerun. Any suggestions?
1st Query: SELECT DISTINCTROW Categories.CategoryName,
Products.ProductName, [Inventory Transactions].ProductID, Sum([Inventory
Transactions].UnitsReceived) AS [Sum Of UnitsReceived], Count(*) AS [Count
Of Inventory Transactions] FROM (Categories INNER JOIN Products ON
Categories.CategoryID = Products.CategoryID) INNER JOIN [Inventory
Transactions] ON Products.ProductID = [Inventory Transactions].ProductID
GROUP BY Categories.CategoryName, Products.ProductName, [Inventory
Transactions].ProductID;
2nd Query: SELECT DISTINCTROW Products.ProductID, Categories.CategoryName,
Products.ProductName, Sum([Event Order Details].Quantity) AS [Sum Of
Quantity], Count(*) AS [Count Of Order Details] FROM (Categories INNER JOIN
Products ON Categories.CategoryID = Products.CategoryID) INNER JOIN [Event
Order Details] ON Products.ProductID = [Event Order Details].ProductID
GROUP BY Products.ProductID, Categories.CategoryName, Products.ProductName
ORDER BY Categories.CategoryName, Products.ProductName;
3rd Query: SELECT DISTINCTROW [Inventory Transactions].ProductID,
Sum([Inventory Transactions].UnitsShrinkage) AS [Sum Of UnitsShrinkage],
Count(*) AS [Count Of Inventory Transactions] FROM [Inventory Transactions]
GROUP BY [Inventory Transactions].ProductID;
4th and troubled query: SELECT [Product Received From Supplier
Totals].CategoryName, [Product Received From Supplier Totals].ProductName,
[Product Received From Supplier Totals].ProductID, [Product Received From
Supplier Totals].[Sum Of UnitsReceived], [Product Going to Event
Totals].[Sum Of Quantity], [Product Shrinkage Totals].[Sum Of
UnitsShrinkage], [Sum Of UnitsReceived]-[Sum Of Quantity]-[Sum Of
UnitsShrinkage] AS [Available Units] FROM ([Product Received From Supplier
Totals] INNER JOIN [Product Going to Event Totals] ON [Product Received From
Supplier Totals].ProductID = [Product Going to Event Totals].ProductID)
INNER JOIN [Product Shrinkage Totals] ON [Product Going to Event
Totals].ProductID = [Product Shrinkage Totals].ProductID GROUP BY [Product
Received From Supplier Totals].CategoryName, [Product Received From Supplier
Totals].ProductName, [Product Received From Supplier Totals].ProductID,
[Product Received From Supplier Totals].[Sum Of UnitsReceived], [Product
Going to Event Totals].[Sum Of Quantity], [Product Shrinkage Totals].[Sum Of
UnitsShrinkage], [Sum Of UnitsReceived]-[Sum Of Quantity]-[Sum Of
UnitsShrinkage];
and order tracking. In it, I have a query based on three other queries that
provide a calculations from three of the template tables -- supplies received
- transactions and shrinkage -- to arrive at product quantities available.
When a new product is entered into the database with a form, the three
foundation queries pick up the new records. However, the query that is based
on these three will not reflect any new records when rerun. Any suggestions?
1st Query: SELECT DISTINCTROW Categories.CategoryName,
Products.ProductName, [Inventory Transactions].ProductID, Sum([Inventory
Transactions].UnitsReceived) AS [Sum Of UnitsReceived], Count(*) AS [Count
Of Inventory Transactions] FROM (Categories INNER JOIN Products ON
Categories.CategoryID = Products.CategoryID) INNER JOIN [Inventory
Transactions] ON Products.ProductID = [Inventory Transactions].ProductID
GROUP BY Categories.CategoryName, Products.ProductName, [Inventory
Transactions].ProductID;
2nd Query: SELECT DISTINCTROW Products.ProductID, Categories.CategoryName,
Products.ProductName, Sum([Event Order Details].Quantity) AS [Sum Of
Quantity], Count(*) AS [Count Of Order Details] FROM (Categories INNER JOIN
Products ON Categories.CategoryID = Products.CategoryID) INNER JOIN [Event
Order Details] ON Products.ProductID = [Event Order Details].ProductID
GROUP BY Products.ProductID, Categories.CategoryName, Products.ProductName
ORDER BY Categories.CategoryName, Products.ProductName;
3rd Query: SELECT DISTINCTROW [Inventory Transactions].ProductID,
Sum([Inventory Transactions].UnitsShrinkage) AS [Sum Of UnitsShrinkage],
Count(*) AS [Count Of Inventory Transactions] FROM [Inventory Transactions]
GROUP BY [Inventory Transactions].ProductID;
4th and troubled query: SELECT [Product Received From Supplier
Totals].CategoryName, [Product Received From Supplier Totals].ProductName,
[Product Received From Supplier Totals].ProductID, [Product Received From
Supplier Totals].[Sum Of UnitsReceived], [Product Going to Event
Totals].[Sum Of Quantity], [Product Shrinkage Totals].[Sum Of
UnitsShrinkage], [Sum Of UnitsReceived]-[Sum Of Quantity]-[Sum Of
UnitsShrinkage] AS [Available Units] FROM ([Product Received From Supplier
Totals] INNER JOIN [Product Going to Event Totals] ON [Product Received From
Supplier Totals].ProductID = [Product Going to Event Totals].ProductID)
INNER JOIN [Product Shrinkage Totals] ON [Product Going to Event
Totals].ProductID = [Product Shrinkage Totals].ProductID GROUP BY [Product
Received From Supplier Totals].CategoryName, [Product Received From Supplier
Totals].ProductName, [Product Received From Supplier Totals].ProductID,
[Product Received From Supplier Totals].[Sum Of UnitsReceived], [Product
Going to Event Totals].[Sum Of Quantity], [Product Shrinkage Totals].[Sum Of
UnitsShrinkage], [Sum Of UnitsReceived]-[Sum Of Quantity]-[Sum Of
UnitsShrinkage];