Multipart ID - Yes again.

[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,
Sanger LLC, M. Prop LLC, K. Prop LLC, T.C. Prop LLC
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
Collins Special Needs Trust
CA-230-0002-002 230 0002 002 California LA - Corona - Fitzpatrick
Country Roads South - Maichel Jeffrey Maichel and Carrie Maichel as Trustees
of the Jeffrey Louis Maichel and Carrie Hackman Maichel Family Trust
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!)

 

[Joker]

That looked better before I posted. Try this:

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


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

J> Contract ID Division Number Subdivision
J> Number Contract Number Region Division Subdivision Name
J> Seller's Name
J> CA-FRS-0001-001 320 0001 001 California Fresno - Wyman
J> Lafomarsino
J> Property Del Lago Development Company
J> CA-FRS-0002-001 320 0002 001 California Fresno - Wyman Salierno
J> Estates - Visalia Reynan and Bardis, Visalia LP
J> CA-FRS-0003-001 320 0003 001 California Fresno - Wyman West
J> Star/Crinklaw Assemblage I West Star Construction, Inc.
J> CA-FRS-0003-002 320 0003 002 California Fresno - Wyman West
J> Star/Crinklaw Assemblage II T.A.M. Prop LLC, K.A.T. Prop LLC, J.D.
J> Prop,
J> Sanger LLC, M. Prop LLC, K. Prop LLC, T.C. Prop LLC
J> CA-230-0001-001 230 0001 001 California LA - Corona -
J> Fitzpatrick Bell
J> Vinyards/Dry Creek Ranchette El Sol Vineyard Hill, LLC
J> CA-230-0002-001 230 0002 001 California LA - Corona -
J> Fitzpatrick
J> Country Roads South - Collins Brooks B Collins, Trustee of the J
J> Foster
J> Collins Special Needs Trust
J> CA-230-0002-002 230 0002 002 California LA - Corona -
J> Fitzpatrick
J> Country Roads South - Maichel Jeffrey Maichel and Carrie Maichel as
J> Trustees
J> of the Jeffrey Louis Maichel and Carrie Hackman Maichel Family Trust
J> CA-230-0003-001 230 0003 001 California LA - Corona -
J> Fitzpatrick Dry
J> Creek II Ranco Capital LLC
J> CA-230-0004-001 230 0004 001 California LA - Corona -
J> Fitzpatrick
J> Fontana-Hopkins Hopkins Real Estate Group
J> CA-300-0001-001 300 0001 001 California LA - Ventura - Coop
J> Acton 136
J> The Casden Company
J> CA-300-0002-001 300 0002 001 California LA - Ventura - Coop
J> Lancaster
J> 132 Benedict Canyon Villa LLC
J> CA-300-0002-002 300 0002 002 California LA - Ventura - Coop
J> Lancaster
J> 181 Antelope Valley Land LLC and Ariel 226 LLC


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

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

 

[John Vinson]

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

The solution is - DON'T DO IT THIS WAY.

You're storing data redundantly. Your "Contract ID" is not an atomic
field, violating "zeroth normal form". As such *the field should not
exist*.

You can generate your ContractID by concatenating the State, city
code, Division Number, and Subdivision Number. These four fields - AS
FOUR FIELDS - could constitute a multifield Primary Key; if you want
to concatenatate them for display, do so in a query expression.


John W. Vinson[MVP]