Access db - auto generate data in a column?

[Busie23]

Column A = First Name
Column B = Last Name

How do I get Column C to automatically come up with Lastname Firstname initial?

Fred Jones = JonesF

 

[statik213]

Originally posted by: Busie23
Column A = First Name
Column B = Last Name

How do I get Column C to automatically come up with Lastname Firstname initial?

Fred Jones = JonesF

Why would you want to duplicate data like that?
I think you would be able to create an index that does this... at least in MySQL, haven't looked at Access in a looong time... If you can index with arbitiary column combinations try something like CONCAT(ColumnB,STRPOS(ColumnA,0)) where CONCAT is the concatanation function in access and the strpos is a function that returns the char at give pos... make sense? Access should have equivevelent functions...

good luck....

 

[EagleKeeper]

The whole purpose of normalizing tables is to prevent redundant data.

If you want the information show as described, then do it via a query or within the display "application" code.

 

[HardcoreRobot]

thats a function for a query or a report
or you can get fancy and write a short vb function to calculate it and update the table

 

[Busie23]

the DB is being called form a php page. Does anyone have some sample code?

 

[EagleKeeper]

Originally posted by: Busie23
the DB is being called form a php page. Does anyone have some sample code?

Then let the page display a concatination of the data that is retreived

 

[Busie23]

Maybe there is a better way to do this. Let me explain what is happening. We have a db has user information in it for an online phone directory. Name, number, posistion, etc. are in there. We have also just recently added photos for everyone as well. All of the photos are named DoeJ.jpg for example. So in the db we have a first name column, last name column, and about 10 other's but then we have the photo name column which to me seems like a waste to type since the info is already in there.

I was initially thinking that instead of typing in DoeJ.jpg, have access create it based off of the first name and last name columns. Then you guys are right about the redundant data. So then there must be a way to have our php pages generate the phot name based off of the access db correct? How do you get it to pull just the first letter of a name out though?

 

[statik213]

Originally posted by: Busie23
Maybe there is a better way to do this. Let me explain what is happening. We have a db has user information in it for an online phone directory. Name, number, posistion, etc. are in there. We have also just recently added photos for everyone as well. All of the photos are named DoeJ.jpg for example. So in the db we have a first name column, last name column, and about 10 other's but then we have the photo name column which to me seems like a waste to type since the info is already in there.

I was initially thinking that instead of typing in DoeJ.jpg, have access create it based off of the first name and last name columns. Then you guys are right about the redundant data. So then there must be a way to have our php pages generate the phot name based off of the access db correct? How do you get it to pull just the first letter of a name out though?

Haven't tried connecting to an Access DB from PHP..... You'll need to figure out what the equivevalent function for Access or ODBC connectivity are, but your SQL ought to look something like this:

SELECT CONCAT(last_name, SUBSTRING(first_name,0,1),'.jpg') AS `photo_file_name`
FROM table

Where CONCAT is a function that concatanates strings (should be there in access and ODBC) and SUBSTRING returns the substring of first_name starting from the 0th index and 1 character long. The third argument for concat merely adds the .jpg extension to comlete the filename.

Makes sense? Like I said, this is how you would do it in MySQL... figure out the exact functions for Access 😉

 

[AFB]

Well, if you want to use PHP instead of using the query like above^, you would just use substr().

For example, say you've stored the first name in varaible $fname and the last name in $lname.

$photo_name = $lname . substr($fname,0,1) . ".jpg";