Field content auto generation?

[Crag Hack]

Hi, I am quite new to Access and I have no idea how to solve this. I have
three fields, the first field's content is say, "You". The second field's
content is "Win". After I input these two fields, is there a way to let the
third field automatically fill in "You Win"?

btw, is there a built-in CHM reference guide for the Access 2003 syntax? I
couldn't find any help file

Thank you.

 

[Jeff Boyce]

Crag

Sorry, not familiar with "CHM reference guide" ... what does this acronym
stand for?

Now, don't bother! If you have two fields with values, you rarely need to
store the combination of those two in a well-normalized relational database
(AKA, a well-normalized Access db).

Instead, use a query to concatenate those two together any time you need to
use the combination.

Regards

Jeff Boyce
Microsoft Access MVP

 

[John W. Vinson]

On Tue, 6 Oct 2009 12:42:09 -0700, Crag Hack <Crag

Hi, I am quite new to Access and I have no idea how to solve this. I have
three fields, the first field's content is say, "You". The second field's
content is "Win". After I input these two fields, is there a way to let the
third field automatically fill in "You Win"?

btw, is there a built-in CHM reference guide for the Access 2003 syntax? I
couldn't find any help file

Thank you.

Press F1...

"The syntax" is more than a bit complex. I've got about three shelf feet of
books in my office describing it.

That said... you are apparently viewing Tables as if they were spreadsheets.
They're not. You should not store data in a field if that data depends on data
in other fields. Instead you can store just Field1 and Field2, and use a Query
based on the table to combine or organize them. In the query grid you can type

[Field1] & " " & [Field2]

to dynamically calculate a string consisting of the contents of Field1, a
blank, and the contents of Field2. This Query can be used as the recordsource
of a Form or Report.

Perhaps you could explain the real-life problem you're trying to solve; or you
could review some of these resources:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

 

[Klatuu]

Compiled Help Manual

It is the standard type for MS compiles help files.

 

[Crag Hack]

Thank you for those helpful links. I will take some time to read them.

Now regarding the real-life problem, I am trying to build a product name
field that's based on three other fields (category1, category2, size). The
category1 contains "men","women" etc, category2 contains "pants","shirts"
etc, and size contains "S","XXL" etc. I have got the NameGeneration query
worked out:
[Category1] & " " & [Category2] & " Size " & [Size]
the result displays perfectly. Then I go to the product name field in the
form, right click and set properties->row scource to NameGeneration query, it
shows a drop down menu with a list of the category1 content only. If I change
the row scource to "SELECT [Category1] & " " & [Category2] & " Size " &
[Size] FROM [abc table]" instead of the query, it shows a drop down menu with
a list of the concatenated fields, which is what I want, but I still have to
click twice to choose the right product name. And if there are so many
different kinds of category1, category2, size data (x,y,z respectively), the
list will eventually have close to x*y*z different product names for me to
choose which will be very annoying. So I was wondering if there is a way to
let the product name field have an eye on those three fields and
automatically fill itself with the concatenated result.

Hope I explained this clearly.
Thank you.

 

[Klatuu]

Possible, but not fool proof and it violates two database normalizations.
First, you are wanting to store a value that can be calculated from existing
data, and 2, it is redundant data.

The reason it is not fool proof is because there is no way to ensure the
user will enter the other values in the correct order, so you could end up
with BrasXLMens.
Not exactly what you would want I presume. I used the example to also
illustrate you need to do some filtering on form to make sure correctly
related values will be entered.

But, the correct way to do what you are wanting to do is to concatenate the
other fields to produce a product name when you need to show it on a form or
on a report.


--
Dave Hargis, Microsoft Access MVP

Thank you for those helpful links. I will take some time to read them.

Now regarding the real-life problem, I am trying to build a product name
field that's based on three other fields (category1, category2, size). The
category1 contains "men","women" etc, category2 contains "pants","shirts"
etc, and size contains "S","XXL" etc. I have got the NameGeneration query
worked out:
[Category1] & " " & [Category2] & " Size " & [Size]
the result displays perfectly. Then I go to the product name field in the
form, right click and set properties->row scource to NameGeneration query, it
shows a drop down menu with a list of the category1 content only. If I change
the row scource to "SELECT [Category1] & " " & [Category2] & " Size " &
[Size] FROM [abc table]" instead of the query, it shows a drop down menu with
a list of the concatenated fields, which is what I want, but I still have to
click twice to choose the right product name. And if there are so many
different kinds of category1, category2, size data (x,y,z respectively), the
list will eventually have close to x*y*z different product names for me to
choose which will be very annoying. So I was wondering if there is a way to
let the product name field have an eye on those three fields and
automatically fill itself with the concatenated result.

Hope I explained this clearly.
Thank you.

Press F1...

"The syntax" is more than a bit complex. I've got about three shelf feet of
books in my office describing it.

That said... you are apparently viewing Tables as if they were spreadsheets.
They're not. You should not store data in a field if that data depends on data
in other fields. Instead you can store just Field1 and Field2, and use a Query
based on the table to combine or organize them. In the query grid you can type

[Field1] & " " & [Field2]

to dynamically calculate a string consisting of the contents of Field1, a
blank, and the contents of Field2. This Query can be used as the recordsource
of a Form or Report.

Perhaps you could explain the real-life problem you're trying to solve; or you
could review some of these resources:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

 

[Crag Hack]

Thank you Klatuu
Your right, it does need more work than just put the strings together. I
will get it when I study more about Access.
Actually I just solved the auto generation myself. I should have put the
[Category1] & " " & [Category2] & " Size " & [Size] in the control source
rather than row source. Now it's working as intened. Thanks

Possible, but not fool proof and it violates two database normalizations.
First, you are wanting to store a value that can be calculated from existing
data, and 2, it is redundant data.

The reason it is not fool proof is because there is no way to ensure the
user will enter the other values in the correct order, so you could end up
with BrasXLMens.
Not exactly what you would want I presume. I used the example to also
illustrate you need to do some filtering on form to make sure correctly
related values will be entered.

But, the correct way to do what you are wanting to do is to concatenate the
other fields to produce a product name when you need to show it on a form or
on a report.


--
Dave Hargis, Microsoft Access MVP

Thank you for those helpful links. I will take some time to read them.

Now regarding the real-life problem, I am trying to build a product name
field that's based on three other fields (category1, category2, size). The
category1 contains "men","women" etc, category2 contains "pants","shirts"
etc, and size contains "S","XXL" etc. I have got the NameGeneration query
worked out:
[Category1] & " " & [Category2] & " Size " & [Size]
the result displays perfectly. Then I go to the product name field in the
form, right click and set properties->row scource to NameGeneration query, it
shows a drop down menu with a list of the category1 content only. If I change
the row scource to "SELECT [Category1] & " " & [Category2] & " Size " &
[Size] FROM [abc table]" instead of the query, it shows a drop down menu with
a list of the concatenated fields, which is what I want, but I still have to
click twice to choose the right product name. And if there are so many
different kinds of category1, category2, size data (x,y,z respectively), the
list will eventually have close to x*y*z different product names for me to
choose which will be very annoying. So I was wondering if there is a way to
let the product name field have an eye on those three fields and
automatically fill itself with the concatenated result.

Hope I explained this clearly.
Thank you.

Press F1...

"The syntax" is more than a bit complex. I've got about three shelf feet of
books in my office describing it.

That said... you are apparently viewing Tables as if they were spreadsheets.
They're not. You should not store data in a field if that data depends on data
in other fields. Instead you can store just Field1 and Field2, and use a Query
based on the table to combine or organize them. In the query grid you can type

[Field1] & " " & [Field2]

to dynamically calculate a string consisting of the contents of Field1, a
blank, and the contents of Field2. This Query can be used as the recordsource
of a Form or Report.

Perhaps you could explain the real-life problem you're trying to solve; or you
could review some of these resources:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

 

[Jeff Boyce]

Thanks, Dave!

Jeff

Compiled Help Manual

It is the standard type for MS compiles help files.