Prev: Hiding zero values in report
Next: Two-line fields
From: MitzDriver on 17 Nov 2009 08:01
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.
From: Tom van Stiphout on 17 Nov 2009 09:17
On Tue, 17 Nov 2009 05:01:01 -0800, MitzDriver
<MitzDriver(a)discussions.microsoft.com> 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.
From: Gina Whipp on 17 Nov 2009 09:23
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/AppIssueBadWord.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" <MitzDriver(a)discussions.microsoft.com> wrote in message
news:8DD64752-6A9C-4189-A1ED-1B3082632DB6(a)microsoft.com...
> 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.


 | 
Pages: 1
Prev: Hiding zero values in report
Next: Two-line fields