Fantastic!!!! Thankyou very much for your help.
Mark
"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
news:401a62e5$0$1757$5a62ac22@freenews.iinet.net.a u...[color=blue]
> Hi Mark.
>
> It makes sense to import the text file into an Access table.
> Combine the date and time into one Date/Time field, and index it.
> Then the subquery should be able to find the correct prior date/time value
> from the index (instead of having to scan the 18k records), so it should
> perform acceptably.
>
> For more info on how to build the subquery, see:
> How to Compare a Field to a Field in a Prior Record
> at:
>
>[/color]
http://support.microsoft.com/default...roduct=acc2000[color=blue]
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users -
http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Mark Reed" <mark.reed75@ntlworld.com> wrote in message
> news:jXrSb.125$jk4.15@newsfep3-gui.server.ntli.net...[color=green]
> > Thanks for that Allen,
> > I've tried what you have suggest but I think I am doing something[/color]
> wrong.[color=green]
> > I'll give you a little more background to my problem as I think that may[/color]
> be[color=green]
> > the cause.
> >
> > The database gets it's inputs from a txt file recovered from the works
> > management system when I run a SQL within it. Towards the end of shift,
> > there could be anything up to 18,000 entries as the WMS SQL asks for[/color][/color]
every[color=blue][color=green]
> > single scan during a set shift. The results of that SQL are then pasted[/color]
> into[color=green]
> > a single field of my Access database.
> > Example of txt result is:
> >
> > 04-DEC-2003 22:21:09 Chowdry, Halim 06313 988558 -6 N5608D[/color][/color]
CCS[color=blue][color=green]
> > LOCA 1 H Picking 14310 725997 Conv
> > Conv -6 04.12.03H 3
> >
> > 04-DEC-2003 22:22:16 Phillips, Lillian 46517H 988567 -1 N6909A[/color][/color]
CCS[color=blue][color=green]
> > LOCA 1 H Picking 14362 681311 Conv
> > Conv -1 04.12.03H 3
> >
> > My first query ([decode1]) breaks the txt file into relevant fields. The
> > second ([decode2]) then changes the some of the fields into values and[/color]
> time[color=green]
> > rather than just text.
> >
> > SELECT DISTINCT DateValue([decode].[Date]) AS actdate,
> > TimeValue([Decode]![Time]) AS acttime, [Name query].[First Name], [Name
> > query].Surname, Decode.Operator, Decode.[Pic Route], Val([Decode]![QTY])[/color]
> AS[color=green]
> > Qty, Val([Decode]![IP Qty]) AS [IP Qty], Decode.Location, Decode.[To
> > Location], Decode.Cartons, Decode.PT, Decode.Job, Val([Decode]![RDT]) AS
> > RDT, Val([Decode]![Trolley]) AS Trolley, Decode.Fromst, Decode.Tost,
> > Decode.Schedule, Decode.Wv
> > FROM [Name query] INNER JOIN Decode ON [Name query].Login =[/color]
> Decode.Operator[color=green]
> > GROUP BY DateValue([decode].[Date]), TimeValue([Decode]![Time]), [Name
> > query].[First Name], [Name query].Surname, Decode.Operator, Decode.[Pic
> > Route], Val([Decode]![QTY]), Val([Decode]![IP Qty]), Decode.Location,
> > Decode.[To Location], Decode.Cartons, Decode.PT, Decode.Job,
> > Val([Decode]![RDT]), Val([Decode]![Trolley]), Decode.Fromst,[/color][/color]
Decode.Tost,[color=blue][color=green]
> > Decode.Schedule, Decode.Wv
> > WITH OWNERACCESS OPTION;
> >
> > All of the other querys in the DB are based on the decode2 query.
> >
> > How would you write the query, given the above information to show the[/color]
> time[color=green]
> > difference between each record?
> >
> > I really appreciate your help with this.
> >
> > Regards,
> > Mark
> >
> >
> >
> > "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
> > news:401a0ad2$0$1736$5a62ac22@freenews.iinet.net.a u...[color=darkred]
> > > Use a subquery to get the most recent login date and time for the same[/color]
> > login[color=darkred]
> > > number.
> > >
> > > That's a matter of typing something like this into the Field row of[/color][/color][/color]
your[color=blue][color=green][color=darkred]
> > > query to create the calculated field:
> > >
> > > Seconds: DateDiff("s", (MyTable.Date + MyTable.Time),
> > > (SELECT TOP 1 (Dupe.Date + Dupe.Time) AS PriorTime
> > > FROM MyTable AS Dupe
> > > WHERE ((Dupe.Login = MyTable.Login) AND ((Dupe.Date + Dupe.Time) <
> > > (Mytable.Date + MyTable.Time)))
> > > ORDER BY Dupe.Date DESC; Dupe.Time DESC ) )
> > >
> > > This would be considerably more efficient if the date and time were[/color][/color]
> stored[color=green][color=darkred]
> > > in the one field. Hopefully your fields are not actually called Date[/color][/color][/color]
and[color=blue][color=green][color=darkred]
> > > Time as theser are reserved words in VBA.
> > >
> > > Consider adding the primary key to the ORDER BY clause as well, so[/color][/color]
> Access[color=green][color=darkred]
> > > can distinguish between 2 records that have exactly the same date and[/color]
> > time.[color=darkred]
> > >
> > > If you want the time as a string instead of a number of seconds, see:
> > >
http://members.rogers.com/douglas.j....iff2Dates.html
> > >
> > >
> > > "Mark Reed" <mark.reed75@ntlworld.com> wrote in message
> > > news:hNmSb.11$jk4.0@newsfep3-gui.server.ntli.net...
> > > > Hi all,
> > > > I have a query (query1) which shows scan date, scan time &[/color][/color]
> operator.[color=green][color=darkred]
> > > One
> > > > scan = 1 record. What I want to do is create a report based on query[/color][/color][/color]
2[color=blue][color=green][color=darkred]
> > > from
> > > > query1 which shows all the scans AND the difference between each[/color][/color][/color]
scan.[color=blue][color=green][color=darkred]
> > > > Something like below. I have spent days on this and can't figure it[/color][/color]
> out.[color=green][color=darkred]
> > > >
> > > > Date Time Login Diff
> > > > 04-Dec-03 23:33:12 27478
> > > > 04-Dec-03 23:33:38 27478 00:00:26
> > > > 04-Dec-03 23:34:00 27478 00:00:22
> > > > 04-Dec-03 23:34:21 27478 00:00:21
> > > > 04-Dec-03 23:35:11 27478 00:00:50[/color][/color]
>
>[/color]
