Multipart ID

[Joker]

I still need help on this one, I'm just not sure how to approach it. I
designed an identifier as part of a re-filing effort. The design is:

RegionID - DivisionID - SubdivisionID - ContractID (i.e. CA-500-0001-0001)

The reason behind the design was to sort the files first by region then by
division. The next part was little tricky in Excel, in each subdivision
there might be one contract or many. If there was just one, then the
project number would go up by one and the contract number would stay at
0001. If there were multiple contracts that made up a subdivision, then the
project would go up by one then remain the same and the contract number
would go up by one for each new contract. This way, you could group what
are called "assemblages" keeping all the different contracts together that
are connected to that particular subdivision. In Excel, I had a string of
if then statements that was based on the first few letters of the
subdivision and the number that pre- and proceeded the number. It was not
perfect but worked to some degree. Is there any way to somewhat automate
this identifier even if the contracts are entered at different times?

 

[Douglas J. Steele]

What you're suggesting is normally referred to as a "smart key", and that's
not a compliment in this case.

It's actually a violation of database normalization principles to combine
multiple fields into one. What you should really do is keep the four fields
separate (Access will allow you to have up to 10 fields in a single index).
If you really need the 4 fields to be concatenated for display purposes, you
can do that as a computed field in a query, and use the query wherever you
would otherwise have used the table.

You can use the DMax function (with "[RegionID] = '" & txtRegionId & "' AND
[Division ID] = '" & txtDivisionId & "' AND [SubdivisionID] = '" &
txtSubdivisionId & "'" as the where component) to determine the highest
Contract ID used so far and increment it to get the appropriate number to
store. You'd use this in the BeforeUpdate event of the form.

 

[Joker]

I'm a little lost on what you suggested on the second part. It is not
necessary for the entire ID to be stored in one field. What I am having the
most trouble with is how to increase the project number or the contract
number. The project number should continue to increase as long as the
subdivisions are different. If they are the same, that indicates an
assemblage and then the contract number should increase. The only way I can
think of is to just track this separately in Excel and them manually update
Access, but thi requires data to be entered twice. Please any ideas would
help at this point.

James

DJS> What you're suggesting is normally referred to as a "smart key",
DJS> and that's
DJS> not a compliment in this case.

DJS> It's actually a violation of database normalization principles to
DJS> combine
DJS> multiple fields into one. What you should really do is keep the
DJS> four fields
DJS> separate (Access will allow you to have up to 10 fields in a single
DJS> index).
DJS> If you really need the 4 fields to be concatenated for display
DJS> purposes, you
DJS> can do that as a computed field in a query, and use the query
DJS> wherever you
DJS> would otherwise have used the table.

DJS> You can use the DMax function (with "[RegionID] = '" & txtRegionId
DJS> & "' AND
DJS> [Division ID] = '" & txtDivisionId & "' AND [SubdivisionID] = '" &
DJS> txtSubdivisionId & "'" as the where component) to determine the
DJS> highest
DJS> Contract ID used so far and increment it to get the appropriate
DJS> number to
DJS> store. You'd use this in the BeforeUpdate event of the form.

DJS> --
DJS> Doug Steele, Microsoft Access MVP
DJS>
DJS> (no e-mails, please!)

 

[Douglas J. Steele]

As I suggested, in the form's BeforeUpdate event, try using the DMax
function to determine the largest value that's currently stored for
ContractID for the given values of RegionID, DivisionID and SubdivisionID.
DMax will either return Null (if there's no ContractID for that combination)
or a number. If it's Null, you'll want ContractID to be 1. If it's a number,
you want one more than that number.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm a little lost on what you suggested on the second part. It is not
necessary for the entire ID to be stored in one field. What I am having
the most trouble with is how to increase the project number or the
contract number. The project number should continue to increase as long
as the subdivisions are different. If they are the same, that indicates
an assemblage and then the contract number should increase. The only way
I can think of is to just track this separately in Excel and them manually
update Access, but thi requires data to be entered twice. Please any
ideas would help at this point.

James

DJS> What you're suggesting is normally referred to as a "smart key",
DJS> and that's
DJS> not a compliment in this case.

DJS> It's actually a violation of database normalization principles to
DJS> combine
DJS> multiple fields into one. What you should really do is keep the
DJS> four fields
DJS> separate (Access will allow you to have up to 10 fields in a single
DJS> index).
DJS> If you really need the 4 fields to be concatenated for display
DJS> purposes, you
DJS> can do that as a computed field in a query, and use the query
DJS> wherever you
DJS> would otherwise have used the table.

DJS> You can use the DMax function (with "[RegionID] = '" & txtRegionId
DJS> & "' AND
DJS> [Division ID] = '" & txtDivisionId & "' AND [SubdivisionID] = '" &
DJS> txtSubdivisionId & "'" as the where component) to determine the
DJS> highest
DJS> Contract ID used so far and increment it to get the appropriate
DJS> number to
DJS> store. You'd use this in the BeforeUpdate event of the form.

DJS> --
DJS> Doug Steele, Microsoft Access MVP
DJS>
DJS> (no e-mails, please!)

 

[Joker]

I have not given up on this idea, but I think it needs to be modified. I
want the Subdivision to be modified automatically as well, I'm just not sure
how to make it happen. Here is some sample data:

Contract ID\Division Number\Subdivision Number\Contract
Number\Region\Division\Subdivision Name\Seller's Name

CA-FRS-0001-001\320\0001\001\California\Fresno - Wyman\Lafomarsino
Property\Del Lago Development Company

CA-FRS-0002-001\320\0002\001\California\Fresno - Wyman\Salierno Estates -
Visalia\Reynan and Bardis, Visalia LP

CA-FRS-0003-001\320\0003\001\California\Fresno - Wyman\West Star/Crinklaw
Assemblage I\West Star Construction, Inc.

CA-FRS-0003-002\320\0003\002\California\Fresno - Wyman\West Star/Crinklaw
Assemblage II\T.A.M. Prop LLC, K.A.T. Prop LLC, J.D. Prop,

CA-230-0001-001\230\0001\001\California\LA - Corona - Fitzpatrick\Bell
Vinyards/Dry Creek Ranchette\El Sol Vineyard Hill, LLC

CA-230-0002-001\230\0002\001\California\LA - Corona - Fitzpatrick\Country
Roads South - Collins\Brooks B Collins, Trustee of the J Foster

CA-230-0002-002\230\0002\002\California\LA - Corona - Fitzpatrick\Country
Roads South - Maichel\Jeffrey Maichel and Carrie Maichel as

CA-230-0003-001\230\0003\001\California\LA - Corona - Fitzpatrick\Dry Creek
II\Ranco Capital LLC

CA-230-0004-001\230\0004\001\California\LA - Corona -
Fitzpatrick\Fontana-Hopkins\Hopkins Real Estate Group

CA-300-0001-001\300\0001\001\California\LA - Ventura - Coop\Acton 136\The
Casden Company

CA-300-0002-001\300\0002\001\California\LA - Ventura - Coop\Lancaster
132\Benedict Canyon Villa LLC

CA-300-0002-002\300\0002\002\California\LA - Ventura - Coop\Lancaster
181\Antelope Valley Land LLC and Ariel 226 LLC


The way it works in Excel is The Region and Division are assigned, then the
project number starts at one for each new division and counts up until it
encounters subdivisions with similar names at which point the contract
number increases. The problem with this is sometimes the subdivisions are
very similar, but are not the same. Anyway, is there a way to duplicate
this in Access? I have a table for the Region and Division, so it seems to
me I would need a table for the Subdivision and the seller called Contract.
How could I make this work in Access? Do I need a separate form for
entering the data for subdivision? Can I some have the Subdivision name be
filled in by combo box and/or manual entry? If the combo box idea works, I
would want to limit it to just the subdivisions in the division. Good
grief. I'm an accountant not a database guy, now I know why.

James
DJS> As I suggested, in the form's BeforeUpdate event, try using the
DJS> DMax
DJS> function to determine the largest value that's currently stored for
DJS> ContractID for the given values of RegionID, DivisionID and
DJS> SubdivisionID.
DJS> DMax will either return Null (if there's no ContractID for that
DJS> combination)
DJS> or a number. If it's Null, you'll want ContractID to be 1. If it's
DJS> a number,
DJS> you want one more than that number.

DJS> --
DJS> Doug Steele, Microsoft Access MVP
DJS>
DJS> (no e-mails, please!)

I'm a little lost on what you suggested on the second part. It is not
necessary for the entire ID to be stored in one field. What I am
having the most trouble with is how to increase the project number or the
contract number. The project number should continue to increase as
long as the subdivisions are different. If they are the same, that
indicates an assemblage and then the contract number should increase.
The only
way I can think of is to just track this separately in Excel and them
manually update Access, but thi requires data to be entered twice.
Please any ideas would help at this point.
James
DJS> What you're suggesting is normally referred to as a "smart key",
DJS> and that's
DJS> not a compliment in this case.
DJS> It's actually a violation of database normalization principles to
DJS> combine
DJS> multiple fields into one. What you should really do is keep the
DJS> four fields
DJS> separate (Access will allow you to have up to 10 fields in a
single
DJS> index).
DJS> If you really need the 4 fields to be concatenated for display
DJS> purposes, you
DJS> can do that as a computed field in a query, and use the query
DJS> wherever you
DJS> would otherwise have used the table.
DJS> You can use the DMax function (with "[RegionID] = '" &
txtRegionId
DJS> & "' AND
DJS> [Division ID] = '" & txtDivisionId & "' AND [SubdivisionID] = '"
&
DJS> txtSubdivisionId & "'" as the where component) to determine the
DJS> highest
DJS> Contract ID used so far and increment it to get the appropriate
DJS> number to
DJS> store. You'd use this in the BeforeUpdate event of the form.
DJS> --
DJS> Doug Steele, Microsoft Access MVP
DJS>
DJS> (no e-mails, please!)