Best approach to representing single orders and kits

[HowardB]

Hi All,

In my scenario

Single products night be thinks like 2-inch wheel or 1 inch wheel in a choice of colours, front axle, back axle etc that can be ordered as distinct products

A kit is distinct product in its own right consisting of a bundled set of other single product components.

Question 1: I would quite like to have single products and kits use the same ProductID index but I think Single products and kits need to be on separate tables in order that the product components included in a kit can be defined. So what I would like to happen is that if I open a kit form on my database in order to define a new kit that it would somehow look at the index for the single product table and take the next assigned ProductID. I am not sure of the best design approach with regards to tables and relationships needed to enable this so suggestions would be welcome.

Question 2: When associating the single product components of a kit with the kit record it will be necessary to select from the entire list or single product components as all single products are potentially valid kit components. If I were to do this using the tbl_single_products as a lookup table it would be a very long lookup list the user would have to scroll through. Are there any functions or techniques that would allow me to dynamically search a lookup list as the user enters text e.g. The user enters "T" the list pointer goes to entries beginning with "T" the user enters "e" the list pointer goes to lookup list entries beginning in "Te" etc.

Question 3: Is there anyway apart from autonumber to create an automatically incrementing index of your choice for use in tables e.g. PROD001, PROD002 etc

Hope that is enough to elicit some feedback,

Many thanks

HowardB

 

[CJ_London]

I would keep them in the same table - kits can use kits. So structure your tables along the following lines:

tblProducts
ProdID autonumber PK
ProdCode text
ProdName text
ProdType integer - 1=component, 2=kit

tblKitLinks
KitID autonumber PK
ProdID long FK - links to ProdID in tblProducts as Kit 'header' record
ComponentID long - links to ProdID in tblProducts as kit component (or sub kit)

tblPrices
PriceID autonumber PK
ProdID long FK links to ProdID in tblProducts
Price Currency
PriceFrom Date - date price is effective from

Are there any functions or techniques that would allow me to dynamically search a lookup list as the user enters text

Use a combo box - ensure the rowsource is sorted on the field that will be displayed

With regards calculated product codes you should not use an autonumber field if the code is supposed to mean something. Autonumber fields cannot be 'reused' and you have no control over the next number (although it will usually increment by 1, the 'next number' can be reset if the db is compacted or copied to a new backend)

On the basis you want an incremental code ensure it is numeric (PROD001 is text, to make it numeric you need to evaluate the last 3 chars as a number) and use a function like dmax to find the last code, then add one - something like

newnum="PROD" & dmax("Val(right(ProdCode,3))","tblProducts")+1

 

[HowardB]

Hi CJ, Thank you that was helpful I think I have cracked it now,

regards

HowardB