Standard value in form based on several tables

[BartBE]

Hello,

Attached to this post, you will find (the relevant part of) my table structure. It's in Dutch, so I will try to explain what the relevant fields are about.

• The table BAREMAS contains all the wages we pay. The wage an employee gets depends on three variables: indexatie, barema and anciënniteit. (EDIT: Since I took the attached screenshot, I have modified the baremas table to have a singular autonumber primary key.)
• BAREMAS.INDEXATIE is a datetime field. It indicates when our wages were adapted to price changes. The literal translation would be indexation, but I don't suppose that would ring any bells, as this system is Belgian.
• BAREMA = salary scale. Text field with only 8 possible values, for example B112.
• ANCIËNNITEIT = seniority. Years range from 0 to 25.
• LOON = wage, which is of course a number.
• FUNCTIE = job title
• RIJKSREGISTERNUMMER functions as employee ID, a unique value to identify employees.

I have a form based on the table WN_Persoonsgegevens. This table has one record per rijksregisternummer. (The form shows all information for one rijksregisternummer at a time.) A subform is based on WN_TEWERKSTELLINGEN (Employments).

Now, on that subform I want the field "brutomaandloon" (wage) to have BAREMAS.LOON as standardvalue where:
- Indexatie is the most recent
- Baremas.Anciënniteit = WN_Tewerkstellingen.Anciënniteit
- Baremas.Barema = Functies.Barema

One final clarification: the field "brutomaandloon" on the form will control the field that bears the same name in the table WN_TEWERKSTELLINGEN. The wages in WN_TEWERKSTELLINGEN will usually correspond with the wages set in the BAREMAS table, but not always. That is why I have to save wages in two tables, and why brutomaandloon (wage) must be an over-writable standard value in the form.

To further give you an idea of what I want:
I have tried to compose a DLookup function, without success. It was along these lines:

Dlookup("[loon]","baremas",[indexatie] in max([indexatie]) AND [baremas]![anciënniteit]=[wn_tewerkstellingen].[anciënniteit] AND [baremas]![barema]=[functies]![barema])

Thanks in advance,

Bart

 

[vbaInet]

I have a form per employee based on the table WN_Persoonsgegevens. One form per rijksregisternummer. A subform is based on WN_TEWERKSTELLINGEN (Employments).

Before we go any further let's just get the above comment clarified. Do you actually have ONE form per employee? Or do you mean that you have one form that displays ALL employees?

 

[RainLover]

I have a link in my signature to a Folder called Naming Conventions. I suggest you might find it helpful.

It is better to have One only primary key and that should be autonumber.

If you don't fix your Table design now you will run into various problems.

----------------------------------------

Ik heb een link in mijn handtekening naar een map genaamd Naamgevingsconventies. Ik stel voor dat je vinden het misschien nuttig.

Het is beter om een alleen primaire sleutel en dat moet autonummering.

Als je niet repareren van je tabel ontwerp nu loopt u in diverse problemen.

 

[BartBE]

Before we go any further let's just get the above comment clarified. Do you actually have ONE form per employee? Or do you mean that you have one form that displays ALL employees?

One form per employee. The link between the main form (based on WN_Persoonsgegevens) and the subform (based on WN_Tewerkstellingen) is Rijksregisternummer (employee ID). The idea behind that is to see the employment history of every employee.

 

[BartBE]

I have a link in my signature to a Folder called Naming Conventions. I suggest you might find it helpful.

It is better to have One only primary key and that should be autonumber.

If you don't fix your Table design now you will run into various problems.

Thanks for the advice. I fixed it in the table Baremas. It now has a singular primary key: an autonumber field called "ID". To ensure every combination of indexation, scale and seniority are unique (which is why I made that combination of fields the primary key), I have installed an index containing these three fields.

 

[RainLover]

Thanks for the advice. I fixed it in the table Baremas. It now has a singular primary key: an autonumber field called "ID". To ensure every combination of indexation, scale and seniority are unique (which is why I made that combination of fields the primary key), I have installed an index containing these three fields.

Personally I think Composite primary keys are more trouble than what they are worth. I prefer Autonumber as Primary key and then make a Unique Index on other fields where necessary.

ID is not a good name for a primary key. If you had 50 Tables then you would have 50 fields called ID. Rather confusing. If your table is tblClients then the PK would be ClientPK or as some do ClientID.

 

[vbaInet]

One form per employee. The link between the main form (based on WN_Persoonsgegevens) and the subform (based on WN_Tewerkstellingen) is Rijksregisternummer (employee ID). The idea behind that is to see the employment history of every employee.

So if you had 100 employees you would have 100 forms?

 

[BartBE]

So if you had 100 employees you would have 100 forms?

Yes. There are several subforms on it, like the one based on WN_Tewerkstellingen. This way, we have all the information on an employee in one place. It also works great to make individual changes. I have other forms for changes that happen to groups of employees.

 

[vbaInet]

Ok, that's just too cumbersome. If you have 100 forms and you need to propagate code changes across all forms how do you manage that?

The recommended ways is to use one form and you can filter it down to display an employee's record by using one of the parameters in the OpenForm method. See here for an explanation:

http://baldyweb.com/wherecondition.htm

 

[BartBE]

Ok, that's just too cumbersome. If you have 100 forms and you need to propagate code changes across all forms how do you manage that?

The recommended ways is to use one form and you can filter it down to display an employee's record by using one of the parameters in the OpenForm method. See here for an explanation:

Now I understand where your question is coming from I should have put it more clearly. Sorry for the confusion - explaining this in a foreign language does not make it easier for me

Anyway, it is in fact one form. Using the navigation bar at the bottom of it, we browse the records of the table WN_Persoonsgegevens. In that table, one record contains the "core" information of one employee: name, address, etc.
In other words, it is one form that has several "pages": one page per employee.
The form contains several subforms based on other tables, such as WN_Tewerkstellingen. It is in this subform that I want to edit the salary field.

 

[vbaInet]

Right, that's alright then.

Maybe I should take up Dutch

 

[BartBE]

I have edited my OP to avoid this misunderstanding.

So, does anyone have an idea on how I can get the standard value I want?

 

[RainLover]

I think I will leave this one to vbaInet as he appears to understand what you are doing where as I am a bit lost with your structure.

BTW Did you get a chance to look at the link I suggested. If you did then posting an update of your relationships would be in order.

 

[vbaInet]

What is the difference between the tables Baremanummers and Ancienniteiten? (sorry the names don't have the Dutch accents)

 

[BartBE]

What is the difference between the tables Baremanummers and Ancienniteiten? (sorry the names don't have the Dutch accents)

No worries

BAREMANUMMERS contain the numbers we use to identify the salary scales. The scale an employee gets, depends on the job he has. (Hence the field "barema" in the table "functies", which contains all relevant job information.)

ANCIENNITEITEN contains numbers from 0 to 25. These numbers stand for seniority years.

Barema (scale), anciënniteit (seniority) and indexatie (indexation) determine the salary.

Both tables' (BAREMANUMMERS and ANCIENNITEITEN) sole purpose is to establish many-to-many relationships between other tables. (See table structure.)

EDIT: I posted a translated table structure in my next post.

 

[BartBE]

I think I will leave this one to vbaInet as he appears to understand what you are doing where as I am a bit lost with your structure.

BTW Did you get a chance to look at the link I suggested. If you did then posting an update of your relationships would be in order.

I read it and followed your advice up to the point of the naming prefixes. The reason for that is simple: my knowledge of VBA is very limited, which means I won't be using it

I understand it must be difficult to understand a structure like that in a foreign language. Which is why I made a translated overview. I should have done this from the start. Any formula you come up with, I'll translate to the proper field/table names.

 

[BartBE]

Bump.

I have a test version of my database if that helps. (Attached)

Do note that not all combinations of barema (scale), anciënniteit (seniority) and indexatie (indexation) have been included in the BAREMAS table.

Can anyone help?

 

[RainLover]

Sorry I can't help. I do only have Access 2003.

I think your relationships are wrong based upon your pic. However as I don't know the data types I can't say for sure.

But linking two primary keys is wrong regardless.

 

[vbaInet]

Indexatie is not sufficient as a field to dictate which Loon is the most recent. Should we be using the ID column instead? Or should it be a combination of the Indexatie and Ancienetteit? What I mean is, if you take Barema "A2" you can see that the there are three records which have a date of 1/8/2012 and one record which is 1/7/2010. You can see that the Indexatie isn't sufficient for indicating the most recent record.

 

[BartBE]

Determining which wage is most recent is not really what I'm trying to do. It is only a part of it. Loon (wage) is determined by anciënniteit, indexatie and barema.

• An employee's function determines the barema. This is a fixed set of wages.
• The employee's years of service (anciënniteit) determine which wage in the set (barema) he gets. The higher the seniority (anciënniteit), the higher the wage.

That should be enough to know an employee's Loon (wage). Graphically (not in Access), you can imagine this as a table with one column for each barema and one row for each anciënniteit. If my function is in barema C121, I get 1500€ as a newcomer, 1525€ after one year of service, etc. Barema C131 has 1600€ at 0 seniority, 1650€ at one year, ... (fictional numbers)

However, we are also required by law to multiply ALL wages (in all baremas) by a certain percentage whenever an indexation occurs. Whenever this occurs, imagine a copy of the existing table (barema / anciënniteit) being made, with updated wages in it.

So to summarize, I need the wage that:

• is in the function's barema,
• corresponds to the proper seniority
• and has been recalculated to the most recent indexation