Updating a table with another table record (CallLogger To FreqCallLog)

[Triacona]

Dear All,

Thank you for a great forum.

I have the following problem.

I have the following tables.
[green]
CallsLogged
Updates

Assistant
FreqAssistants

CallLogger
FreqLoggers
[/green]
The tables have the following relationships.

[green]CallsLogged[/green] the central table, has the following relationships.
Table [green]CallsLogged[/green] with primary key field [COLOR=red yellow]Reference[/color] [blue]linked to[/blue] primary key field [COLOR=red yellow]CallReference[/color] in Table [green]CallLogger[/green]

Table [green]CallsLogged[/green] with primary key field [COLOR=red yellow]Reference[/color] [blue]linked to[/blue] primary key field [COLOR=red yellow]AssignedReference [/color]in Table [green]Assistant[/green]

The next 2 links are to the tables
[green]
FreqAssistants
FreqLoggers
[/green]

Table [green]FreqAssistants[/green] with primary key field [COLOR=red yellow]NameCode[/color] [blue]linked to[/blue] primary key field [COLOR=red yellow]AssistantCode[/color]in Table [green]Assistant[/green]

AND

Table [green]FreqLoggers[/green] with primary key field [COLOR=red yellow]NameCode[/color] [blue]linked to[/blue] primary key field [COLOR=red yellow]RegistrarCode[/color]in Table [green]CallLogger[/green]

What I want to happen is the following.

When in my form [teal]LogCall[/teal] when the user goes to the Assistant area to fill in the [COLOR=red yellow] AssistantCode [/color] into the table [green]Assistant[/green]
If they fill in a recognised code TS for example, if that code exists in the table[green]FreqAssistants[/green];
for the data in table [green]Assistant[/green]to be updated with the already stored
Name, Surname, TelephoneNumber, E-mail from the table [green]FreqAssistants[/green];
as it is tedious for users to continually type in the same details again and again.


Any help will be greatly appreciated

Thank you!

Thank you,

Kind regards

Triacona

 

[PHV]

to be updated with the already stored
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
FAQ181-2886

 

[Andrzejek]

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

 

[Triacona]

Dear All,

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 [green]FreqAssistants[/green] with primary key field [COLOR=red yellow]NameCode[/color]
[blue]linked to[/blue]
primary key field [COLOR=red yellow]AssistantCode[/color]in Table [green]Assistant[/green]

AND

Table [green]FreqLoggers[/green] with primary key field [COLOR=red yellow]NameCode[/color]
[blue]linked to[/blue]
primary key field [COLOR=red yellow]RegistrarCode[/color]in Table [green]CallLogger[/green]

It should be....

Table [green]FreqAssistants[/green] with primary key field [COLOR=red yellow]NameCode[/color]
[blue]linked to[/blue]
field AssistantCode
in Table [green]Assistant[/green]

So one FreqAssistants to many Assistant

AND

Table [green]FreqLoggers[/green] with primary key field [COLOR=red yellow]NameCode[/color]
[blue]linked to[/blue]
field RegistrarCode
in Table [green]CallLogger[/green]

So one FreqLoggers to many CallLogger

Any more help would be appreciated.

Thank you

Thank you,

Kind regards

Triacona