Data needs tweeking...

[OlneyFD]

I have been working on a simple database to calculate the attendance percentage per employee based on a total of dates (Incidents) and responses (Activities). I am trying to get it to check a third table (Available) to factor. The third table is used to check if the employee was available at the time of the incident. If they weren't available, then the incident date will not count toward their percentage.



Any help would be greatly appreciated. I have bene writting queries and can't seem to get the formula right. I think I could do it in Excel, but needs to be in access.

[hertfordkc]

Two questions occur to me, but there are a bunch of variables.
The two questions are: is the percentage calculated as
TotalResponses/(TotalIncidents - Unavailabilities)*100
and
Is unavailability determined by a boolean value from Table Available or by lack of record in Table Available?

[OlneyFD]

Two questions occur to me, but there are a bunch of variables.
The two questions are: is the percentage calculated as
TotalResponses/(TotalIncidents - Unavailabilities)*100

Yes that is the way the percentage is to be calculated.

Is unavailability determined by a boolean value from Table Available or by lack of record in Table Available?

It is based on the lack of a table record in ACTIVITIES to match the record in the INCIDENT table.

Thank you for your assistance!

[OlneyFD]

Ok....been working on this for some time now and I have found a work around, but still running into brick walls!

I have finally got queries built to give me the Total Incidents, the Unavailability of a member and the total responses that the member made all together.
I am still having problems getting the data to play nice with each other. I would be MORE than happy to send this to someone for assistance. I have a May 1st deadline and it is not looking good!

Thank you in advance for the assistance. Send me an IM and I will respond within a day or to as I work every 3rd day.

[hertfordkc]

Ok....been working on this for some time now and I have found a work around, but still running into brick walls!

I have finally got queries built to give me the Total Incidents, the Unavailability of a member and the total responses that the member made all together.
I am still having problems getting the data to play nice with each other. I would be MORE than happy to send this to someone for assistance. I have a May 1st deadline and it is not looking good!
Thank you in advance for the assistance. Send me an IM and I will respond within a day or to as I work every 3rd day.

You say that you have queries that give you the individual elements of the calculation. Why not create another query with the calculated percentage based on the individual elements? It may not be pretty or elegant, but if it gets the job done...celebrate.

[OlneyFD]

You say that you have queries that give you the individual elements of the calculation. Why not create another query with the calculated percentage based on the individual elements? It may not be pretty or elegant, but if it gets the job done...celebrate.

That is what I am having trouble accomplishing although I am having trouble getting it to do what I need. I REALLY don't know much about access. I guess I am going to have to find someone to work through this with me or build it. I am sure it would take someone that knows what they are doing very little time. I have spent months on this dang thing!