Search results

Hello all, I'm struggling to convert an SQL statement to DSum. The table setup is as follows: DaySheets (Table 1) which contain multiple Shifts (Table 2) which are worked by various Employees (Table 3). The SQL statement in question is: SELECT DaySheets.DSdate, Sum(Shifts.ShiftDuration)*24...

I was wondering if anybody had a solution to the following problem... I have found it discussed several times on various forums but not a solution. Access 2010, using a Navigation Form (the problem does not replicate outside of a navigation form) If a change is made to a subdatasheet (for...

Thank you for the tips - I'll take a look and hopefully come up with a better solution!

Thanks for the reply. The first table is employees. The second table is what shifts they worked. So each employee needs a separate PDF file of their work history. Interestingly, today it didn't throw up the memory issue so I don't know what is hogging the record locks or memory. The code is...

Hello all, Table 1 and Table 2 are joined. I have a loop for outputting reports of all the records in Table 1, showing their linked data in Table 2. The problem was whenever there were no linked records in Table 2 I got a 2427 error. So I decided to add a Dcount into the loop to check if...

Thread resurrection... This was working, but since coming back several months later it isn't and I don't understand why. To summarise - on a report, the previous row's finish time is subtracted from the current row's start time. Dim lastvalue As Date Private Sub Detail_Format(Cancel As...

That's great - thank you very much! Very straightforward

That's an idea I've not come across before - the usual pointers are to either use a subquery or move around a recordsetclone. I understand exactly what you mean, but I have no idea how I would achieve that?!

It's a report showing the work history for an employee. It is necessary to show how long the employee has off between each shift. So on the subform the calculated control draws the previous record's finish time and subtracts that from the current record's start time. I'd like to replicate this...

It's a calculated control on the report detail, so every line

The report (and subform) have an ORDER BY DESC, so it is returning the previous value but only because of that - I should have said that in the initial post. My understanding is that using DLookup is not viable when the records do not necessarily follow an incremental increase in the primary...

Hello, I currently use the following function in a subform to successfully retrieve the previous record's value (a date): Function GetPreviousValue(frm As Form, strField As String) As Variant On Error GoTo Err_GetPrevValue Dim rst As Recordset Set rst = frm.RecordsetClone...

I think I have this working using the SQL. I had an issue with the same field names across different tables, even though I used full references. But by changing the foreign key names the statement above appears to work (presumably aliases may also have worked). Thank you for the help

Thank you for the prompt reply! I think I may have been unhelpful by trying to simplify the design, so the attached image will hopefully clarify the set up by giving a bigger picture (superfluous fields have still been removed for simplicity). Having given it more thought, I considered setting...

Hello, I'd like to know how I go about the following: Each station is allocated a vehicle. That vehicle is then used on call outs. The table design is therefore as follows: tblVehicle vehicleID (primary key) tblStation stationID (primary key) vehicleID (foreign key from tblVehicle)...

Thank you very much for the offer - as soon as I get the opportunity to strip it down that would be wonderful.

No speculation, I have done it... but it behaves exactly as I'd expect it to :confused: The only requirement in tblMovement is that it has a unique primary key. Whether a value is entered for the foreign key is surely irrelevant to maintaining referential integrity? If I delete the employee...

Yes, it is a one to many join on tblEmployee to tblMovement tblEmployee EmployeeID (primary key) tblMovement MovementID (primary key) EmployeeID (foreign key) So there can in principle be an entry in tblMovement which does not have an employee allocated to it (this is prevented in the forms...

Yes, this is what I get when I use SELECT tblEmployee.*, tblMovement.* FROM tblEmployee LEFT JOIN tblMovement ON tblEmployee.employeeID = tblMovement.employeeID 1 entry per employee if the employee has either: (i) no entries in movement table, or (ii) 1 entry in the movement table. Multiple...

No, this is the problem. They may have none, or some, or many entries in the movement table. But I need this query to return a list of every employee, irrespective of whether they have any entries in the movement table. But if they do have entries in the movement table, I want to display the...