concatenation of a field value and a counter

[Dr Poot]

Hello, I would appreciate any help on trying to do this. To keep it simple,
the problem involves 2 tables, tblCompany and tblJob [one to many,
respectively]. tblCompany has a 3 letter code as a field. tblJob has a
field (named Job_number) which I want to take the value of this 3 letter code
concatenated with a counter (min 1, max 999) that increments by 1 for every
record in Job under each Company record (I don't think autonumber would be an
appropriate function to use here, perhaps Dmax?). So in the end, Job_number
has values like {XXX/001, XXX/002, XXX/003, YYY,001}. I can do this easily
in C++, although I just don't know VB and access well enough. To generate
this counter, I'm also thinking about introducing a count field which returns
the number of existing Job_numbers + 1, not sure which way to go though.
Much appreciated,

- Matt

 

[Stefan Hoffmann]

hi,

Hello, I would appreciate any help on trying to do this. To keep it simple,
the problem involves 2 tables, tblCompany and tblJob [one to many,
respectively]. tblCompany has a 3 letter code as a field. tblJob has a
field (named Job_number) which I want to take the value of this 3 letter code
concatenated with a counter (min 1, max 999) that increments by 1 for every
record in Job under each Company record (I don't think autonumber would be an
appropriate function to use here, perhaps Dmax?). So in the end, Job_number
has values like {XXX/001, XXX/002, XXX/003, YYY,001}.

How have you modeled the relationship between your two tables?

mfG
--> stefan <--

 

[Klatuu]

If you have a one to many between company and job, I would guess the 3 letter
company code would be the primary key of the company table and a foreign key
in the job table. If this is not the case and you are relatiing the tables
using an autonumber field in company and a long field in job to maintain your
relation, you don't need the company code in the job table.

In either case, don't concatentate the two values into one field. Keep them
separately. If you want to display the job number to a user in a form or
report, do your concatenation then.

If you want sequential job numering by company, here is a function that will
return the highest current number for the current company and add one to it
to create the next number. Be aware there may be issues here if this is a
multi user environment, and you may have to address them to ensure there are
not gaps and that the same number doesn't get assigned twice.

Private Function GetNextJobNumber(strCompany as String) As Long

GetNextJobNumber = Nz(DMax("[job_number]",_
"tblJob", "[company_code] = '" & strCompany & "'"),0) + 1

End Function

Now, if you want to concatenate them in the format you describe:

strWholeThing = strCompany & "/" & Format(GetNextJobNumber), "000")

 

[Dr Poot]

Thanks for your feedback, I will now explain the problem in the terms you
have used. My table of relationships are as follows (primary keys listed):

one Company_ID - many Job_ID
one Job_ID - many Tape_ID
one Tape_ID - many Segment_ID
where all table names are the ID name but with "tbl" in front, and without
"_ID". All ID's are autonumber and there are four tables. The 3 letter
company code I was referring to before is not the primary key, and only
appears in tblCompany. The form that I use (called frmCompany) is one with
nested subforms, where the nesting is ordered as described above (hopefully
that makes sense, or is obvious).

So what I would like out of this is when a new record is added, that once
the 3 digit code is entered AND the user clicks to the first subform
(frmSubJob - not sure what the convention is for labelling subforms, but I
hope mine is understood), the Job code becomes: "[three digit code]/[number
of jobs under this company + 1]"

I designed a simple query to come up with the number of Jobs under each
company - but firstly I'm not sure how to concatenate this with the 3 letter
company code and secondly to set it as the value for txtJobNumber in
frmSubJob. Thirdly, I'm not sure if this is even the most appropriate way of
getting this number. The aforementioned query is pasted below.

SELECT tblCompany.Company_ID, Count(tblJob.Job_ID) AS CountOfJob_ID
FROM tblCompany INNER JOIN tblJob ON tblCompany.Company_ID = tblJob.Company_ID
GROUP BY tblCompany.Company_ID
HAVING (((tblCompany.Company_ID)=[Forms]![frmCompany]![Company_ID]));

Many thanks,

- Matthew

If you have a one to many between company and job, I would guess the 3 letter
company code would be the primary key of the company table and a foreign key
in the job table. If this is not the case and you are relatiing the tables
using an autonumber field in company and a long field in job to maintain your
relation, you don't need the company code in the job table.

In either case, don't concatentate the two values into one field. Keep them
separately. If you want to display the job number to a user in a form or
report, do your concatenation then.

If you want sequential job numering by company, here is a function that will
return the highest current number for the current company and add one to it
to create the next number. Be aware there may be issues here if this is a
multi user environment, and you may have to address them to ensure there are
not gaps and that the same number doesn't get assigned twice.

Private Function GetNextJobNumber(strCompany as String) As Long

GetNextJobNumber = Nz(DMax("[job_number]",_
"tblJob", "[company_code] = '" & strCompany & "'"),0) + 1

End Function

Now, if you want to concatenate them in the format you describe:

strWholeThing = strCompany & "/" & Format(GetNextJobNumber), "000")

Hello, I would appreciate any help on trying to do this. To keep it simple,
the problem involves 2 tables, tblCompany and tblJob [one to many,
respectively]. tblCompany has a 3 letter code as a field. tblJob has a
field (named Job_number) which I want to take the value of this 3 letter code
concatenated with a counter (min 1, max 999) that increments by 1 for every
record in Job under each Company record (I don't think autonumber would be an
appropriate function to use here, perhaps Dmax?). So in the end, Job_number
has values like {XXX/001, XXX/002, XXX/003, YYY,001}. I can do this easily
in C++, although I just don't know VB and access well enough. To generate
this counter, I'm also thinking about introducing a count field which returns
the number of existing Job_numbers + 1, not sure which way to go though.
Much appreciated,

- Matt