Access novice question

Corcs001

Hi

I'm pretty new to Access and there is a field I am using called Cus_No. The person who created it gave it a text data type instead of auto-number, I have to leave the number already given to each customer as they are and I want to put the year they were created in front of the number that is already there eg. Cus_no 001 becomes 05-001. Is there a way to quickly do this for every column or will I have to do it manually?

Thanks.

TommyGunne

First off, if you want to perform any real operations on databases, you are much better off in investing in something like oracle, or other more direct DBMSs. The way I would do this is to go into the SQL entering area of access, can't remember how to get there cos I haven't used it in years, but you will be able to find out from a quick google if you don't know already. Then enter the following code:

UPDATE your_table
set cus_no = SELECT to_char
(SYSDATE, 'YY')||cus_no
FROM your_table;

Obviously changing your_table to the table name desired, and SYSDATE to the field in the table that contains the creation date, cos its just showing todays year as is. Et voila.

I'm sure someone has a more ergonomic way that involves better use of MS's features, but this should work.


EDIT: Also, it is generally good practice to test stuff like this before you use it on the full data. Also, it might be a good idea to set it to a new column, (eg alter table your_table add new_column text(30)) and if it works as desired, then you can do what you like with the original column.

Also, not exactly sure why you would want to do this but just to be warned, it is possible that what you are doing is not good practice. Post why you are doing this as well. Also, you should get a second opinion first.

--amadeus--

Bit of database snobbery there A small company with a few dozen / hundred customers doesn't need / couldn't afford or support a full blown Oracle installation. For simple stuff like tracking addresses and contact names Access is more than good enough and it has teh benefit of being usable (in the main) by people with minimal technical skills.

OP - does your table have a creation date? Will there be lots of different pre-fixes (05 / 06 / 07 etc) or just one? Do you have to make a permanent change to teh recordset or would having the "new" number appear on reports be enough? Will this new identifyer be automatically generated from now on?

There are probably dozens of ways of doing this from an extra field in reports and forms that concatenates the new year value with the old cust number through to an export to Excel, merge data and re-import and right up to SQL solutions. The best one is going to depend on the answers to those questions.

Tom Dunne

TommyGunne wrote: »

First off, if you want to perform any real operations on databases, you are much better off in investing in something like oracle, or other more direct DBMSs. The way I would do this is to go into the SQL entering area of access, can't remember how to get there cos I haven't used it in years, but you will be able to find out from a quick google if you don't know already. Then enter the following code:

UPDATE your_table
set cus_no = SELECT to_char
(SYSDATE, 'YY')||cus_no
FROM your_table;

Obviously changing your_table to the table name desired, and SYSDATE to the field in the table that contains the creation date, cos its just showing todays year as is. Et voila.

I'm sure someone has a more ergonomic way that involves better use of MS's features, but this should work.


EDIT: Also, it is generally good practice to test stuff like this before you use it on the full data. Also, it might be a good idea to set it to a new column, (eg alter table your_table add new_column text(30)) and if it works as desired, then you can do what you like with the original column.

Also, not exactly sure why you would want to do this but just to be warned, it is possible that what you are doing is not good practice. Post why you are doing this as well. Also, you should get a second opinion first.

Start off by ignoring pretty much everything in the above post.

To answer your question, yes, there is a way of doing this on every column, but as --amadeus-- says, it depends on a load of things.

Post up some more information on the table layout, what other fields are there and how exactly you determine what year each cus_no belongs to (e.g. is there another date field in the table, or do you simply want to update them all with the one year?).

AccessQuery

Hello,
I've been running Access DBs for business for quiet a number of years. If you'd like, send me a copy of your DB with test data on board and I'd be happy to have a look at it for you.

PM me for address etc.