Sharing Data across two tables

[mvpdep]

Good afternoon I have a main table called Accounts with all company detail. I want to set up another table called Phone Calls to track how often a client is called and the results.

If in account we have company name how do I set up my phone calls table to share the data.

For instance if I place the first call to abc corp and I leave a message I would like the call table to track who the call was made to...what type of call it was and the result.

From a reporting perspective I want to set things up so I can analyze how many calls were made weekly, monthly all up and by company.

Of all calls how many resulted in voice mail how many left message how many scheduled apt etc.

Please if anyone can help it would be appreciated or please point me in the direction of another post that explains this better.

I hope I have phrased my question appropriately and thanks in advance for your help.

 

[Pampers]

Hi mvpdep,
I'm not sure, but I think you want to create a second table, tblPhoneCalls, which looks like this:

PhoneCallID (primary key) autonumber
AccountID (foreign key) - duplicates ok
PhoneCallNumber
etc...

Your table Account should be something like this:
AccountID (autonumber)
AccountName (text)
etc...

Now create a relationship (1- n) between the two tables, in the relationship view. Set the referential integrity, and click cascading updates.

Create a new form, with a mainform (based on tblAccounts) and a subform (continuous - based on tblPhoneCalls), and link them together with the subform property LinkChild/MasterField. The fields which link are AccountID. Now you can record an unlimited number of phonecalls for every account (1).





Pampers -

you're neven too young to learn

 

[mvpdep]

Hi Pampers thank you a couple of quick questions:

Do I create my relationship between the two as 1 - n literally or where you picking n as a variable for me to chose?

I do have a mainform for tblaccounts within which i have the tabs. If I identified a tab as phone calls and put the subform there would it still work as you have defined it above?

Thanks.

 

[formerTexan]

Hi,

(1-n) is symbolizing a one-to-many relationship in the data. One Account, many PhoneCalls. You will create the relationship at the time of data entry through the use of primary and foreign keys.

For yoru 2nd question, the naswer is yes providing you heed the rest of Pampers advice.

Cheers,
Bill Butler

 

[Pampers]

Hi mvpdep,
One step at the time. Did you create your phonecall table? And how does it look; please give the fieldname's of this table, and the fieldnames of tblAccounts. We take it from there...

Pampers -

you're neven too young to learn