Basic question re table structure (1 Viewer)

[okanagan]

I need a table to store pay rates and how they change with time. Let's say there are two classes of workers (class A, class B) and each class has two levels (level 1, level2). The rates change periodically and I want to retain the historical rates. If I were doing this in Excel I would use a sheet like this:

etc.
Would my Access table look the same? I ask because I understand one wants to avoid having duplicate items in a database. Is there a way to avoid having the repeated dates?
Thanks in advance!

 

[arnelgp]

repeating date on your case is but normal.
it shows the Effective date the rate must apply.

 

[onur_can]

Since each ratio is different, it would be pointless to deduct the repeated dates. Then it will be incomplete in your query result. You can adapt the same information you have provided in your excel example above to the access table.

 

[gemma-the-husky]

You have no duplicate items.
You need an unique index or PK, based on the first 3 columns.

then the active rate is the rate for the record with the greatest date, which is both less than (or maybe less than or equal to) today's date, but greater than (or more likely greater than or equal to) the pay date, with the appropriate settings for "class" and "level"

(I thought I had posted this some time ago, but I realised I hadn't.)

 

[The_Doc_Man]

There is nothing wrong with using exactly what you have as a table as long as you recognize that the unique key is a date, a class, and a level as a triad. That is, the date, class, and level are INDEPENDENT values and the rate is the DEPENDENT value. And what you suggest doing is quite common. I might add one caveat. If there will be no table that depends on this table, then what you have is fine. IF AND ONLY IF there would be a child table of this proposed table, I would add an autonumber as the prime key, strictly for convenience of handling.

However, if you DO make a table like this, be advised that "Date" is a reserved word in Access so pick another name for that field. As it happens, so is "level." Neither Class nor Rate are reserved, though to be honest, I'm rather surprised that "Class" ISN'T a reserved word.

 

[Rene vK]

The PK could be to combine class & level. The Rate and Date are the fields that change over the years.

 

[The_Doc_Man]

@Rene vK - As described by the OP, the date is an independent variable that must be part of the prime key or at least part of a 3-way index.

 

[Rene vK]

@Rene vK - As described by the OP, the date is an independent variable that must be part of the prime key or at least part of a 3-way index.

sorry

 

[Pat Hartman]

Use an autonumber as the PK for the table and use that for any relationships. Then create a compound unique index of the three fields that define uniqueness. You must use the Index dialog to create a multi-field index.