criteria - exclude

  • Thread starter Thread starter VilMarci
  • Start date Start date
V

VilMarci

Hi,

I need to distribute interview dates.
Have a table like:

InterviewID,Name,IntDate,IntTime
1,xxxx,2004.12.15,9:00
2,xxxx,2004.12.15,10:00
3,xxxx,2004.12.16,10:00
4,yyyy,2004.12.15,10:00

The IntTime source is in an other static table named TimeSrc with times from
9:00 to 18:00 with a step 15min.
I need a query that I can assign to a combo box, and depending on the name
and the date, offers me only the avaible interview times.

So if I select "xxxx" as a name and 2004.12 as a date, the list shall give
all the values stored int the IntTime coloumn of the TimeSrc table BUT 9:00
and 10:00.

Excluding itself is not a problem, but I have no idea how to set the other
two conditions...

Any help welcome.

Marton
 
The trick for making this kind of query is to build the basic query
using both tables. Join the tables using the conventional tools and
include fields from both tables. Run the query, and you will see the
records where the joining field is identical in both tables.

Go back to the query design and right-click on the "join" line. It
will offer three choices 1) where both fields are equal (an "inner
join") 2) include all the records from table A 3) include all the
records from table B. These latter two are called "outer" joins.
Experiment with the latter two types -- you will want all the records
from TimeSrc.

Finally, examine the fields from the two tables. The "missing" records
will have a Null value in the joining field in one of the tables.
Apply a criteria to that field, thus showing just the "missing"
records.





Hi,

I need to distribute interview dates.
Have a table like:

InterviewID,Name,IntDate,IntTime
1,xxxx,2004.12.15,9:00
2,xxxx,2004.12.15,10:00
3,xxxx,2004.12.16,10:00
4,yyyy,2004.12.15,10:00

The IntTime source is in an other static table named TimeSrc with times from
9:00 to 18:00 with a step 15min.
I need a query that I can assign to a combo box, and depending on the name
and the date, offers me only the avaible interview times.

So if I select "xxxx" as a name and 2004.12 as a date, the list shall give
all the values stored int the IntTime coloumn of the TimeSrc table BUT 9:00
and 10:00.

Excluding itself is not a problem, but I have no idea how to set the other
two conditions...

Any help welcome.

Marton
Click to expand...


**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security
 
Marton,

Try it something like this...

SELECT TimeScr.Times
FROM TimeScr
WHERE TimeScr.Times Not In (SELECT IntTime FROM MainTable WHERE [Name] =
[Forms]![YourForm]![Name] AND [IntDate] = [Forms]![YourForm]![IntDate])

Use this query as the Row Source of the IntTime combobox on your form.

By the way, as an aside, 'Name' is a Reserved Word (i.e. has a special
meaning) in Access, and as such should not be used as the name of a
field or control or database object.
 
Hi all,
Thanks for the help, now this part is clear.
There's one thing missing: An interview is 45 min long.

Is it possible to exclude times from that are within 45 mins?

Like:
9:30 is reserved because it is a starting time of an interview. Times
between 9:00 and 10:00 shall be excluded from the list. (They are stored in
short time format)

Thanks,
Marton


Steve Schapel said:
Marton,

Try it something like this...

SELECT TimeScr.Times
FROM TimeScr
WHERE TimeScr.Times Not In (SELECT IntTime FROM MainTable WHERE [Name] =
[Forms]![YourForm]![Name] AND [IntDate] = [Forms]![YourForm]![IntDate])

Use this query as the Row Source of the IntTime combobox on your form.

By the way, as an aside, 'Name' is a Reserved Word (i.e. has a special
meaning) in Access, and as such should not be used as the name of a
field or control or database object.

--
Steve Schapel, Microsoft Access MVP

Hi,

I need to distribute interview dates.
Have a table like:

InterviewID,Name,IntDate,IntTime
1,xxxx,2004.12.15,9:00
2,xxxx,2004.12.15,10:00
3,xxxx,2004.12.16,10:00
4,yyyy,2004.12.15,10:00

The IntTime source is in an other static table named TimeSrc with times from
9:00 to 18:00 with a step 15min.
I need a query that I can assign to a combo box, and depending on the name
and the date, offers me only the avaible interview times.

So if I select "xxxx" as a name and 2004.12 as a date, the list shall give
all the values stored int the IntTime coloumn of the TimeSrc table BUT 9:00
and 10:00.

Excluding itself is not a problem, but I have no idea how to set the other
two conditions...

Any help welcome.

Marton
Click to expand...
Click to expand...
 
Back
Top