Query SQL

  • Thread starter Thread starter Michele Zenna
  • Start date Start date
M

Michele Zenna

Hi, I have a question about a Acces. I have a table where I save a record
about a employes vacation, the table have three fields: IdEmployees,
date_from, date to. Now I want to know how many employees are in vacation
per day, but I can't do this with SQL. I ask you if someone can do a query
about this without using a code (VBA).
Thanks for all
 
Dear Michele:

SELECT COUNT(*) AS CountOnVacation
FROM YourTableNameGoesHere
WHERE #04/23/04# BETWEEN [date_from] AND [date to]

You might want to make a report of it, showing the number of employees
on vacation for every day in a month (or week, etc.)

Create a table with a date/time column and put in a row for every day
imaginable. Call this CalendarDate. The column I'll call XDate.

SELECT XDate, (SELECT COUNT(*) FROM YourTableNameGoesHere
WHERE XDate BETWEEN [date_from] AND [date to]) AS CountOnVacation
FROM CalendarDate
WHERE XDate BETWEEN #03/01/2004# AND #03/31/2004#

The above gives the count of employees on vacation for each date in
March.

All this assumes you have not recorded any times with the dates!

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Perhaps, I don't explain well what i can do so i will some example of
records:
Id_Employees date_from date_to
1 01/01/04 10/01/04
2 06/01/04 09/01/04
1 02/02/04 21/02/04
3 05/02/04 05/02/04
.... ..... .....

Now I want to know how many employees are in vacation per day:

day NumberOfEmployeesInVacation
01/01/04 1
02/01/04 1
03/01/04 1
04/01/04 1
05/01/04 1
06/01/04 2
....... ...

Can you think that what you suggest me doe it?
Thanks

Tom Ellison said:
Dear Michele:

SELECT COUNT(*) AS CountOnVacation
FROM YourTableNameGoesHere
WHERE #04/23/04# BETWEEN [date_from] AND [date to]

You might want to make a report of it, showing the number of employees
on vacation for every day in a month (or week, etc.)

Create a table with a date/time column and put in a row for every day
imaginable. Call this CalendarDate. The column I'll call XDate.

SELECT XDate, (SELECT COUNT(*) FROM YourTableNameGoesHere
WHERE XDate BETWEEN [date_from] AND [date to]) AS CountOnVacation
FROM CalendarDate
WHERE XDate BETWEEN #03/01/2004# AND #03/31/2004#

The above gives the count of employees on vacation for each date in
March.

All this assumes you have not recorded any times with the dates!

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Hi, I have a question about a Acces. I have a table where I save a record
about a employes vacation, the table have three fields: IdEmployees,
date_from, date to. Now I want to know how many employees are in vacation
per day, but I can't do this with SQL. I ask you if someone can do a query
about this without using a code (VBA).
Thanks for all
Click to expand...
Click to expand...
 
Dear Michele:

The second query I gave in my first post was meant to do something
just like this. What happened when you tried it?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Perhaps, I don't explain well what i can do so i will some example of
records:
Id_Employees date_from date_to
1 01/01/04 10/01/04
2 06/01/04 09/01/04
1 02/02/04 21/02/04
3 05/02/04 05/02/04
... ..... .....

Now I want to know how many employees are in vacation per day:

day NumberOfEmployeesInVacation
01/01/04 1
02/01/04 1
03/01/04 1
04/01/04 1
05/01/04 1
06/01/04 2
...... ...

Can you think that what you suggest me doe it?
Thanks

Tom Ellison said:
Dear Michele:

SELECT COUNT(*) AS CountOnVacation
FROM YourTableNameGoesHere
WHERE #04/23/04# BETWEEN [date_from] AND [date to]

You might want to make a report of it, showing the number of employees
on vacation for every day in a month (or week, etc.)

Create a table with a date/time column and put in a row for every day
imaginable. Call this CalendarDate. The column I'll call XDate.

SELECT XDate, (SELECT COUNT(*) FROM YourTableNameGoesHere
WHERE XDate BETWEEN [date_from] AND [date to]) AS CountOnVacation
FROM CalendarDate
WHERE XDate BETWEEN #03/01/2004# AND #03/31/2004#

The above gives the count of employees on vacation for each date in
March.

All this assumes you have not recorded any times with the dates!

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Hi, I have a question about a Acces. I have a table where I save a record
about a employes vacation, the table have three fields: IdEmployees,
date_from, date to. Now I want to know how many employees are in vacation
per day, but I can't do this with SQL. I ask you if someone can do a query
about this without using a code (VBA).
Thanks for all
Click to expand...
Click to expand...
Click to expand...
 
Excuse me, I try it and it work fine but I want to avoid to make a table
with a days of year, becasue I must up to date this table each year.
I try to do the same things without create a table "CalenderDate".
Thanks for all


Tom Ellison said:
Dear Michele:

The second query I gave in my first post was meant to do something
just like this. What happened when you tried it?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Perhaps, I don't explain well what i can do so i will some example of
records:
Id_Employees date_from date_to
1 01/01/04 10/01/04
2 06/01/04 09/01/04
1 02/02/04 21/02/04
3 05/02/04 05/02/04
... ..... .....

Now I want to know how many employees are in vacation per day:

day NumberOfEmployeesInVacation
01/01/04 1
02/01/04 1
03/01/04 1
04/01/04 1
05/01/04 1
06/01/04 2
...... ...

Can you think that what you suggest me doe it?
Thanks

Tom Ellison said:
Dear Michele:

SELECT COUNT(*) AS CountOnVacation
FROM YourTableNameGoesHere
WHERE #04/23/04# BETWEEN [date_from] AND [date to]

You might want to make a report of it, showing the number of employees
on vacation for every day in a month (or week, etc.)

Create a table with a date/time column and put in a row for every day
imaginable. Call this CalendarDate. The column I'll call XDate.

SELECT XDate, (SELECT COUNT(*) FROM YourTableNameGoesHere
WHERE XDate BETWEEN [date_from] AND [date to]) AS CountOnVacation
FROM CalendarDate
WHERE XDate BETWEEN #03/01/2004# AND #03/31/2004#

The above gives the count of employees on vacation for each date in
March.

All this assumes you have not recorded any times with the dates!

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Hi, I have a question about a Acces. I have a table where I save a record
about a employes vacation, the table have three fields: IdEmployees,
date_from, date to. Now I want to know how many employees are in vacation
per day, but I can't do this with SQL. I ask you if someone can do a query
about this without using a code (VBA).
Thanks for all
Click to expand...
Click to expand...
Click to expand...
 
Dear Michele:

If you put the dates for the next 20 years in the table, you won't
have to change this for a long time. Your query would need to filter
to the desired range of dates. Wouldn't that be better than adding to
the table every year?

The possible dates have to come from somewhere. I have no suggestion
at the moment what that would be, except to create the table of dates.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Excuse me, I try it and it work fine but I want to avoid to make a table
with a days of year, becasue I must up to date this table each year.
I try to do the same things without create a table "CalenderDate".
Thanks for all


Tom Ellison said:
Dear Michele:

The second query I gave in my first post was meant to do something
just like this. What happened when you tried it?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Perhaps, I don't explain well what i can do so i will some example of
records:
Id_Employees date_from date_to
1 01/01/04 10/01/04
2 06/01/04 09/01/04
1 02/02/04 21/02/04
3 05/02/04 05/02/04
... ..... .....

Now I want to know how many employees are in vacation per day:

day NumberOfEmployeesInVacation
01/01/04 1
02/01/04 1
03/01/04 1
04/01/04 1
05/01/04 1
06/01/04 2
...... ...

Can you think that what you suggest me doe it?
Thanks

Dear Michele:

SELECT COUNT(*) AS CountOnVacation
FROM YourTableNameGoesHere
WHERE #04/23/04# BETWEEN [date_from] AND [date to]

You might want to make a report of it, showing the number of employees
on vacation for every day in a month (or week, etc.)

Create a table with a date/time column and put in a row for every day
imaginable. Call this CalendarDate. The column I'll call XDate.

SELECT XDate, (SELECT COUNT(*) FROM YourTableNameGoesHere
WHERE XDate BETWEEN [date_from] AND [date to]) AS CountOnVacation
FROM CalendarDate
WHERE XDate BETWEEN #03/01/2004# AND #03/31/2004#

The above gives the count of employees on vacation for each date in
March.

All this assumes you have not recorded any times with the dates!

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Hi, I have a question about a Acces. I have a table where I save a record
about a employes vacation, the table have three fields: IdEmployees,
date_from, date to. Now I want to know how many employees are in vacation
per day, but I can't do this with SQL. I ask you if someone can do a
query
about this without using a code (VBA).
Thanks for all
Click to expand...
Click to expand...
Click to expand...
 
Ok, I will do what you suggested me. If I can, I want to do the last one
question, how I can create a table "CalenderDate" in "automatic" so I can't
enter each record of day one by one.
Thanks for all, Michele


Tom Ellison said:
Dear Michele:

If you put the dates for the next 20 years in the table, you won't
have to change this for a long time. Your query would need to filter
to the desired range of dates. Wouldn't that be better than adding to
the table every year?

The possible dates have to come from somewhere. I have no suggestion
at the moment what that would be, except to create the table of dates.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Excuse me, I try it and it work fine but I want to avoid to make a table
with a days of year, becasue I must up to date this table each year.
I try to do the same things without create a table "CalenderDate".
Thanks for all


Tom Ellison said:
Dear Michele:

The second query I gave in my first post was meant to do something
just like this. What happened when you tried it?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Perhaps, I don't explain well what i can do so i will some example of
records:
Id_Employees date_from date_to
1 01/01/04 10/01/04
2 06/01/04 09/01/04
1 02/02/04 21/02/04
3 05/02/04 05/02/04
... ..... .....

Now I want to know how many employees are in vacation per day:

day NumberOfEmployeesInVacation
01/01/04 1
02/01/04 1
03/01/04 1
04/01/04 1
05/01/04 1
06/01/04 2
...... ...

Can you think that what you suggest me doe it?
Thanks

Dear Michele:

SELECT COUNT(*) AS CountOnVacation
FROM YourTableNameGoesHere
WHERE #04/23/04# BETWEEN [date_from] AND [date to]

You might want to make a report of it, showing the number of employees
on vacation for every day in a month (or week, etc.)

Create a table with a date/time column and put in a row for every day
imaginable. Call this CalendarDate. The column I'll call XDate.

SELECT XDate, (SELECT COUNT(*) FROM YourTableNameGoesHere
WHERE XDate BETWEEN [date_from] AND [date to]) AS CountOnVacation
FROM CalendarDate
WHERE XDate BETWEEN #03/01/2004# AND #03/31/2004#

The above gives the count of employees on vacation for each date in
March.

All this assumes you have not recorded any times with the dates!

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Hi, I have a question about a Acces. I have a table where I save a record
about a employes vacation, the table have three fields: IdEmployees,
date_from, date to. Now I want to know how many employees are in vacation
per day, but I can't do this with SQL. I ask you if someone can do a
query
about this without using a code (VBA).
Thanks for all
Click to expand...
Click to expand...
Click to expand...
 
Dear Michele:

I should have seen that coming!

OK, what I would do is to put just the first row in the table, the one
with the earliest date. Next, I would write and test a query that
adds on day to that and inserts that. Finally, I would write a VBA
module to call that in a loop, say 1000 times.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Ok, I will do what you suggested me. If I can, I want to do the last one
question, how I can create a table "CalenderDate" in "automatic" so I can't
enter each record of day one by one.
Thanks for all, Michele


Tom Ellison said:
Dear Michele:

If you put the dates for the next 20 years in the table, you won't
have to change this for a long time. Your query would need to filter
to the desired range of dates. Wouldn't that be better than adding to
the table every year?

The possible dates have to come from somewhere. I have no suggestion
at the moment what that would be, except to create the table of dates.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Excuse me, I try it and it work fine but I want to avoid to make a table
with a days of year, becasue I must up to date this table each year.
I try to do the same things without create a table "CalenderDate".
Thanks for all


Dear Michele:

The second query I gave in my first post was meant to do something
just like this. What happened when you tried it?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Perhaps, I don't explain well what i can do so i will some example of
records:
Id_Employees date_from date_to
1 01/01/04 10/01/04
2 06/01/04 09/01/04
1 02/02/04 21/02/04
3 05/02/04 05/02/04
... ..... .....

Now I want to know how many employees are in vacation per day:

day NumberOfEmployeesInVacation
01/01/04 1
02/01/04 1
03/01/04 1
04/01/04 1
05/01/04 1
06/01/04 2
...... ...

Can you think that what you suggest me doe it?
Thanks

Dear Michele:

SELECT COUNT(*) AS CountOnVacation
FROM YourTableNameGoesHere
WHERE #04/23/04# BETWEEN [date_from] AND [date to]

You might want to make a report of it, showing the number of employees
on vacation for every day in a month (or week, etc.)

Create a table with a date/time column and put in a row for every day
imaginable. Call this CalendarDate. The column I'll call XDate.

SELECT XDate, (SELECT COUNT(*) FROM YourTableNameGoesHere
WHERE XDate BETWEEN [date_from] AND [date to]) AS CountOnVacation
FROM CalendarDate
WHERE XDate BETWEEN #03/01/2004# AND #03/31/2004#

The above gives the count of employees on vacation for each date in
March.

All this assumes you have not recorded any times with the dates!

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Hi, I have a question about a Acces. I have a table where I save a
record
about a employes vacation, the table have three fields: IdEmployees,
date_from, date to. Now I want to know how many employees are in
vacation
per day, but I can't do this with SQL. I ask you if someone can do a
query
about this without using a code (VBA).
Thanks for all
Click to expand...
Click to expand...
Click to expand...
 
Back
Top