Best solution?, Sorry if this has been covered before.

[Guest]

I can't find a way to generate a table value from other bits of data within
the record. The tutorials I have read state that if you need to calculate a
value, then do it dynamically on the form, however I need the generated code
to be stored/retrieved, as I need to use it as a link to another table
containing information about sub objects of the original master record.

Also, the number of sub objects a master record will have isn't written in
stone, so is it possible to dynamically create sub-records using the same sub
table, from the main master record form.

I'm new to access so maybe there is an obvious solution that i'm missing,
but also confirmation that all or some of it is doable would also be
appreciated.

Thanks,

Matt.

 

[Amy Blankenship]

Could you be more specific about what you are trying to achieve?

-Amy

 

[Joseph Meehan]

I can't find a way to generate a table value from other bits of data
within the record. The tutorials I have read state that if you need
to calculate a value, then do it dynamically on the form, however I
need the generated code to be stored/retrieved, as I need to use it
as a link to another table containing information about sub objects
of the original master record.

Also, the number of sub objects a master record will have isn't
written in stone, so is it possible to dynamically create sub-records
using the same sub table, from the main master record form.

I'm new to access so maybe there is an obvious solution that i'm
missing, but also confirmation that all or some of it is doable would
also be appreciated.

Thanks,

Matt.

Please excuse me if my suggestion if off target as I am not sure if I
understand what you want to do.

Can you use a calculation in a query? Maybe use an update query to add
the results to a blank field in the table.

 

[Guest]

Could you be more specific about what you are trying to achieve?

I have a table which describes a manufacturing process, from the data within
this table I generate a 10digit unique job code.

I have a second table which contain sub-processes which are tied
to/contained within the master process, and I need a field on both tables to
act as both a database link, but it also needs to mean something to the
database user, and the only field on the master record I can use is the
generated job code.

My second question is, as the number of sub-processes tied to a master
record isn't fixed, is it possible to have a 'Generate/Add new Sub Process'
button on the main master form?

Hope this clarifies?

PS, there may be two replies, the first one got screwed up,

Cheers,

Matt.

 

[BruceM]

What happens to the 10-digit number if any of the fields from which it is
formed change? If the number needs to be a permanent part of the record,
then you can certainly store the value. If it needs to remain the same even
if any of the fields from which it is constructed change, then you need to
store it. However, if you need the value to change if any of the fields
change, then you should calculate it on the fly.
If the number is subject to change, you certainly do not want it to be part
of a relationship (a linking field). If it is to remain fixed (an invoice
number, for instance) then it can be used as the linking field. However,
you can use any unique value you choose as the linking field. The user
never needs to see it. You can add an Autonumber field to the main table,
and a corresponding Number field to the sub-process table, then relate the
two fields one-to-many. It can be your secret.
Since you specify that the number of sub-processes is subject to change, and
wonder on that basis about adding a New Record button, I suspect there may
be a design issue here. What you need, from what I can tell, is something
like this for your table structure:

tblProcess
ProcessID (Primary Key, or PK)
Other fields that are unique to the Process

tblSubProcess
SubProcessID (Primary Key, or PK)
ProcessID (Foreign Key, or FK)
Other fields that are unique to the sub-process

Create a one-to-many relationship between the ProcessID fields. Build a
form (frmProcess) based on tblProcess, and another (fsubSubProcess) based on
tblSubProcess. In design view, drag the icon for frmSubProcess onto
frmProcess. You can now add any number of sub-process records for any
particular process. If you set the default view of frmSubProcess to
Continuous you can see the subprocesses lined up one above the other on the
screen. If you set the default view to Single you can only view the
sub-processes one at a time. Your choice.

Maybe you already have something of the sort I have described, but your
explanation is rather vague, so I am taking a bit of a guess here.

 

[Amy Blankenship]

I have a table which describes a manufacturing process, from the data
within
this table I generate a 10digit unique job code.

I have a second table which contain sub-processes which are tied
to/contained within the master process, and I need a field on both tables
to
act as both a database link, but it also needs to mean something to the
database user, and the only field on the master record I can use is the
generated job code.

You're making an assumption that your user needs to see the field that acts
as a foreign key in the relationship. That is almost never a good idea.
The chief requirements in a key field are:

1) It is unique to that particular record in the field
2) It is a permanent part of the record, and won't change, EVER
3) It doesn't take up a lot of storage space
4) It can't be null

For this reason, Autonumber fields are almost always used to establish
relationships between tables. You can have your more meaningful calculated
field visible on the form by all means, but the user doesn't need to know or
care what field is the actual source of the link "under the hood."

My second question is, as the number of sub-processes tied to a master
record isn't fixed, is it possible to have a 'Generate/Add new Sub
Process'
button on the main master form?

If you establish the relationship in the relationships window by dragging
the field containing the Autonumber from the parent table into the field you
set up to contain that number in the child table, then you can use the
wizard to create a form with a subform on it. To do this, just select each
table, then select all fields from the tables. You'll want to hide the
"key" fields in the resulting forms by setting their visibility to false.
The resulting form/subfom combination will already have the capability for
showing existing subprocesses associated with a process and for adding new
ones.

HTH;

Amy