difficulty with queries

M

Maxtor21

i've been struggling with this for a couple weeks now and haven't been able
to find aything in the foums on it.
k, i am try to make a query that will sort by date (only displaying the
parameter date), then count the results to show on a weekly report,
forcasting for next week (which i am also haveing probs with)

table 1
last name || fist name|| vacation start date|| vacation end date


and i want the result to be

query 1
date || count of ppl on vaction on that date

report 1

monday||tuesday||.........so on
10 12

the prob i have is that if i do the query on a specific day, if it's not on
the start or end date it doesn't show them on vacation. not sure that i'm
explaining it correctly so if i'm not completely clear i'll supply more info.
thanks in advance!
 
J

Jason Lepack

Copy and paste this code in SQL View of the Query Designer.

SELECT
[Enter a Date],
count([last name])
FROM
table1
WHERE
[Enter a Date]
BETWEEN [vacation start date] AND [vacation end date]
 
D

Duane Hookom

Another option if you want to see a range of dates and how many are on
vacation is to create a table of dates "tblDates" with a single date field
"TheDate" and values for all possible dates ie: 1/1/2007 - 12/31/2010.

Then create a query with SQL view of:

SELECT TheDate, Count([Last Name]) AS NumOnVacation
FROM tblDates, tblMaxtor21
WHERE TheDate Between [Vacation Start Date] And [Vacation End Date]
GROUP BY TheDate
ORDER BY TheDate;

--
Duane Hookom
Microsoft Access MVP


Jason Lepack said:
Copy and paste this code in SQL View of the Query Designer.

SELECT
[Enter a Date],
count([last name])
FROM
table1
WHERE
[Enter a Date]
BETWEEN [vacation start date] AND [vacation end date]

i've been struggling with this for a couple weeks now and haven't been able
to find aything in the foums on it.
k, i am try to make a query that will sort by date (only displaying the
parameter date), then count the results to show on a weekly report,
forcasting for next week (which i am also haveing probs with)

table 1
last name || fist name|| vacation start date|| vacation end date

and i want the result to be

query 1
date || count of ppl on vaction on that date

report 1

monday||tuesday||.........so on
10 12

the prob i have is that if i do the query on a specific day, if it's not on
the start or end date it doesn't show them on vacation. not sure that i'm
explaining it correctly so if i'm not completely clear i'll supply more info.
thanks in advance!
Click to expand...
Click to expand...
 
M

Maxtor21

thankyou for the suggestions gentlemen, i will give both options a shot
tonight and let you know how they work out.

Duane Hookom said:
Another option if you want to see a range of dates and how many are on
vacation is to create a table of dates "tblDates" with a single date field
"TheDate" and values for all possible dates ie: 1/1/2007 - 12/31/2010.

Then create a query with SQL view of:

SELECT TheDate, Count([Last Name]) AS NumOnVacation
FROM tblDates, tblMaxtor21
WHERE TheDate Between [Vacation Start Date] And [Vacation End Date]
GROUP BY TheDate
ORDER BY TheDate;

--
Duane Hookom
Microsoft Access MVP


Jason Lepack said:
Copy and paste this code in SQL View of the Query Designer.

SELECT
[Enter a Date],
count([last name])
FROM
table1
WHERE
[Enter a Date]
BETWEEN [vacation start date] AND [vacation end date]

i've been struggling with this for a couple weeks now and haven't been able
to find aything in the foums on it.
k, i am try to make a query that will sort by date (only displaying the
parameter date), then count the results to show on a weekly report,
forcasting for next week (which i am also haveing probs with)

table 1
last name || fist name|| vacation start date|| vacation end date

and i want the result to be

query 1
date || count of ppl on vaction on that date

report 1

monday||tuesday||.........so on
10 12

the prob i have is that if i do the query on a specific day, if it's not on
the start or end date it doesn't show them on vacation. not sure that i'm
explaining it correctly so if i'm not completely clear i'll supply more info.
thanks in advance!
Click to expand...
Click to expand...
Click to expand...
 
M

Maxtor21

i can use both options, they are both great thankyou again guys.

Maxtor21 said:
thankyou for the suggestions gentlemen, i will give both options a shot
tonight and let you know how they work out.

Duane Hookom said:
Another option if you want to see a range of dates and how many are on
vacation is to create a table of dates "tblDates" with a single date field
"TheDate" and values for all possible dates ie: 1/1/2007 - 12/31/2010.

Then create a query with SQL view of:

SELECT TheDate, Count([Last Name]) AS NumOnVacation
FROM tblDates, tblMaxtor21
WHERE TheDate Between [Vacation Start Date] And [Vacation End Date]
GROUP BY TheDate
ORDER BY TheDate;

--
Duane Hookom
Microsoft Access MVP


Jason Lepack said:
Copy and paste this code in SQL View of the Query Designer.

SELECT
[Enter a Date],
count([last name])
FROM
table1
WHERE
[Enter a Date]
BETWEEN [vacation start date] AND [vacation end date]

On May 1, 1:25 am, Maxtor21 <[email protected]>
wrote:
i've been struggling with this for a couple weeks now and haven't been able
to find aything in the foums on it.
k, i am try to make a query that will sort by date (only displaying the
parameter date), then count the results to show on a weekly report,
forcasting for next week (which i am also haveing probs with)

table 1
last name || fist name|| vacation start date|| vacation end date

and i want the result to be

query 1
date || count of ppl on vaction on that date

report 1

monday||tuesday||.........so on
10 12

the prob i have is that if i do the query on a specific day, if it's not on
the start or end date it doesn't show them on vacation. not sure that i'm
explaining it correctly so if i'm not completely clear i'll supply more info.
thanks in advance!
Click to expand...
Click to expand...
Click to expand...
 
Top