Verifying Data exists in DB
- Thread starter vandy05
- Start date
I just want it to make sure the name is already in the DB (I understand if someone miss spells a name it can create duplicate fields but this is better than people not entering their contacts into the DB) in the other table and store it in the phone contact table as well so we know who communicated with who and when.
table 1 - site table - fields include items like ID (auto number), site name, mailing address, shipping address etc.
table 2 - site personnel - ID (linking to sites in table 1), name, phone, fax, email
table 3 - phone contact table - ID (linking to sites in table 1), date contacted, person contacted, comments
each site has at least 4 personnel.
form for phone contacts auto fills out site name, and project info based on table 1 - only item stored in the phone contact table from that is the ID. since there is more than one person at each site, we have to store the person contacted name as well (or am i just crazy).
if a person is not in the database, i want an error to appear and the user to be able to hit a command button (for a macro that opens up the site personnel form to fill out contact person information such as phone etc)
also there is a report that is required for each time you contact a person. it has to be printed out and signed. so all the information (including the items from table 1 such as site name and project info) are printed for our paper records which are required.
Does this make sense or am I just crazy?
table 2 - site personnel - ID (linking to sites in table 1), name, phone, fax, email
table 3 - phone contact table - ID (linking to sites in table 1), date contacted, person contacted, comments
each site has at least 4 personnel.
form for phone contacts auto fills out site name, and project info based on table 1 - only item stored in the phone contact table from that is the ID. since there is more than one person at each site, we have to store the person contacted name as well (or am i just crazy).
if a person is not in the database, i want an error to appear and the user to be able to hit a command button (for a macro that opens up the site personnel form to fill out contact person information such as phone etc)
also there is a report that is required for each time you contact a person. it has to be printed out and signed. so all the information (including the items from table 1 such as site name and project info) are printed for our paper records which are required.
Does this make sense or am I just crazy?
Okay so I am running across some problems.
I was hoping you could take a look at the DB and help me out a little.
I think it will make more sense once you see it.
One of the problems I am having is that each site may work on more than one protocol, thus each site personnel will work on more than one protocol.
So right now it is set up in such a way that each time a site works on a new protocol, we have to enter them into the site table...and now i noticed you have to enter the personnel again as well with the new ID number.
We have to do this right now because we need reports to show all the information about a site all at once.
Help please!
I was hoping you could take a look at the DB and help me out a little.
I think it will make more sense once you see it.
One of the problems I am having is that each site may work on more than one protocol, thus each site personnel will work on more than one protocol.
So right now it is set up in such a way that each time a site works on a new protocol, we have to enter them into the site table...and now i noticed you have to enter the personnel again as well with the new ID number.
We have to do this right now because we need reports to show all the information about a site all at once.
Help please!
RuralGuy
AWF VIP
- Local time
- Today, 06:50
- Joined
- Jul 2, 2005
- Messages
- 13,825
To start with, each table should have an AutoNumber as a PrimaryKey and if you want to relate the table to another table then include the other table's PrimaryKey as a ForeignKey field. You got the second part but not the first part of what I just said. Then study your relationships carefully. I would have expected to see a "SitePersonnel" table with a maybe only two ForeignKey fields: SiteID and PersonnelID. With that design you could determine all of the Personnel for each site. You might use the same type of design for the Protocol table. Have you studied up on Normalization at all? There are quite a few good sources on the subject. Google is your friend.
boblarson
Smeghead
- Local time
- Today, 05:50
- Joined
- Jan 12, 2001
- Messages
- 32,059
Just because I know it can be hard to know if you have found good material by Googling, here's a great Normalization tutorial (by MVP Crystal Long):
http://www.accessmvp.com/strive4peace/Access_Basics_Crystal_080113_Chapter_03.pdf
http://www.accessmvp.com/strive4peace/Access_Basics_Crystal_080113_Chapter_03.pdf
boblarson
Smeghead
- Local time
- Today, 05:50
- Joined
- Jan 12, 2001
- Messages
- 32,059
Sure, post whatever questions you have as we have a good supply of help here. Depending on what they are it may be that they should be in new posts.
