Graham,
I'm constructing the database using a Job table and a Employee Usage
table.
I have one form for the job with the employee usage appearing as a
subform.
The subform has a lookup combo for employee names in it that is populated
through a query. I tried an unmatched query which sort of worked but
didn't
look at the employees for that job but rather employees for all jobs which
exhausted the list after all the employees had been selected for jobs. I
need to be able to create a query that only removes people that have
already
been selected for jobs in the same time period.
I've set up some indexing that stops the same person being added twice to
the same job and also where the start dates are the same but the date
entry
has to be exactly the same date. The unique index doesn't block out a
span
of time, so if you had a job open for 3 days, you can still add the same
employee's name to anoher job on the next day. So that doesn't work very
effectively either.
I have start dates and end dates in both tables. The Employee Usage table
recieves default entries from the Job Table. I rely heavily on queries
because I dont write code or understand VB all that well. Is this too
complex a problem for an Access database? I do appreciate your comments.
Graham R Seach said:
Kirko,
It depends on the location of the dates. If they're in tables that the
combo's ControlSource can get at, then you write the date criteria right
into the query. If the dates are in textboxes on the form, you can still
write them into the query using the [Forms!MyForm!MyTextBox] construct,
or
you can build the combo's ControlSource in code.
Where you get the dates from, and what are the names of the
tables/queries
and fields that the combo uses as its source? Also, how do the date
fields
relate to the combo's ControlSource (are they in the same table/query)?
Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------
Kirko said:
Where do you write that Graham? In the Combo box properties source?
:
Kirko,
Perhaps a DISTINCT query might do:
SELECT DISTINCT MyName FROM MyTable
If not, the syntax for filtering out dates is as follows:
WHERE MyDate NOT BETWEEN Date1 AND Date2
Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
I'm trying to set a combo box in a form that looks up names that
don't
already appear in the form. But the names in the list can be used
by
more
than one Job as long as the dates don't conflict.
And thats another problem...how do you use a start and an end date
to
block
out a span of time so a name can't be double booked?
