Linking Tables and Creating Reports based on those linked tables (1 Viewer)

rexmorgan

Registered User.
Local time
Yesterday, 20:03
Joined
May 31, 2010
Messages
47
I am not sure where to begin with the question. So I will begin by trying to describe what I have and what I need. I have a table (tblSpotter) with Info about people, i.e. First Last Name, address, phone, etc. I have another table (tblStormVer) that List phone calls received. The idea is that I would like to keep a log of the phone calls that I receive from the individuals in the first table. In the tblStormVer table I have Contact_Moment (an autonumber), call_datetime (current date/time), Remarks, First Name, Last Name, In-Out (whether the phone call is incoming or outgoing).

Now what I would like to do is Link the First and Last Name data in the tblStormVer to the First Name Last Name data in the tblSpotter table.

Ultimately I would like to be able to create a Report (that includes much of the data from the tblSpotter) sorting out records from the current day or current week or last week, etc. Not sure if I described this well enough, if not please let me know and I will attempt to do a better job. I have a basic understanding of Access but this has me scratching my head. Thanks in advance for any assistance. I can upload data if that will help.
 

jzwp22

Access Hobbyist
Local time
Yesterday, 23:03
Joined
Mar 15, 2008
Messages
2,629
Repeating the first and last name in tblStormVer would not follow good relational database practices. You would however store the key field that relates to the person in tblSpotter. For example, I would use a primary key in tblSpotter as follows:

tblSpotter
-pkSpotterID primary key, autonumber
-FirstName
-LastName
-address
-phone

...and then reference the pkSpotterID in tblStormVer as a foreign key. This primary key-->foreign key will allow you to build the one-to-many relationship between the two tables. (one person has many calls).

tblStormVer
-Contact_Moment primary key, autonumber
-fkSpotterID foreign key to tblSpotter
-call_datetime (current date/time)
-Remarks
-In-Out (whether the phone call is incoming or outgoing)

In the relationship window, you would set up the relationship between the two tables by joining the pkSpotterID of tblSpotter to the fkSpotterID of tblStormVer.

You would then create a query that uses the two tables and base your report on that query
 

Users who are viewing this thread

Top Bottom