Reorganising Database

[Moses76]

I have an employee database .

It has two main tables. Employee Info and other one Details about Types of Leave Taken,number of hours and date on which it was taken.

If I want to sum the total for each employee all the types of leave that was taken over a period of time - month or year - what would be the best way to do it . Right now I have queries set up for each type of leave and their sums so about 20 different queries.

What would be the simplest / most effective way to do it ?

 

[boblarson]

Have a generic report with a generic query which sums the leave grouped on Employee and Type of leave.

Then, have a form which lets you select a time period and then open the report like

Dim strWhere As String
 
strWhere = "[EmployeeID]=" & Me.EmployeeID & " And [DateTakenField] Between " & Format(Me.StartDate, "\#mm\/dd\/yyyy\#") & " And " & Format(Me.EndDate, "\#mm\/dd\/yyyy\#")
 
DoCmd.OpenReport "ReportNameHere", acViewPreview, WhereCondition:=strWhere

See here for a sample of how you can use a generic report:
http://downloads.btabdevelopment.com/Samples/reports/Sample-GenericReport.zip

 

[Moses76]

This would be for one Emp ID correct ? I am looking for this

Name Sick vacation stat
Name 1 total sick total vacation total stat
Name 2
.
.
.Name n Grand total Grand Total Grand total
sick vacation stat

Have a generic report with a generic query which sums the leave grouped on Employee and Type of leave.

Then, have a form which lets you select a time period and then open the report like

Dim strWhere As String
 
strWhere = "[EmployeeID]=" & Me.EmployeeID & " And [DateTakenField] Between " & Format(Me.StartDate, "\#mm\/dd\/yyyy\#") & " And " & Format(Me.EndDate, "\#mm\/dd\/yyyy\#")
 
DoCmd.OpenReport "ReportNameHere", acViewPreview, WhereCondition:=strWhere

See here for a sample of how you can use a generic report:
http://downloads.btabdevelopment.com/Samples/reports/Sample-GenericReport.zip

 

[boblarson]

This would be for one Emp ID correct ? I am looking for this

Name Sick vacation stat
Name 1 total sick total vacation total stat
Name 2
.
.
.Name n Grand total Grand Total Grand total
sick vacation stat

Regardless of what you want, the first step is the generic query with generic report. Then we can get the code to open it the way you want. So, it all depends on what you want to see. If you look at my sample, you can see it pull the report for different things. It's all a matter of telling it what you want when you open it.