Seems to be simple, just can't find the answer - Dual Combobox selection

[CIC]

I don't know if I'm completely dense or if it is the flu that is causing my cognitive abilities to go right out the window. In my mind, this is the simplest thing, but maybe I'm going about it all wrong.

I am creating a worksheet form, that pulls data from multiple lookup tables, then some simple calucations will occur.

I am starting with (what I thought) would be the simplest, which is using a table called PayScale.

In Payscale, each record contains:

Position1 (txt)
Rate1 (number)
Rate2 (number)
Rate3 (number)
..
Rate10 (number)
i.e.

Position1     85.70     87.78     91.24     93.74
Position2     92.78     94.89     96.66     98.88    101.12      104.12     108.90     111.11

**There are 22 positions (therefore 22 records), with all positions having between 4 and 10 different pay rates for EACH position (all rates are in each record and they are different for each record)

So, on my WorkSheet Form, I have started off with a combobox to select the Position, then a separate combobox to select the Rate# in hopes to populate a txtbox with the rate from the table. Additionally, I need to be able to have them enter into another txtbox, the number of days that the Position will be paid for, to generate a total in a THIRD txt Box.

So visually, I would like it to be:

Combobox ComboBox TxtBox TxtBox TxtBox
Position1   Rate3  85.74     3     257.22

I have many more sections to this Worksheet to work out, but my hope is that once I have this basis figured out, I can apply it to the remaining sections.

I have not done ANY "programming" in Access is years and so I may need direction on where to place code that may be required.

As always, any help is appreciated!

 

[jzwp22]

Position1 (txt)
Rate1 (number)
Rate2 (number)
Rate3 (number)

If the above is your table structure, it is wrong. It sounds like you are approaching Access as if it were a spreadsheet which it is not.

The table structure in Access must follow the rules of normalization. (see this site for more on normalizaton).

If each position can have many rates, that is a one-to-many relationship which is handled with two tables:

tblPositions
-pkPositionID primary key, autonumber
-txtPositionName


tblRates
-pkRateID primary key, autonumber
-fkPositionID foreign key to tblPositions
-currRate

So for 1 position in tblPositions, you will have your 4 to 10 rates as RECORDS in tblRates

A question for you. Can a rate apply to more than 1 position? If so, that is a different structure. If a rate can apply to many positions that is another one-to-many relationship. If you have 2 one-to-many relationships between the same two entities (positions and rates in your case), you have a many-to-many relationship which is handled as follows:

tblPositions
-pkPositionID primary key, autonumber
-txtPositionName


tblRates
-pkRateID primary key, autonumber
-currRate


tblPositionRates
-pkPositionRateID primary key autonumber
-fkPositionID foreign key to tblPositions
-fkRateID foreign key to tblRates

The tblPositionRates is a junction table which is how many-to-many relationships are handled.

 

[CIC]

Thank you for the response! You've taken a lot of time to detail what is wrong and after reading more onormailzation (I found the Wikipedia version very hard to follow, but found a better one on About.com) I believe I understand.

However, your response has raised a question with regard to the second table.

If each position can have many rates, that is a one-to-many relationship which is handled with two tables:

tblPositions
-pkPositionID primary key, autonumber
-txtPositionName

tblRates
-pkRateID primary key, autonumber
-fkPositionID foreign key to tblPositions
-currRate

So for 1 position in tblPositions, you will have your 4 to 10 rates as RECORDS in tblRates

My issue is that each position has unique rates associated and I need to be able to select the rate for each person in the worksheet.

So, in my tblRates, would the series of 10 different rates associated to Position1 become one record to include all ten rates? I'm really having a hard time wrapping my head around this, as I assumed that the position and it's related (possible) rates would be all part of one table, but I willing to do whatever it takes to make this work.

Please let me know if I am understanding this correctly, in that the tblRates will contain 14 records (one for each position) and each of those records will have the unique set of pay rates that can apply to each position.

I'm really very sorry for being so thick (I am a hardware technician who has not programmed for many years).

Thanks for all your help!

 

[jzwp22]

...I need to be able to select the rate for each person in the worksheet.

I would recommend getting your table structure set up properly first because any form (i.e. your worksheet) you have now will not work once the tables are set up properly.

Setting the tables up properly is more important than the forms. Forms should be designed around the proper table structure not the other way around.

Since you said that the rates are unique, than that corresponds to the first structure I proposed:

tblPositions
-pkPositionID primary key, autonumber
-txtPositionName


tblRates
-pkRateID primary key, autonumber
-fkPositionID foreign key to tblPositions
-currRate

Now, you said that you have to select a rate for a person. Is the position, also related to the person? Can a person have different rates over time?