Solved

Auto increment value

Posted on 2012-03-27
13
669 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 50 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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 450 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

Webinar: Deploying MySQL in production 6/22 11am

Join Percona’s Senior Operations Engineer, Daniel Kowalewski as he presents Deploying MySQL in production on Thursday, June 22, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7).

Question has a verified solution.

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

A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

729 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