Search results

  1. A

    Non-continuous counting in query

    Hi MajP, I have re-imported my data and it is now working exactly as i want it, thank you so much, really appreciate all your help (y) Anna
  2. A

    Non-continuous counting in query

    Hi MajP, I'm so sorry, i THINK this is me not updating the data correctly, i am just re-importing the data and will test again, hopefully in the next few hours. I will let you know.... Thanks Anna
  3. A

    Non-continuous counting in query

    Hi MajP, Thanks for this. I now have 1 query; for an employee where the latest record in the occurences table is ABS, it isn't including this in the occurences count. Is there a way round this? Also, for some employees that only have 1 ABS in the whole data set, they are showing on the query...
  4. A

    Non-continuous counting in query

    Hi MajP, Sorry for the delay in responding to this; with the current climate and personal factors i have not had the chance to look at this for a week or two. Thank you for this; sorry that was my mistake and as you said i hadn't updated the code correctly. I have now updated the code and it...
  5. A

    Non-continuous counting in query

    Sorry, i didnt post the error, this is what it is saying:
  6. A

    Non-continuous counting in query

    Thanks everyone, i must have copied into the table without deleting existing data. I have got rid of the duplicates, however now when i run the query this is the message i get: The date formats are the same as in the database that CJ sent over so i don't know what the problem is? Thanks Anna
  7. A

    Non-continuous counting in query

    Hi ebs17, I had a look at your zip file and it did exactly as i wanted. Therefore i tried to put it into my database, but got this error: I have attached my database, are you able to put your work in for me, as i can't get it to work? Thanks Anna
  8. A

    Non-continuous counting in query

    Thanks CJ. This is my SQL: SELECT Occurences.ClockID, Occurences.WorkType, Count(Occurences.WorkType) AS CountOfWorkType FROM Occurences WHERE (((Occurences.WorkType)="ABS") AND ((Nz((SELECT TOP 1 WorkType FROM Occurences T WHERE ClockID=Occurences.ClockID and WorkDate>Occurences.WorkDate order...
  9. A

    Non-continuous counting in query

    Hi CJ, Do you mean that if there is more than one record in the table with the same work record date, this error comes up, regardless of whether this record has a different ClockID? for example in the table there are 5 records with the date 1/4/2019 and the worktype as ABS, but these are for 5...
  10. A

    Non-continuous counting in query

    Thanks for the replies everybody. I have implemented CJ_London's subquery but can't get it to give the result i want. When i copy and paste the SQL into my database and change all of the field and table names and try and execute the query, it says " At most one record can be returned by this...
  11. A

    Non-continuous counting in query

    Hello, I have a table in my database that contains is a list of all the work record dates for each employee, along with a "type", which is either "ABS" (absence) or "work". For example an extract of my table looks like this: Clock number Date Type 1 1/1/20...
Top Bottom