Need help with make-table query - Should be quite a simple solution

[ravness]

Hi All,

I have an employee table (tblEmployees1) which has all employee information including their grades (sGrade).

I want their grades to be converted from Manager, Senior Manager, Director etc. into M, SM, D etc in a new table.

I made a table (example of it shown below) (tblGradeFilter) in order to convert these grades into codes.

ID - sGradeRaw -sGrade
1 - Manager - M
2 - Senior Manager - SM
3 - Director - D
4 -Trainee - Other

I have also used a make-table query to form a relationship between the sGrade in the tblEmployees1 and the sGradeRaw in the above table.

The newly formed table from the query, tblEmployees has all fields from tblEmployees1 except the sGrade from tblGradeFilter

This works fine! But i need it to have some kind of rule which means anything input which is not in the sGradeRaw entries should appear by default as "Other"

Please assist. I think this could be done by the criteria in the make-table query but not sure how.

 

[dkinley]

Hi!

Just going to throw this out there in regards for this being a make table query? This sort of thing can be done from a standard query without the need to make a table. The only reason I can understand to actually make it into another table is if you making snapshots at periodic intervals and you want this table stored but then that would require you to change the name of the table each time.

Anyhow, I propose usage of the Switch function. More details in the Access Help but it might look similar to something like the following in the QBE ...

Grade: Switch ([SGrade]=1, "M", [SGrade]=2, "SM", [SGrade]=3, "D", [SGrade]=4, "Other")

-dK

 

[ravness]

Hi,

The reason this is a make table query is due to the user wanting to be able to check what the "others" are if needed and the original "tblEmployees1" is the table where data is directly imported to and overwritten so we just want to keep the full titles just in case.

Im struggling getting this switch formula to work any ideas poeple

 

[dkinley]

The switch function is the criteria. Could you strip down your db or what-have-you and post it. I might be able to assist better this way.

-dK

 

[ravness]

Ive spotted the error

if you use it like this

Switch ([tblEmployees1]![SGrade]="Manager", "M", [tblEmployees1]![SGrade]="Senior Manager", "SM", [tblEmployees1]![SGrade]="Director", "D", [tblEmployees1]![SGrade]=4, "Other")

The problem is the bit in bold, when sGrade = anything else other the ones listed then it should be something like Null but i dont know how to code it.

 

[ravness]

Hi i have removed lots of stuff and uploaded my database just showing the two employee tables and the grade filter table.

if you could have a go and see what im doing wrong it would be much appreciated.

basically i want anythign typed in other than the grade filter rules to be displayed as "other" rather than have to keep adding it as a rule each time somebody finds a new type of grade!

any help would be much much appreciated!

 

[Rabbie]

You could try

Grade: iif([SGrade]=1, "M", iif([SGrade]=2, "SM",iif( [SGrade]=3, "D","Other")))

 

[ravness]

ive already tried the IIF function.

the code would be as follows:

IIf([tblEmployees1]![SGrade]="Manager","M",IIf([tblEmployees1]![SGrade]="Senior Manager","SM",IIf([tblEmployees1]![SGrade]="Director","D","Other")))

But the problem is when you run the maketable query, only the records which satsify the requirements are copied over and the records which have grades which should be classified as "other" are completely omitted.

thanks for trying though

 

[ravness]

SELECT tblEmployees1.sConsultantCode, tblEmployees1.sCountry, tblEmployees1.sSurname, tblEmployees1.sFirstName, tblGradeFilter.sGrade, tblEmployees1.sType, tblEmployees1.sDivision, tblEmployees1.dteDateStarted, tblEmployees1.dteDateLeft, tblEmployees1.dWorkHours INTO tblEmployees
FROM tblGradeFilter RIGHT JOIN tblEmployees1 ON tblGradeFilter.sGradeRaw = tblEmployees1.sGrade
WHERE (((tblGradeFilter.sGrade)=IIf([tblEmployees1]![SGrade]="Manager","M",IIf([tblEmployees1]![SGrade]="Senior Manager","SM",IIf([tblEmployees1]![SGrade]="Director","D","Other")))));

The above is the SQL view of the query if you use the IIF function

 

[dkinley]

Ive spotted the error

if you use it like this

Switch ([tblEmployees1]![SGrade]="Manager", "M", [tblEmployees1]![SGrade]="Senior Manager", "SM", [tblEmployees1]![SGrade]="Director", "D", [tblEmployees1]![SGrade]=4, "Other")
The problem is the bit in bold, when sGrade = anything else other the ones listed then it should be something like Null but i dont know how to code it.

You could use > 4 for this condition and add another comma and use IsNull([sGrade]), "Other".

This can be fixed with the above or with Rabbie's suggestion; however, I am thinking of resolving the issue at a different place.

Since you seem to be comfortable with another table (tblGradeFilter) into your database (and this fits better under the rules of normalization) there are several benefits to gain in this implementation.

On your forms where you assign the grade - use a combo box that stores the ID of tblGradeFilter and presents the sGradeRaw field to the user, then all you would need to do is add another row called "Other" to tblGradeFilter. The combo box values would be populated from tblGradeFilter (limit to list - yes). The bound column would be the ID field and the columns width would be 0";2" where the first column in the row source is the ID and the second is the raw field.

You will recieve the added benefit of data consistency since a mispelling will not throw your counts off. The user is reading sGradeRaw, but actually selecting the ID field. In this instance, in the Employees table, they will be storing the foreign key of tblGradeFilter in the sGrade field.

Then there is nothing special about the query, no tricks, no gimmicks that you have to remember because you are using your left join to place in the sGrade you want in the other table and 'Other' will automatically be placed there.

The only other thing that will need to be modified is that you will need to do data validation to make sure the user selects something from the combo box.

The other benefit of the combo box method is if you add a grade then you have nothing else to do. If you don't use this method then you have to remember to alter the query and get back into all 24+ conditions to make sure it tests properly, etc etc.

Now, if this doesn't sound like something you want to do, then you can remove the tblGradeFilter and we can build the proper statement. I would recommend the previous because you have 24 different grades and the statement will get quite lengthy because we have to cover if tblEmployees field is null or does not contain any of the 24 (different spellings).

If you could ensure without a doubt that they put nothing or will never misspell one, then you could use:

SGrade: IIf(IsNull([tblEmployees1].[sGrade]),"Other",[tblGradeFilter].[sGrade])

You could ensure this through the use of a combo box. Either something is selected or it is not. I would say put in an "Other" and an "Unknown" on the combo box and force the validation so you can differentiate if the user knew and just didn't put anything or really had no idea. (Or put in an "Other" and if left blank the query inserts "Unknown" to relinquish the data validation requirment but you still would not know if a true unknown because they could have just forgot).

This make sense or has my logic went through too many circles?

-dK

 

[ravness]

Really good idea with the combo box thing but unfortunately this database is being imported from another program which doesnt have any real validation and is being used across europe hence all the deviation in naming conventions!

It looks like I will need the lengthy formula i've tried doing this its really confusing


is the below correct? its not working

IIf([tblEmployees1]![SGrade]="Manager","M",IIf([tblEmployees1]![SGrade]="Senior Manager","SM",IIf([tblEmployees1]![SGrade]="Director","D",IIf(IsNull([tblEmployees1]![sGrade]),"Other"))))

 

[ravness]

ive tried so many revisions of this formula and all of them miss out creating the other records...

please could some one open the attachment and do the correct coding on the query?

this would be much appreciated even only for the 3/4 grades ive listed so far and i can work on the rest

 

[dkinley]

You need this for all 24 grades? or just the ones in the original post?

-dK

 

[ravness]

I wont need it for any of the grades which correspond with "Other" so that would cut it down by a few.

However if someone started it off and got it working for a few of the grades, i could carry on the formula if you know what i mean unless its really complicated lol

 

[raskew]

Assuming that the programming issues are eventually sorted out, there's still a basic structure problem here. Taking the (US Army) military pay-grade structure as an example (which is similar throughout all of the services):

O1 - 2d Lieutenant
O2 - 1st Lieutenant
O3 - Captain
O4 - Major
O5 - Lt. Colonel
O6 - Colonel
O7 - Brigadier General

Mention O3 or O1 or O5 and you know where the officer stands in the overall pecking-order, since the O? indicates the ranking level order.

Now, moving to your example:

1 - Manager
2 - Senior Manager
3 - Director
4 - Trainee

I can't tell for sure, but I'm guessing that the pecking order (bottom to top) goes like this:

4 - Trainee
1 - Manager
2 - Senior Manager
3 - Director

Even if you displayed it in a different manner (obviously you can't accurately sort it based on ID), it still makes no sense and the viewer can't tell by the ID who is in what position. ('M', 'SM', 'D', 'Other' does nothing to clarify the situation.)

And, I've gotta ask: Is there nothing between Trainee and Manager, maybe like 'Employee' i.e. who's doing the work?

Do you see anything wrong with this picture?

Bob (US Army, Retired)

 

[ravness]

Hey,

Thanks for the reply. Interesting analysis there.

The table isnt used as a display for end users but mainly for functionality purposes in the backend of the db so this table wont actually be viewed by the end user.

The purpose is to change all employee grades into shortened codes like SM, C etc. from an early stage in processing, and there are several UseFlags to filter out different employees based on the divisions in which they are working and loads of queries which have been developed already using this employee table and other linked tables.

So I suppose the ID thing can be removed, I kept it in there just in case i needed it for some kind of lookup function but it doesnt seem like I will be using it like that now.

Please let me know if anyone has managed to solve the problem

 

[dkinley]

You were almost there ...

If you are using two tables in a query and they each have a field name that is the same, then you need to use . notation instead of ! .

Replace the sGrade column with this so the field is made with sGrade in the new table ...

sGrade: IIf([tblEmployees1].[SGrade]="Manager","M", IIf([tblEmployees1].[SGrade]="Senior Manager","SM",IIf([tblEmployees1].[SGrade]="Director","D","Other")))

-dK

 

[ravness]

cheers that would have worked but when i made it for all 24 rules, the expression was too complex and it didnt allow me to use it

you did help me out with it massively though by the dots error!

i realised the first formula i ever tried, replacing [ ] with dots worked...simple as a pimple!

no long codes needed and the grade filter table wasnt a waste of time either!

thank you all that helped with this!

sGrade: IIf(tblEmployees1.SGrade=tblGradeFilter.sGradeRaw,tblGradeFilter.sGrade,"Other")

​

​

 

[dkinley]

No problem .. sorry for making a mountain out of a molehill and all - just wanted to make sure you were using best practices (didn't know it was an import until after =]).

I promise next time it will be easier.

-dK