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  

Database Design Idea



 
 
Thread Tools Display Modes
  #11  
Old July 23rd, 2009, 03:49 PM posted to microsoft.public.access.tablesdbdesign
andycambo via AccessMonster.com
external usenet poster
 
Posts: 5
Default Database Design Idea

Gina Whipp wrote:
Andy,


Sorry, I didn't read what I wrote properly. Thank-you very much for your
advise, it has been a great help.

Andy.

--
Message posted via http://www.accessmonster.com

  #12  
Old July 23rd, 2009, 04:04 PM posted to microsoft.public.access.tablesdbdesign
Gina Whipp
external usenet poster
 
Posts: 3,500
Default Database Design Idea

You're welcome...

--
Gina Whipp

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

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

"andycambo via AccessMonster.com" u53471@uwe wrote in message
news:9981f031cbce7@uwe...
Gina Whipp wrote:
Andy,


Sorry, I didn't read what I wrote properly. Thank-you very much for your
advise, it has been a great help.

Andy.

--
Message posted via http://www.accessmonster.com



  #13  
Old July 26th, 2009, 12:47 PM posted to microsoft.public.access.tablesdbdesign
andycambo via AccessMonster.com
external usenet poster
 
Posts: 5
Default Database Design Idea

http://i630.photobucket.com/albums/u...ationships.jpg

Picture of relationships ^^^

I have manged to get a little bit of work done today. I've come to the point
were I have to create the relationships between the tables. I'm hoping my
tables are okay so it will just be a case of choosing the right relationships.


I'm not totally sure what I'm doing the right thing here though. I've read
my book a couple of tmes but it isn't totally clear, to me, what I should do.
I don't know what join type to use as there are 3 choices.

1 Only include rows where the joined fields from both tables are equal.

2. Include ALL records from 'tblMatters' and only those records from
'tblAppearance' where the joined fields are equal.

3 Include ALL records from 'tblAppearance' and only those records from
'tblMatters' where the joined fields are equal.

So, going from the picture from above. How to I relate the tblMatters to
tblResults, tblAppearance, tblAtPolice, tblAtCourt and tblClientProfile
(which I've just realised I've missed the field off). What Join type am I to
use?

Just to clear things up. 1 client can have several matters but each matter
will only have one appearance, result and court or police station record.

Thanks in advance,
Andy.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200907/1

  #14  
Old July 26th, 2009, 01:56 PM posted to microsoft.public.access.tablesdbdesign
Gina Whipp
external usenet poster
 
Posts: 3,500
Default Database Design Idea

Andy,

1. Do not worry about the Join Type. Simply check...

Enforce Refrential Integrity
AND
Cascade Update Related Fields

2. To make this as easy as I can think of... Join the little yellow key
(PK) to the table with the same name/same field name in the adjoining
table(FK).

Example: tblClientProfile-cpClientID to tblMatters-mClientID

Keep doing that until you have created all relationships.

3. Remove tblResults_1 located all the way to the right. You can have more
then one join on a single table, so no need to put in two same tables.

4. Reading material which may be easier to understand then the book...

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/acc...resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

--
Gina Whipp

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

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

"andycambo via AccessMonster.com" u53471@uwe wrote in message
news:99a60fbbd0eb8@uwe...
http://i630.photobucket.com/albums/u...ationships.jpg

Picture of relationships ^^^

I have manged to get a little bit of work done today. I've come to the
point
were I have to create the relationships between the tables. I'm hoping my
tables are okay so it will just be a case of choosing the right
relationships.


I'm not totally sure what I'm doing the right thing here though. I've
read
my book a couple of tmes but it isn't totally clear, to me, what I should
do.
I don't know what join type to use as there are 3 choices.

1 Only include rows where the joined fields from both tables are equal.

2. Include ALL records from 'tblMatters' and only those records from
'tblAppearance' where the joined fields are equal.

3 Include ALL records from 'tblAppearance' and only those records from
'tblMatters' where the joined fields are equal.

So, going from the picture from above. How to I relate the tblMatters to
tblResults, tblAppearance, tblAtPolice, tblAtCourt and tblClientProfile
(which I've just realised I've missed the field off). What Join type am I
to
use?

Just to clear things up. 1 client can have several matters but each
matter
will only have one appearance, result and court or police station record.

Thanks in advance,
Andy.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200907/1



  #15  
Old July 26th, 2009, 05:33 PM posted to microsoft.public.access.tablesdbdesign
andycambo via AccessMonster.com
external usenet poster
 
Posts: 5
Default Database Design Idea

Gina Whipp wrote:
Andy,

1. Do not worry about the Join Type. Simply check...


Thanks once again for your reply.

I was doing what you said but I kept getting an error message. What I didn't
realise, until I read a few articles, was the Foreign Key needed to be a long
number. In my case it wasn't so I was getting repeated error messages. I've
now sorted that and here is my relationship table.

http://i630.photobucket.com/albums/u...onships_v2.jpg

I don't think I've missed anything off this.

Thanks for the links, those articles will be handy material.
Andy.

--
Message posted via http://www.accessmonster.com

  #16  
Old July 26th, 2009, 06:02 PM posted to microsoft.public.access.tablesdbdesign
Gina Whipp
external usenet poster
 
Posts: 3,500
Default Database Design Idea

Andy,

All looks great except you forgot Cascading Updates on tblMatters -
tblResults and you can remove rMatterID from tblResults as it will be linked
by rUFNID. Fix those and all looks perfect.

--
Gina Whipp

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

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

"andycambo via AccessMonster.com" u53471@uwe wrote in message
news:99a89102f5aa8@uwe...
Gina Whipp wrote:
Andy,

1. Do not worry about the Join Type. Simply check...


Thanks once again for your reply.

I was doing what you said but I kept getting an error message. What I
didn't
realise, until I read a few articles, was the Foreign Key needed to be a
long
number. In my case it wasn't so I was getting repeated error messages.
I've
now sorted that and here is my relationship table.

http://i630.photobucket.com/albums/u...onships_v2.jpg

I don't think I've missed anything off this.

Thanks for the links, those articles will be handy material.
Andy.

--
Message posted via http://www.accessmonster.com



  #17  
Old July 27th, 2009, 02:04 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Database Design Idea

On Sun, 26 Jul 2009 08:56:20 -0400, "Gina Whipp"
wrote:

Cascade Update Related Fields


I'd use this with caution, Gina. It is never necessary if you're creating a
relationship from an Autonumber primary key to a Long Integer child table,
since an autonumber field is never updateable; if you're joining on some sort
of natural key, you should still be very careful about using cascade updates,
since it will take effect only when you *edit* the value of a main table
field, and it will cause all related records to be changed, which may not be
the result you want.
--

John W. Vinson [MVP]
  #18  
Old July 27th, 2009, 02:52 AM posted to microsoft.public.access.tablesdbdesign
Gina Whipp
external usenet poster
 
Posts: 3,500
Default Database Design Idea

John,

Thanks for the tidbit. I have never had a problem but then I rarely use
Autonumber and normally I want my related records updated when editing the
main table. BUT you bring a good point I am passing on advice without
taking into consider that everyone doesn't program like me...

--
Gina Whipp

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

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

"John W. Vinson" wrote in message
news
On Sun, 26 Jul 2009 08:56:20 -0400, "Gina Whipp"

wrote:

Cascade Update Related Fields


I'd use this with caution, Gina. It is never necessary if you're creating
a
relationship from an Autonumber primary key to a Long Integer child table,
since an autonumber field is never updateable; if you're joining on some
sort
of natural key, you should still be very careful about using cascade
updates,
since it will take effect only when you *edit* the value of a main table
field, and it will cause all related records to be changed, which may not
be
the result you want.
--

John W. Vinson [MVP]



  #19  
Old July 27th, 2009, 05:40 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Database Design Idea

On Sun, 26 Jul 2009 21:52:46 -0400, "Gina Whipp"
wrote:

John,

Thanks for the tidbit. I have never had a problem but then I rarely use
Autonumber and normally I want my related records updated when editing the
main table. BUT you bring a good point I am passing on advice without
taking into consider that everyone doesn't program like me...


Do you (routinely) edit the value *of a primary key field*? OK, I'll trust you
- you know what you're doing! - but it's something I'd NEVER let my users do.

Of course cascading updates applies only to editing that field; you can edit
other fields in a parent table without CU getting into the mix.
--

John W. Vinson [MVP]
  #20  
Old July 27th, 2009, 06:14 PM posted to microsoft.public.access.tablesdbdesign
Gina Whipp
external usenet poster
 
Posts: 3,500
Default Database Design Idea

John,

In one case I have no choice because the Client INSISTS they have the
ability to do! (I am refering to once Client in particular) and of course,
he pays when he *messes* the database up which is a routine with him bit
that is another story. Then there was the case where data that was brought
in form another source with matchind PK's to the existing data and I had to
run some update queries to change the PK's so I could get it into the table.
(I told them let's try not to do that again.

But other then that NO, NEVER, have you lost your mind... Okay I go
overboard. I just got in the habit of doing it that because of the above
scenarios. And since doing it that way never *hurt* anything I never
stopped. Of course in my latest adventure I set the PK as Autonumber and
gave them a *make-believe* PK because they wanted to edit it and I'm seeing
how that goes... The don't have a Purchase Order number until AFTER the PO
gets approved for ordering.



--
Gina Whipp

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

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

"John W. Vinson" wrote in message
...
On Sun, 26 Jul 2009 21:52:46 -0400, "Gina Whipp"

wrote:

John,

Thanks for the tidbit. I have never had a problem but then I rarely use
Autonumber and normally I want my related records updated when editing the
main table. BUT you bring a good point I am passing on advice without
taking into consider that everyone doesn't program like me...


Do you (routinely) edit the value *of a primary key field*? OK, I'll trust
you
- you know what you're doing! - but it's something I'd NEVER let my users
do.

Of course cascading updates applies only to editing that field; you can
edit
other fields in a parent table without CU getting into the mix.
--

John W. Vinson [MVP]



 




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 05:59 PM.


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