automatic field completion

[fergcu]

Ok, i'm very new to access, so please be patient!

Right, disclaimer over, hopefully someone can help me with this.

Essentially, I have a table of employees who have something called an OUC - this is a group identifier which allows us to recognise certain info about them. The first three digits relate to the line of business (LOB) that the employee is in.

ie. John Doe - JPGA2X; Joe Bloggs - JPGB3Y; Frank McRank - JDH3B;

John Doe and Joe Bloggs both are in the IT LOB (as defined by JPG), and Frank is in Finance (as defined by JDH).

I currently put the OUC in the employee table, and have got a translation table for OUC->LOB (ie the left three digits of ouc's, and how they equate to the correct LOB). What i can't work out how to do is get the two tables to interact. How do i get the employee table to automatically work out which LOB the employee is linked to?

I hope this is clear, and that someone is able to work out why i am being such an idiot about this! (it's probably basic principles of Access that i've skipped over in my haste!!).

Thanks in advance,

Ferg.

 

[Dreamweaver]

You could use DLookup("LOB","YourTable",Left$([LOB],3)='" & Me![OUC] & "'")

Thats of the top of my head so don't blame me It's late here LOL

Just but that in an extra field on the form you add the OUC too.

As An afterthought you could do it with a combo box if what your after is to be seen in the same field you add the OUC too.

Mick

 

[fergcu]

Thanks Dreamweaver - will have a look into that, but i have a feeling that it won't quite do what i want - because although it will display the correct lob on the form, it wont actually save that info in a table will it? My ideal would be for the employee table to have the LOB automatically filled in - but from what i understand, you cant do calculations/expressions in table view can you?

The reason i want to do this is because we may want to do queries/reports which will perhaps show the number of people in 'Health' that have a first name beginning with F or some such thing - by doing the calculation in the form, i can't find a way to display that info in a query.

Any thoughts?

Ferg

 

[neileg]

A few thoughts:
1) If LOB has two segments with meaning, it should be stored as two fields. You can concatenate them any time for display purposes.
2) Tables don't interact with tables (unless you use a table level lookup which is a nightmare, so don't!). Interraction occurs through a query that joins the tables based on one or more linking fields.
3) You don't enter or view data in a table, you use a form. The way that data is stored in a table is irrelevant to the user, it's under the designer's control and depends on what produces the most efficient design. The form makes it user friendly.
4) In general, you don't store duplicate data. The only time this might be important is where the standard data changes with time, and you need to store a snapshot of the data at the time of creating a record. An example of this might be a price in an invoice record. Subsequent price changes should not alter the historic data in the invoice.