combine different product tiers into single drop-down list?

[dbnDavid]

I'm constructing a database for a journal and I want to set up an orders form
where orders can be placed either for an annual volume (two, occasionally
three, issues) or for a single issue. I'd like it to be possible for a volume
order to be entered on the order form as a single catalogue item, not as two
separate items.

I have a Volumes table and a separate (related) Issues table, and I have
created a union query which successfully allows me to display both Volumes
and Issues in a single merged catalogue list. But Issues remain a subset of
Volumes, which is presumably why the union query doesn't work as a data
source for a combo box on the order details form that would permit the user
to select the requested item from a simplified drop-down list of the whole
catalogue.

Any work-around suggestions wld be much appreciated, tho unfortunately my
VBA skills are close to zero.

 

[Jeff Boyce]

Not sure I clearly "see" your situation... Is there a chance you could work
with different SKUs (or something similar) to represent single issue vs.
annual ?subscription?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Klatuu]

What is necessary is to have an inventory system that is strong enough to do
what is commonly known as 'kit" inventory. That is you have a strock number
for each item. An item can be an individual item or a collection (Kit) of
items.
If you inventory system will not currently support this, it is not a simple
rewrite. Just doing it at order entry is not nearly enough. It has to go
through your entire system to work correctly.

 

[dbnDavid]

Thanks Jeff & Klatuu

Both yr answers give me helpful pointers - once I had tracked down what SKU
means!

Taking up your suggestion Klatuu, my actual inventory is extremely simple
and it shouldn't be a big task to rework. I can see now that I have in fact
already been trying to configure it to accommodate "kit" items (useful
concept). But this is where I run into this problem of sets and subsets that
I can't get to run together.

It would be very helpful to study some examples to see a model. The
Northwind sample database doesn't really get down to inventory level. I tried
taking a look at the Inventory Control template in Office, but unlike the
Northwind sample it's just a template, not populated with any data, and with
no actual records to examine it's rather hard to figure out what it really
does.