Excluding results in a Query

[AJ]

I have hit a wall...

I have a fairly basic, yet confusing database that is used to track lesson
bookings, and staff availability.

Staff availability is recorded by saving all contact info to one table, and
then all linked daily availabilty into 7 related tables (Monday - sunday)

I have a query that searches all of this availabilty and allows me to post a
report that is nicley colour coded to show when staff are "Available" and
"Not Available" at each hour of the day. I have a different report for each
day of the week. Due to the size of my staff, I would like to EXCLUDE people
from these reports if they do not have any "Available" hours on that day.

Within the Query, I have the feilds [Stafffirst], [Stafflast] (names)... and
then the hours of [8:00am] through [8:00pm].

On an initial attempt I just put the criteria <>"Not Available" under 8:00
am... which worked great at exluding anyone who was not available at 8:00
am... but it also excluded staff who happened to be available later in the
day... but were simply not scheduled at 8:00....

Does my dilema make any sense to anyone out there?

I guess Im looking for an expression that would only exclude staff names
that did not have ANY "Available" hours on a given day.

Thoughts?

thanks in advance.

 

[KARL DEWEY]

I have the feilds [Stafffirst], [Stafflast] (names)... and then the hours
of [8:00am] through [8:00pm].
Your first problem is that your table is not a relational database but a
spreadsheet.
then all linked daily availabilty into 7 related tables (Monday - sunday)
Your second is having a table for each day of the week.

Use one table to record staff names and personal data - DOB, HireDate, etc.
Second table kinked in a one-to-many relationship from the Staff table on
StaffID (primary key) to StaffID in the Work table.
The work table to have following fields --
WorkID - autonumber - primary key
StaffID - number - long ingeger
WorkDateStart - DateTime
WorkDateEnd - DateTime


--
KARL DEWEY
Build a little - Test a little

I have hit a wall...

I have a fairly basic, yet confusing database that is used to track lesson
bookings, and staff availability.

Staff availability is recorded by saving all contact info to one table, and
then all linked daily availabilty into 7 related tables (Monday - sunday)

I have a query that searches all of this availabilty and allows me to post a
report that is nicley colour coded to show when staff are "Available" and
"Not Available" at each hour of the day. I have a different report for each
day of the week. Due to the size of my staff, I would like to EXCLUDE people
from these reports if they do not have any "Available" hours on that day.

Within the Query, I have the feilds [Stafffirst], [Stafflast] (names)... and
then the hours of [8:00am] through [8:00pm].

On an initial attempt I just put the criteria <>"Not Available" under 8:00
am... which worked great at exluding anyone who was not available at 8:00
am... but it also excluded staff who happened to be available later in the
day... but were simply not scheduled at 8:00....

Does my dilema make any sense to anyone out there?

I guess Im looking for an expression that would only exclude staff names
that did not have ANY "Available" hours on a given day.

Thoughts?

thanks in advance.

 

[AJ]

Hey Karl,

Thanks...

I do have the 1 to many relationships set... i think I just formatted the
tables a little differently than you had suggested.

Is it possible to have the query eliminate any staff who are not working on
a given date?


Thanks

I have the feilds [Stafffirst], [Stafflast] (names)... and then the hours

of [8:00am] through [8:00pm].
Your first problem is that your table is not a relational database but a
spreadsheet.
then all linked daily availabilty into 7 related tables (Monday - sunday)
Your second is having a table for each day of the week.

Use one table to record staff names and personal data - DOB, HireDate, etc.
Second table kinked in a one-to-many relationship from the Staff table on
StaffID (primary key) to StaffID in the Work table.
The work table to have following fields --
WorkID - autonumber - primary key
StaffID - number - long ingeger
WorkDateStart - DateTime
WorkDateEnd - DateTime


--
KARL DEWEY
Build a little - Test a little

I have hit a wall...

I have a fairly basic, yet confusing database that is used to track lesson
bookings, and staff availability.

Staff availability is recorded by saving all contact info to one table, and
then all linked daily availabilty into 7 related tables (Monday - sunday)

I have a query that searches all of this availabilty and allows me to post a
report that is nicley colour coded to show when staff are "Available" and
"Not Available" at each hour of the day. I have a different report for each
day of the week. Due to the size of my staff, I would like to EXCLUDE people
from these reports if they do not have any "Available" hours on that day.

Within the Query, I have the feilds [Stafffirst], [Stafflast] (names)... and
then the hours of [8:00am] through [8:00pm].

On an initial attempt I just put the criteria <>"Not Available" under 8:00
am... which worked great at exluding anyone who was not available at 8:00
am... but it also excluded staff who happened to be available later in the
day... but were simply not scheduled at 8:00....

Does my dilema make any sense to anyone out there?

I guess Im looking for an expression that would only exclude staff names
that did not have ANY "Available" hours on a given day.

Thoughts?

thanks in advance.

 

[Lance]

How are you storing their currently scheduled work hours?

 

[KARL DEWEY]

Post the SQL of your query. Open the query in design view, click on menu
VIEW - SQL View, highkight all in the new window, copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little

Hey Karl,

Thanks...

I do have the 1 to many relationships set... i think I just formatted the
tables a little differently than you had suggested.

Is it possible to have the query eliminate any staff who are not working on
a given date?


Thanks

I have the feilds [Stafffirst], [Stafflast] (names)... and then the hours

of [8:00am] through [8:00pm].
Your first problem is that your table is not a relational database but a
spreadsheet.

Staff availability is recorded by saving all contact info to one table, and

then all linked daily availabilty into 7 related tables (Monday - sunday)
Your second is having a table for each day of the week.

Use one table to record staff names and personal data - DOB, HireDate, etc.
Second table kinked in a one-to-many relationship from the Staff table on
StaffID (primary key) to StaffID in the Work table.
The work table to have following fields --
WorkID - autonumber - primary key
StaffID - number - long ingeger
WorkDateStart - DateTime
WorkDateEnd - DateTime


--
KARL DEWEY
Build a little - Test a little

I have hit a wall...

I have a fairly basic, yet confusing database that is used to track lesson
bookings, and staff availability.

Staff availability is recorded by saving all contact info to one table, and
then all linked daily availabilty into 7 related tables (Monday - sunday)

I have a query that searches all of this availabilty and allows me to post a
report that is nicley colour coded to show when staff are "Available" and
"Not Available" at each hour of the day. I have a different report for each
day of the week. Due to the size of my staff, I would like to EXCLUDE people
from these reports if they do not have any "Available" hours on that day.

Within the Query, I have the feilds [Stafffirst], [Stafflast] (names)... and
then the hours of [8:00am] through [8:00pm].

On an initial attempt I just put the criteria <>"Not Available" under 8:00
am... which worked great at exluding anyone who was not available at 8:00
am... but it also excluded staff who happened to be available later in the
day... but were simply not scheduled at 8:00....

Does my dilema make any sense to anyone out there?

I guess Im looking for an expression that would only exclude staff names
that did not have ANY "Available" hours on a given day.

Thoughts?

thanks in advance.

 

[AJ]

Here's the SQL....
Let me know if I am able to get where I am hoping to with this.

Thanks


SELECT LessonBooker.[First Name], LessonBooker.Phone, LessonBooker.[SKI /
BOARD], LessonBooker.Type, LessonBooker.Age, LessonBooker.Ability,
LessonBooker.Time, LessonBooker.[# of People], LessonBooker.Date, [Staff
Log].[First Inst], [Staff Log].[Last Inst], [Staff Log].Phone, [Staff
Log].Email, LessonBooker.[Informed Instructor], LessonBooker.[Instructor
Confirmed]
FROM LessonBooker INNER JOIN [Staff Log] ON LessonBooker.Request = [Staff
Log].[Last Inst]
WHERE (((LessonBooker.Date)=[Enter Date ##-OOO-##]) AND (([Staff Log].[Last
Inst]) Is Not Null));

Post the SQL of your query. Open the query in design view, click on menu
VIEW - SQL View, highkight all in the new window, copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little

Hey Karl,

Thanks...

I do have the 1 to many relationships set... i think I just formatted the
tables a little differently than you had suggested.

Is it possible to have the query eliminate any staff who are not working on
a given date?


Thanks

I have the feilds [Stafffirst], [Stafflast] (names)... and then the hours
of [8:00am] through [8:00pm].
Your first problem is that your table is not a relational database but a
spreadsheet.

Staff availability is recorded by saving all contact info to one table, and
then all linked daily availabilty into 7 related tables (Monday - sunday)
Your second is having a table for each day of the week.

Use one table to record staff names and personal data - DOB, HireDate, etc.
Second table kinked in a one-to-many relationship from the Staff table on
StaffID (primary key) to StaffID in the Work table.
The work table to have following fields --
WorkID - autonumber - primary key
StaffID - number - long ingeger
WorkDateStart - DateTime
WorkDateEnd - DateTime


--
KARL DEWEY
Build a little - Test a little


:

I have hit a wall...

I have a fairly basic, yet confusing database that is used to track lesson
bookings, and staff availability.

Staff availability is recorded by saving all contact info to one table, and
then all linked daily availabilty into 7 related tables (Monday - sunday)

I have a query that searches all of this availabilty and allows me to post a
report that is nicley colour coded to show when staff are "Available" and
"Not Available" at each hour of the day. I have a different report for each
day of the week. Due to the size of my staff, I would like to EXCLUDE people
from these reports if they do not have any "Available" hours on that day.

Within the Query, I have the feilds [Stafffirst], [Stafflast] (names)... and
then the hours of [8:00am] through [8:00pm].

On an initial attempt I just put the criteria <>"Not Available" under 8:00
am... which worked great at exluding anyone who was not available at 8:00
am... but it also excluded staff who happened to be available later in the
day... but were simply not scheduled at 8:00....

Does my dilema make any sense to anyone out there?

I guess Im looking for an expression that would only exclude staff names
that did not have ANY "Available" hours on a given day.

Thoughts?

thanks in advance.

 

[KARL DEWEY]

I am not following your method. Post sample date for both tables.
--
KARL DEWEY
Build a little - Test a little

Here's the SQL....
Let me know if I am able to get where I am hoping to with this.

Thanks


SELECT LessonBooker.[First Name], LessonBooker.Phone, LessonBooker.[SKI /
BOARD], LessonBooker.Type, LessonBooker.Age, LessonBooker.Ability,
LessonBooker.Time, LessonBooker.[# of People], LessonBooker.Date, [Staff
Log].[First Inst], [Staff Log].[Last Inst], [Staff Log].Phone, [Staff
Log].Email, LessonBooker.[Informed Instructor], LessonBooker.[Instructor
Confirmed]
FROM LessonBooker INNER JOIN [Staff Log] ON LessonBooker.Request = [Staff
Log].[Last Inst]
WHERE (((LessonBooker.Date)=[Enter Date ##-OOO-##]) AND (([Staff Log].[Last
Inst]) Is Not Null));

Post the SQL of your query. Open the query in design view, click on menu
VIEW - SQL View, highkight all in the new window, copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little

Hey Karl,

Thanks...

I do have the 1 to many relationships set... i think I just formatted the
tables a little differently than you had suggested.

Is it possible to have the query eliminate any staff who are not working on
a given date?


Thanks

:

I have the feilds [Stafffirst], [Stafflast] (names)... and then the hours
of [8:00am] through [8:00pm].
Your first problem is that your table is not a relational database but a
spreadsheet.

Staff availability is recorded by saving all contact info to one table, and
then all linked daily availabilty into 7 related tables (Monday - sunday)
Your second is having a table for each day of the week.

Use one table to record staff names and personal data - DOB, HireDate, etc.
Second table kinked in a one-to-many relationship from the Staff table on
StaffID (primary key) to StaffID in the Work table.
The work table to have following fields --
WorkID - autonumber - primary key
StaffID - number - long ingeger
WorkDateStart - DateTime
WorkDateEnd - DateTime


--
KARL DEWEY
Build a little - Test a little


:

I have hit a wall...

I have a fairly basic, yet confusing database that is used to track lesson
bookings, and staff availability.

Staff availability is recorded by saving all contact info to one table, and
then all linked daily availabilty into 7 related tables (Monday - sunday)

I have a query that searches all of this availabilty and allows me to post a
report that is nicley colour coded to show when staff are "Available" and
"Not Available" at each hour of the day. I have a different report for each
day of the week. Due to the size of my staff, I would like to EXCLUDE people
from these reports if they do not have any "Available" hours on that day.

Within the Query, I have the feilds [Stafffirst], [Stafflast] (names)... and
then the hours of [8:00am] through [8:00pm].

On an initial attempt I just put the criteria <>"Not Available" under 8:00
am... which worked great at exluding anyone who was not available at 8:00
am... but it also excluded staff who happened to be available later in the
day... but were simply not scheduled at 8:00....

Does my dilema make any sense to anyone out there?

I guess Im looking for an expression that would only exclude staff names
that did not have ANY "Available" hours on a given day.

Thoughts?

thanks in advance.

 

[nikki grefe]

hello all
I am new to all of this blogging and www sites beyond porn of course! just
kidding, as well as a very good database for quick information and
reasearch. I am grateful to all of you for your comments. You are all very
generious! I am the village idiot in this arena, I am having an impossible
time getting people to view my jewelry website! I don't know how to get
more people to enter. If anyone has any ideas about how I could go about
making it better, as well as garnering more traffic.!!!

Nicole Grefe

elizabethgrefegemstones.com

 

[AJ]

Each day of the week has a table that contains a column for each hour of the
day. Within each hour there is a lookup that allows the user to select
"Available" or "Not Available".

What I would like to do is find a way to eliminate Query results where a
staff member has been noted as not available at all hours of a day.
I want to include anyone who has even shown 1 hour of availability.

???

How are you storing their currently scheduled work hours?

I have hit a wall...

I have a fairly basic, yet confusing database that is used to track lesson
bookings, and staff availability.

Staff availability is recorded by saving all contact info to one table, and
then all linked daily availabilty into 7 related tables (Monday - sunday)

I have a query that searches all of this availabilty and allows me to post a
report that is nicley colour coded to show when staff are "Available" and
"Not Available" at each hour of the day. I have a different report for each
day of the week. Due to the size of my staff, I would like to EXCLUDE people
from these reports if they do not have any "Available" hours on that day.

Within the Query, I have the feilds [Stafffirst], [Stafflast] (names)... and
then the hours of [8:00am] through [8:00pm].

On an initial attempt I just put the criteria <>"Not Available" under 8:00
am... which worked great at exluding anyone who was not available at 8:00
am... but it also excluded staff who happened to be available later in the
day... but were simply not scheduled at 8:00....

Does my dilema make any sense to anyone out there?

I guess Im looking for an expression that would only exclude staff names
that did not have ANY "Available" hours on a given day.

Thoughts?

thanks in advance.

 

[Ian Michael Andrews]

I don't know how to answer you.

hello all
I am new to all of this blogging and www sites beyond porn of course!
just kidding, as well as a very good database for quick information and
reasearch. I am grateful to all of you for your comments. You are all
very generious! I am the village idiot in this arena, I am having an
impossible time getting people to view my jewelry website! I don't know
how to get more people to enter. If anyone has any ideas about how I
could go about making it better, as well as garnering more traffic.!!!

Nicole Grefe

elizabethgrefegemstones.com

I have hit a wall...

I have a fairly basic, yet confusing database that is used to track
lesson
bookings, and staff availability.

Staff availability is recorded by saving all contact info to one table,
and
then all linked daily availabilty into 7 related tables (Monday - sunday)

I have a query that searches all of this availabilty and allows me to
post a
report that is nicley colour coded to show when staff are "Available" and
"Not Available" at each hour of the day. I have a different report for
each
day of the week. Due to the size of my staff, I would like to EXCLUDE
people
from these reports if they do not have any "Available" hours on that day.

Within the Query, I have the feilds [Stafffirst], [Stafflast] (names)...
and
then the hours of [8:00am] through [8:00pm].

On an initial attempt I just put the criteria <>"Not Available" under
8:00
am... which worked great at exluding anyone who was not available at 8:00
am... but it also excluded staff who happened to be available later in
the
day... but were simply not scheduled at 8:00....

Does my dilema make any sense to anyone out there?

I guess Im looking for an expression that would only exclude staff names
that did not have ANY "Available" hours on a given day.

Thoughts?

thanks in advance.