Using Link Tables

[Joker]

OK, I think that using a link table may be the better way to go for creating
these Multipart IDs that I want. If I link the database with Excel i can
get it to run the complex If/Then statements I designed to create these
things in the first place. Can anyone suggest the best way to link them so
that I have the least amount of input? I would like to fill in one form,
sort and update the Excel file and have it return the new ID. Will this
work or will the universe implode for suggesting such a thing?

James



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.

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.

 

[Douglas J. Steele]

The best way is to do it in Access!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

OK, I think that using a link table may be the better way to go for
creating these Multipart IDs that I want. If I link the database with
Excel i can get it to run the complex If/Then statements I designed to
create these things in the first place. Can anyone suggest the best way
to link them so that I have the least amount of input? I would like to
fill in one form, sort and update the Excel file and have it return the
new ID. Will this work or will the universe implode for suggesting such a
thing?

James



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.

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]

No kidding! The link table comes with all kinds of trouble. I think I see
where I went wrong. I have been trying to do this too much like Excel. I
did not have a separate table for the Sub Division or the Contract. I'm
going to have to rethink this whole thing.

DJS> The best way is to do it in Access!

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

OK, I think that using a link table may be the better way to go for
creating these Multipart IDs that I want. If I link the database with
Excel i can get it to run the complex If/Then statements I designed to
create these things in the first place. Can anyone suggest the best
way to link them so that I have the least amount of input? I would like
to fill in one form, sort and update the Excel file and have it return
the new ID. Will this work or will the universe implode for suggesting
such a thing?
James

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.

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