Record missing on query

[Guest]

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?

 

[John Vinson]

On Mon, 17 Jan 2005 17:23:01 -0800, Functioning Mortal <Functioning

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?

Correct the error in the query.

If you'ld like help doing so, please post the SQL view of all four
queries and some sample data which should be appearing but isn't.

John W. Vinson[MVP]

 

[Guest]

Thanks John

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];