MS Access VBA help

[Asakura]

hi guys.
i'm trying to create a access database with vba coding for a attendance notebook whereby it will do the calculations for the attendance percentage upon changes made in the field

e.g.

john mary harry tom ken
DAY1 P P P P P
DAY2 P P A A A
DAY3 A A P P P
DAY4 P P P P A
DAY5 A P P P P

percentage 60% 80% 80% 80% 60%


what i mean is, if i were to change the number of days, and change the no. of P(present) and A(absent)
the field in the percentage rows will automatically calculate the percentage of the attendance for that particular row.

is this possible? thx for ani enlightening help

[Ecniv]

Table of attendees
AttendeeID - auto
AttendeeFN - text (forename)
AttendeeSN - text (surname)
other info fields

Table of DaysMeetings
DaysMeetingsID - auto
MeetingDate - date
AttendeeID - number - long
Status - text (1) - P or A

link the two tables in the querybuilder and use the sum button (backwards E at the top).
For each day you need to get the count of Ps and As

Sql statement might be something like:

Select
    MeetingDate,
    count(Status=P) as Present,
    count(Status=A) as Absent,
    count(Status=P)/(count(Status=P)+count(Status=A) as PercentagePresent
From 
    DaysMettings
Group By
    MeetingDate

This is very rough, from my head and I'm not feeling well so it probably won't work. If I'm feeling better tomorrow I'll try an example of it in a db and post up if it works.

[Ecniv]

Hi

Attached is a possibility for this type of project using your test data.

However I think you should rethink it and find a better way to display/enter the data.

I'm not sure is there will be a significant increase in processing should there be lots of people/meetings and I think that there is probably something else wrong (but I cannot put my finger on it).

Anyway, take a look if you wish, use at you own peril

[Asakura]

hey thx.
but erm. actually im trying to calculate the percentage of the attendance for each individual over a period of time...
sorry if i din make myself clear

[Ecniv]

Possibly?

Select
   Person,
   Count(Attendence='P') as TotalPresent,
   Count(Attendence='A') as TotalAbsent,
   Format(TotalPresent/(TotalPresent+TotalAbsent),'#0%') AS PercPresent
From
   tableofdata
Where
   DateField between #01 Jan 2005# and #01 Feb 2005#

If that doesn't run or runs but incorrectly, then it probably would need to be sub queries for the counts, by person and the main select would use those results to perform the calculation...


If you want the calculation as well as the totals, it wouold be better to fill the control by code and then run from top row to last row of hte display control totalling up, entering these into an array or into the control as the last row...

[Asakura]

many thx for your help
i have attached a sample of my database after sorting out a better layout of the attendance...could you help me with it?
im realli sorry, im a complete VBA and SQL idiot.

P = present
A = absent
L = late (present)
VL = very late (absent)
NA = date will not be counted in the percentage

[Asakura]

help? ani1 ?

[Ecniv]

Read up on normalisation.