Filling a table from a checklist

[mr.wizzard]

Hi. I have little experience with access, I used it las in high school... Anyway, what I need to do is a simple management software for a car repair shop. I have a list in an excel sheet with all the possible tasks (labor) and parts that can be replaced on a particular car. For example, there's a row for "headlamp alignment", "oil change", etc.

I have already created an entity with vehicle information, and I now need to create a Repair History entity, where the mechanic can tick (check) from the labor list every required job for a particular license plate. I would like that the elements checked be copied to that particular vehicle's Repair History. I want the labor list to remain unmodified, and serve as a general list for all cars. I hope I have explained myself correctly. Any help highly appreciated.



Regards;
Juan Castro.

[NTC]

Microsoft Access gives you this ability in the 'LookUp' field type.

So your Labor List remains as a stand alone table.

Your Repair History table is going to have a field called maybe 'LaborPerformed' - - and when you set up this field in the table you want to define it as a LookUp field ; as you do this the wizard is going to prompt you as to what table do you want to look up and you will select your Labor List table.

[mr.wizzard]

Wow, thanks NTP! I managed to have access deploy a drop-down that lists the ID and Description of every available labor/parts and allows the user to select via checkboxes which are required. The IDs are stored on the car's table, under the LaborPerformed column, as a comma-separated list. As Borat would put it: "great success".

Thanks again!
Juan Castro
Cali, Colombia.

[NTC]

Let me add a note on LookUp fields for other novices that may read this string: It is a good feature provided by Microsoft, but at the same time frowned upon by serious DB designers.

And there are situations where it can make you very confused. The key to all this is that what is 'bound' and what you see displayed may not be the same.

To be safe - - a LookUp of a single column table works great and isn't confusing. Such as the common 2 letter State initials....

But when you tell it to lookup a multicolumn table - then it wants to bind just one of those fields - - and it will typically default to the first column.

So let's say you set up that traditional 2 letter State initials and enter all 50....but that you set up this table with the autonumber field as the first field..... Over in the customer address table - when you set the State field to be a lookup - it is going to set it up with that autonumber field being bound.....so although you might see AK or MN or whatever - - what is really being stored is 1 or 20 or whatever; and this can totally be confusing when it comes to sorts and other stuff....

So just be aware of this - - - and in general if it is a single column - - then the bound info and displayed info is always the same....so there is no confusion....