DS wrote:
Hi,
I'm building an Inventory form. I have a Product Form and ono that form
I want to place a sub-form showing the sold products along with the
Products that were received. I have a calculated field to show me what
my current inventory is. However, I want to only use one Sub-Form as
oppossed to two. When I try to set up a query using the Products table
in the center and Orders on one side and Sales on another Access can't
return results with the two tables. If i remove either the Orders or the
Sales the Query works. Are there any samples of a situation like this
out there? Thanks
DS
You need to use a Union query. Note you must have the same number of
fields in each query.
- The first query determines field names, etc so the first field I have
called ACTION can be referred to by the OrderBy clause. Additionall
sorting on the filed SOLD_DATE will sort for both the first and second
queries as the results for the RECEIVED query are appended to the bottom
of the first query.
- You can also add a WHERE clause with each query to maybe limit by date.
- The Orderby clause will sort the results from the two queries by Date
and then ction so for sales and received on the same day Received will
show first.
SELECT "SOLD" AS "ACTION,PRODUCT_ID, SOLD_DATE,SOLD_QUANT FROM TBL_SOLD
WHERE SOLD_DATE > #1 MAY 2000#
UNION
SELECT "RECEIVED",PRODUCT_ID, RECEIVED_DATE,RECEIVED_QUANT FROM TBL_SOLD
WHERE RECEIVED_DATE > #1 MAY 2000#
ORDER BY SOLD_DATE, ACTION
Gavin
