Form from Qry + 3 more fields ?

  • Thread starter Thread starter Mercy
  • Start date Start date
M

Mercy

I have a question.

This was posted in "Queries" . but I didn't receive a
reply there yet. so I'm posting it here ^_^

I have a Form "OrderForm" which has a subform based on
the "Summary." "Summary" is:

SELECT [ManufacturerName], Details.Vendor, [OrderNum],
[Product], Details.ProductId, [Unit], Sum
(Details.Quantity) AS totalQuantity, [Cost]
FROM Details
GROUP BY [ManufacturerName], Details.Vendor,
[OrderNum],
[Product], Details.ProductId, [Unit], [Cost];

The thing is ... I need to add 3 more pieces of
information to each record in the subform. These 3
pieces of info are: "ActualQtyOrdered", "ActualUnit",
and "TotalCost"

The Information in the Subform "Summary" is from a Qry
which pulls it's information from a UNION Qry "Details".
Details pulls it's information from a tables Fabric &
Trim.
Here's a brief explanation of a few fields in "Summary"
Product --> Product to be Ordered
Unit --> Unit that totalQuantity is calculated in
totalQuantity --> The MINIMUM amount of the Product
Required
Cost --> The official Cost for an Official unit of this
product.

The fields that I want to add:

"ActualQtyOrdered": This is a new piece of information
that the USER is supposed to enter that

"ActualUnit": This is the "Official" Unit with which to
Order the Product. This is a value pulled from a Product
Table

"TotalCost": This is Calculated field that will multiply
Cost & ActualQtyOrdered for each record in Summary.

To put it simply I want a table/subform that has all the
info
of "Summary" plus 3 more fields. Is there any way to
do this?

Thanks for any input!
-Mercy
 
"ActualQtyOrdered": This is a new piece of information
that the USER is supposed to enter that

"ActualUnit": This is the "Official" Unit with which to
Order the Product. This is a value pulled from a Product
Table

"TotalCost": This is Calculated field that will multiply
Cost & ActualQtyOrdered for each record in Summary.
Click to expand...

What Table should these fields be stored in?

Note that neither a UNION query nor a TOTALS query can ever be
updateable; your Subform - as currently structured - simply won't
allow you to edit records in its recordsource, so adding fields to one
of the tables will not work.

I'd reconsider your table structure. It sounds like you need the
*information* in the UNION/Totals query to be available, but that your
subform should be based on an OrderDetails table!
 
Back
Top