union query with form critera

[toddlisa2]

I have a report that displays totals from a union quey. I want to have
two where clauses: a date range and a status (sold, inventory, or
both). Here is where I am at:
1) Open Report kicks off Open Form (Criteria )
2) Criteria Form colects date range and status (group box radio button)
3) When you press Preview the form visible=false and the query that the
report uses takes the info from the form: [Form]!...
The problem I have is the WHERE for the staus will differ by sold_price
(>)(>=)(=) 0.
I can't set this in the report filter becuase the end result does not
contain sold_price.

Here is my union query (The AND will change to > for sold, >= for all
and = for inventory:

SELECT book_inventory.purchase_location AS Location, "Book" AS Type,
Count(*) AS Total,Sum(book_inventory.sold_price) AS Profit
FROM book_inventory
WHERE (((book_inventory.purchase_Date)>=[forms]![Date
Range]![BeginDate] And (book_inventory.purchase_Date)<=[forms]![Date
Range]![EndDate]))
AND ((book_inventory.sold_price)>=0)
GROUP BY book_inventory.purchase_location

UNION ALL
SELECT CD_inventory.purchase_location, "CD",
Count(*),Sum(cd_inventory.sold_price)
FROM CD_inventory
WHERE (((CD_inventory.purchase_Date)>=[forms]![Date
Range]![BeginDate] And (CD_inventory.purchase_Date)<=[forms]![Date
Range]![EndDate]))
AND ((cd_inventory.sold_price)>=0)
GROUP BY CD_inventory.purchase_location

UNION ALL SELECT movie_inventory.purchase_location, "Movie",
COUNT(*),Sum(movie_inventory.sold_price)
FROM movie_inventory
WHERE (((movie_inventory.purchase_Date)>=[forms]![Date
Range]![BeginDate] And (movie_inventory.purchase_Date)<=[forms]![Date
Range]![EndDate]))
AND ((movie_inventory.sold_price)>=0)
GROUP BY movie_inventory.purchase_location;

Is the only way to do this with three reports and three buttons on the
form based on what you want or is there a better way. I hope that
makes sense.

 

[Steve Schapel]

Toddlisa,

Assuming I correctly understand what you want (I am not 100% sure), I
think it is something like this...

WHERE (((book_inventory.purchase_Date) Between [Forms]![Date
Range]![BeginDate] And [Forms]![Date Range]![EndDate]) AND
(((book_inventory.sold_price)>0 And [Forms]!Date Range]![Status]=1) Or
((book_inventory.sold_price)=0 And [Forms]!Date Range]![Status]=2) Or
([Forms]!Date Range]![Status]=3)))

Having said this, it would appear that in may be an unwise idea to have
3 separate tables book_inventory, CD_inventory, and movie_inventory.
Most likely it would be better for all of this data to be in one table,
with an additional field to desxignate Type (book, CD, movie). Are you
able to consider a change to your table design?

 

[toddlisa2]

No beacue there is different data for each type, ie Book has ISBN,
movie has a DVD/VHS, etc. There is no status type, I determine the
status by the sold price. Inventory has sold_price = 0, Sold > 0, and
All has sold_price >= 0 or no where clause. A status column will not
work for this since an item can be both Sold and All or Inventory and
All.

Basically the WHERE part that I need to change will need to be one of
the three:
book_inventory.sold_price)>=0) ALL
book_inventory.sold_price)=0) Inventory
book_inventory.sold_price)>0) Sold

So the variable that will change is not the price but the equality
sign.

 

[Steve Schapel]

Toddlisa,

No beacue there is different data for each type, ie Book has ISBN,
movie has a DVD/VHS, etc.

This is not a good reason to use separate tables. Absolutely. If there
are a lot of fields which are specific to each type, then you should use
a sub-typing design, with the common fields in a single table, and
linked type tables.

There is no status type, I determine the

status by the sold price. Inventory has sold_price = 0, Sold > 0, and
All has sold_price >= 0 or no where clause. A status column will not
work for this since an item can be both Sold and All or Inventory and
All.

Yes, I know this. My example used 'Status' to refer to the name of the
Option Group that you mentioned that you use to select which group
(sold/inventory/both) you want to include in your report.

Basically the WHERE part that I need to change will need to be one of
the three:
book_inventory.sold_price)>=0) ALL
book_inventory.sold_price)=0) Inventory
book_inventory.sold_price)>0) Sold

So the variable that will change is not the price but the equality
sign.

I believe I understand the requirement. Did you try the Where clause I
suggested? If it doesn't work as required, what is the problem?

 

[toddlisa2]

I merged all the data into 1 table. I misunderstood the status part of
your reply; I thought you meant add a status column in the table. Yes
that worked wonderful. Also merging all the data into one table has
lessened the amounts of queries I need. Thanks so much for you
response.

Happy New Year.