Availablity check query

[idlewild]

I'm using a select query to attempt to create a list of jobs available to be booked onto. However i get multiple records due to the relationship between

job>booking<Engineer

The form it is being used on the booking form as a subform. the query is called "Current Available Jobs"

if anyone has any suggestions would be really appreciated!

Paul

 

[KjWhal]

I'm using a select query to attempt to create a list of jobs available to be booked onto. However i get multiple records due to the relationship between

job>booking<Engineer

The form it is being used on the booking form as a subform. the query is called "Current Available Jobs"

if anyone has any suggestions would be really appreciated!

Paul

I couldn't make any heads or tails of your form but here's the query I wrote that should get you what you need

IF you plan on adding jobs to booking where Booking.BookedOn can be No

SELECT Job.Job, Job.JobTitle, Job.StatusID, Job.Profession, Job.JobDescription, Job.AircraftID, Aircraft.AircraftType, Aircraft.AircraftManufacturer
FROM Job, Aircraft, Booking
WHERE Job.ID = Booking.JobID AND Booking.BookedOn = No

Otherwise,

' This will select any job whose ID is not found in Booking.JobID

SELECT Job.Job, Job.JobTitle, Job.StatusID, Job.Profession, Job.JobDescription, Job.AircraftID, Aircraft.AircraftType, Aircraft.AircraftManufacturer
FROM Job, Aircraft, Booking
WHERE Job.ID <> Booking.JobID

 

[idlewild]

I couldn't make any heads or tails of your form but here's the query I wrote that should get you what you need

IF you plan on adding jobs to booking where Booking.BookedOn can be No

SELECT Job.Job, Job.JobTitle, Job.StatusID, Job.Profession, Job.JobDescription, Job.AircraftID, Aircraft.AircraftType, Aircraft.AircraftManufacturer
FROM Job, Aircraft, Booking
WHERE Job.ID = Booking.JobID AND Booking.BookedOn = No

Otherwise,

' This will select any job whose ID is not found in Booking.JobID

SELECT Job.Job, Job.JobTitle, Job.StatusID, Job.Profession, Job.JobDescription, Job.AircraftID, Aircraft.AircraftType, Aircraft.AircraftManufacturer
FROM Job, Aircraft, Booking
WHERE Job.ID <> Booking.JobID

excellent will have a look at that now! thanks very much for the help!

paul