Relationship problem

[Andy Roberts]

Hi all

This one is driving me crazy - (especially as I'm sure its simple!)

I am creating a DB which will work like Northwind. The problem I have is
that my products table is causing me some issues. Unlike Northwind my
products have serial numbers

So I have many products the same but with different serial numbers eg

Product1 0001
Product1 0002
Product1 0003
Product2 0099
Product2 0098 etc

I have a products table which lists Product 1, Product 2 etc and then a
serial number table with all my serial numbers in (Is this correct?) When I
assign a product to an order then I want to see the product name and the
product serail number (ie the combination of the two tables). It gets a bit
complicated as I want to make the serial number field in the serial number
table unique - However I have products which dont have a serial number (eg
cables) - They are listed as one entry with a stock count.

BUT I want to also see these in a list when I assign a product to an order.
If you look at Northwind then I would need to have duplicated data in the
products table (ie Prodcut 1 would appear many times and would just have a
different serial number for each record - this is obviously data duplication
and I dont want that.

Also I want to not see a product/serial number combination if already
assigned to an order - how is this achieved. At the moment there are no
forms as I am trying to get the table data correct.

I apologise if this is explained badly!

Thanks

Andy Roberts

Not sure if I'm explaining this correctly!

 

[Allen Browne]

You will need more tables here.

The product table should contain no quantities or serial numbers.

You need another table for acquisitions (when you buy in stock), and another
for invoices (when you sell them).

These will have associated Detail tables (like Order Details in Northwind),
that will have fields such as:
- InvoiceDetailID AutoNumber p.k.
- InvoiceID foreign key to tblInvoice.InvoiceID
- ProductID foreign key to tblProduct.ProductID
- SerialNum Text the serial number.
- Quantity Number (Long) the number received/sold.

You can create a Validation Rule on the table:
(SerialNum Is Null) OR (Quantity = 1)
This ensures that anything sold by serial number is a quantity of 1.

If you wish, you can create a dual-field index on ProductID + SerialNum that
is Unique, but IgnoreNulls. That could be a problem though, e.g. if
something is returned and resold. It may be better to run these kinds of
checks in Form_BeforeUpdate (the BeforeUpdate event procedure of your form),
so the user can override the warning and proceed anyway. Use a DLookup() in
Form_BeforeUpdate to see if the combination has already been sold.

 

[Arvin Meyer]

You will need to change some of your conditions to make this work. See
comments in-line:

I am creating a DB which will work like Northwind. The problem I have is
that my products table is causing me some issues. Unlike Northwind my
products have serial numbers

So I have many products the same but with different serial numbers eg

Product1 0001
Product1 0002
Product1 0003
Product2 0099
Product2 0098 etc

I have a products table which lists Product 1, Product 2 etc and then a
serial number table with all my serial numbers in (Is this correct?) When I
assign a product to an order then I want to see the product name and the
product serail number (ie the combination of the two tables). It gets a bit
complicated as I want to make the serial number field in the serial number
table unique - However I have products which dont have a serial number (eg
cables) - They are listed as one entry with a stock count.

No banana! You can have only 1 NULL value as a serial number if you want to
make it a Unique index and none as a Primary Key. That's the rules, you'll
need to change or abandon using the serial number as a unique value.

BUT I want to also see these in a list when I assign a product to an order.
If you look at Northwind then I would need to have duplicated data in the
products table (ie Prodcut 1 would appear many times and would just have a
different serial number for each record - this is obviously data duplication
and I dont want that.

It isn't data duplication if you include the serial number. The rows are
different. If you simply want to show Product 1, leave the serial number out
of the query.

Also I want to not see a product/serial number combination if already
assigned to an order - how is this achieved. At the moment there are no
forms as I am trying to get the table data correct.

No problem. Create a query that includes the OrderID and use the following
condition in the OrderID Criteria row:

Is Null

Now, you will only get unassigned products.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access