automatic field entry based on several fields

[sparky963]

Hi

I am an amature with databases and a bit rusty too. Can anyone help me with this problem?

basically I want to automatically enter a unique asset code for one table that is based on product and manuafacturer's codes from other tables

e.g. for audio visual eqipment

The first table would be a manufacturers table with the manufacturers code as the primary key e.g. JVC, HTC (hitachi), SHP (sharp) etc

The second table would be a product table with the product code as the primary key e.g. AMP, DVD, LCD (lcd tv) etc.

( i would also have a customer table and an asset repair table)

In my asset table I would like to enter an asset code as the primary key of the type JVC-DVD-001, JVC-DVD-002, HTC-LCD-001 etc by looking up codes from the other tables and then adding the last number.
(Ideally once the manufacturer's code has been selected only the product codes for products made by that manufacturer would be displayed).
(Also it would be ideal if referential integrity could be set up so that the asset code components can only contain valid manufacturer and product keys.)

The asset code cannot be a compound key as I want to have a repair records table in which the asset code is entered as a single field with referential integrity to the asset field in the asset table.

When entering repairs I would like the lookup field to display only the assets belonging to a particular customer.

 

[edtab]

One approach you can take:

- built a manufacturer table containing at least two fields namely

 

[sparky963]

Only one cryptic reply eh?
ah well better get the text books out then!!

 

[gemma-the-husky]

my idea - dont do it in quite the way you are trying ....

in the product table have a column that identifies the manufacturer, and another that identifies the product type

so you get

(i am showing manf and product but these are actually lookups)

prod code manf Prodtype
ST715A Sony DVD
ST715B Sony DVD
ST716A Sony DVD
ST715C Sony DVD
ST717A Sony DVD
ST715B Sony DVD
T410X Toshiba DVD
T510X Toshiba DVD
T411X Toshiba DVD
T412X Toshiba DVD

Now, when you have a new asset, pick the asset type from this table - you can have cascading combo boxes to first preselect sony, dvd to limit your selections. Then allocate it to a customer.

if you think about it, the asset code is REALLY not as important as you think it is since you can always idenitfy what sort of asset it is from these lookups - IT REALLY IS sufficient to just give the assets Numeric tags - but if you still feel you need a sequential asset number you can still have one, of course. From thep onit of view of a customer, you can easily find the customers assets, sorted by prod type, or sorted by date of purchase etc - The actual asset code is not critical - it should just be used to link the other bits together.

One or two issues you will get (not related to asset codes)

a) how do you categorise equipment, so you can be sure you can find it again!

dvd player
dvd recorder
dvd recorder + hard disk
dvd player/vhs combi {particularly tricky}

are these all different, or all the same category
etc - you might need to provide a more general random model number search

b) secondly, assuming you dont prepopulate the dbs with every asset model number, you need to be able to add new products to the datbase, if a customer brings in an item you havent seen before


hope this helps

 

[sparky963]

Thanks Gemma

some good ideas I can make use of