Combine Info from 2 fields into a third

[JeepsR4Mud]

I am designing a database, and entry forms concerning some staff issues at work. Each site (33 of them) has to submit a weekly report. What makes each record unique is both the site location and the week.

After thinking about the logistics of trying to always link those two fields, I decided that perhaps it was easier to create a third field, ID that linked the information from the fields INSTITUTION and WEEK (site ID and date identifying the week of the report).

Is there a way to have the data in the ID field, automatically generated after the values for INSTITUTION and WEEK are entered?

I'm trying to reduce key strokes AND make this VERY user friendly.

THANKS so much.

Gayle Ann

 

[Jack Cowley]

Generally speaking you would not create a field that is the combination of two other fields. You can combine the two fields at any time with concatenation so I am not sure why you want to create the ID field. How will having the ID field help your database? My apologies if I am missing something....

Jack

 

[JeepsR4Mud]

I didn't explain it very well.

I'm trying (note the word "trying" ) to creat a database to record weekly reports from 33 different offices throughout the state (work for the state).

What makes each record unique is both the site abreviation (ABC) and the week. (ABC and 09-08-02).

I also have several tables that are linked to the main table. These are tables for questions that canhave multiple answers, for example staff injuries. Heaven forbid, but it is possible to have more than one staff injury in a week.

So, I thought it would be efficient to assign each record a unique ID, a combination of the site name and the date. Therefore, it would function to a certian degree like an employee number.

If I retain the field INSTITUTION, I can always pull out the records specific to a certain site, and if I retain date, I can view all records from a particular week.

But, it seemed prudent to design some typ[e of ID field to lik the primary table with all the other tables.

Did I explain that well?

 

[Jack Cowley]

I am easily confused so this may not be the exactly what you are asking, but it does create the 'unique' ID you are looking for... Open your 'main' table in design view and while holding down the Shift key click on the Institution field and then the Date field. Both items will be highlighted. Now you can click on the Key icon and you will have created a compound Primary Key. All should work as before and you have your unique ID as well for this table.

I hope that I have made sense and that this will work for you!

Jack

 

[JeepsR4Mud]

I've never done a compound primary key.

But, it would simply somehow create a bond between the two fields that would always link the values?

I thought though that with a primary key, you couldn't repeat the values.

I have 33 sites that will submit a report each week, so for one year institution ABC will be listed. And of course the DATE field willbe repeated 33 times, but, both would never be repeated together. There would NEVER be 2 ABC records for the week of 09-08-02.

Does that make sense?

I KNOW what I want to do. I'm just not good at explainint it.

Gayle Ann

 

[Jack Cowley]

Gayle Ann -

You can still enter ABC as many times as you like and the Date as many times as you like, you just can't have two records with the same Location and Date. If you try to enter ABC and 1/2/2002 and that combination already exists you will get a message from Access, but if it does not exist you can go on your merry way.

I hope I have made it a bit more clear. If not, I am a patient man so keep asking!

Jack

 

[JeepsR4Mud]

A man who is patient?

Didn't know such a thing existed? What a remarkable find!

The double key though would appear to be easier than creating another field.

So, I'd just have those same two fields in my other tables, like staff injury?

Currently, I have the ID field in each table, with one to many relationships from the primary table to the other tables.

When I create the relationships between the tables, are both fields automatically inculded?

Gayle Ann
Who is very greatful for your patience.

 

[Jack Cowley]

Gayle Ann -

I am not sure how you have your relationships set up so this a hard one to answer and get it right. You only need to join your Many table via one of the ID's, but you may have a Many to Many situation and then that requires a Junction table that would have the Location and Date fields only. Now I bet your head is spinning as I know mine is!

To get you started in Database Normalization here is a very simple tutorial , but, unfortunately it does not get into Many to Many relationships. The Northwind db that comes will Access has a Many to Many relationship and Help, as bad as it is, may give you some 'help'. If you are not sure then you can list your tables something like this and I willl try to assist you in setting it up correctly if it is not already correct, which it probably is!

Example

tblMain
Location (PK)
EnterDate (PK)
other fields...

tblLocation
LocationID (PK)
Location (FK {Foreign Key})
other fields...

tblOther
OtherID (PK)
other fields....

Jack

 

[JeepsR4Mud]

Currently, I have:

tblWeeklyReport
ID (PK)
INSTITUTION
WEEK
other fields

Staff Injury
ID (PK)
other fields

Assaults
ID (PK)
other fields

Inmate ER Trips
ID (PK)
other fields

etc...

I do have a table called INSTITUTIONS, which pretty much serves as a table from which to get values in the Weekly Report form.


It would seem that while creating a compound key is the most EFFICIENT and technically correct thing to do, perhaps it is also the most complicated thing to do?

However, I'm always up to new challenges. And, I generally learn by doing, so...

I have the other tables as subforms in my primary form, Weekly Report. What I like about the ID key is that as soon as it is entered, the value pops up in all of the subforms.

I need to install the Northwinds DB.

Eventually, if I get this working with any degree of proficiency, the plan is to install it on the department server and have all the sites enter their own data. But, the forcast for that is January, mostly because I said I needed some time to do the Access stuff,a nd because we want to make sure we have the info in the reports we want.

Have to say I like playing with it, but then I always liked puzzles.

Thanks.

 

[Jack Cowley]

It would appear that Staff Injury, Assults, etc. are lookup tables and it looks like you are on the right track to me, but that is hardly gospel. And your forms/subform sounds right too.

I do not think the compound key will complicate anything. All it means is that you cannot enter the same location and date for two records and you won't have that anyway. If you do not like the idea of them being "primary" then you can remove the primary keys then click on the Index icon on the menu bar (to the right of the Key); type in a name in the Index Name field; in the lower part set 'Unique' to Yes; select Location in the Field Name field, move to the next line down and in the Field Name select the Date field. Now you will not be able to enter duplicate data but the fields will not show as 'Primary'.

I hope that I am not confusing the issue. Do what works best for you as this is YOUR database and you should have it anyway you like it, though you do want it to be properly normalized or you will have problems later one creating queries, etc. I say go for the gusto!

Jack

 

[JeepsR4Mud]

It's that properly normalized thing that is the key.

I should be done with grad school this year, and I've already been looking for other positions. I want this designed properly, even if it means living on Extra Strength Tylenol, because I don't want anyone to inherit my incompetencies.

I'm going to finish some data entry, so I have some real data, then play around with the compound primary key.

I have a one to many relationship on ID between the WEEKLY REPORT table and all of the other tables.

So, I'll play with the many to many.

I probably won't be playing with it till Friday. I have a PowerPoint presentation on which I'm working for someone else. They like the way I use the animation and sound. This one uses the Mission Impossible music. And, I have class tomorrow afternoon.

I also want to do some more data entry. I'm finding some mistakes and also have some suggestions for additional information to be added.

Access is one of those programs that is like CHemistry. The books and much of the info available is for peopole who already extremely proficient. But, there isn't much in the way of resources to get people from novice to expert other than the kidness of others.

THanks again.

 

[Jack Cowley]

You are a busy woman and I commend you on your perserverance, dedication and desire to want to do it right. If you can do PowerPoint then Access should be easy for you! PowerPoint leaves me breathless and I admire anyone who can get it to play a tune.

I wish you the very best and If you have questions you know that you are welcome here at any time!

Continued success in school, with PowerPoint and with Access!

Jack

 

[JeepsR4Mud]

I'll answer all your PowerPoint questions if you answer my Access ones!

PowerPoint is like a puzzle too. Knowing what you want to do, then figuring out how to do it.

WIth Access, I know there are puzzle pieces, but they are are jumbled, and I don't know how they fit together.

 

[Jack Cowley]

A very kind offer indeed! The good news is that I NEVER do anything in PowerPoint as I have no reason to and Access as an avocation for me. Nothing is riding on my ability to create a database so life is good!

I will, of course, answer your questions if I can. I am a moderator at another site so I don't get over here as often as I would like so I somethings go missing. Not to worry as there are some VERY erudite individuals here and you are in excellent hands!

Jack