Tagging first occurrence

  • Thread starter Cryptographic_ICE
  • Start date
C

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!
 
J

Jeff Boyce

I believe you posted this in another newsgroup. Did you check there for
responses?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
C

Cryptographic_ICE

I was given the formula

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);

But with no explanation of how to use it. I tried to build a query with
it but I did not work. I'm not sure if I'm doing it right.
 
J

Jeff Boyce

My suggestion, elsewhere, was to not bother updating. Use a query to find
them instead.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top