Adding a character in front of a field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sunny Sethi - 09 Aug 2004 20:03 GMT

Hi,

In a table I have 1 field(expense_id) which is a auto-
number field.

I want to create another field using the same value as the
(expense_id) BUT appending H in front of it.

e.g. expense_id - value = 1
refernce_if - value = H1

Thanks
Sunny
sethi.sunny@infrastructure.gc.ca

Reply to this Message

'69 Camaro - 09 Aug 2004 20:40 GMT

Hi, Sunny.

1.) Create a new field in your table, called reference_if.
2.) Create a new query and open the SQL View pane and write a SQL statement
such as the following:

UPDATE myTableName
SET reference_if = "H" & expense_id;

3.) Run the query.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

> Hi,
>
[quoted text clipped - 10 lines]
> Sunny
> sethi.sunny@infrastructure.gc.ca

Reply to this Message

Douglas J. Steele - 09 Aug 2004 22:55 GMT

NO! That's a violation of relational database theory: a field in a table
should not be totally derivable from other fields in the table.

Instead, create a query that has a computed field reference_if: "H" &
expense_id, then use the query wherever you would otherwise have used the
table.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Hi, Sunny.
>
[quoted text clipped - 28 lines]
> > Sunny
> > sethi.sunny@infrastructure.gc.ca

Reply to this Message

'69 Camaro - 10 Aug 2004 01:28 GMT

Thanks, Doug, for that very important reminder! I forgot to mention it in
my earlier post, obviously. However, there are a few reasons to temporarily
(or permanently) violate the rules of relational database theory, such as
when the need arises to match table structures or to improve the performance
of queries. The bottom line is that one needs to be fully grounded in
proper relational database theory, and then know when relational database
rules can be bent.

Gunny

> NO! That's a violation of relational database theory: a field in a table
> should not be totally derivable from other fields in the table.
[quoted text clipped - 36 lines]
> > > Sunny
> > > sethi.sunny@infrastructure.gc.ca

Reply to this Message