Tagging the first occurrence of a record

[Cryptographic_ICE]

Hello,

I would like to "Tag" the first occurrence of a Job_number. I have an
access 2003 database with columns that look similar to:

Name Job_Number Record_Number First_Time
John Doe 99999 1 on/off (this box should be checked)
John Doe 99999 2 on/off
John Doe 99999 3 on/off
John Doe 99999 4 on/off
Jane Smith 99998 5 on/off (this box should be checked)
Jane Smith 99998 6 on/off


As you can see the job number is not unique and may appear many times.
What I need to do is find the first occurrence of this job number and
check the box in the First_Time field. I was thinking it would look
like this

1.) Find all similar job numbers (for this example 99999 or 99998)
2.) Then find out which one has the lowest record number (unique to
each row)
3.) Check the box under the First_time field.

I'm not sure the best way to do this. any help would be appreciated.
Thanks in advance!

 

[Jeff Boyce]

It sounds like you already have all the info you need to determine each
Job_Number's "first" Record_Number. It would not be a good idea to add
another field to say "this one!" (as doing so will require also adding code
to keep all the data synchronized after changes).

Instead, consider creating a Totals query that groups by Job_Number and
finds the Minimum Report_Number.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

You can use an update query with a subquery which identifies the lowest
record number for each job number:

UPDATE YourTable AS T1
SET First_Time = TRUE
WHERE Record_Number =
(SELECT MIN(Record_Number)
FROM YourTable AS T2
WHERE T2. Job_Number = T1. Job_Number);

However, you don't need have a First_Time column at all, and moreover should
not do so, as this creates redundancy in the table which leaves the door open
to update anomalies. You can always return the relevant rows using a
straightforward SELECT query equivalent of the above:

SELECT *
FROM YourTable AS T1
WHERE Record_Number =
(SELECT MIN(Record_Number)
FROM YourTable AS T2
WHERE T2. Job_Number = T1. Job_Number);

Ken Sheridan
Stafford, England

 

[Cryptographic_ICE]

We have the First_time field for compatibility with other programs
(like SharePoint 2003 Pivot Chart Web Parts). Our database is setup to
check that box when someone creates the first job. However we need to
update the records from last year. I don't know much about Access.
How do I create an update query with the code you have given? thanx