Dialog form / parameter query issue

  • Thread starter Thread starter Lisa
  • Start date Start date
L

Lisa

I would like to (have been trying to) design a parameter query to build

a report.


The report will show ProductID, ProductName, UnitsInStock,
SumQuantityOnOrder along with a text box holding an IIf statement. This

is for a home-based dessert business db. The goal is to know, at any
given time, if there is enough product on hand to meet orders for the
product.

I tried creating a dialog form with txtStartDate and txtEndDate. On the

query in the field StartDate I used:
Between[Forms]![frmProductOnOrderDialog]![txtStartDate] And
[Forms]![frmProductOnOrderDialog]![txtEndDate]
However, I still don't SUM the needed field.

When I add the field StartDate to the query I then cannot SUM the # of
QuantityOnOrder. Each order shows as a seperate entry in the query.
When I leave off the field StartDate I can SUM the # of QuantityOnOrder

but cannot breakdown the report as needed to if product on hand meets
orders for a given time.


Any suggestions or assistance would be greatly appreciated. I have
looked at this all day (creating and deleting dozen of queries, forms
and reports) trying to make it work.
 
After designing, deleting, designing & deleting...I now have deleted
the form and report. The query's SLQ currently is:
SELECT Products.ProductID, Products.ProductName, Products.UnitsInStock,
Sum([Order Details Extended].Quantity) AS SumOfQuantity
FROM Products INNER JOIN ([Order Details Extended] INNER JOIN [Orders
Qry] ON [Order Details Extended].OrderID = [Orders Qry].OrderID) ON
Products.ProductID = [Order Details Extended].Products.ProductID
GROUP BY Products.ProductID, Products.ProductName,
Products.UnitsInStock, [Orders Qry].ShipDate;

Thank you for any assistance you can offer.
 
Get rid of the , [Orders Qry].ShipDate at the end of the SQL.

Your final SQL should look like:

SELECT Products.ProductID, Products.ProductName, Products.UnitsInStock,
Sum([Order Details Extended].Quantity) AS SumOfQuantity
FROM Products INNER JOIN ([Order Details Extended] INNER JOIN [Orders
Qry] ON [Order Details Extended].OrderID = [Orders Qry].OrderID) ON
Products.ProductID = [Order Details Extended].Products.ProductID
WHERE [Orders Qry].ShipDate BETWEEN
[Forms]![frmProductOnOrderDialog]![txtStartDate] AND
[Forms]![frmProductOnOrderDialog]![txtEndDate]
GROUP BY Products.ProductID, Products.ProductName,
Products.UnitsInStock;
 
Using the above SLQ I get an error:
Syntax error in join operation

The highlighted area is ...INNER JOIN...[Order Details
Extended].OrderID = [Orders Qry].OrderID)
The [Orders Qry] is highlighted.

Thanks again for your patience
 
Try:

SELECT Products.ProductID, Products.ProductName, Products.UnitsInStock,
Sum([Order Details Extended].Quantity) AS SumOfQuantity
FROM (Products INNER JOIN [Order Details Extended] ON
Products.ProductID = [Order Details Extended].Products.ProductID) INNER JOIN
[Orders
Qry] ON [Order Details Extended].OrderID = [Orders Qry].OrderID
WHERE [Orders Qry].ShipDate BETWEEN
[Forms]![frmProductOnOrderDialog]![txtStartDate] AND
[Forms]![frmProductOnOrderDialog]![txtEndDate]
GROUP BY Products.ProductID, Products.ProductName,
Products.UnitsInStock;
 
Back
Top