Decision on Column values in Query

[Muaz]

I am facing one problem and needs our guidance.
My scenario is as follow:

I have employee attendance in Excel i.e Emplpyee Number, Day1, Day2, Day3....Day31
I have a table in access that have columns Employee Number, Day1, Day2, Day3...Day31, Presents, Annual Leaves etc
Excel file data needs to be transferred in Access table but the number of Presents, Annual Leaves etc needs to be calculated in Access during transfer not in Excel.

Now due to limited knowledge I do not understand how to resolve this issue and how to start. How to calculate the number of Present and Leaves.

Any body can guide me ?

Thanking in advance.

Muaz

 

[pr2-eugin]

You need to set up the tables in Access properly. The current structure is not a Normalized. Normally Access tables contains, rows rather than columns.. Google the topic, and work on some simple tutorials.

With your example. Your table should be designed as,

tblEmployeeAttendance
attID - PK (auto number)
employeeID - FK
workDate
hoursWorked

You would be able to figure out if they were present or absent, any hours in loss in simple Queries, you do not even need to store calculations.

 

[Mihail]

Excel file data needs to be transferred in Access table but the number of Presents, Annual Leaves etc needs to be calculated in Access during transfer not in Excel.

Lets broke this in smaller pieces.

Excel file data needs to be transferred in Access...

Why ?
You need to transfer only once, only that worksheet in order to see if it is possible to calculate in Access or you need to setup a DB in Access in order to move the business from Excel to Access forever ?

...table...

Again, you have some constrain to move data to a table or you need to move data into a new database ? Is not the same thing.

...but the number of Presents, Annual Leaves etc needs to be calculated in Access during transfer

During transfer ? One second after is too late ?

 

[Muaz]

Thank you for suggestions