CODEWITHSUNDEEP
sqldatabasevb6ms-access-2007
Handy Manny
Handy Manny

Reputation: 388

compute total days work using query from vb6 to msaccess

I am making a payroll system using vb6 with ms access as database. I have 3 tables in ms access namely:

tblemployee,   tblattendance,   tblpayroll

I want to get the payroll of all employees for the month of ex april, year 2013. All these datas will be from tblattendance.

Ex. (Some fields are hidden) tblattendance: 

        Empid  Day  Month  Year  totalhourworkfor1day  
        1      1    april  2013  8
        2      1    april  2013  5
        1      2    april  2013  8
        2      2    april  2013  5

tblpayroll: 

        empid  month  year  Totalhoursworkfor1month
        1      april  2013  16
        2      april  2013  10

After querying for the month of april year 2013, all the totalhourworkfor1day for 1 employee will be added and the result will save in to Totalhoursworkfor1month.

How to do this? Considering that from my vb6, I only need to select the month and year to generate the payroll and after it will display to the listview

Tried code:

insert into tblpayroll(empid,month,year,hourswork)
select id,month, year, sum(hourswork) from tblattendance where empid=id group by empid;

Upvotes: 0

Views: 1598

Answers (1)

user2204315
user2204315

Reputation:

Does this help? The dates are just one field. I have used the MinOfWorkDay so as to avoid complications with months ending on 28,29,30,31. But this can be worked around if you want to have "month ending". In any event you could format results to simply show the month/year.

It could further enhanced by the HAVING clause defaulting to the month just ended, or using "from" and "to" dates entered on a form. 

INSERT INTO tblPayroll ( Empid, HoursWorked, MonthEnd )
SELECT tblWork.Empid, Sum(tblWork.WorkHours) AS SumOfWorkHours, Min(tblWork.Workday) AS MinOfWorkday
FROM tblWork
GROUP BY tblWork.Empid
HAVING (((Min(tblWork.Workday)) Between #4/1/2013# And #4/30/2013#));

I'd be pleased to give more help if you come back with more questions.

15 Apr: I boobed! I assumed you would be processing your data in MS Access. I don't know vb6 so can't help any more. Could your whole project be done in access?

Upvotes: 1

Related Questions