Not a lookup list (I think)

[sellis]

Here I go again. I swear I need a class but it needs to be a moderate to advanced one.

I am working with a tables that have voluntary life codes A=20K, B=40K, C=60K, and so on. One table has the Employee information and says the A, B, C part. Another table shows that the A means 20K, etc. I am trying to run a queary of some sort to translate these two fields and I have lost my mind. I do not want my report to say A, B, C... I want it to say 20K, 40K, 60K because then I have to do further calculations upon those numbers. Am I just being stupid here or is there some great formula I am missing. Thanks

 

[boblarson]

Here I go again. I swear I need a class but it needs to be a moderate to advanced one.

I am working with a tables that have voluntary life codes A=20K, B=40K, C=60K, and so on. One table has the Employee information and says the A, B, C part. Another table shows that the A means 20K, etc. I am trying to run a queary of some sort to translate these two fields and I have lost my mind. I do not want my report to say A, B, C... I want it to say 20K, 40K, 60K because then I have to do further calculations upon those numbers. Am I just being stupid here or is there some great formula I am missing. Thanks

You need to include both tables in the report's record source and then you would link them on the field that shows the letter and then you would use the number value from the second table instead of from the employee information table.

 

[sellis]

Thanks - I knew I was being stupid. This seems to be causing an additional probem though. I have three fields that I am doing this with, and once I complete the entries for all three fields, then the entries that do not have all three fields are going away. I have tried all of the variations of the Not Null Is Null Not "" - everything the Microsoft website says to use, but I am still only get the columns that contain all three items. Any suggestion there.

 

[boblarson]

You need to use an outer join for the tables that might not have data for a particular value. So, what tables are you using in this query? If only two then you would probably use the one that has the codes and for the link that links the code fields together you would double-click on it and it would bring up 3 options (the first one will have already been selected) and you change it to one of the other two - whichever one says something like "Select ALL Records from CodeTableNameHere and only those records which match in TheOtherTableNameHere."

 

[sellis]

I swear I am going to send you my first born. You are the man. THANK YOU THANK YOU THANK YOU! That was so easy to fix, but not something I would easily have known. I have so many little quirks like that in my huge mass of stuff to do. Thanks soooooo much!!!!

 

[boblarson]

Glad we could help

I swear I am going to send you my first born.

Noooooooooooooo!!!!!!!!!!!!!! I have 3 already and that is quite enough thanks.

Glad to be able to help.