From: Nad on
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
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
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
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.
> .
>