Relational Databases and combining field values

[kaleidoscopicdk]

I have a multiple part question.

I am setting up a Job Log Database for our Creative Services group. Our clients could be any of our internal departments.

I want the job number to be comprised of the Division Code, Department Code and a sequential number based on that unique division and department combination; i.e., 001-200-1

I set up a second database with the department and division (value lists) and department and division codes (calculated using the case function).

First: Is there an advantage to having these values in another database vs. in another layout of the job log database?

Second: If they should be in the same database, how do I copy the fields with all their accompanying definitions and formats?

Third: How do I concatinate the fields to create the numbering system I specified above. (Division Code, Department Code and a sequential number based on that unique division and department combination; i.e., 001-200-1)

I'm getting lost in the manual and any help you can provide would be greatly appreciated!

[fishma]

I'll try to answer as best as I understand your questions...

1. The benifit of seperating values into a seperate table and relating to them via a unique id, is that if the data needs to be changed, you only have to change the data once (at the related record).

2. You should always normalize your data. Bottom line, that information should be in a seperate table.

3. To concatenate your id you can use the following equation...

Store the field as a calculation(text)

department_code & " - " & division_code & " - " & id

where id is your sequential number field.

I hope this helps you...

Fishma!