Mapping of Data From Various Columns

[paulS30berks]

I have an employee with an NI Code of A

NI Contribution thresholds are in the format:

A1a A1b A1c A1d

1000 100 10.00 50.00

However there are many NI Codes and many columns of thresholds for each Code. I am trying to write a query to say:

If NI Code = x then give me values from columns x1a x1b x1c x1d etc

Can anyone help?

Thanks

 

[MStef]

As I can understand you, you have to learn something about
DATABASE NORMALIZATION.

 

[paulS30berks]

Thanks for your reply, however I am trying to look at another way of querying the data rather than breaking down into seperate tables.

 

[The_Doc_Man]

Paul, normalization isn't about breaking down into separate tables all of the time. Sometimes it is about finding your lookup table and adding one MORE column to support a range lookup, but then entering new rows with different values for the new field, and making the lookup include the new field as part of what is looked up.

That came out convoluted, but if you think we are telling you you need multiple new tables, you are wrong. And your problem IS solved through normalization. You are doing a spread-sheet like function when you try to do what you just described.

 

[paulS30berks]

thanks for your reply.

Each Employee has a different threshold (there are 8 thresholds per employee) and there are some 11000 employees. I have tried to place contents into a spreadsheet draw up a pivot table and then placing into a table - just seems alittle long winded.

 

[The_Doc_Man]

paul, "long-winded" is when you have to hard-code every last one of your IF statements for each employee because you DIDN'T break it down so that you could see the pattern and figure out how to choose which threshold.

You have 11,000 employees. Do you have 8 x 11,000 threshold POSSIBILITIES? Or maybe an employee might fall into one of 20-50 possible threshold patterns in which case you could merely store the pattern ID?

Seriously, if you don't normalize THIS kind of data, you will be doing it all by hand-coding in Access. Or anything else, for that matter. I cannot believe for a moment that every employee's NI pattern is absolutely unique. It might be something you must compute, but it cannot be unique.

Paul, this is a case when some up-front analysis to find the pattern is going to save you TONS of work down the road. Seriously, do it now and take the time. (IOW, pay now or pay lots more later.)