UNTESTED UNTESTED
I did not have the field names you used so you need to substitute mine for
yours --
SELECT [tblVehicles].[Commission Number]
FROM tblVehicles LEFT JOIN Dispatch ON [tblVehicles].[Commission Number] =
[Dispatch].[Commission Number]
GROUP BY [tblVehicles].[Commission Number]
HAVING ([Dispatch].[Commission Number] Is Null) OR (Max([CheckOut]) Is Not
Null AND [CheckIn] Is Not Null)) OR ([EstRtn] <= CVDate[Enter date-time
wanted]) AND [CheckOut] Is Not Null)
1- ([Dispatch].[Commission Number] Is Null) == Never been dispatch.
2- OR (Max([CheckOut]) Is Not Null AND [CheckIn] Is Not Null) == Dispatched
but has been returned.
3- OR ([EstRtn] <= CVDate[Enter date-time wanted]) AND [CheckOut] Is Not
Null) == Out but due back on or before need.
--
Build a little, test a little.
sonofroy said:
In might lie my problem with my query I DO have TWO tables and my query is
definitely messed up I am unsure how to write both tables in
:
What I posted as a suggestion was TWO tables - Vehicles & Dispatch.
Your query indicates you made it all one table.
--
Build a little, test a little.
:
I tried intergrating the one from the previous poster with variations
SELECT [Commission Number]
FROM tblVehicles
WHERE #08/03/2009 14:50:00# <=[CheckOut]
AND (#08/03/2009 14:50:00# >=[CheckIn]
Or [CheckOut] Is Null)
My thinking was if I inversed the less than greater than it would give me
everything not already checked out. I have something wrong somewhere because
it keeps asking for my input. I am pretty novice at SQL building I have only
done a handfuls. Thanks for helping me
:
Post the SQL of your query.
--
Build a little, test a little.
:
I agree with the table desing you have suggested and I went ahead and created
it that why but now I am having difficulties writing the query for it. It
keeps asking for my to input the values for vehicle number and I do not that
to occur. When someone requests a vehicle I want to run the query and only
ask for a check out date to find what vehicles are available
:
At 08/03/09 2:50 the data for 64219 would not look like this -- 08/03/09
12:00 pm 08/03/09 3:00 pm as it has not returned at that time.
You should have a table with the vehicles and their information with a
second table for dispatching and related as one-to-many.
Vehicles --
VechID - Autonumber - primary key
VechNum - text
Type - Sedan 2Dr
Make - Prexous
Year - 1994
etc.
Dispatch --
DisptID - Autonumber - primary key
VechID - number - long integer - foreign key
CheckOut - DateTime
CheckIn - DateTime
EstRtn - DateTime
Driver -
Purpose -
--
Build a little, test a little.
:
Sorry about that. I have a table "tblvehicles" with fields as described below
[Vehicle Number],[Date Assigned],[Time Assigned],[Date Returned],[Time
Returned]
sample records of vehicles checked out and this information is in the
"tblvehicles"
64202 08/03/09 7:00 am 08/03/09 2:00 pm
64219 08/03/09 12:00 pm 08/03/09 3:00 pm
64077 08/03/09 7:00 am 08/03/09 9:00 am
What I am trying to accomplish is to run a query of some sort that will give
a list of which vehicle numbers are available at the time of the run query
that are not checked out. In this case if the query was ran on 08/03/09 2:50
pm vehicle 64202 and 64077 should be returned in the query.
:
I'm not sure how you can have "unique vehicles" with multiple times a day.
Please provide table, query, and field names with some sample records.
Hint: when you get a reply with "can we assume", it generally begs you to
provide greater detail so we don't have to "assume".
--
Duane Hookom
Microsoft Access MVP
:
my table is setup to only have unique vehicles but each vehicle can be
checked out multiple times a day so there is part of my confusion not to
create duplicates
:
Can we assume you have a table of unique vehicles? Can we assume your
returned fields always have values for expected returns? Can we assume your
query name is qryNoName?
Try something like:
SELECT tblVehicles.*
FROM tblVehicles
WHERE [Vehicle Number] NOT IN
(SELECT [Vehicle Number]
FROM qryNoName
WHERE Now() Between [Date Assigned] + [Time Assigned] AND [Date Returned]
+ [Time Returned])
--
Duane Hookom
Microsoft Access MVP
:
I am working on vehicle tracking database and I have a query with these fields
Vehicle Number Date Assigned Time Assigned Date Returned Time
Returned
How can I create a function that will tell me what vehicles are available at
the time the query is opened for assignment so I do not create a duplicate. I
have 12 unique vehicle numbers to choose from. I have tried unmatched query
and several different functions but nothing is working. Please help! Thanks
