Novice
Windows 7 64bit 
Access 2007
I am a new access user and I have several questions primarily can i relate field names to a primary key in another table? I would greatly appreciate it if someone could offer some direction in the development of my database. I can't offer much but if someone would look at what i have and help me get it working I would gladly split the 1000 they are offering me right down the middle.
Expert
Windows XP Access 2000
Yup! That's called a "Foreign Key" and is managed through the "Relationships" manager in Access. All you have to do is open the Relationships window and then link the two tables together by clicking and dragging the field from one table to it's counterpart in the other table.
P.S.
Be careful about offering monies over the interwebs! You could get all sorts of crackpots (like me, for example!!!)
Master of Nothing
Windows XP Access 2000
There are many sites available to learn about Access basics.
To start:
http://www.accessmvp.com/Strive4Peace/
http://databases.about.com/od/specif...malization.htm
Google: "Access Normalization" or "Access basics"
Novice
Windows 7 64bit Access 2007
K first Rawb thanks so much for replying your my favorite kind of crackpot.
secondly I know i didnt put much info in the initial post and probly worded my question incorrectly, my issue is can i make field names the same as entries in another table ie..
table 1 has a field "primary key" called competency code and the records are recorded as cca01 cca02 ccb01 ccb02 etc...
competency code
cca01
cca02
ccb01
ccb02
i then have another table for employees and their ratings in each of these categories setup with employee as the primary key and cca01 as a field cca02 as a field and so on with the employee's rating on a number scale 1-5 in these fields so ...Julie is rated as a 4 in cca01, 3 in cca02
Employee cca01 cca02 ccb01 ccb02
Julie 4 3 4 4
this flows over into requirements by position (more tables) showing the requirements by the same number scale
competency code receptionist executive assistant 1 executive assistant 2
cca01 5 4 4
cca02 3 4 5
so i can then say julie has this position which requires a 5 in cca01 a 3 in cca02 and she is rated as a 4 in cca01, 3 in cca02 ..so she needs to work to improve in cca01 and be able to see if she wanted to move to another position what the requirements are compared to her ratings in that position.
i have tables for each dept with position requirements, a position table and an employee table with ratings on each of the competency codes.
I know i more than likely need to create tables with dept and a complete list of all the competency codes
VIP
Windows XP Access 2010 32bit
whatever you do Make sure you understand Normalization. you want your database structure in at least 3rd Normal Form. Can't emphasize that enough.
Expert
Windows XP Access 2000
Ooooohhhhhhhh! I got ya.
I'm not aware of any way to do that. However, you could create a Table like the following:
EmployeeCC
Employee|Competency Code|Competency Rating
Julie|cca01|4
Julie|cca02|3
Julie|ccb01|4
Julie|ccb02|4
CCReqs
Position|Competency Code|Required Rating
Receptionist|cca01|5
Receptionist|cca02|3
Executive Assistant 1|cca01|4
Executive Assistant 1|cca02|4
Executive Assistant 2|cca01|4
Executive Assistant 2|cca02|5
Of course, you'd probably want to use PositionIDs that refer to a Position Table (and EmployeeIDs that refer to an Employee Table) instead of just using the names.
Novice
Windows 7 64bit Access 2007
Thanks ssanfu I will check these out. I did do an online video tutorial but it never touched on this subject so i have searched the forums and I have a manual from Que but again cant find the answer, this could be because I am not looking in the right places. I could really use some direction like "your tryin to make a subform so read about those" or "no it doesnt work like that you have to change this".
Novice
Windows 7 64bit Access 2007
Thank you so much guys. I am going to create those tables right now Rawb you really helped push me in the right direction
Master of Nothing
Windows XP Access 2000
You guys posted while I was typing, but here it is anyway.....
From what you ave provided I have some thoughts..... (this is top of the head stuff, subject to change, and worth what you paid for it)
Read Crystal's tutorial. I still read it and pick up things.
The reason I say to read the tutorial is that you are "committing spreadsheet". That is when you design your tables to look like Excel. You have to forget about Excel (spreadsheets). A relational database is not a spreadsheet.
For example, the Employee table example you gave. You have data (cca02) as field names. A better structure would (MIGHT) be (as a start):
Code:tblEmployee ------------- EmpID (PK) FName LName 1 Julie Jones 2 Sam Spade
Code:tblCompetency ------------- CompetencyID Category 1 cca01 2 cca02 3 ccb01 4 ccb02Code:tblPosition ------------ PositionID PosDesc 1 receptionist 2 executive assistant 1 3 executive assistant 2
---------------
Junction tables
---------------
Code:tblEmp_Rating --------------- Rating_PK EmpID_FK CompetencyID_FK CompetancyRating 1 1 1 4 2 1 2 3 3 1 3 4 4 1 4 4
Code:tblPositionCompetency ------------ PosCompID PositionID_FK CompetencyID_FK Rating 1 1 1 5 2 2 1 4 3 3 1 4 4 1 2 3 5 2 2 4 6 3 2 4
Using your structure, what would happen if you wanted to add another position? Or another competency? You would have to change your tables, forms, queries, code and reports.
If you take the time to design a structure that is in 3rd normal form, you add the new position to a table and you are done!
Start the design process using paper and pencil (or a white board - my favorite)... it really helps.....
Remember, the ideas expressed here have a limited guarantee.....
whoops, it just expired
Novice
Windows 7 64bit Access 2007
Darn guarantee has expired..
Novice in need of direction
Direction needed.....
Posting Permissions
Reply With Quote