Form and Query Problem

[Kelvin Leong]

Hi there,

I have 2 tables, Order and OrderDet, linked by OrderID. The relationship
between Order and OrderDet is one-to-many.

Order Table OrderDet Table

OrderID | OrderDate | | OrderDetID | OrderID | Emp
| Amt |
---------------------------
--------------------------------------------------
Order01 | 01/01/2007 | | OrderDet01 | Order01 | Amy |
30.00 |
| OrderDet02 | Order01
| Ben | 45.00 |

I have a query as follows for a form:
SELECT Order.OrderID, Order.OrderDate, Sum(IIf([Emp]="Amy",[Amt],0)) AS
SalesAmy, Sum(IIf([Emp]="Ben",[Amt],0)) AS SalesBen, [SalesAmy]+[SalesBen]AS
Subtotal
FROM [Order] INNER JOIN [OrderDet] ON Order.OrderID = OrderDet.OrderID
GROUP BY Order.OrderID, Order.OrderDate
ORDER BY Order.OrderID;

This form display the above info as the following:
OrderID | OrderDate | SalesAmy | SalesBen | Subtotal |
------------------------------------------------------------------------
Order01 | 01/01/2007 | 30.00 | 45.00 | 75.00 |

Everytime i load this form I get 2 pop-ups to Enter Parameter Value. I just
click OK and the form still displays the information. But how do I get rid of
the pop-ups. It's kinda annoying.

Kelvin

 

[storrboy]

First, what fields are the parameters for?
Second, take the 'Subtotal' math out of the query and use a calculated
control on the form.

 

[Kelvin Leong]

Hi Storrboy,

I have a query as follows for a form:
SELECT Order.OrderID, Order.OrderDate, Sum(IIf([Emp]="Amy",[Amt],0)) AS
SalesAmy, Sum(IIf([Emp]="Ben",[Amt],0)) AS SalesBen, [SalesAmy]+[SalesBen]AS
Subtotal
FROM [Order] INNER JOIN [OrderDet] ON Order.OrderID = OrderDet.OrderID
GROUP BY Order.OrderID, Order.OrderDate
ORDER BY Order.OrderID;

I use the following expressions in the query
Sum(IIf([Emp]="Amy",[Amt],0)) AS SalesAmy, and
Sum(IIf([Emp]="Ben",[Amt],0)) AS SalesBen

helps me to achieve the following view, which I wanted:
OrderID | OrderDate | SalesAmy | SalesBen | Subtotal |
------------------------------------------------------------------------
Order01 | 01/01/2007 | 30.00 | 45.00 | 75.00 |

If I only use:

IIf([Emp]="Amy",[Amt],0) AS SalesAmy, and
IIf([Emp]="Ben",[Amt],0) AS SalesBen, and remove
[SalesAmy]+[SalesBen] AS Subtotal

I will get the following view:
OrderID | OrderDate | SalesAmy | SalesBen |
----------------------------------------------------------
Order01 | 01/01/2007 | 30.00 | 0.00 |
Order01 | 01/01/2007 | 0.00 | 45.00 |

I will have duplicate OrderID listed on the form.

Those pop-up parameters are aliases for the expressions used:
IIf([Emp]="Amy",[Amt],0) AS SalesAmy, and
IIf([Emp]="Ben",[Amt],0) AS SalesBen

which are derived from the fields "Emp" and "Amt". If Emp = Amy, then the
column will show the Amt value. If not, it will be shown as 0.00. This way, I
can split 1 field into 2 or more based on the field "Emp".

Adding the Sum function for the iif will help me achieve the view which I
wanted as shown above.

 

[storrboy]

I will have duplicate OrderID listed on the form.

I forgot it would do that. Sorry.
As for the parameters, I have a feeling they have to do with grouping.
Since you have no criteria for the OrderID or Date, try making acopy
of the query and remove those two fields so that there is no grouping.
If the parameter requests go away, then you may need to restructure
this so that there is a subquery, one to do the math, one to do the
groupings.