Table Field derived from another

[Paul Dennis]

I have a table with [Meeting Number] as a field. I would like another field
to be derived from it, i.e. if [Meeting Number] is 200565 then [Year] would
be 2005.

I would like to do this at the table level rather than form since I don't
want the field [Year] seen.

Is this possible ?

 

[Sprinks]

Paul,

If a field is dependent on another, you don't need it--calculate it
on-the-fly in a query or invisible form expression:

=Left([Meeting Number],4)

Sprinks

 

[kingston via AccessMonster.com]

I assume that you know about data normalization and have your own reason for
doing this even though you shouldn't...

You can add this field to the table and then populate it via a query, macro,
subroutine, or a combination of these. A quick and dirty way of doing this
is to add the field to the form anyway and to hide it. Then after the
meeting field is updated, run a macro or code to update the field. If you
really don't want it on the form, use an update query to update the table
when the meeting field is updated.

I have a table with [Meeting Number] as a field. I would like another field
to be derived from it, i.e. if [Meeting Number] is 200565 then [Year] would
be 2005.

I would like to do this at the table level rather than form since I don't
want the field [Year] seen.

Is this possible ?

 

[Paul Dennis]

I know, I know, however I need this in another query were I need to link 2
tables via the Year. Since the [Meeting Number] has the Year in it I have the
data but you can only link one field to another you can't link a partial
field.

Paul,

If a field is dependent on another, you don't need it--calculate it
on-the-fly in a query or invisible form expression:

=Left([Meeting Number],4)

Sprinks

I have a table with [Meeting Number] as a field. I would like another field
to be derived from it, i.e. if [Meeting Number] is 200565 then [Year] would
be 2005.

I would like to do this at the table level rather than form since I don't
want the field [Year] seen.

Is this possible ?

 

[Paul Dennis]

Yep know about data normalization, however I need it because I need this in
another query were I need to link 2 tables via the Year. Since the [Meeting
Number] has the Year in it I have the data but you can only link one field to
another you can't link a partial field.

I have already created the field and run an update query for the existing
data, but I wanted a clean way of updating the field just when a new record
was created and not to have to run a query every time.

I don't mind putting it on the form if I have to, I would just change the
font so it can't be seen, however don't want to use an event, can I use the
Row Source to say =Left([Meeting Number],4)?

I assume that you know about data normalization and have your own reason for
doing this even though you shouldn't...

You can add this field to the table and then populate it via a query, macro,
subroutine, or a combination of these. A quick and dirty way of doing this
is to add the field to the form anyway and to hide it. Then after the
meeting field is updated, run a macro or code to update the field. If you
really don't want it on the form, use an update query to update the table
when the meeting field is updated.

I have a table with [Meeting Number] as a field. I would like another field
to be derived from it, i.e. if [Meeting Number] is 200565 then [Year] would
be 2005.

I would like to do this at the table level rather than form since I don't
want the field [Year] seen.

Is this possible ?

 

[Bill Edwards]

Your meeting number field should probably be split into two fields
Meeting Number -> 65
MeetingYear -> 2005

If you want do display them both as a single value then use MeetingYear &
MeetingNumber

 

[kingston via AccessMonster.com]

You can link on a partial field (or any other calculated field) if you design
your query in SQL. Just create a temporary link and go into SQL design mode.
Change the temporary field to in the join statement to: Left([Meeting Number],
4). You won't be able to go back to visual design mode but you avoid all the
other hassle.

Yep know about data normalization, however I need it because I need this in
another query were I need to link 2 tables via the Year. Since the [Meeting
Number] has the Year in it I have the data but you can only link one field to
another you can't link a partial field.

I have already created the field and run an update query for the existing
data, but I wanted a clean way of updating the field just when a new record
was created and not to have to run a query every time.

I don't mind putting it on the form if I have to, I would just change the
font so it can't be seen, however don't want to use an event, can I use the
Row Source to say =Left([Meeting Number],4)?

I assume that you know about data normalization and have your own reason for
doing this even though you shouldn't...

[quoted text clipped - 14 lines]

 

[Sprinks]

Paul,

This way madness lies.

Create a query that calculates the year from your MeetingRoom table.
Include all other fields from the table that you want in the final recordset.
Save it and create a second query that links your query to the other table,
via the calculated field.

Hope that helps.

Sprinks

Yep know about data normalization, however I need it because I need this in
another query were I need to link 2 tables via the Year. Since the [Meeting
Number] has the Year in it I have the data but you can only link one field to
another you can't link a partial field.

I have already created the field and run an update query for the existing
data, but I wanted a clean way of updating the field just when a new record
was created and not to have to run a query every time.

I don't mind putting it on the form if I have to, I would just change the
font so it can't be seen, however don't want to use an event, can I use the
Row Source to say =Left([Meeting Number],4)?

I assume that you know about data normalization and have your own reason for
doing this even though you shouldn't...

You can add this field to the table and then populate it via a query, macro,
subroutine, or a combination of these. A quick and dirty way of doing this
is to add the field to the form anyway and to hide it. Then after the
meeting field is updated, run a macro or code to update the field. If you
really don't want it on the form, use an update query to update the table
when the meeting field is updated.

I have a table with [Meeting Number] as a field. I would like another field
to be derived from it, i.e. if [Meeting Number] is 200565 then [Year] would
be 2005.

I would like to do this at the table level rather than form since I don't
want the field [Year] seen.

Is this possible ?

 

[Sprinks]

Bill,

With all of us are staring at the trees, thanks for taking a step back and
looking at the forest.

Sprinks

Your meeting number field should probably be split into two fields
Meeting Number -> 65
MeetingYear -> 2005

If you want do display them both as a single value then use MeetingYear &
MeetingNumber

I have a table with [Meeting Number] as a field. I would like another field
to be derived from it, i.e. if [Meeting Number] is 200565 then [Year]
would
be 2005.

I would like to do this at the table level rather than form since I don't
want the field [Year] seen.

Is this possible ?

 

[Paul Dennis]

I agree but this is a late design requirement and I would have to change more
this way.

Your meeting number field should probably be split into two fields
Meeting Number -> 65
MeetingYear -> 2005

If you want do display them both as a single value then use MeetingYear &
MeetingNumber

I have a table with [Meeting Number] as a field. I would like another field
to be derived from it, i.e. if [Meeting Number] is 200565 then [Year]
would
be 2005.

I would like to do this at the table level rather than form since I don't
want the field [Year] seen.

Is this possible ?

 

[Paul Dennis]

Sounds good, but point 4 is a killer

You can link on a partial field (or any other calculated field) if you design
your query in SQL. Just create a temporary link and go into SQL design mode.
Change the temporary field to in the join statement to: Left([Meeting Number],
4). You won't be able to go back to visual design mode but you avoid all the
other hassle.

Yep know about data normalization, however I need it because I need this in
another query were I need to link 2 tables via the Year. Since the [Meeting
Number] has the Year in it I have the data but you can only link one field to
another you can't link a partial field.

I have already created the field and run an update query for the existing
data, but I wanted a clean way of updating the field just when a new record
was created and not to have to run a query every time.

I don't mind putting it on the form if I have to, I would just change the
font so it can't be seen, however don't want to use an event, can I use the
Row Source to say =Left([Meeting Number],4)?

I assume that you know about data normalization and have your own reason for
doing this even though you shouldn't...

[quoted text clipped - 14 lines]

Is this possible ?

 

[kingston via AccessMonster.com]

Actually, that's Left([Meeting Number],4). My message just got cut apart.
Sprinks has a way to do basically the same thing visually. I like the visual
queries in Access too, but SQL is simply more capable and secure, especially
since it can be embedded in code.

Sounds good, but point 4 is a killer

You can link on a partial field (or any other calculated field) if you design
your query in SQL. Just create a temporary link and go into SQL design mode.

[quoted text clipped - 20 lines]

 

[Bill Edwards]

Personally, a change of this type I would make now, even if it is a late
requirement. It's probably less work to change it now that it would be to
work around it for all eternity. But that's your choice (no offense
intended).

I agree but this is a late design requirement and I would have to change
more
this way.

Your meeting number field should probably be split into two fields
Meeting Number -> 65
MeetingYear -> 2005

If you want do display them both as a single value then use MeetingYear
&
MeetingNumber

I have a table with [Meeting Number] as a field. I would like another
field
to be derived from it, i.e. if [Meeting Number] is 200565 then [Year]
would
be 2005.

I would like to do this at the table level rather than form since I
don't
want the field [Year] seen.

Is this possible ?