Select Count and Update? 1

[iamtrying]

I am trying to compare a sikbal table and a sikconv table. The fields in the sikbal table are LName, FName, Department, & SikBalance. The fields in the sikConversion table are LName, FName, Department, & SikConverted.

If the LName & Fname are listed in the sikbal table, the person is eligible for sick conversion.

The final conversion will be the lesser of the two fields sikBalance & sikConverted.

If their name is on sikbal but not on sikconv, then they get 40hrs added to their sikBalance

 

[randy700]

First, your tables are not normalized. You should have the personal data (Names, Dept, etc) in a separate table with some kind of ID field as the primary key. Use the ID field in the other table(s) as a foreign key.

That being said, you may need to run more than one process. Using the Unmatched Query Wizard you can find who is on sikbal but not on silconv. An update query will then add the 40 hours.

A simple query with the tables joined on (FullName?) will provide you with the balance and converted data. Now, a simple IF statement will give you the smaller number.


Randy

 

[iamtrying]

Randy700,

I have added a primary key for each table. Do you mean join the fname & lname to make a full name? They are now separate fields.

 

[randy700]

As you currently have them, a concatenated field in a query would be the only way to compare, unless you were able to use the SAME primary key for each individual in each table. If that's the case, use it rather than the names to join the tables.

I would NOT move the first and last names into one field. Keeping them in separate fields is definitely the proper method. My suggestion about normalization refers to having them in BOTH tables, which is duplication of data - violating the rules of normalization.


Randy

 

[iamtrying]

Can you give me an example of an IF Statement to compare the two tables?

 

[randy700]

In your query...

Selection:iif(sikbal.SikBalance > sikconv.SikConverted, sikconv.SikConverted, sikbal.SikBalance)


Randy

 

[iamtrying]

Are you sure this is the correct syntax?

 

[PHV]

What have you tried so far and where in your SQL code are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[iamtrying]

I am trying to get the lesser of the two columns. This is a simple query of the tables

SELECT Sikbal.[Sick Balance], Sikbal.FName, Sikbal.LName, Sikconv.[Sick Converted], Sikconv.FName, Sikconv.LName
FROM Sikbal INNER JOIN Sikconv ON Sikbal.ID = Sikconv.ID;

 

[randy700]

Where is the IIF statement I gave you?

SELECT Sikbal.[Sick Balance], 
       Sikbal.FName, 
       Sikbal.LName, 
       Sikconv.[Sick Converted], 
       Sikconv.FName, 
       Sikconv.LName[COLOR=red],
       iif(Sikbal.[Sick Balance] < Sikconv.[Sick Converted], Sikbal.[Sick Balance], Sikconv.[Sick Converted] AS MySelection[/color]
FROM Sikbal INNER JOIN Sikconv ON Sikbal.ID = Sikconv.ID

Randy

 

[PHV]

Something like this ?
SELECT B.[Sick Balance], B.FName, B.LName, C.[Sick Converted], C.FName, C.LName
, IIf(C.ID Is Null, B.[Sick Balance]+40, IIf(B.[Sick Balance]<C.[Sick Converted],B.[Sick Balance],C.[Sick Converted])) AS finalConversion
FROM Sikbal B
LEFT JOIN Sikconv C ON B.ID = C.ID

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[iamtrying]

PHV,

Both of you are on the money with the exception of adding 40. I have been told every record meeting the selection criteria will receive a flat 40 hours. It is not added to the existing balance.

 

[PHV]

So, you wanted simply this ?
SELECT B.[Sick Balance], B.FName, B.LName, C.[Sick Converted], C.FName, C.LName
, IIf(C.ID Is Null, 40, IIf(B.[Sick Balance]<C.[Sick Converted],B.[Sick Balance],C.[Sick Converted])) AS finalConversion
FROM Sikbal B
LEFT JOIN Sikconv C ON B.ID = C.ID

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886