A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

one to many or many to one



 
 
Thread Tools Display Modes
  #1  
Old November 17th, 2009, 02:01 PM posted to microsoft.public.access.tablesdbdesign
MitzDriver
external usenet poster
 
Posts: 11
Default one to many or many to one

The last time I did anything with a database was with dBaseI. And I know this
is a very simple thing for you all......But

Using access 2007 I have two tables.

TblListings
ID autonumber
owner text
address text
phone text
mlsno text PF

TblShowings
ID autonumber PF
date
time
agent
agent phone.

For every TblListing there will be many showings. How do I set the
relationship between the two? Do I, should I, change the PF on each table? Do
I need to add a field to TblShowings? I would like to use a form frmListings
with a subform frmShowings. So for each Listing, all past and scheduled
showings are there.
Your help would be greatly appriciated.
Ads
  #2  
Old November 17th, 2009, 03:17 PM posted to microsoft.public.access.tablesdbdesign
Tom van Stiphout[_2_]
external usenet poster
 
Posts: 1,653
Default one to many or many to one

On Tue, 17 Nov 2009 05:01:01 -0800, MitzDriver
wrote:

I don't know what you mean by "PF".
TblShowings should have an additional field ListingsID. Then in the
Relationships window you can draw the line from TblListings.ID to
TblShowings.ListingsID *and enforce that relationship*.

-Tom.
Microsoft Access MVP


The last time I did anything with a database was with dBaseI. And I know this
is a very simple thing for you all......But

Using access 2007 I have two tables.

TblListings
ID autonumber
owner text
address text
phone text
mlsno text PF

TblShowings
ID autonumber PF
date
time
agent
agent phone.

For every TblListing there will be many showings. How do I set the
relationship between the two? Do I, should I, change the PF on each table? Do
I need to add a field to TblShowings? I would like to use a form frmListings
with a subform frmShowings. So for each Listing, all past and scheduled
showings are there.
Your help would be greatly appriciated.

  #3  
Old November 17th, 2009, 03:23 PM posted to microsoft.public.access.tablesdbdesign
Gina Whipp
external usenet poster
 
Posts: 3,500
Default one to many or many to one

MitzDriver,

Changed your table around a bit to show how to join... (I also added
another table for Agents.)

tblListings
ID autonumber
owner text
address text
phone text
mlsno text PF
lAgentID (FK)

tblShowings
sShowingID (PK)
sListingID (FK)
sDateShown
lsTimeShown
sAgentID (FK - Should only be populated if shown by a different Agent then
assigned to the Listing)
etc...

tblAgents
aAgentID (PK)
aFirstName
aLastName
aPhoneNumber
etc...

I'm going to also suggest you reconsider the name of a few of your fields,
date and time, because they are reserved words in Access. Have a look at
this link for a complete list... http://allenbrowne.com/Ap****ueBadWord.html

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"MitzDriver" wrote in message
...
The last time I did anything with a database was with dBaseI. And I know
this
is a very simple thing for you all......But

Using access 2007 I have two tables.

TblListings
ID autonumber
owner text
address text
phone text
mlsno text PF

TblShowings
ID autonumber PF
date
time
agent
agent phone.

For every TblListing there will be many showings. How do I set the
relationship between the two? Do I, should I, change the PF on each table?
Do
I need to add a field to TblShowings? I would like to use a form
frmListings
with a subform frmShowings. So for each Listing, all past and scheduled
showings are there.
Your help would be greatly appriciated.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:24 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 OfficeFrustration.
The comments are property of their posters.