Moving data from popup to subform

Hi Bill,

I've crossposted this and set followups to
microsoft.public.tables.dbdesign, where it seems to belong.

Presumably you have tables for Customers, Orders (any customer can have
zero or more Orders) and OrderDetails (any order can include one or more
items).

The classic OrderDetails table, like the one in NorthWind, has fields
for
OrderID (foreign key into Orders table)
ProductID (foreign key into a single Products table)
UnitPrice
Quantity (units are stored in the Products table)
Discount
TaxRate
If you have multiple unrelated differently structured tables of
products, one per category, you have to go through horrible contortions
to do what should be simple routine things like printing an invoice. And
of course any time a new product category appears you have to add a new
table and redesign half the user interface and reports. There's a saying
"Don't store data in table names".

There are various ways round the problem:

1: Occam's Razor: do these varying lists of attributes belong in your
problem domain? E.g. motor stators: do you really need to be able to
query the database on any combination of the 15 fields you mention in
order to find the right stator (you haven't mentioned this as a
consideration in your interface design), or do you just need to be able
to display this information so the user can confirm that the right part
is being selected? If the latter, you don't need 15 fields unique to
stators, just one field called "Specification" or "Description".

2: Subclassing (though this doesn't avoid the problem of having to
revise forms, reports and code every time a new category is invented).

3: Normalisation: Instead of having a different set of fields for each
product category, store each piece of information about each product as
a record in a related table. E.g.:

A single Products table like this:
ProductID (PK)
CategoryID (FK into Categories table)
ManufacturerID (FK into Manufacturers table)
MFRPartNumber
PartName
PartDescription
UnitOfMeasure
UnitsPerPack
UnitPrice
TaxCategory

along with an Attributes table, e.g.
AttributeName )
CategoryID ) (PK)
UnitOfMeasure
Comment

and a ProductsAttributes table
ProductID }
AttributeName } PK
CategoryID }
Value




On Wed, 5 May 2004 13:07:22 -0700, "Bill"
wrote:

Hi John,

Indeed, the ideal would be only one products table and
this is the case for most vendors. However, my products
happen to be industrial electrical components. Each
category of products has very unique fields to properly
identify and describe them. For example Motor Staters have
about 15 fields describing various options for an
individual unit. Disconnects, Combination Starter Boxes,
Circuit Breakers, Bus Plugs and Bus Bars, etc. all have
their unique fields and it seems contrary to normalization
to create an overall products table with enough fields to
describe all the options for all the products. If that
were the case there would be 100 or more fields in the
table, most of which unused.

I have the data containers designed and much of the data
entered. The vision I described is based on data and
arrangment of current tables/queries. I admit the
arrangmemt needs some work (especially concerning how to
sort through a list of product to include in an orders
table).

So, can you suggest a method to select records from the
various products tables to include in an order form while
viewing both?

Thanks for your consideration.

Bill
-----Original Message-----
Hi Bill,

One very basic principle in designing database
applications is that data
structure and user interface are two quite distinct
things. The data
structure should reflect the real-world entities you are
modelling, not
the user interface you envisage. If you get the data
structure right,
there's plenty of flexibility in interface design - but
if you start
with the interface and then create tables that match your
forms you'll
have a much harder time.

In an orders database you'd typically have one table for
Products. For
Categories, there'd be a related table: there'd be a
Category or
CategorhyID field in the Product table. Take a close look
at the
Northwind sample database that comes with Access: it has
a typical
structure of products, categories, suppliers, customers,
orders and
order details.

On Tue, 4 May 2004 12:15:03 -0700, "Bill"
wrote:

I've created several popup continuous forms. The
underlying table has no relationship with any other
tables. They represent unique items for sale in a
particular category. I have other tables like it of
other
categories, again with each item (record) unique.

A form that contains customer information has a sub form
for orders. They are linked by a customer ID. I intend
to
place buttons on the form to popup the categories forms
as
I need them. I want to select record(s) from the
categories popups and create a new record with this data
on the orders sub form. Thus, when I finish manipulating
the forms the orders table will contain all the data I
selected from the popups - like "Items in a shopping
cart".

I am envisioning the records in a category being items
purchased, category tables (forms) being "shopping
aisles", the orders table (form) being the "shopping
cart"
and the main form being the "store"

I am having trouble with the code syntax in getting the
data from the categories record to the orders table
(form). The sub form and pop ups complicates things

Can you help with the syntax?
Is this an efficient (logical) way to accomplish this
task?

*Yes, I know William of Ockham was writing about entities, not
attributesg.


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.