Append with One Table

[Guest]

I'm trying to figure out how to have a field in a table that is a calculated
value. Is that possible? I have two fields: chrFirstName and chrLastName. I
want the calculated field to be something like: "=[chrFirstname] & " " &
[chrLastName]". Do I input this? I've been reading on the append queries;
would that solve the issue? I came across this: "you can create a full name
calculated field by combining the text from the first name and last name
fields. You can then append the full name values to a full name field in a
table." That sounds like what I want but I have NO idea how to do that. I
only have one table: tblName - do I need two? Where would the calculated
field reside?

 

[Guest]

Hi, Jennifer. The simple answer is that you can't do calculations in a table.
Tables are static; they hold stuff. However, we can use queries to make
lots of calculations, and store the results back in the table. Queries are
dynamic; they DO stuff.

So, let's say that you have a "Contacts" table and you have two fields
FirstName and LastName. You want to fill in the field FullName in the
Contacts table which will contain the FirstName, a space, and the LastName.

Create a query and add the Contacts table to it. Drag the three fields
FirstName, LastName and FullName into the query window.

Change your query to an Update Query, and click in the "Update To" row of
the FullName field. Type in the following:

FirstName & " " & LastName

When you run the query, it will fill in the FullName for each record as
"FirstName LastName" (with the space between them).

However, you probably wouldn't want to actually store the FullName in the
table; you don't need to. You'll want the FullName in reports, sure, but you
can calculate it on the fly using almost the same syntax in the report that
you would have used in the update query.

Or, create a query and put in your Firstname and LastName fields as before.
( In this example, we'll say that you don't have a FullName column, just
First and Last.) Then in the third column of the query, in the top row, type
the following:

FullName: FirstName & " " & LastName

Whenever you run the query, you'll see three columns; the two from your
table, and the FullName column calculated whenever you need it. Then you can
base your forms and reports on the Query and not on the table.

Play around with this idea. Remember that Access is a real "division of
labor" program; each part of the database (tables, queries, forms and
reports) do one thing well. Use the part that does each job best.

 

[Guest]

Ken, you have more than answered my question. I really can't thank you enough
- this is one of the best replies I've received!
--
Thank you! - Jennifer

Hi, Jennifer. The simple answer is that you can't do calculations in a table.
Tables are static; they hold stuff. However, we can use queries to make
lots of calculations, and store the results back in the table. Queries are
dynamic; they DO stuff.

So, let's say that you have a "Contacts" table and you have two fields
FirstName and LastName. You want to fill in the field FullName in the
Contacts table which will contain the FirstName, a space, and the LastName.

Create a query and add the Contacts table to it. Drag the three fields
FirstName, LastName and FullName into the query window.

Change your query to an Update Query, and click in the "Update To" row of
the FullName field. Type in the following:

FirstName & " " & LastName

When you run the query, it will fill in the FullName for each record as
"FirstName LastName" (with the space between them).

However, you probably wouldn't want to actually store the FullName in the
table; you don't need to. You'll want the FullName in reports, sure, but you
can calculate it on the fly using almost the same syntax in the report that
you would have used in the update query.

Or, create a query and put in your Firstname and LastName fields as before.
( In this example, we'll say that you don't have a FullName column, just
First and Last.) Then in the third column of the query, in the top row, type
the following:

FullName: FirstName & " " & LastName

Whenever you run the query, you'll see three columns; the two from your
table, and the FullName column calculated whenever you need it. Then you can
base your forms and reports on the Query and not on the table.

Play around with this idea. Remember that Access is a real "division of
labor" program; each part of the database (tables, queries, forms and
reports) do one thing well. Use the part that does each job best.

I'm trying to figure out how to have a field in a table that is a calculated
value. Is that possible? I have two fields: chrFirstName and chrLastName. I
want the calculated field to be something like: "=[chrFirstname] & " " &
[chrLastName]". Do I input this? I've been reading on the append queries;
would that solve the issue? I came across this: "you can create a full name
calculated field by combining the text from the first name and last name
fields. You can then append the full name values to a full name field in a
table." That sounds like what I want but I have NO idea how to do that. I
only have one table: tblName - do I need two? Where would the calculated
field reside?

 

[Guest]

Ok, I think I did something wrong

I did the query just as you said, but when I open the table back up, I see
this:

chrFirstName chrLastName chrCombinedName
Jennifer Cali chrFirstName chrLastName
Jack Traper chrFirstName chrLastName
Andy Zoller chrFirstName chrLastName

It seems to be reading my entry as text and not as a formula. How do I make
it read as a formula to show the combined name and not (what appears to be)
the formula I entered?



--
Thank you! - Jennifer

Hi, Jennifer. The simple answer is that you can't do calculations in a table.
Tables are static; they hold stuff. However, we can use queries to make
lots of calculations, and store the results back in the table. Queries are
dynamic; they DO stuff.

So, let's say that you have a "Contacts" table and you have two fields
FirstName and LastName. You want to fill in the field FullName in the
Contacts table which will contain the FirstName, a space, and the LastName.

Create a query and add the Contacts table to it. Drag the three fields
FirstName, LastName and FullName into the query window.

Change your query to an Update Query, and click in the "Update To" row of
the FullName field. Type in the following:

FirstName & " " & LastName

When you run the query, it will fill in the FullName for each record as
"FirstName LastName" (with the space between them).

However, you probably wouldn't want to actually store the FullName in the
table; you don't need to. You'll want the FullName in reports, sure, but you
can calculate it on the fly using almost the same syntax in the report that
you would have used in the update query.

Or, create a query and put in your Firstname and LastName fields as before.
( In this example, we'll say that you don't have a FullName column, just
First and Last.) Then in the third column of the query, in the top row, type
the following:

FullName: FirstName & " " & LastName

Whenever you run the query, you'll see three columns; the two from your
table, and the FullName column calculated whenever you need it. Then you can
base your forms and reports on the Query and not on the table.

Play around with this idea. Remember that Access is a real "division of
labor" program; each part of the database (tables, queries, forms and
reports) do one thing well. Use the part that does each job best.

I'm trying to figure out how to have a field in a table that is a calculated
value. Is that possible? I have two fields: chrFirstName and chrLastName. I
want the calculated field to be something like: "=[chrFirstname] & " " &
[chrLastName]". Do I input this? I've been reading on the append queries;
would that solve the issue? I came across this: "you can create a full name
calculated field by combining the text from the first name and last name
fields. You can then append the full name values to a full name field in a
table." That sounds like what I want but I have NO idea how to do that. I
only have one table: tblName - do I need two? Where would the calculated
field reside?

 

[Guest]

Hi, Jennifer. Thanks for the kind words.

It looks like you may have put quote marks in the formula. The only quote
marks should be the ones around the space between the names. It ought to look
like this:

chrCombinedName: chrFirstName &" " & chrLastName

If it doesn't work, then please copy the exact text of the formula and paste
it back into your reply.

I hope this works for you!

Ok, I think I did something wrong

I did the query just as you said, but when I open the table back up, I see
this:

chrFirstName chrLastName chrCombinedName
Jennifer Cali chrFirstName chrLastName
Jack Traper chrFirstName chrLastName
Andy Zoller chrFirstName chrLastName

It seems to be reading my entry as text and not as a formula. How do I make
it read as a formula to show the combined name and not (what appears to be)
the formula I entered?



--
Thank you! - Jennifer

Hi, Jennifer. The simple answer is that you can't do calculations in a table.
Tables are static; they hold stuff. However, we can use queries to make
lots of calculations, and store the results back in the table. Queries are
dynamic; they DO stuff.

So, let's say that you have a "Contacts" table and you have two fields
FirstName and LastName. You want to fill in the field FullName in the
Contacts table which will contain the FirstName, a space, and the LastName.

Create a query and add the Contacts table to it. Drag the three fields
FirstName, LastName and FullName into the query window.

Change your query to an Update Query, and click in the "Update To" row of
the FullName field. Type in the following:

FirstName & " " & LastName

When you run the query, it will fill in the FullName for each record as
"FirstName LastName" (with the space between them).

However, you probably wouldn't want to actually store the FullName in the
table; you don't need to. You'll want the FullName in reports, sure, but you
can calculate it on the fly using almost the same syntax in the report that
you would have used in the update query.

Or, create a query and put in your Firstname and LastName fields as before.
( In this example, we'll say that you don't have a FullName column, just
First and Last.) Then in the third column of the query, in the top row, type
the following:

FullName: FirstName & " " & LastName

Whenever you run the query, you'll see three columns; the two from your
table, and the FullName column calculated whenever you need it. Then you can
base your forms and reports on the Query and not on the table.

Play around with this idea. Remember that Access is a real "division of
labor" program; each part of the database (tables, queries, forms and
reports) do one thing well. Use the part that does each job best.

I'm trying to figure out how to have a field in a table that is a calculated
value. Is that possible? I have two fields: chrFirstName and chrLastName. I
want the calculated field to be something like: "=[chrFirstname] & " " &
[chrLastName]". Do I input this? I've been reading on the append queries;
would that solve the issue? I came across this: "you can create a full name
calculated field by combining the text from the first name and last name
fields. You can then append the full name values to a full name field in a
table." That sounds like what I want but I have NO idea how to do that. I
only have one table: tblName - do I need two? Where would the calculated
field reside?

 

[Guest]

OK, my mistake; when you created the Update Query, I had forgotten that for
this one part of the program, the query will not automatically enclose the
field names in square brackets. Type your query like this:

[chrFirstName] & " " & [chrLastName]

In almost every other part of Access, the program puts those square brackets
in automagically. You have to HAVE the brackets, but this is the only place
that you have to TYPE them.

Sorry about that.

Ok, I think I did something wrong

I did the query just as you said, but when I open the table back up, I see
this:

chrFirstName chrLastName chrCombinedName
Jennifer Cali chrFirstName chrLastName
Jack Traper chrFirstName chrLastName
Andy Zoller chrFirstName chrLastName

It seems to be reading my entry as text and not as a formula. How do I make
it read as a formula to show the combined name and not (what appears to be)
the formula I entered?



--
Thank you! - Jennifer

Hi, Jennifer. The simple answer is that you can't do calculations in a table.
Tables are static; they hold stuff. However, we can use queries to make
lots of calculations, and store the results back in the table. Queries are
dynamic; they DO stuff.

So, let's say that you have a "Contacts" table and you have two fields
FirstName and LastName. You want to fill in the field FullName in the
Contacts table which will contain the FirstName, a space, and the LastName.

Create a query and add the Contacts table to it. Drag the three fields
FirstName, LastName and FullName into the query window.

Change your query to an Update Query, and click in the "Update To" row of
the FullName field. Type in the following:

FirstName & " " & LastName

When you run the query, it will fill in the FullName for each record as
"FirstName LastName" (with the space between them).

However, you probably wouldn't want to actually store the FullName in the
table; you don't need to. You'll want the FullName in reports, sure, but you
can calculate it on the fly using almost the same syntax in the report that
you would have used in the update query.

Or, create a query and put in your Firstname and LastName fields as before.
( In this example, we'll say that you don't have a FullName column, just
First and Last.) Then in the third column of the query, in the top row, type
the following:

FullName: FirstName & " " & LastName

Whenever you run the query, you'll see three columns; the two from your
table, and the FullName column calculated whenever you need it. Then you can
base your forms and reports on the Query and not on the table.

Play around with this idea. Remember that Access is a real "division of
labor" program; each part of the database (tables, queries, forms and
reports) do one thing well. Use the part that does each job best.

I'm trying to figure out how to have a field in a table that is a calculated
value. Is that possible? I have two fields: chrFirstName and chrLastName. I
want the calculated field to be something like: "=[chrFirstname] & " " &
[chrLastName]". Do I input this? I've been reading on the append queries;
would that solve the issue? I came across this: "you can create a full name
calculated field by combining the text from the first name and last name
fields. You can then append the full name values to a full name field in a
table." That sounds like what I want but I have NO idea how to do that. I
only have one table: tblName - do I need two? Where would the calculated
field reside?

 

[Guest]

Hi Ken,
Ok, here's my steps:
1. Created a new query
2. Set query type to "Update"
3. Brought three fields from tblNames into the query (chrFirstName,
chrLastName, chrCombinedName)
4. In the "Update To" box under chrCombinedName I entered the text:
chrFirstName & " " & chrLastName
5. Click out of the cell

When I click out of the cell it automatically enteres the "", so it shows up
in the cell as "chrFirstName" & " " & "chrLastName" instead of chrFirstName &
" " & chrLastName

How can I make it stop inserting the ""?


--
Thank you! - Jennifer

Hi, Jennifer. Thanks for the kind words.

It looks like you may have put quote marks in the formula. The only quote
marks should be the ones around the space between the names. It ought to look
like this:

chrCombinedName: chrFirstName &" " & chrLastName

If it doesn't work, then please copy the exact text of the formula and paste
it back into your reply.

I hope this works for you!

Ok, I think I did something wrong

I did the query just as you said, but when I open the table back up, I see
this:

chrFirstName chrLastName chrCombinedName
Jennifer Cali chrFirstName chrLastName
Jack Traper chrFirstName chrLastName
Andy Zoller chrFirstName chrLastName

It seems to be reading my entry as text and not as a formula. How do I make
it read as a formula to show the combined name and not (what appears to be)
the formula I entered?



--
Thank you! - Jennifer

Hi, Jennifer. The simple answer is that you can't do calculations in a table.
Tables are static; they hold stuff. However, we can use queries to make
lots of calculations, and store the results back in the table. Queries are
dynamic; they DO stuff.

So, let's say that you have a "Contacts" table and you have two fields
FirstName and LastName. You want to fill in the field FullName in the
Contacts table which will contain the FirstName, a space, and the LastName.

Create a query and add the Contacts table to it. Drag the three fields
FirstName, LastName and FullName into the query window.

Change your query to an Update Query, and click in the "Update To" row of
the FullName field. Type in the following:

FirstName & " " & LastName

When you run the query, it will fill in the FullName for each record as
"FirstName LastName" (with the space between them).

However, you probably wouldn't want to actually store the FullName in the
table; you don't need to. You'll want the FullName in reports, sure, but you
can calculate it on the fly using almost the same syntax in the report that
you would have used in the update query.

Or, create a query and put in your Firstname and LastName fields as before.
( In this example, we'll say that you don't have a FullName column, just
First and Last.) Then in the third column of the query, in the top row, type
the following:

FullName: FirstName & " " & LastName

Whenever you run the query, you'll see three columns; the two from your
table, and the FullName column calculated whenever you need it. Then you can
base your forms and reports on the Query and not on the table.

Play around with this idea. Remember that Access is a real "division of
labor" program; each part of the database (tables, queries, forms and
reports) do one thing well. Use the part that does each job best.

:

I'm trying to figure out how to have a field in a table that is a calculated
value. Is that possible? I have two fields: chrFirstName and chrLastName. I
want the calculated field to be something like: "=[chrFirstname] & " " &
[chrLastName]". Do I input this? I've been reading on the append queries;
would that solve the issue? I came across this: "you can create a full name
calculated field by combining the text from the first name and last name
fields. You can then append the full name values to a full name field in a
table." That sounds like what I want but I have NO idea how to do that. I
only have one table: tblName - do I need two? Where would the calculated
field reside?

 

[Guest]

I got it! If I add the brackets around the names, it works: [chrFirstName] &"
" & [chrLastName]

Thanks - I couldn't have got this far alone!
--
Thank you! - Jennifer

Hi, Jennifer. Thanks for the kind words.

It looks like you may have put quote marks in the formula. The only quote
marks should be the ones around the space between the names. It ought to look
like this:

chrCombinedName: chrFirstName &" " & chrLastName

If it doesn't work, then please copy the exact text of the formula and paste
it back into your reply.

I hope this works for you!

Ok, I think I did something wrong

I did the query just as you said, but when I open the table back up, I see
this:

chrFirstName chrLastName chrCombinedName
Jennifer Cali chrFirstName chrLastName
Jack Traper chrFirstName chrLastName
Andy Zoller chrFirstName chrLastName

It seems to be reading my entry as text and not as a formula. How do I make
it read as a formula to show the combined name and not (what appears to be)
the formula I entered?



--
Thank you! - Jennifer

Hi, Jennifer. The simple answer is that you can't do calculations in a table.
Tables are static; they hold stuff. However, we can use queries to make
lots of calculations, and store the results back in the table. Queries are
dynamic; they DO stuff.

So, let's say that you have a "Contacts" table and you have two fields
FirstName and LastName. You want to fill in the field FullName in the
Contacts table which will contain the FirstName, a space, and the LastName.

Create a query and add the Contacts table to it. Drag the three fields
FirstName, LastName and FullName into the query window.

Change your query to an Update Query, and click in the "Update To" row of
the FullName field. Type in the following:

FirstName & " " & LastName

When you run the query, it will fill in the FullName for each record as
"FirstName LastName" (with the space between them).

However, you probably wouldn't want to actually store the FullName in the
table; you don't need to. You'll want the FullName in reports, sure, but you
can calculate it on the fly using almost the same syntax in the report that
you would have used in the update query.

Or, create a query and put in your Firstname and LastName fields as before.
( In this example, we'll say that you don't have a FullName column, just
First and Last.) Then in the third column of the query, in the top row, type
the following:

FullName: FirstName & " " & LastName

Whenever you run the query, you'll see three columns; the two from your
table, and the FullName column calculated whenever you need it. Then you can
base your forms and reports on the Query and not on the table.

Play around with this idea. Remember that Access is a real "division of
labor" program; each part of the database (tables, queries, forms and
reports) do one thing well. Use the part that does each job best.

:

I'm trying to figure out how to have a field in a table that is a calculated
value. Is that possible? I have two fields: chrFirstName and chrLastName. I
want the calculated field to be something like: "=[chrFirstname] & " " &
[chrLastName]". Do I input this? I've been reading on the append queries;
would that solve the issue? I came across this: "you can create a full name
calculated field by combining the text from the first name and last name
fields. You can then append the full name values to a full name field in a
table." That sounds like what I want but I have NO idea how to do that. I
only have one table: tblName - do I need two? Where would the calculated
field reside?

 

[Guest]

You are welcome.

The price of assistance is this. Someday, you'll be reading one of these
groups, and you're going to say "Wow! I know the answer to THIS question!"
You must answer it. Someday.

Have a great evening.

I got it! If I add the brackets around the names, it works: [chrFirstName] &"
" & [chrLastName]

Thanks - I couldn't have got this far alone!
--
Thank you! - Jennifer

Hi, Jennifer. Thanks for the kind words.

It looks like you may have put quote marks in the formula. The only quote
marks should be the ones around the space between the names. It ought to look
like this:

chrCombinedName: chrFirstName &" " & chrLastName

If it doesn't work, then please copy the exact text of the formula and paste
it back into your reply.

I hope this works for you!

Ok, I think I did something wrong

I did the query just as you said, but when I open the table back up, I see
this:

chrFirstName chrLastName chrCombinedName
Jennifer Cali chrFirstName chrLastName
Jack Traper chrFirstName chrLastName
Andy Zoller chrFirstName chrLastName

It seems to be reading my entry as text and not as a formula. How do I make
it read as a formula to show the combined name and not (what appears to be)
the formula I entered?



--
Thank you! - Jennifer


:

Hi, Jennifer. The simple answer is that you can't do calculations in a table.
Tables are static; they hold stuff. However, we can use queries to make
lots of calculations, and store the results back in the table. Queries are
dynamic; they DO stuff.

So, let's say that you have a "Contacts" table and you have two fields
FirstName and LastName. You want to fill in the field FullName in the
Contacts table which will contain the FirstName, a space, and the LastName.

Create a query and add the Contacts table to it. Drag the three fields
FirstName, LastName and FullName into the query window.

Change your query to an Update Query, and click in the "Update To" row of
the FullName field. Type in the following:

FirstName & " " & LastName

When you run the query, it will fill in the FullName for each record as
"FirstName LastName" (with the space between them).

However, you probably wouldn't want to actually store the FullName in the
table; you don't need to. You'll want the FullName in reports, sure, but you
can calculate it on the fly using almost the same syntax in the report that
you would have used in the update query.

Or, create a query and put in your Firstname and LastName fields as before.
( In this example, we'll say that you don't have a FullName column, just
First and Last.) Then in the third column of the query, in the top row, type
the following:

FullName: FirstName & " " & LastName

Whenever you run the query, you'll see three columns; the two from your
table, and the FullName column calculated whenever you need it. Then you can
base your forms and reports on the Query and not on the table.

Play around with this idea. Remember that Access is a real "division of
labor" program; each part of the database (tables, queries, forms and
reports) do one thing well. Use the part that does each job best.

:

I'm trying to figure out how to have a field in a table that is a calculated
value. Is that possible? I have two fields: chrFirstName and chrLastName. I
want the calculated field to be something like: "=[chrFirstname] & " " &
[chrLastName]". Do I input this? I've been reading on the append queries;
would that solve the issue? I came across this: "you can create a full name
calculated field by combining the text from the first name and last name
fields. You can then append the full name values to a full name field in a
table." That sounds like what I want but I have NO idea how to do that. I
only have one table: tblName - do I need two? Where would the calculated
field reside?

 

[Pieter Wijnen]

On the other hand, why store the combination? Bad practice having redundant
data in the table!
Use a Query to Combine the fields as needed
ie

Select A.ChrFirstName, A.ChrLastName, A.ChrFirstName& ' ' & A.ChrLastName AS
FullName From MyTable A

Will always give the correct answer (even if somebody changes their name)

Pieter

I got it! If I add the brackets around the names, it works: [chrFirstName]
&"
" & [chrLastName]

Thanks - I couldn't have got this far alone!
--
Thank you! - Jennifer

Hi, Jennifer. Thanks for the kind words.

It looks like you may have put quote marks in the formula. The only
quote
marks should be the ones around the space between the names. It ought to
look
like this:

chrCombinedName: chrFirstName &" " & chrLastName

If it doesn't work, then please copy the exact text of the formula and
paste
it back into your reply.

I hope this works for you!

Ok, I think I did something wrong

I did the query just as you said, but when I open the table back up, I
see
this:

chrFirstName chrLastName chrCombinedName
Jennifer Cali chrFirstName chrLastName
Jack Traper chrFirstName chrLastName
Andy Zoller chrFirstName chrLastName

It seems to be reading my entry as text and not as a formula. How do I
make
it read as a formula to show the combined name and not (what appears to
be)
the formula I entered?



--
Thank you! - Jennifer


:

Hi, Jennifer. The simple answer is that you can't do calculations in
a table.
Tables are static; they hold stuff. However, we can use queries to
make
lots of calculations, and store the results back in the table.
Queries are
dynamic; they DO stuff.

So, let's say that you have a "Contacts" table and you have two
fields
FirstName and LastName. You want to fill in the field FullName in
the
Contacts table which will contain the FirstName, a space, and the
LastName.

Create a query and add the Contacts table to it. Drag the three
fields
FirstName, LastName and FullName into the query window.

Change your query to an Update Query, and click in the "Update To"
row of
the FullName field. Type in the following:

FirstName & " " & LastName

When you run the query, it will fill in the FullName for each record
as
"FirstName LastName" (with the space between them).

However, you probably wouldn't want to actually store the FullName in
the
table; you don't need to. You'll want the FullName in reports, sure,
but you
can calculate it on the fly using almost the same syntax in the
report that
you would have used in the update query.

Or, create a query and put in your Firstname and LastName fields as
before.
( In this example, we'll say that you don't have a FullName column,
just
First and Last.) Then in the third column of the query, in the top
row, type
the following:

FullName: FirstName & " " & LastName

Whenever you run the query, you'll see three columns; the two from
your
table, and the FullName column calculated whenever you need it. Then
you can
base your forms and reports on the Query and not on the table.

Play around with this idea. Remember that Access is a real "division
of
labor" program; each part of the database (tables, queries, forms and
reports) do one thing well. Use the part that does each job best.

:

I'm trying to figure out how to have a field in a table that is a
calculated
value. Is that possible? I have two fields: chrFirstName and
chrLastName. I
want the calculated field to be something like: "=[chrFirstname] &
" " &
[chrLastName]". Do I input this? I've been reading on the append
queries;
would that solve the issue? I came across this: "you can create a
full name
calculated field by combining the text from the first name and last
name
fields. You can then append the full name values to a full name
field in a
table." That sounds like what I want but I have NO idea how to do
that. I
only have one table: tblName - do I need two? Where would the
calculated
field reside?

 

[Pieter Wijnen]

On the other hand, why store the combination? Bad practice having redundant
data in the table!
Use a Query to Combine the fields as needed
ie

Select A.ChrFirstName, A.ChrLastName, A.ChrFirstName& ' ' & A.ChrLastName AS
FullName From MyTable A

Will always give the correct answer (even if somebody changes their name)

Pieter

I got it! If I add the brackets around the names, it works: [chrFirstName]
&"
" & [chrLastName]

Thanks - I couldn't have got this far alone!
--
Thank you! - Jennifer

Hi, Jennifer. Thanks for the kind words.

It looks like you may have put quote marks in the formula. The only
quote
marks should be the ones around the space between the names. It ought to
look
like this:

chrCombinedName: chrFirstName &" " & chrLastName

If it doesn't work, then please copy the exact text of the formula and
paste
it back into your reply.

I hope this works for you!

Ok, I think I did something wrong

I did the query just as you said, but when I open the table back up, I
see
this:

chrFirstName chrLastName chrCombinedName
Jennifer Cali chrFirstName chrLastName
Jack Traper chrFirstName chrLastName
Andy Zoller chrFirstName chrLastName

It seems to be reading my entry as text and not as a formula. How do I
make
it read as a formula to show the combined name and not (what appears to
be)
the formula I entered?



--
Thank you! - Jennifer


:

Hi, Jennifer. The simple answer is that you can't do calculations in
a table.
Tables are static; they hold stuff. However, we can use queries to
make
lots of calculations, and store the results back in the table.
Queries are
dynamic; they DO stuff.

So, let's say that you have a "Contacts" table and you have two
fields
FirstName and LastName. You want to fill in the field FullName in
the
Contacts table which will contain the FirstName, a space, and the
LastName.

Create a query and add the Contacts table to it. Drag the three
fields
FirstName, LastName and FullName into the query window.

Change your query to an Update Query, and click in the "Update To"
row of
the FullName field. Type in the following:

FirstName & " " & LastName

When you run the query, it will fill in the FullName for each record
as
"FirstName LastName" (with the space between them).

However, you probably wouldn't want to actually store the FullName in
the
table; you don't need to. You'll want the FullName in reports, sure,
but you
can calculate it on the fly using almost the same syntax in the
report that
you would have used in the update query.

Or, create a query and put in your Firstname and LastName fields as
before.
( In this example, we'll say that you don't have a FullName column,
just
First and Last.) Then in the third column of the query, in the top
row, type
the following:

FullName: FirstName & " " & LastName

Whenever you run the query, you'll see three columns; the two from
your
table, and the FullName column calculated whenever you need it. Then
you can
base your forms and reports on the Query and not on the table.

Play around with this idea. Remember that Access is a real "division
of
labor" program; each part of the database (tables, queries, forms and
reports) do one thing well. Use the part that does each job best.

:

I'm trying to figure out how to have a field in a table that is a
calculated
value. Is that possible? I have two fields: chrFirstName and
chrLastName. I
want the calculated field to be something like: "=[chrFirstname] &
" " &
[chrLastName]". Do I input this? I've been reading on the append
queries;
would that solve the issue? I came across this: "you can create a
full name
calculated field by combining the text from the first name and last
name
fields. You can then append the full name values to a full name
field in a
table." That sounds like what I want but I have NO idea how to do
that. I
only have one table: tblName - do I need two? Where would the
calculated
field reside?

--

 

[Pieter Wijnen]

Not quite true, you only need brackets with non-hungarian notation names
(containing spaces etc)
else you can (should) prefix with the table name (or Alias)

ie

UPDATE MyTable As A SET A.Combo = A.First & ' ' & A.Last

Pieter

(but it's no point in storing the redundant data in the first place, as it
can be calculated in a query or control or function...)

OK, my mistake; when you created the Update Query, I had forgotten that
for
this one part of the program, the query will not automatically enclose the
field names in square brackets. Type your query like this:

[chrFirstName] & " " & [chrLastName]

In almost every other part of Access, the program puts those square
brackets
in automagically. You have to HAVE the brackets, but this is the only
place
that you have to TYPE them.

Sorry about that.

Ok, I think I did something wrong

I did the query just as you said, but when I open the table back up, I
see
this:

chrFirstName chrLastName chrCombinedName
Jennifer Cali chrFirstName chrLastName
Jack Traper chrFirstName chrLastName
Andy Zoller chrFirstName chrLastName

It seems to be reading my entry as text and not as a formula. How do I
make
it read as a formula to show the combined name and not (what appears to
be)
the formula I entered?



--
Thank you! - Jennifer

Hi, Jennifer. The simple answer is that you can't do calculations in a
table.
Tables are static; they hold stuff. However, we can use queries to
make
lots of calculations, and store the results back in the table. Queries
are
dynamic; they DO stuff.

So, let's say that you have a "Contacts" table and you have two fields
FirstName and LastName. You want to fill in the field FullName in the
Contacts table which will contain the FirstName, a space, and the
LastName.

Create a query and add the Contacts table to it. Drag the three fields
FirstName, LastName and FullName into the query window.

Change your query to an Update Query, and click in the "Update To" row
of
the FullName field. Type in the following:

FirstName & " " & LastName

When you run the query, it will fill in the FullName for each record as
"FirstName LastName" (with the space between them).

However, you probably wouldn't want to actually store the FullName in
the
table; you don't need to. You'll want the FullName in reports, sure,
but you
can calculate it on the fly using almost the same syntax in the report
that
you would have used in the update query.

Or, create a query and put in your Firstname and LastName fields as
before.
( In this example, we'll say that you don't have a FullName column,
just
First and Last.) Then in the third column of the query, in the top
row, type
the following:

FullName: FirstName & " " & LastName

Whenever you run the query, you'll see three columns; the two from your
table, and the FullName column calculated whenever you need it. Then
you can
base your forms and reports on the Query and not on the table.

Play around with this idea. Remember that Access is a real "division
of
labor" program; each part of the database (tables, queries, forms and
reports) do one thing well. Use the part that does each job best.

:

I'm trying to figure out how to have a field in a table that is a
calculated
value. Is that possible? I have two fields: chrFirstName and
chrLastName. I
want the calculated field to be something like: "=[chrFirstname] & "
" &
[chrLastName]". Do I input this? I've been reading on the append
queries;
would that solve the issue? I came across this: "you can create a
full name
calculated field by combining the text from the first name and last
name
fields. You can then append the full name values to a full name field
in a
table." That sounds like what I want but I have NO idea how to do
that. I
only have one table: tblName - do I need two? Where would the
calculated
field reside?

--

 

[Guest]

Thank you, Pieter; you are correct, of course. I religiously avoid using
spaces in field names (in Access, they're generally OK, but other databases
sometimes freak out about them) so I tend to neglect the brackets.

But in the Update Query, you have to type the brackets even when you haven't
used any spaces. This is the only (? very few places, anyway) where Access
doesn't put in the brackets for you.

And a good point about not storing redundant data. I had said that further
on, I think.

Not quite true, you only need brackets with non-hungarian notation names
(containing spaces etc)
else you can (should) prefix with the table name (or Alias)

ie

UPDATE MyTable As A SET A.Combo = A.First & ' ' & A.Last

Pieter

(but it's no point in storing the redundant data in the first place, as it
can be calculated in a query or control or function...)

OK, my mistake; when you created the Update Query, I had forgotten that
for
this one part of the program, the query will not automatically enclose the
field names in square brackets. Type your query like this:

[chrFirstName] & " " & [chrLastName]

In almost every other part of Access, the program puts those square
brackets
in automagically. You have to HAVE the brackets, but this is the only
place
that you have to TYPE them.

Sorry about that.

Ok, I think I did something wrong

I did the query just as you said, but when I open the table back up, I
see
this:

chrFirstName chrLastName chrCombinedName
Jennifer Cali chrFirstName chrLastName
Jack Traper chrFirstName chrLastName
Andy Zoller chrFirstName chrLastName

It seems to be reading my entry as text and not as a formula. How do I
make
it read as a formula to show the combined name and not (what appears to
be)
the formula I entered?



--
Thank you! - Jennifer


:

Hi, Jennifer. The simple answer is that you can't do calculations in a
table.
Tables are static; they hold stuff. However, we can use queries to
make
lots of calculations, and store the results back in the table. Queries
are
dynamic; they DO stuff.

So, let's say that you have a "Contacts" table and you have two fields
FirstName and LastName. You want to fill in the field FullName in the
Contacts table which will contain the FirstName, a space, and the
LastName.

Create a query and add the Contacts table to it. Drag the three fields
FirstName, LastName and FullName into the query window.

Change your query to an Update Query, and click in the "Update To" row
of
the FullName field. Type in the following:

FirstName & " " & LastName

When you run the query, it will fill in the FullName for each record as
"FirstName LastName" (with the space between them).

However, you probably wouldn't want to actually store the FullName in
the
table; you don't need to. You'll want the FullName in reports, sure,
but you
can calculate it on the fly using almost the same syntax in the report
that
you would have used in the update query.

Or, create a query and put in your Firstname and LastName fields as
before.
( In this example, we'll say that you don't have a FullName column,
just
First and Last.) Then in the third column of the query, in the top
row, type
the following:

FullName: FirstName & " " & LastName

Whenever you run the query, you'll see three columns; the two from your
table, and the FullName column calculated whenever you need it. Then
you can
base your forms and reports on the Query and not on the table.

Play around with this idea. Remember that Access is a real "division
of
labor" program; each part of the database (tables, queries, forms and
reports) do one thing well. Use the part that does each job best.

:

I'm trying to figure out how to have a field in a table that is a
calculated
value. Is that possible? I have two fields: chrFirstName and
chrLastName. I
want the calculated field to be something like: "=[chrFirstname] & "
" &
[chrLastName]". Do I input this? I've been reading on the append
queries;
would that solve the issue? I came across this: "you can create a
full name
calculated field by combining the text from the first name and last
name
fields. You can then append the full name values to a full name field
in a
table." That sounds like what I want but I have NO idea how to do
that. I
only have one table: tblName - do I need two? Where would the
calculated
field reside?

--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4079 spam emails to date.
Paying users do not have this message in their emails.
Get the free SPAMfighter here: http://www.spamfighter.com/len