matching partner/pair record

[laffeg]

I have been tasked with importing data from a clocking on system to analyse worked hours.

There is no option to amend the format of the export which is not great

surname​

forename​

Field2​

Field3​

xxx
xxx

11/06/2013​

19.18
xxx
xxx

11/06/2013​

5.37+
xxx
xxx

12/06/2013​

5.41+
xxx
xxx

12/06/2013​

19.21


The plus indicates a night shift worker
eg started at 19.18 and finished at 5.37 on the 12th

so for every record I have staff id , date, and clock time ( I have stripped out the + ) and created a yes/no field to identify the records where field2 should actually be field2+1

I have sat in query design screen for ages and cant think how on earth I am going to calculate hours worked for a given staff member and date combination.

for every combination of staff id and date there should be 2 records - a clock in and a clock out

I thought about creating new fields clockin and clockout but struggling to see how I can link the 2 "paired" records together

vb script with dlookup for every record ?

any ideas ?

 

[CJ_London]

Your format is strange - is it just the way you have laid it out or is that how it actually comes?

I've reinterpreted your data as:

[B][U]TimeTbl[/U][/B]
Surname(Text)    Forename(Text)   TDate(Date)    Time(text)
xxx                    xxx           11/06/2013   19.18
xxx                    xxx           11/06/2013   5.37+
xxx                    xxx           12/06/2013   5.41+
xxx                    xxx           12/06/2013   19.21

But this raises some questions and clarifications:

• Is time presented as 19.18 or 19:18? (just to clarify - assumed 19.18)
• Are these records for one or more people? in which case can you indicate by changiing xxx to yyy (assumed one person)
• Is there any significance in the order? for example the last two records appear to be the opposite way round to the first two records - or is this how they come? (assumed as they come)
• You only have surname and forename - is it safe to assume you do not have any duplicate names? (assumed safe)

This query will work using the structure as defined above

SELECT DISTINCT TimeTbl.Surname, TimeTbl.Forename, TimeTbl.TDate, TimeValue(Format([TTime],"Short Time")) AS StartTime, TimeValue(Format(Replace((SELECT First(TTime) FROM TimeTbl as Tmp WHERE Surname=TimeTbl.Surname and forename=timetbl.forename and tdate=timetbl.tdate and ttime<>timetbl.ttime),"+",""),"Short Time")) AS EndTime, TimeValue(Format(1+[Endtime]-[StartTime],"Short Time")) AS TotalTime
FROM TimeTbl
WHERE (((TimeTbl.TTime)=(Select min(TTime) From Timetbl as Tmp WHERE Surname=TimeTbl.Surname and forename=timetbl.forename and tdate=timetbl.tdate and ttime not like "*+")))

 

[laffeg]

thanks CJ for the reply

your questions....

time came in as 19.18

I have used text functions to work out the position of the period/full stop and then created a field called "clock" with format of short time

the examples were for 1 person - clockings for 11th and 12th shown

no order

I have stripped out forename and surname from your statement and replaced with staff id field which will be unique

when running it I am getting "data type mismatch in criteria expression"

noticed you still had the + in the statement ? - I have already stripped it out again using string functions - can I just take that bit out of the criteria ?

 

[CJ_London]

Since you have changed things from what I sent you, you'll need to post the full sql you are using for me to determine the error.

I have used text functions to work out the position of the period/full stop and then created a field called "clock" with format of short time

I designed the query to convert the data as supplied and clarified in my post

The error is occuring because you are trying to compare datatypes which are not the same - e.g.compare text to string so I'll also need to see the datatypes you actually are using. It may be your text functions above are causing the problem

noticed you still had the + in the statement ? - I have already stripped it out again using string functions - can I just take that bit out of the criteria ?

Without knowing what you now have in your criteria it is not possible for me to say You've changed both the firstname/surname and the time values

 

[laffeg]

Hi

my new sql is as follows:-

SELECT DISTINCT Clockings2.field1, Clockings2.field2, TimeValue(Format([clock],"Short Time")) AS StartTime, TimeValue(Format(Replace((SELECT First(clock) FROM Clockings2 as Tmp WHERE field1=Clockings2.field1 and field2=Clockings2.field2 and clock<>Clockings2.clock),"+",""),"Short Time")) AS EndTime, TimeValue(Format(1+[Endtime]-[StartTime],"Short Time")) AS TotalTime
FROM Clockings2
WHERE (((Clockings2.clock)=(Select min(clock) From Clockings2 as Tmp WHERE field1=Clockings2.field1 and field2=Clockings2.field2 and clock not alike "%+")));

table is called clockings2
field1 is the unique staff id
field2 is the date field
clock is the clock-in or clock-out time

 

[CJ_London]

OK, A couple of things, are you using ANSI92 syntax? (I do which was my original post, but then I corrected to Access SQL) so if you aren't then check my updated post.

Alike "%+" (which is ANSI92 SQL) should be Like "*+" (Access SQL). To check go to Access Options>Object Designers>Query Design and see if the box is ticked.

Secondly [Clock] should be in the format 19.18, 5.37+ etc. If you have now converted to a time you need to unconvert it because the query uses the + character to determine which time is the start of shift.

The logic is in the final criteria

WHERE (((Clockings2.clock)=(Select min(clock) From Clockings2 as Tmp WHERE field1=Clockings2.field1 and field2=Clockings2.field2 and clock not like "*+")))

it selects the minimum time for a given date where the time does not include+.

so if times were 19.18 and 5.35+, the minimum time is 19.18 (since the 5.37+ is excluded so there is only one value)

Alternatively if times are 8.35 and 14.00 then 8.35 is the minimum of the two values

 

[laffeg]

Hi
sorry for delay in replying

it is access sql so I have changed it to Like "*+"

I have also replaced my stripped out field "clock" with the original "field3"

however I am now getting syntax error. Can't see how TimeValue will work on the string field3 when it has . and + in it ??

new query is below - your help is appreciated.

SELECT DISTINCT Clockings2.field1, Clockings2.field2, TimeValue(Format([field3],"Short Time")) AS StartTime, TimeValue(Format(Replace((SELECT First(field3 FROM Clockings2 as Tmp WHERE field1=Clockings2.field1 and field2=Clockings2.field2 and Clockings2.field3<>Clockings2.field3),"+",""),"Short Time")) AS EndTime, TimeValue(Format(1+[Endtime]-[StartTime],"Short Time")) AS TotalTime
FROM Clockings2
WHERE (((Clockings2.field3)=(Select min(field3) From Clockings2 as Tmp WHERE field1=Clockings2.field1 and field2=Clockings2.field2 and field3 not like "*+")));

 

[CJ_London]

Here is the revised code using your table and field names - you should just be able to post into the sql window and it will run.

SELECT DISTINCT Clockings2.field1, Clockings2.field2,  TimeValue(Format([field3],"Short Time")) AS StartTime, TimeValue(Format(Replace((SELECT [COLOR=red]First(field3)[/COLOR] FROM Clockings2 as Tmp WHERE field1=Clockings2.field1 and field2=Clockings2.field2 an[COLOR=red]d[/COLOR][COLOR=red] field3[/COLOR]<>Clockings2.field3),"+",""),"Short Time")) AS EndTime, TimeValue(Format(1+[Endtime]-[StartTime],"Short Time")) AS TotalTime
FROM Clockings2
WHERE (((Clockings2.field3)=(Select min(field3) From Clockings2 as Tmp WHERE field1=Clockings2.field1 and field2=Clockings2.field2 and field3 not like "*+")))

I also note that you have changed your field names from your original post - I'm now assuming forename/surname are combined into field1, the date is field2 and time is field3

 

[laffeg]

hi

yes field1 is just the staff id ( numeric )

now getting "data type mismatch in criteria expression"

assume it is the min(field3) as field3 is a text field

bet you wish you hadn't replied now

 

[CJ_London]

No - field3 is expected to be a text field. Field1 and Field2 can be numeric, text or date types so sounds like you have not copied across quite right.


I've attached a small db based on what you have told me with the working query

 

[CJ_London]

sorry - forgot to attach