Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-30-2011, 12:07 PM   #1
rexmorgan
Newly Registered User
 
Join Date: May 2010
Posts: 47
Thanks: 2
Thanked 1 Time in 1 Post
rexmorgan is on a distinguished road
Linking Tables and Creating Reports based on those linked tables

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.

rexmorgan is offline   Reply With Quote
Old 01-30-2011, 08:03 PM   #2
jzwp22
Access Hobbyist
 
Join Date: Mar 2008
Posts: 2,629
Thanks: 0
Thanked 311 Times in 308 Posts
jzwp22 has a spectacular aura about jzwp22 has a spectacular aura about jzwp22 has a spectacular aura about
Re: Linking Tables and Creating Reports based on those linked tables

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
jzwp22 is offline   Reply With Quote
Reply

Tags
linking tables , reportsfromlinkedtables

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Creating relationships & PKs/FKs with linked tables noboffinme Theory and practice of database design 2 05-27-2009 04:14 PM
Linking the Linked tables in Access Sreedevi Tables 1 10-17-2007 05:06 AM
Problem with connection string while creating linked tables ramesh.elapakur Modules & VBA 10 03-13-2007 01:32 AM
Linked Tables not linking Benny1973 General 2 09-29-2005 04:20 AM
Creating a Form linked to 2 tables Stevefsn Tables 3 09-19-2003 06:52 AM




All times are GMT -8. The time now is 10:19 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World