Plz...Help
in [General]
|
Prev: How do I change actual data from "date with exact time" to jus
Next: filter on subform looking at Week End date/prob navigate on main f
From: Nad on 5 Apr 2010 09:29 Dear Guys, I made a vacation database in which I have vacation form to put the employee request. But before I filling the vacation form I want to check how many times the employee took the vacation in the current year i. e. when I select the name or ID of the employee in the vacation form a message should show in the text box of the form that how many times and days he took the vacation in the current year. I have 2 tables Emp and vacation with one to many relationship I tried to solve it in many ways I didn't . Please help to solve it. Regards
From: Daryl S on 5 Apr 2010 11:09 Nad - You can display the total days taken using a DSum. Try something like this, but substitute your field and control names. You can look up DSum in help: Add a text box to your form, and set the record source to this: =DSum("[Days]","[vacation]","[EmployeeID] = " & Me.EmployeeID) or if EmployeeID is a text field, then this: =DSum("[Days]","[vacation]","[EmployeeID] = '" & Me.EmployeeID & "'") You can do the same with DCount to count the number of vacation records there are. -- Daryl S "Nad" wrote: > Dear Guys, > I made a vacation database in which I have vacation form to put the employee > request. But before I filling the vacation form I want to check how many > times the employee took the vacation in the current year i. e. when I select > the name or ID of the employee in the vacation form a message should show in > the text box of the form that how many times and days he took the vacation in > the current year. > I have 2 tables Emp and vacation with one to many relationship > I tried to solve it in many ways I didn't . Please help to solve it. > Regards >
From: Hans Up on 5 Apr 2010 11:55 Nad wrote: > I made a vacation database in which I have vacation form to put the employee > request. But before I filling the vacation form I want to check how many > times the employee took the vacation in the current year i. e. when I select > the name or ID of the employee in the vacation form a message should show in > the text box of the form that how many times and days he took the vacation in > the current year. > I have 2 tables Emp and vacation with one to many relationship We would be better able to help if you supply information about your tables. What fields (and their data types) are relevant to your problem? In other words, how are you recording vacations? For example, if your vacations table contains a numeric field emp_id and a date/time field vdate --- so the table contains one row for each vacation day taken by each employee, you could do a GROUP BY query. SELECT v.emp_id, Count(v.vdate) AS Vacation_Days FROM vacations AS v GROUP BY v.emp_id; But I have no idea whether that suggestion is appropriate for your situation. If you want better help, please give us details.
From: Nad on 6 Apr 2010 01:29
Many thanks for your reply. First i will try your suggestion then i will come to U. Thanks again for your help. "Hans Up" wrote: > > We would be better able to help if you supply information about your > tables. What fields (and their data types) are relevant to your > problem? In other words, how are you recording vacations? > > For example, if your vacations table contains a numeric field emp_id and > a date/time field vdate --- so the table contains one row for each > vacation day taken by each employee, you could do a GROUP BY query. > > SELECT v.emp_id, Count(v.vdate) AS Vacation_Days > FROM vacations AS v > GROUP BY v.emp_id; > > But I have no idea whether that suggestion is appropriate for your > situation. If you want better help, please give us details. > . > |