Design for Multiple Product Codes

[Bill Gates]

Hi

I am Creating a Database ( Inventory Management ) And the Products I
have Multiple unique Codes (this is Industry Standard). How do I use
these codes in other tables like Invoices , Purchase Order , Credit
Notes etc.

For Examples A Product say Item 1 is having 3 unique Codes (say 001,
201 and AB01) how do I get the name and other details of Item 1 in any
Document I want.

I'm Established a One to Many Relationship between Items Table and
Codes Table. So far so good. But how do I relate other tables ... say
Invoices table so that when I put any of the code numbers (say 001,
201 and AB01) I get the details of the Item (name, Rate etc.)

I would be grateful to you all if you can suggest some sites where
some free Data Models are available for learning (like
www.databseanswers.com or some good sites where I can find some free
Access Templates (I tried Microsoft's Site but its very basic)

Thanks to you all.
Udi

 

[Duane Hookom]

It would really help if you entered a few records into your posting so we
knew if you wanted the codes list across or down or comma delimited or
whatever. There is some code that would concatenate them at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

 

[udi]

Hi Duane

Thanks for your immediate response.

My Project is still at the Data Modelling Stage and I'm thinking of
how to create Relations amonf various elements.

Well The case can be presented as follows

I have a product say a Book and it is having Multiple Codes
like ISBN and since most of the books in this place donot have ISBN we
have multiple codes like

1) Industry Specific Code like ISBN etc. ex.81-7366-1073
2) Manufacturer's own Code printed on the Product (product's which
donot have Industry Standard Code) ex. 1236
3) Our Bar Code with none of the above 1234567890

The Product we have can have any one or all the above codes.

Now What We want is that when preparing an Invoice (or any other
document), When we enter any of the above Codes, in the Product Code
Field the Details of the product should fill up.

Thamks again
Udi

 

[Duane Hookom]

I would create tables like:

tblCodeTypes (one record for each of Mfg, ISBN, BarCode,...)
==============
CodeTypeID autonumber PrimaryKey
CodeType (Text)

tblBookCodes
==============
BookCodeID autonumber PrimaryKey
BookID primary key from your table of books
CodeTypeID link to tblCodeTypes.CodeTypeID
CodeValue text like "81-7366-1073", "1236", ....

You can then worry about your display when you are ready to create reports.

 

[udi]

Hi Duane

I have almost the same table structure ... but how do I design the
Invoices table ?? and how do I design the form so that any code (like
ISBN) gets the required product details??

Thanks
udi

 

[Duane Hookom]

What are you invoicing? If you are invoicing books, I expect you have a
purchase order table with order details of the books. Each book should have
related codes.

--
Duane Hookom
MS Access MVP

Hi Duane

I have almost the same table structure ... but how do I design the
Invoices table ?? and how do I design the form so that any code (like
ISBN) gets the required product details??

Thanks
udi

 

[udi]

The case is not like that

The user can enter any one of the n number of codes in the
Invoice/P.O/any other form

e.g I have a Product say "MS Access Development" with 3 unique codes
(say 0123, 12345678, MSAD001) what I want is when the user enters any
of these one 3 codes in a common Product_Code field the relevant book
details should be fetched from the Prodcuts Table (say Pub. Date,
Rate, Author, Discount etc.)

How Should I design the Invoice (or any other table that uses
products) table and how should I set the relationships.


thanks
udi

 

[Duane Hookom]

Are all the codes (say 0123, 12345678, MSAD001) unique to only "MS Access
Development"?

 

[udi]

Yes.... All the Codes are unique to only "MS Access
Development".

thanks
udi

 

[Duane Hookom]

You can then create a combo box with a row source of something like:
SELECT BookID, CodeValue
FROM tblBookCodes
ORDER by CodeValue
Bind the first column but don't display it.