I
Ian
I have best/closest match query problem.
I have a table of repairs stages which has lots of stages for any given
job number. I have used a query to give me a unique list of jobs as
follows
qryUniqRepDetail
JobNo,ReportedDate,ReportedTime,JobValue
the original table has lots of other fields in but those 4 are all that
really matter the rest is padding
I also have a series of CSV files that I am being sent by contractors
that indicates when the job is complete and/or how much they want paying.
The problem I have is they have sent files with duplicates in for many
reasons (that don't matter but just make me to want to murder someone
because it really should not be happening but this is the real world and
it does).
I have found some VBA code and adapted it to my needs that allows me to
import the data in all of these many small CSV files into one Stages
table. Again with quite a few fields in but these are the only ones that
matter
tblStageImports
ID,JobNo,CompDate,CompTime,JobValue
For which I have the following query
SELECT si.ID, si.JobNo, si.CompDate, si.CompTime, qrd.JobValue-
si.JobValue AS AbsDiff
FROM qryUniqRepDetail AS qrd INNER JOIN tblStageImports AS si ON
qrd.JobNo = si.JobNo
WHERE ([qrd].[JobValue]-[si].[JobValue] Is Not Null)
ORDER BY si.JobNo, [qrd].[JobValue]-[si].[JobValue], si.CompDate,
si.CompTime;
Which gives me data like this
ID JobNo CompDate CompTime AbsDiff
7059 AR0009233 22/12/2008 12:09:00 £0.00
3065 AR0009233 22/12/2008 09:58:33 £0.01
4396 AR0009233 22/12/2008 15:45:00 £0.05
7060 RS0010195 09/01/2009 12:16:00 £0.00
3066 RS0010195 09/01/2009 16:10:53 £0.01
4397 RS0010195 09/01/2009 17:00:00 £0.01
7061 BB0010196 09/01/2009 12:16:00 £0.00
3067 BB0010196 09/01/2009 16:10:53 £0.00
What I need is the JobNo and the ID of the job where the ABSDiff is the
lowest and CompDate and CompTime are the first after ReportedDate and
ReportedTime.
I have tried grouping them with First, Last, Max and Min and probably
several other combinations to get it to give me the correct job and ID
number but to no avail. I have a sneaky suspicion that I need a subquery
but not sure how to go about it.
For istance if in the qryUniqRepDetail query the job AR0009233 had a
ReportedDate of 20/12/2008 and time of 09:34:00 then the ID I would be
looking for is 7059. If however job BB0010196 had a reported date of
09/01/2009 and time of 12:45:00 then ID 7061 would be incorrect and I
need 3067 returned. Please don't ask why and how this can happen as I
will be all day ranting about it
suffice to say it's not what should
be happening but I have to deal with it for now.
If anyone can help I would be very grateful or maybe you know of some
resource on the web where this has already be answered and can point me
to it (even something similar as I am pretty sure I can adapt something
close) that would be just as good. I have searched using google but must
not be putting the correct search terms in.
Thanks. Ian.
I have a table of repairs stages which has lots of stages for any given
job number. I have used a query to give me a unique list of jobs as
follows
qryUniqRepDetail
JobNo,ReportedDate,ReportedTime,JobValue
the original table has lots of other fields in but those 4 are all that
really matter the rest is padding
I also have a series of CSV files that I am being sent by contractors
that indicates when the job is complete and/or how much they want paying.
The problem I have is they have sent files with duplicates in for many
reasons (that don't matter but just make me to want to murder someone
because it really should not be happening but this is the real world and
it does).
I have found some VBA code and adapted it to my needs that allows me to
import the data in all of these many small CSV files into one Stages
table. Again with quite a few fields in but these are the only ones that
matter
tblStageImports
ID,JobNo,CompDate,CompTime,JobValue
For which I have the following query
SELECT si.ID, si.JobNo, si.CompDate, si.CompTime, qrd.JobValue-
si.JobValue AS AbsDiff
FROM qryUniqRepDetail AS qrd INNER JOIN tblStageImports AS si ON
qrd.JobNo = si.JobNo
WHERE ([qrd].[JobValue]-[si].[JobValue] Is Not Null)
ORDER BY si.JobNo, [qrd].[JobValue]-[si].[JobValue], si.CompDate,
si.CompTime;
Which gives me data like this
ID JobNo CompDate CompTime AbsDiff
7059 AR0009233 22/12/2008 12:09:00 £0.00
3065 AR0009233 22/12/2008 09:58:33 £0.01
4396 AR0009233 22/12/2008 15:45:00 £0.05
7060 RS0010195 09/01/2009 12:16:00 £0.00
3066 RS0010195 09/01/2009 16:10:53 £0.01
4397 RS0010195 09/01/2009 17:00:00 £0.01
7061 BB0010196 09/01/2009 12:16:00 £0.00
3067 BB0010196 09/01/2009 16:10:53 £0.00
What I need is the JobNo and the ID of the job where the ABSDiff is the
lowest and CompDate and CompTime are the first after ReportedDate and
ReportedTime.
I have tried grouping them with First, Last, Max and Min and probably
several other combinations to get it to give me the correct job and ID
number but to no avail. I have a sneaky suspicion that I need a subquery
but not sure how to go about it.
For istance if in the qryUniqRepDetail query the job AR0009233 had a
ReportedDate of 20/12/2008 and time of 09:34:00 then the ID I would be
looking for is 7059. If however job BB0010196 had a reported date of
09/01/2009 and time of 12:45:00 then ID 7061 would be incorrect and I
need 3067 returned. Please don't ask why and how this can happen as I
will be all day ranting about it
suffice to say it's not what shouldbe happening but I have to deal with it for now.
If anyone can help I would be very grateful or maybe you know of some
resource on the web where this has already be answered and can point me
to it (even something similar as I am pretty sure I can adapt something
close) that would be just as good. I have searched using google but must
not be putting the correct search terms in.
Thanks. Ian.