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. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|