Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Auto increment value

Posted on 2012-03-27
13
Medium Priority
?
678 Views
Last Modified: 2012-03-28
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.
0
Comment
Question by:currentdb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
13 Comments
 
LVL 40

Expert Comment

by:als315
ID: 37774982
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
 
LVL 1

Author Comment

by:currentdb
ID: 37774997
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
 
LVL 24

Assisted Solution

by:johanntagle
johanntagle earned 200 total points
ID: 37775010
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 40

Expert Comment

by:als315
ID: 37775046
"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
 
LVL 1

Author Comment

by:currentdb
ID: 37775047
For now the db is stored in Access. Later on I will export it to MySQL.
0
 
LVL 40

Expert Comment

by:als315
ID: 37775064
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
 
LVL 1

Author Comment

by:currentdb
ID: 37775074
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
 
LVL 40

Accepted Solution

by:
als315 earned 1800 total points
ID: 37775127
OK, try this sample (form tblMovies)
DBIncr.mdb
0
 
LVL 1

Author Comment

by:currentdb
ID: 37775205
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
 
LVL 40

Expert Comment

by:als315
ID: 37775249
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
 
LVL 1

Author Comment

by:currentdb
ID: 37775276
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
 
LVL 40

Expert Comment

by:als315
ID: 37775293
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
 
LVL 1

Author Comment

by:currentdb
ID: 37775355
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question