Search results

I'm sorry, I don't follow re the null value that "Null entries that don't correspond to tblMovements table" I get 1 entry per employee if the employee has either (i) no entries in movement table, or (ii) 1 entry in the movement table. There are multiple entries per employee if there is more...

Sorry, I'm sure I'm being obtuse, but I know what the query needs to ask for but I can't get the syntax. The WHERE criteria is: Same pilot status: tblEmployee.EmployeeStatus = Me.OnOff And Either -Top result if an entry in movement table exists: SELECT TOP 1 M.MovementID FROM tblMovement AS...

Sorry, I meant to say - if I remove the tblEmployee.EmployeeStatus = Me.OnOff I get the same behaviour as before: if there are no entries in the movements table the query excludes them (and obviously the list remains the same irrespective of the Me.OnOff toggle). SELECT tblEmployee.*...

I think you are right with the IsNull idea... The code above will only generate results for Employees who have an entry in the Movements table; if there are no entries at all in the movements table which relate to that employee then they are being excluded. If add the following: SELECT...

Hello, Thank you for the reply. My understanding is that the LEFT JOIN should do the job, but it doesn't (no errors, just still returns the same result). I've been reading around and I think it is to do with needing to move some of the criteria from the WHERE clause to the JOIN clause, but I...

One more thing to iron out, I'm afraid... At the moment it excludes anyone who does not yet have an entry in tblMovement... how would I go about including those people (albeit with a blank entry for their last movement date)? Sorry to trouble you once again!

Yes, the subform shows each employee's details, and I wanted to add their latest movement to the subform. With your help, I have managed to get it working - I just needed to add a join between the 2 tables to the statement you gave: SELECT tblEmployee.*, tblMovement.* FROM tblEmployee INNER...

Thank you once again for the prompt reply. It is still not working, I'm afraid - it returns 1 record (no errors). I decided to strip the SQL statement down to see where it is going wrong. I removed the criteria to differentiate between ON/OFF and between arrival/departure movements: "SELECT...

Many thanks for the reply. I still can't get that to work, but I am not sure I am approaching it correctly and I perhaps created confusion by oversimplifying my explanation thinking the only issue was the subquery. So I will take it back a step... The subform record source was set in vba using...

Hello, I have a continuous subform listing each employee which is based on a SQL statement, which changes depending upon the toggle switch located in the subform header. I have this working OK. I now wish to add another field to the subform which will be based on a joined table. I am...

Many thanks for the advice once again. I'll look into Max as well as thoroughly testing the sql. I'm sure I'll be back with more problems!

Well I don't seem to be getting very far on my VBA/SQL! (Edit - made a little more progress eventually but I'll leave it on here in case I'm going down the wrong line completely) I have started off with a very basic setup as a building block - one table. tblShift .fldShiftID .fldShiftStart...

Thanks for the speedy and comprehensive reply. Your suggested table design makes sense, and I'll give it a go. I've encountered that problem in the past so I have the shift start and stop fields set to dd mm yy hh:nn, and I tend to use either an input mask or controls for data entries. The...

Thank you once again for the reply. The pointers on the usage of queries/subforms/tables has been very useful, and it will certainly help guide me through from now on. This is keeping track of shifts that have happened in the past, rather than forecasting. In that regard, I currently have a...

Thank you both for the prompt replies. I've been reading up about subqueries today and they do look like a possibility. I understood the example where a subquery was used to find the previous record in the same table and I got the idea in the examples where subqueries were used across different...

Hello all, I'm hoping for some more pointers on table design as I start to build my database up. The relevant parts of the database for my current problem area are: tblSHIFT - a table recording each workshift performed. This table includes the following fields: ShiftID - primary key...

I've been working through some VBA to get 2 records automatically generated. I can get it to work, but it is messy. The tCREW has a composite primary key made up of the Shift's ID and the Employee's ID. This means that the first record cannot be saved until it has an employee assigned to it...

Thanks for the reply. The relevant part of the design for this form is: tSHIFT = tracking the details of a workshift (the location, date, vehicle allocated)... entered on the main form tEMPLOYEE = self explanatory tCREW = the 2 people who worked each shift and the specific hours they worked...

I've had a go at one part (when a new record is created in the main form, 2 new records are created in the subform and one field in each record filled with different data). Although it appears to work, it does not seem elegant so I was wondering if there was a better way to have done this? I...

I would like to add 2 new records into a subform whenever a new record is created in the main form. Ideally, I would also like to autofill some fields in those new records based on data entered into 2 fields of the main form (but I realise this may be a little too adventurous!). The main form...