By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,148 Members | 759 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,148 IT Pros & Developers. It's quick & easy.

Inventory Form

P: n/a
DS
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
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
gj
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
Nov 13 '05 #2

P: n/a
Try a Union Query

"DS" <bo******@optonline.net> wrote in message
news:dp**********************@news4.srv.hcvlny.cv. net...
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

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.