Calculated Total with beginning Quantity

[michellecahal]

Hi, I have a database to keep track of bus drivers' points. For any negative actions, they have points taken away, for any positive, they have points added (I wish this would work for my kids...).

But now, instead of just coming to a maximum of 30 points before letting that driver go, HR wants it to be more positive.

I had the expression:
Total: IIf([TotalPointsForOffense]>0,IIf([TotalPointsGiven]>0,[TotalPointsForOffense]-[TotalPointsGiven],[TotalPointsForOffense]),[TotalPointsGiven])

This added up all the offense points and subtracted out the given points.

I changed that to:
Total: IIf([TotalPointsForOffense]>0,IIf([TotalPointsGiven]>0,[TotalPointsGiven]-[TotalPointsForOffense],[TotalPointsForOffense]),[TotalPointsGiven])

So now the negative is subtracted from the total with the positives added.

My dilemma is that we need to start from 95. For example, a driver has 95 points and had a wreck which removed 20 points, ending in 75 points.

Is there a way to modify the last expression with a beginning quantity?

Thank you,
Michelle

 

[jzwp22]

I'm a little unclear are [TotalPointsForOffense] and [TotalPointsGiven] fields in a table? Are there a set number of points for certain offenses? How/under what conditions are points given?

When you say that an employee now starts with 95 points, does that mean if they hit zero they are let go?

Do the points get reset each year back to 95?
Do all drivers start out with 95 points or can that value vary?

 

[MSAccessRookie]

Total: IIf([TotalPointsForOffense]>0,IIf([TotalPointsGiven]>0,[TotalPointsGiven]-[TotalPointsForOffense],[TotalPointsForOffense]),[TotalPointsGiven])

The IIf() Statement currently returns the amount of offensive points assigned to a driver as a negative number if it exceeds the number of points given.

When you need to add a fixed starting value (95), all you need to do is change the formula to add the value to 95. If there are too many offenses, the value will eventually go down.

Total: 95 + (IIf([TotalPointsForOffense]>0,IIf([TotalPointsGiven]>0,[TotalPointsGiven]-[TotalPointsForOffense],[TotalPointsForOffense]),[TotalPointsGiven]))

 

[michellecahal]

In the beginning, the committee wanted the drivers to start out with zero points. Offenses would drive up the points (30 in one year gets one released from their duties). Now, to put a positive slant on the program, the drivers will start out at 95 and lose points as offenses are made.

On the other hand, the drivers are now able to gain points to cancel out some of the offense points. These are the [TotalPointsGiven] points. I know that's a little confusing, as at first, they were 'subtracted' from the Offense Points.

There is a set maximum to offenses. But these can be entered manually since they'll fluctuate by severity of the given offense.

Once the driver reaches 65 in Driver Points, they are let go. Or if they receive an Offense that equals 30 points, it's immediate termination.

All drivers start out with 95, and it gets reset every year.

EXAMPLE:
Driver, John Smith, starts on Jan. 1 with 95 points. He commits a severe traffic violation worth 25 points. His Driver Points total is now 70. He then earns a perfect score on his annual inspection which adds 3 points to make his total 73. He goes the rest of the year with no further actions. On Jan 1 of the next year, his Driver Points total is 95 again.

It's a pretty small database right now. I'll zip and attach it.

Thanks!

 

[michellecahal]

Sorry, NEW DriverTotalPoints is the query.

Another problem with the IIF is that I can't get a total to show if there are no offense points taken.

 

[MSAccessRookie]

Sorry, NEW DriverTotalPoints is the query.

Another problem with the IIF is that I can't get a total to show if there are no offense points taken.

The answer to that question lies in the possible values of Points Taken and Points Given.

According to the formula, when there are no points taken, the value reverts to the number of points given. What if no points have been given either?

If (when no points have been assigned), the value is allowed to be NULL, and not defaulted to 0, then that could be your answer.

 

[jzwp22]

I was going to recommend a completely different approach to your table structure that should be easier in the long run. If you consider the awarding or the taking away of points as a transaction event you could get your net points with a query or two. You would then have 95+(-/+net) to show the current points

I have attached an example database with the new structure. I also separated the point range to two separate numeric fields, that way you can validate that the points assigned fall within the range. Technically speaking, the two fields should be in a separate, but related table since there are many limits (upper and lower) for each transaction description.

 

[michellecahal]

The IIf() Statement currently returns the amount of offensive points assigned to a driver as a negative number if it exceeds the number of points given.

When you need to add a fixed starting value (95), all you need to do is change the formula to add the value to 95. If there are too many offenses, the value will eventually go down.

Total: 95 + (IIf([TotalPointsForOffense]>0,IIf([TotalPointsGiven]>0,[TotalPointsGiven]-[TotalPointsForOffense],[TotalPointsForOffense]),[TotalPointsGiven]))

Thank you! This formula fixed what I was missing!

 

[michellecahal]

I was going to recommend a completely different approach to your table structure that should be easier in the long run. If you consider the awarding or the taking away of points as a transaction event you could get your net points with a query or two. You would then have 95+(-/+net) to show the current points

I have attached an example database with the new structure. I also separated the point range to two separate numeric fields, that way you can validate that the points assigned fall within the range. Technically speaking, the two fields should be in a separate, but related table since there are many limits (upper and lower) for each transaction description.

I'm looking at this structure. It makes sense, and I really like it better than what I came up with (I'm so new at this...). I bet this saves a lot of headaches for me. Thanks so much!

 

[michellecahal]

JZWP, in the tblPointTransactions, how do I get the dropdown for Driver ID? Is it possible to use Lastname as the lookup?

I was going to recommend a completely different approach to your table structure that should be easier in the long run. If you consider the awarding or the taking away of points as a transaction event you could get your net points with a query or two. You would then have 95+(-/+net) to show the current points

I have attached an example database with the new structure. I also separated the point range to two separate numeric fields, that way you can validate that the points assigned fall within the range. Technically speaking, the two fields should be in a separate, but related table since there are many limits (upper and lower) for each transaction description.

 

[jzwp22]

Because a driver can have many point transactions, the best way to handle data entry would be with a main form/subform set up. The main form would be based on the driver table while the subform would be based the tblPointTransactions. I have added the form frmDriver that illustrates the main form/subform design in the attached database.

 

[michellecahal]

This is beautiful! I need to get into an Access class immediately.
It absolutely works for my needs. Can you tell me what the Ing in IngPoints means? Can I now create a query on the amount of points retained by each driver? Thank you immensely! I'm learning so much just by taking apart the pieces you helped me with! (I'm still trying to figure out why foreign keys are needed...I'll get there!).

Because a driver can have many point transactions, the best way to handle data entry would be with a main form/subform set up. The main form would be based on the driver table while the subform would be based the tblPointTransactions. I have added the form frmDriver that illustrates the main form/subform design in the attached database.

 

[jzwp22]

The lng stands for long integer number datatype. I use the prefixes to remind me of the data type for each field (dte=date, sp=single precision number, db=double precision number, txt=text etc.). I used this datatype assuming that you assigned points as whole numbers without any decimals. If you do assign partial points then you would have to use a single precision number datatype.

Can I now create a query on the amount of points retained by each driver?

Yes

I made a change to the subform in my earlier sample database; it is attached.

Using some dummy data, I included a series of queries to illustrate how I would do the point calculations.

 

[michellecahal]

Integer is correct. No decimal places. I'm looking at the forms and tables now. You've nailed it. Thank you so much for taking the time to show me how to do this!

The lng stands for long integer number datatype. I use the prefixes to remind me of the data type for each field (dte=date, sp=single precision number, db=double precision number, txt=text etc.). I used this datatype assuming that you assigned points as whole numbers without any decimals. If you do assign partial points then you would have to use a single precision number datatype.



Yes

I made a change to the subform in my earlier sample database; it is attached.

Using some dummy data, I included a series of queries to illustrate how I would do the point calculations.

 

[jzwp22]

You're welcome.

 

[michellecahal]

One last question for the day...When I run the report, I'd like to see everyone's score. If they have had no point transactions, then I'd like to see 95 as their "score". I joined the tblDrivers to the qryYTDPointStatus, then changed it to 2: Include ALL records from tblDrivers.... (please see attachments).

Since doing this, I get the list of every driver, but no score.

 

[jzwp22]

You are on the right track; you want to do a left join from the driver table to the query, but I would use the qryYTDPointSummaryByDriver query.

SELECT tblDrivers.pkDriverID, tblDrivers.LastName, tblDrivers.FirstName, qryYTDPointSummaryByDriver.YTDPoints
FROM tblDrivers LEFT JOIN qryYTDPointSummaryByDriver ON tblDrivers.pkDriverID = qryYTDPointSummaryByDriver.fkDriverID;

Now if you view the data from this query, the YTDPoints field will be null for those drivers that do not have any data. What we would really like to see is zero in place of the null field, so we can use an IIF() function in conjunction with the IsNull() function to do a quick evaluation. If the field is null, put a zero in the field, if it is not null put in the YTDPoints. This expression will do that:


IIF(IsNull(qryYTDPointSummaryByDriver.YTDPoints),0,qryYTDPointSummaryByDriver.YTDPoints)


So the query now becomes this:

SELECT tblDrivers.pkDriverID, tblDrivers.LastName, tblDrivers.FirstName, IIF(IsNull(qryYTDPointSummaryByDriver.YTDPoints),0,qryYTDPointSummaryByDriver.YTDPoints) AS YTDPoints
FROM tblDrivers LEFT JOIN qryYTDPointSummaryByDriver ON tblDrivers.pkDriverID = qryYTDPointSummaryByDriver.fkDriverID;

I'll save the above query as: qryYTDPointSummaryForAllDrivers

Now, we can use the above query to do the same thing that qryYTDPointStatus did for only the drivers with points but now for all drivers:

SELECT qryYTDPointSummaryForAllDrivers.pkDriverID, qryYTDPointSummaryForAllDrivers.LastName, qryYTDPointSummaryForAllDrivers.FirstName, 95+qryYTDPointSummaryForAllDrivers.YTDPoints as YTDPointStatus
FROM qryYTDPointSummaryForAllDrivers;

I'll save this as qryYTDPointStatusForAllDrivers


The revised DB is attached

 

[michellecahal]

Lord have mercy! If you had a support button, I'd sure use it. Thank you again.

 

[jzwp22]

You're welcome again.

BTW, if you noticed in the database I provided I had a table called tblPointReset. The sole record in that table contains the 95 starting value. You can reference this record in the qryPointStatusForAllDrivers rather than hard coding the 95. The query would need to utilize a Dlookup() function in place of the 95 as follows:

SELECT qryYTDPointSummaryForAllDrivers.pkDriverID, qryYTDPointSummaryForAllDrivers.LastName, qryYTDPointSummaryForAllDrivers.FirstName, Dlookup("lngPointResetValue","tblPointReset", "pkPtResetID=1")+qryYTDPointSummaryForAllDrivers.YTDPoints AS YTDPointStatus
FROM qryYTDPointSummaryForAllDrivers;

The advantage of doing this is that if your HR department changes that value of 95 to some other number, you would only need to change that one record from 95 to the new value. You would not have to go back and change any query as you would if the number was hard coded.

 

[michellecahal]

I'm not sure what I'm doing wrong. I've added the expression, but I only get null responses. Shouldn't the output look like:

(qryYTDPointSummaryForAllDrivers)

pkDriverID....LastName........FirstName............YTDPoints..
...030...........Smith.................Joe....................95.....
...010............Who.................Ever...................78.....

Instead I get nothing in the YTD column. It should show 95 if no point transactions have made or the actual running total if transactions HAVE been made.

I have to apologize for taking up so much of your time. Please see the attached db.
mc

You're welcome again.

BTW, if you noticed in the database I provided I had a table called tblPointReset. The sole record in that table contains the 95 starting value. You can reference this record in the qryPointStatusForAllDrivers rather than hard coding the 95. The query would need to utilize a Dlookup() function in place of the 95 as follows:

SELECT qryYTDPointSummaryForAllDrivers.pkDriverID, qryYTDPointSummaryForAllDrivers.LastName, qryYTDPointSummaryForAllDrivers.FirstName, Dlookup("lngPointResetValue","tblPointReset", "pkPtResetID=1")+qryYTDPointSummaryForAllDrivers.YTDPoints AS YTDPointStatus
FROM qryYTDPointSummaryForAllDrivers;

The advantage of doing this is that if your HR department changes that value of 95 to some other number, you would only need to change that one record from 95 to the new value. You would not have to go back and change any query as you would if the number was hard coded.