Auto increment value

Hello Experts,

I made a small database that has 7 fields:
Record_ID
Language
Dict_name
Word_ID
Video_name
Webpage link location
Webpage link video

If I have these values:
Record_Id = 1
Language = E
Dict_name= able
Word_ID = A290001
Video_name = A290001.mov
Webpage link location = www.mysite/index/dict/A290001.html
Webpage link video = www.mysite.com/index/dict/videos/A290001.mov

Now if I add a new record and a new name for the field Dict_name, how I can make the field Word_ID to automatically adjust a new number? If I add the word "car", then the Word_Id field should change to A290002. Same thing the field Video_name. The last 2 fields, webpage link location and webpage link video should change as well like www.mysite/index/dict/A290002.html and www.mysite.com/index/dict/videos/A290002.mov


Any help would be greatly appreciated.

Many thanks in advance.
LVL 1
currentdbAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

als315Commented:
You can do it with dmax function, for example, but why you need it? What is "A29" and how it will be incremented? You already have Record_ID and you can create your Word_ID ="A29" & format(Record_ID,"0000") when you need it.
As I see, you should not to store this fields in db (only if you like to be able manully modify them):
Word_ID
Video_name
Webpage link location
Webpage link video
0
currentdbAuthor Commented:
Hi als315,

"A29" is just a value. If it begins at A290001, it will be incremented to A290002 when a new entry is added in the database. If you think that Record_Id is not needed, then I have to define a different primary key.

As I explained, if I enter a new word (in the Dict_name field), the other 4 fields (Word_ID
Video_name, Webpage link location, Webpage link video) should automatically increment. I don't have to enter manually a new assigned value again and again. The database will have 800 words, so it will be a pain in the ass to just assign a value to them one by one.

Does dmax function do all this change ?

Thanks again.
0
johanntagleCommented:
See if something like this works for you.  I employed a trigger - Note that I only did word_id and video_name - you should be able to expand it to include the others:

mysql> create table currentdb_table (record_id int not null auto_increment primary key, dict_name varchar(20), word_id varchar(20), video_name varchar(20));
Query OK, 0 rows affected (0.09 sec)

mysql> delimiter |
mysql> create trigger currentdb_insert 
    -> before insert on currentdb_table 
    -> for each row begin   
    -> DECLARE temp_int integer; 
    -> DECLARE temp_word varchar(20); 
    -> set temp_int=(select ifnull(max(record_id),0)+1 from currentdb_table); 
    -> set temp_word=concat('A', 290000+temp_int); 
    -> set NEW.word_id=temp_word;  
    -> set NEW.video_name=concat(temp_word,'.mov'); 
    -> end;|
Query OK, 0 rows affected (0.09 sec)

mysql> delimiter ;
mysql> insert into currentdb_table (dict_name) values ('able');
Query OK, 1 row affected (0.00 sec)

mysql> insert into currentdb_table (dict_name) values ('car');
Query OK, 1 row affected (0.00 sec)

mysql> select * from currentdb_table;
+-----------+-----------+---------+-------------+
| record_id | dict_name | word_id | video_name  |
+-----------+-----------+---------+-------------+
|         1 | able      | A290001 | A290001.mov |
|         2 | car       | A290002 | A290002.mov |
+-----------+-----------+---------+-------------+
2 rows in set (0.00 sec)

Open in new window


Having done that, I would recommend that you reconsider your design, especially on storing the link and video location urls.  If they will all have the same base url, I suggest that you have that base url as a setting in your application instead and just store A290001.html.  This way, if you change locations later on, you only need to change the application setting instead of all records in the database.  Going further, you really only need to store word_id and just dynamically form the others, since they follow a single base format.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

als315Commented:
"If you think that Record_Id is not needed, then I have to define a different primary key."
I think it is the only field you need to construct all other fields.
Your data are stored in MySQL or in Access DB?
0
currentdbAuthor Commented:
For now the db is stored in Access. Later on I will export it to MySQL.
0
als315Commented:
You can't use same code for Access and MySQL. What kind of code you need? And you don't  answered: why you like to store this name in DB when you can construct it every time you need it?
0
currentdbAuthor Commented:
Hi als315,

I know Access and MYSQL do not use the same code. For now I need the code that works under Access, the one for DMAX function.

>>why you like to store this name in DB when you can construct it every time you need it?
It's for tracking purposes. If we add a new name in this "dictionary", we don't need to figure an id number for this dictionary and its video. It is not needed to construct it every time, at least for now.

Thanks again for your patience.
0
als315Commented:
OK, try this sample (form tblMovies)
DBIncr.mdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
currentdbAuthor Commented:
I made a small change and added the field "Dict_name". The field Word_ID does not increment even if I modified the line in Module 1 (the line that refers to the proper form).

Attached is the sample.
DBIncr-v2.mdb
0
als315Commented:
I am using field's "default value" for filling it from function. You can see it in my example. Check also table name in function Next_WordID (Module1)
0
currentdbAuthor Commented:
I can see the default value for "Word_ID" in the form tblMovies as well as in Module1.

But here I added the field "Dict_name" before the field "Word_Id", and changed Module 1 accordingly to point to tblMovies1 instead of tblMovies2. Except there is a bug error and it points to the same line that I changed in Module 1, which is A = DMax("[Word_ID]", "tblMovies1")
0
als315Commented:
In Dmax parameter: "tblmovies" is name of table, not a form, you shouldn't change it.
Default value is assigned to a new records in your table:
default value
0
currentdbAuthor Commented:
My bad, it was my fault. I mean when I created the second form to incorporate the "Dict_name" field, I forgot to set the default value as specified.

Now it works and thank you so much for your help :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.