Assign a count?

[Brian]

I have a list of calls done by technician in chronological order. I would
like to assign a count by call so it shows this is job 1, job 2, job 3, etc.
My 4 fields are date, job ID, techician, and time started.

Any ideas?

Thank you in advance,
Brian

 

[KARL DEWEY]

Your date and time need to be a single field.
This query works for the combined date time --

SELECT Q.techician, Q.Date, [job ID], (SELECT COUNT(*) FROM Brian_A Q1
WHERE Q1.[techician] = Q.[techician]
AND Q1.Date < Q.Date)+1 AS Rank
FROM Brian_A AS Q
ORDER BY Q.techician, Q.Date, [job ID];

If your date and time fields are datatype DateTime then you can use this --


SELECT Q.techician, Q.Date, Q.[time started], [job ID], (SELECT COUNT(*)
FROM Brian_A Q1
WHERE Q1.[techician] = Q.[techician]
AND Q1.Date+Q1.[time started] < Q.Date+Q.[time started])+1 AS Rank
FROM Brian_A AS Q
ORDER BY Q.techician, Q.Date, [job ID];

If those fields are not DateTime then it depends a whole lot of the type and
structure of the contents.

 

[Brian]

Karl,
I will try that shortly. My fields are date and time. Just so we're clear,
your statement would allow me to generate this:

Date1 Tech1 JobID Job1
Date1 Tech1 JobID Job2
Date1 Tech1 JobID Job3
Date1 Tech2 JobID Job1
Date1 Tech2 JobID Job2
Date2 Tech1 JobID Job1
etc.

I'll report back with results.

Thanks again,
Brian

Your date and time need to be a single field.
This query works for the combined date time --

SELECT Q.techician, Q.Date, [job ID], (SELECT COUNT(*) FROM Brian_A Q1
WHERE Q1.[techician] = Q.[techician]
AND Q1.Date < Q.Date)+1 AS Rank
FROM Brian_A AS Q
ORDER BY Q.techician, Q.Date, [job ID];

If your date and time fields are datatype DateTime then you can use this --


SELECT Q.techician, Q.Date, Q.[time started], [job ID], (SELECT COUNT(*)
FROM Brian_A Q1
WHERE Q1.[techician] = Q.[techician]
AND Q1.Date+Q1.[time started] < Q.Date+Q.[time started])+1 AS Rank
FROM Brian_A AS Q
ORDER BY Q.techician, Q.Date, [job ID];

If those fields are not DateTime then it depends a whole lot of the type and
structure of the contents.
--
KARL DEWEY
Build a little - Test a little

I have a list of calls done by technician in chronological order. I would
like to assign a count by call so it shows this is job 1, job 2, job 3, etc.
My 4 fields are date, job ID, techician, and time started.

Any ideas?

Thank you in advance,
Brian

 

[Brian]

I am getting a data mismatch in the criteria expression. Here is the
statement I am using....
SELECT q.wruid, q.Date, q.begin_sec, [wrwoid], (SELECT COUNT(*)
FROM wfm_assign_count Q1
WHERE q1.wruid = q.wruid
AND q1.Date+q1.begin_sec < q.Date+q.begin_sec)+1 AS Rank
FROM wfm_assign_count AS Q
ORDER BY q.wruid, q.Date, [wrwoid];

As for the date and time....I can do it either way. I broke them out from a
single date/time field into separate fields for date and time in the
wfm_assign_count query.

Karl,
I will try that shortly. My fields are date and time. Just so we're clear,
your statement would allow me to generate this:

Date1 Tech1 JobID Job1
Date1 Tech1 JobID Job2
Date1 Tech1 JobID Job3
Date1 Tech2 JobID Job1
Date1 Tech2 JobID Job2
Date2 Tech1 JobID Job1
etc.

I'll report back with results.

Thanks again,
Brian

Your date and time need to be a single field.
This query works for the combined date time --

SELECT Q.techician, Q.Date, [job ID], (SELECT COUNT(*) FROM Brian_A Q1
WHERE Q1.[techician] = Q.[techician]
AND Q1.Date < Q.Date)+1 AS Rank
FROM Brian_A AS Q
ORDER BY Q.techician, Q.Date, [job ID];

If your date and time fields are datatype DateTime then you can use this --


SELECT Q.techician, Q.Date, Q.[time started], [job ID], (SELECT COUNT(*)
FROM Brian_A Q1
WHERE Q1.[techician] = Q.[techician]
AND Q1.Date+Q1.[time started] < Q.Date+Q.[time started])+1 AS Rank
FROM Brian_A AS Q
ORDER BY Q.techician, Q.Date, [job ID];

If those fields are not DateTime then it depends a whole lot of the type and
structure of the contents.
--
KARL DEWEY
Build a little - Test a little

I have a list of calls done by technician in chronological order. I would
like to assign a count by call so it shows this is job 1, job 2, job 3, etc.
My 4 fields are date, job ID, techician, and time started.

Any ideas?

Thank you in advance,
Brian

 

[Brian]

Works magically using your first statement. Just very very slow on as little
as 150 records. Thanks so much for your help...now I'm hooked.

I am getting a data mismatch in the criteria expression. Here is the
statement I am using....
SELECT q.wruid, q.Date, q.begin_sec, [wrwoid], (SELECT COUNT(*)
FROM wfm_assign_count Q1
WHERE q1.wruid = q.wruid
AND q1.Date+q1.begin_sec < q.Date+q.begin_sec)+1 AS Rank
FROM wfm_assign_count AS Q
ORDER BY q.wruid, q.Date, [wrwoid];

As for the date and time....I can do it either way. I broke them out from a
single date/time field into separate fields for date and time in the
wfm_assign_count query.

Karl,
I will try that shortly. My fields are date and time. Just so we're clear,
your statement would allow me to generate this:

Date1 Tech1 JobID Job1
Date1 Tech1 JobID Job2
Date1 Tech1 JobID Job3
Date1 Tech2 JobID Job1
Date1 Tech2 JobID Job2
Date2 Tech1 JobID Job1
etc.

I'll report back with results.

Thanks again,
Brian

Your date and time need to be a single field.
This query works for the combined date time --

SELECT Q.techician, Q.Date, [job ID], (SELECT COUNT(*) FROM Brian_A Q1
WHERE Q1.[techician] = Q.[techician]
AND Q1.Date < Q.Date)+1 AS Rank
FROM Brian_A AS Q
ORDER BY Q.techician, Q.Date, [job ID];

If your date and time fields are datatype DateTime then you can use this --


SELECT Q.techician, Q.Date, Q.[time started], [job ID], (SELECT COUNT(*)
FROM Brian_A Q1
WHERE Q1.[techician] = Q.[techician]
AND Q1.Date+Q1.[time started] < Q.Date+Q.[time started])+1 AS Rank
FROM Brian_A AS Q
ORDER BY Q.techician, Q.Date, [job ID];

If those fields are not DateTime then it depends a whole lot of the type and
structure of the contents.
--
KARL DEWEY
Build a little - Test a little


:

I have a list of calls done by technician in chronological order. I would
like to assign a count by call so it shows this is job 1, job 2, job 3, etc.
My 4 fields are date, job ID, techician, and time started.

Any ideas?

Thank you in advance,
Brian