field duplication in tables

[SDA]

How would I automatically fill in one field in a form or table, based
on an entry in another field? For example: how would I fill in a
product's catalog number, based on the product name selected from a
combo box?

 

[Roger Carlson]

It's not possible in a table. It is possible in a form, but mostly, you
don't want to. Proper database design would be to have store those values
only once in a separate table and use a join query to retrieve the values.

I suggest you do some research into normalization and database design. I've
got a couple of blog series that introduce the topic. I suggest you start
here:

What Is Normalization
http://rogersaccessblog.blogspot.com/2008/12/what-is-normalization-part-i.htmlEntity-Relationship Diagramming http://rogersaccessblog.blogspot.com/2009/01/entity-relationship-diagramming-part-i.htmland follow these up with some more detailed examples hereatabase Design Tutorialshttp://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=238]DatabaseDesign TutorialsI think they'll be well worth your time.----Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L"SDA" <[email protected]> wrote in messageHow would I automatically fill in one field in a form or table, based> on an entry in another field? For example: how would I fill in a> product's catalog number, based on the product name selected from a> combo box?

 

[Roger Carlson]

Well, that formatting was odd. Try this:

I suggest you do some research into normalization and database design. I've
got a couple of blog series that introduce the topic. I suggest you start
here:


What Is Normalization
http://rogersaccessblog.blogspot.com/2008/12/what-is-normalization-part-i.htmlEntity-Relationship Diagramming http://rogersaccessblog.blogspot.com/2009/01/entity-relationship-diagramming-part-i.htmland follow these up with some more detailed examples hereatabase Design Tutorialshttp://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=238I think they'll be well worth your time.----Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L"Roger Carlson" <[email protected]> wrote in messagenews:[email protected]...> It's not possible in a table. It is possible in a form, but mostly, youdon't want to. Proper database design would be to have store those valuesonly once in a separate table and use a join query to retrieve the values.>> I suggest you do some research into normalization and database design.I've got a couple of blog series that introduce the topic. I suggest youstart here:>> What Is Normalization>http://rogersaccessblog.blogspot.com/2008/12/what-is-normalization-part-i.htmlEntity-Relationship Diagramminghttp://rogersaccessblog.blogspot.com/2009/01/entity-relationship-diagramming-part-i.htmland follow these up with some more detailed exampleshereatabase DesignTutorialshttp://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=238]DatabaseDesign TutorialsI think they'll be well worth your time.----RogerCarlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.comWant answers to your Access questions in your Email? Free subscription:http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L"SDA"<[email protected]> wrote inmessageHow would I automatically fill in one field in a form or table, based>on an entry in another field? For example: how would I fill in a> product'scatalog number, based on the product name selected from a> combo box?>

 

[Roger Carlson]

I don't know what's going on with my editor. I'll try it one last time:

What Is Normalization
http://rogersaccessblog.blogspot.com/2008/12/what-is-normalization-part-i.htmlEntity-Relationship Diagramminghttp://rogersaccessblog.blogspot.com/2009/01/entity-relationship-diagramming-part-i.htmland follow these up with some more detailed examples hereatabase Design Tutorialshttp://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=238I think they'll be well worth your time----Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L"

 

[Roger Carlson]

One more time:

I suggest you do some research into normalization and database design. I've
got a couple of blog series that introduce the topic. I suggest you start
here:

What Is Normalization
http://rogersaccessblog.blogspot.com/2008/12/what-is-normalization-part-i.html

Entity-Relationship Diagramming
http://rogersaccessblog.blogspot.com/2009/01/entity-relationship-diagramming-part-i.html

and follow these up with some more detailed examples here:

Database Design Tutorials
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=238

I think they'll be well worth your time.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[aaron.kempf]

Roger;

you're an idiot... It is possible-- it's just not possible with Jet.

With SQL Server, you can write triggers to do things like this.

-Aaron

 

[SDA]

Actually, I really just want to add a value onto a form. Right now,
the user selects a catalog number from a combo box, and this is stored
in the table. This is fine for data storage purposes, but it's not
very user friendly because we are left just looking at a list of
catalog numbers. I would like the user to be able to see the product
name that corresponds to the catalog number they just selected. I
just want to fill in a text box on the form with the product name
after the user selects a catalog number from this list. I think it
might take a little code, but I'm just not sure how.

 

[John W. Vinson]

Actually, I really just want to add a value onto a form. Right now,
the user selects a catalog number from a combo box, and this is stored
in the table. This is fine for data storage purposes, but it's not
very user friendly because we are left just looking at a list of
catalog numbers. I would like the user to be able to see the product
name that corresponds to the catalog number they just selected. I
just want to fill in a text box on the form with the product name
after the user selects a catalog number from this list. I think it
might take a little code, but I'm just not sure how.

No code is needed.

Use a Combo Box on the form to select the catalog number. The Row Source of
the combo would be a query on the products table, including both the catalog
number and the product name. The name will show when the combo is dropped
down, helping the user ensure the right selection. You can also put a textbox
on the form with a control source

=comboboxname.Column(1)

The Column property is zero based so this will display the product name if
that's the second field in the rowsource query.

 

[SDA]

No code is needed.

Use a Combo Box on the form to select the catalog number. The Row Source of
the combo would be a query on the products table, including both the catalog
number and the product name. The name will show when the combo is dropped
down, helping the user ensure the right selection. You can also put a textbox
on the form with a control source

=comboboxname.Column(1)

The Column property is zero based so this will display the product name if
that's the second field in the rowsource query.

That worked. Thanks!

 

[Roger Carlson]

Well, since this is a question was about Access in an Access forum, I
thought it was appropriate to give an answer about Access. I didn't discuss
DB2 or FoxPro either.

--
--Roger Carlson
MS Access MVP
www.rogersaccesslibrary.com


Roger;

you're an idiot... It is possible-- it's just not possible with Jet.

With SQL Server, you can write triggers to do things like this.

-Aaron