Updating a table with another table record (CallLogger To FreqCallLog)
Updating a table with another table record (CallLogger To FreqCallLog)
(OP)
Dear All,
Thank you for a great forum.
I have the following problem.
I have the following tables.
CallsLogged
Updates
Assistant
FreqAssistants
CallLogger
FreqLoggers
The tables have the following relationships.
CallsLogged the central table, has the following relationships.
Table CallsLogged with primary key field Reference linked to primary key field CallReference in Table CallLogger
Table CallsLogged with primary key field Reference linked to primary key field AssignedReference in Table Assistant
The next 2 links are to the tables
FreqAssistants
FreqLoggers
Table FreqAssistants with primary key field NameCode linked to primary key field AssistantCodein Table Assistant
AND
Table FreqLoggers with primary key field NameCode linked to primary key field RegistrarCodein Table CallLogger
What I want to happen is the following.
When in my form LogCall when the user goes to the Assistant area to fill in the AssistantCode into the table Assistant
If they fill in a recognised code TS for example, if that code exists in the tableFreqAssistants;
for the data in table Assistantto be updated with the already stored
Name, Surname, TelephoneNumber, E-mail from the table FreqAssistants;
as it is tedious for users to continually type in the same details again and again.
Any help will be greatly appreciated![[smile] smile](https://www.tipmaster.com/images/smile.gif)
Thank you!
Thank you for a great forum.
I have the following problem.
I have the following tables.
CallsLogged
Updates
Assistant
FreqAssistants
CallLogger
FreqLoggers
The tables have the following relationships.
CallsLogged the central table, has the following relationships.
Table CallsLogged with primary key field Reference linked to primary key field CallReference in Table CallLogger
Table CallsLogged with primary key field Reference linked to primary key field AssignedReference in Table Assistant
The next 2 links are to the tables
FreqAssistants
FreqLoggers
Table FreqAssistants with primary key field NameCode linked to primary key field AssistantCodein Table Assistant
AND
Table FreqLoggers with primary key field NameCode linked to primary key field RegistrarCodein Table CallLogger
What I want to happen is the following.
When in my form LogCall when the user goes to the Assistant area to fill in the AssistantCode into the table Assistant
If they fill in a recognised code TS for example, if that code exists in the tableFreqAssistants;
for the data in table Assistantto be updated with the already stored
Name, Surname, TelephoneNumber, E-mail from the table FreqAssistants;
as it is tedious for users to continually type in the same details again and again.
Any help will be greatly appreciated
![[smile] smile](https://www.tipmaster.com/images/smile.gif)
Thank you!
Thank you,
Kind regards
Triacona
RE: Updating a table with another table record (CallLogger To FreqCallLog)
It's rarely a good idea to store derived/calculated values ...
Have a look here:
http://r937.com/relational.html
Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: Updating a table with another table record (CallLogger To FreqCallLog)
If you already have Name, Surname, TelephoneNumber, E-mail stored in the table FreqAssistants, why would you copy all of this into another table? Why not just store (for example) AssistantsID in another table?
PHV pointed you to some information about relational data bases. One great thing about it is: do not repeat the same info in multiple locations. Why? What if one of your Assistants got married and changed her last name? You would need to go to all places to change it.
Have fun.
---- Andy
RE: Updating a table with another table record (CallLogger To FreqCallLog)
Thank you both for your replies.
I am still a bit of a noob...
In this case the data for the frequent assistants or loggers is the only data that will be updated.
In terms of the Assistant Table and CallLogger Table that data will not be changed, as it will be kept for historic reasons.
I made an error in the following:
Table FreqAssistants with primary key field NameCode
linked to
primary key field AssistantCodein Table Assistant
AND
Table FreqLoggers with primary key field NameCode
linked to
primary key field RegistrarCodein Table CallLogger
It should be....
Table FreqAssistants with primary key field NameCode
linked to
field AssistantCode
in Table Assistant
So one FreqAssistants to many Assistant
AND
Table FreqLoggers with primary key field NameCode
linked to
field RegistrarCode
in Table CallLogger
So one FreqLoggers to many CallLogger
Any more help would be appreciated.
Thank you :)
Thank you,
Kind regards
Triacona