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 |
#1
|
|||
|
|||
Relationships, back end
Hope this is the appropriate group to ask.
I have two general questions and one a bit more specific. (a) Are relationships defined in the back end effective in the front end? (b) Is it preferable to define relationships in FE or BE? (c1) Is it necessary to define relationships for subforms to work or (c2) How do relationships affect the working of subforms? I have a form with two subforms. Each subform also has a subform. (Each combination appears on a separate tab of a tab control.) One works, one doesn't and I cannot find why. The bad one shows all the detail records. All the properties seem to be set in a similar manner so I am now looking more widely. Any tips on what else might be worth looking at would be appreciated also. TIA -- Len __________________________________________________ ____ remove nothing for valid email address. |
#2
|
|||
|
|||
Relationships, back end
On Wed, 20 May 2009 11:13:39 +1000, "Len B"
wrote: a: Try it. b: BE c1: No, but it is necessary for lots of other db design related reasons. c2: They may restrict what you can enter, so that you won't violate referential integrity. If a subform shows more rows than you expected, inspect the LinkMasterFields and LinkChildFields properties of the subform control. -Tom. Microsoft Access MVP Hope this is the appropriate group to ask. I have two general questions and one a bit more specific. (a) Are relationships defined in the back end effective in the front end? (b) Is it preferable to define relationships in FE or BE? (c1) Is it necessary to define relationships for subforms to work or (c2) How do relationships affect the working of subforms? I have a form with two subforms. Each subform also has a subform. (Each combination appears on a separate tab of a tab control.) One works, one doesn't and I cannot find why. The bad one shows all the detail records. All the properties seem to be set in a similar manner so I am now looking more widely. Any tips on what else might be worth looking at would be appreciated also. TIA |
#3
|
|||
|
|||
Relationships, back end
comments inline.
"Len B" wrote in message ... Hope this is the appropriate group to ask. I have two general questions and one a bit more specific. (a) Are relationships defined in the back end effective in the front end? yes. (b) Is it preferable to define relationships in FE or BE? if your BE db is where you store the tables, and the FE db has links to those tables (that's the normal BE/FE setup), then we're on the same page. you can "draw the lines" between linked tables, in the FE Relationships window, but you can't enforce referential integrity on table links. so if, when you say "define", you mean set the parent/child links AND enforce referential integrity, then you must do that to native tables - in other words, in the BE db. (c1) Is it necessary to define relationships for subforms to work or if you mean "define relationships in the Relationships window", no it's not "necessary". but you should, because defining relationships and enforcing referential integrity is about ensuring the validity of the data. the fact that it's easier to work with mainform/subform setups when those two things are done, is a great by-product, but not the reason for doing it. (c2) How do relationships affect the working of subforms? strictly speaking, they don't, in themselves. there are numerous non-traditional uses of subforms that don't involve table relationships at all, or stand the usual setup on its' head. but once you define a parent/child relationship at the table level, and enforce referential integrity, and then base a mainform/subform on those parent/child tables, Access will pretty much demand that you set up it up right, or it won't work. I have a form with two subforms. Each subform also has a subform. (Each combination appears on a separate tab of a tab control.) One works, one doesn't and I cannot find why. The bad one shows all the detail records. if you have a subform that shows all the records in the child table, rather than only the records related to the parent record displayed in the mainform, then it sounds like you don't have the mainform/subform properly linked. open the mainform in Design view. click ONCE on the subform, within the mainform, to select it. in the Properties box, look at the LinkChildFields and LinkMasterFields properties. the first property should be set to the name of the foreign key field in the child table (and make sure that field is included in the subform's RecordSource), and the second property should be set to the name of the primary key field in the parent table (again, make sure the primary key field is included in the mainform's RecordSource. hth All the properties seem to be set in a similar manner so I am now looking more widely. Any tips on what else might be worth looking at would be appreciated also. TIA -- Len __________________________________________________ ____ remove nothing for valid email address. |
#4
|
|||
|
|||
Relationships, back end
Thanks Tom
When I said that the properties seem to be set similarly in both subforms I was including the link fields. The fact that they did look similar in both the good and bad subforms is what led me to question relationships. If you are interested, I have included more info and another question in my reply to tina's post. -- Len __________________________________________________ ____ remove nothing for valid email address. "Tom van Stiphout" wrote in message ... | On Wed, 20 May 2009 11:13:39 +1000, "Len B" | wrote: | | a: Try it. | b: BE | c1: No, but it is necessary for lots of other db design related | reasons. | c2: They may restrict what you can enter, so that you won't violate | referential integrity. | | If a subform shows more rows than you expected, inspect the | LinkMasterFields and LinkChildFields properties of the subform | control. | | -Tom. | Microsoft Access MVP | | | Hope this is the appropriate group to ask. | | I have two general questions and one a bit more specific. | (a) Are relationships defined in the back end effective in the front end? | (b) Is it preferable to define relationships in FE or BE? | (c1) Is it necessary to define relationships for subforms to work or | (c2) How do relationships affect the working of subforms? | | I have a form with two subforms. Each subform also has a subform. (Each | combination appears on a separate tab of a tab control.) One works, one | doesn't and I cannot find why. The bad one shows all the detail records. All | the properties seem to be set in a similar manner so I am now looking more | widely. Any tips on what else might be worth looking at would be appreciated | also. | | TIA |
#5
|
|||
|
|||
Relationships, back end
Thanks tina
The FE/BE split is exactly as you said. The link fields looked ok but I'll look at them again in the light of your definitions. The fact that they seemed ok led me to look at the relationships as the problem. Yes I meant creating the 'link lines' and enforcing referential integrity when I said 'define relationships'. Looking at the relationships prompts this question - The three tables (and fields) concerned are A B C (tbl)Equipment (tbl)Maintenance (tbl)Movements EquipmentID (PK) MaintenanceID (PK) MoveID (PK) EquipmentNum (RU) EquipmentID (FK,A) EquipmentNum ??(FK,A)?? LastMoveID (FK,C) MaintenanceCost MoveTo PK=primary FK=foreign RU=Reqd+Unique (effectively another PK but data type is txt) Subform on B works. Relationship is A(1)-B(many)on EquipmentID There is a relationship between C(1)-A(many) on MoveID/LastMoveID. I think I also need one A-C on EquipmentNum but when I try to create one, access complains that there is already a relationship defined and offers to delete it. Do I really need the existing relationship or can I delete it to create the new one? -- Len __________________________________________________ ____ remove nothing for valid email address. "tina" wrote in message ... | comments inline. | | "Len B" wrote in message | ... | Hope this is the appropriate group to ask. | | I have two general questions and one a bit more specific. | (a) Are relationships defined in the back end effective in the front end? | | yes. | | (b) Is it preferable to define relationships in FE or BE? | | if your BE db is where you store the tables, and the FE db has links to | those tables (that's the normal BE/FE setup), then we're on the same page. | you can "draw the lines" between linked tables, in the FE Relationships | window, but you can't enforce referential integrity on table links. so if, | when you say "define", you mean set the parent/child links AND enforce | referential integrity, then you must do that to native tables - in other | words, in the BE db. | | (c1) Is it necessary to define relationships for subforms to work or | | if you mean "define relationships in the Relationships window", no it's not | "necessary". but you should, because defining relationships and enforcing | referential integrity is about ensuring the validity of the data. the fact | that it's easier to work with mainform/subform setups when those two things | are done, is a great by-product, but not the reason for doing it. | | (c2) How do relationships affect the working of subforms? | | strictly speaking, they don't, in themselves. there are numerous | non-traditional uses of subforms that don't involve table relationships at | all, or stand the usual setup on its' head. but once you define a | parent/child relationship at the table level, and enforce referential | integrity, and then base a mainform/subform on those parent/child tables, | Access will pretty much demand that you set up it up right, or it won't | work. | | | I have a form with two subforms. Each subform also has a subform. (Each | combination appears on a separate tab of a tab control.) One works, one | doesn't and I cannot find why. The bad one shows all the detail records. | | if you have a subform that shows all the records in the child table, rather | than only the records related to the parent record displayed in the | mainform, then it sounds like you don't have the mainform/subform properly | linked. open the mainform in Design view. click ONCE on the subform, within | the mainform, to select it. in the Properties box, look at the | LinkChildFields and LinkMasterFields properties. the first property should | be set to the name of the foreign key field in the child table (and make | sure that field is included in the subform's RecordSource), and the second | property should be set to the name of the primary key field in the parent | table (again, make sure the primary key field is included in the mainform's | RecordSource. | | hth | | All | the properties seem to be set in a similar manner so I am now looking more | widely. Any tips on what else might be worth looking at would be | appreciated | also. | | TIA | -- | Len | __________________________________________________ ____ | remove nothing for valid email address. | | | | |
#6
|
|||
|
|||
Relationships, back end
suggest the following changes to tables A and C, as
tblEquipment EquipmentID (pk) EquipmentNum (get rid of the LastMoveID field in this table) (and btw, a primary key field can be text. if the equipment number assigned to a given item will never change, and is absolutely unique - if a piece of equipment breaks down and is replaced, the new piece will get a *new* equipment number - then you should be able to use it as the pk for this table, if you want. but you can certainly use a separate field for pk, as you're now doing.) tblMovements MoveID EquipmentID (fk from tblEquipment) MoveTo relationship would be tblEquipment.EquipmentID 1:n tblMovements.EquipmentID and btw, i'm guessing that you're tracking the movement of equipment from location to location, correct? if so, i might have a table listing all locations, with as much detail describing locations as you need; then tblMovements would actually be a join table between tblEquipment and tblLocations, as tblMovements MoveID (pk) EquipmentID (fk from tblEquipment) LocationID (fk from tblLocations) MoveDate (if you include a move date, you can always find where a piece of equipment is currently located - it will be the record with the newest date for that piece of equipment, in tblMovements.) hth "Len B" wrote in message ... Thanks tina The FE/BE split is exactly as you said. The link fields looked ok but I'll look at them again in the light of your definitions. The fact that they seemed ok led me to look at the relationships as the problem. Yes I meant creating the 'link lines' and enforcing referential integrity when I said 'define relationships'. Looking at the relationships prompts this question - The three tables (and fields) concerned are A B C (tbl)Equipment (tbl)Maintenance (tbl)Movements EquipmentID (PK) MaintenanceID (PK) MoveID (PK) EquipmentNum (RU) EquipmentID (FK,A) EquipmentNum ??(FK,A)?? LastMoveID (FK,C) MaintenanceCost MoveTo PK=primary FK=foreign RU=Reqd+Unique (effectively another PK but data type is txt) Subform on B works. Relationship is A(1)-B(many)on EquipmentID There is a relationship between C(1)-A(many) on MoveID/LastMoveID. I think I also need one A-C on EquipmentNum but when I try to create one, access complains that there is already a relationship defined and offers to delete it. Do I really need the existing relationship or can I delete it to create the new one? -- Len __________________________________________________ ____ remove nothing for valid email address. "tina" wrote in message ... | comments inline. | | "Len B" wrote in message | ... | Hope this is the appropriate group to ask. | | I have two general questions and one a bit more specific. | (a) Are relationships defined in the back end effective in the front end? | | yes. | | (b) Is it preferable to define relationships in FE or BE? | | if your BE db is where you store the tables, and the FE db has links to | those tables (that's the normal BE/FE setup), then we're on the same page. | you can "draw the lines" between linked tables, in the FE Relationships | window, but you can't enforce referential integrity on table links. so if, | when you say "define", you mean set the parent/child links AND enforce | referential integrity, then you must do that to native tables - in other | words, in the BE db. | | (c1) Is it necessary to define relationships for subforms to work or | | if you mean "define relationships in the Relationships window", no it's not | "necessary". but you should, because defining relationships and enforcing | referential integrity is about ensuring the validity of the data. the fact | that it's easier to work with mainform/subform setups when those two things | are done, is a great by-product, but not the reason for doing it. | | (c2) How do relationships affect the working of subforms? | | strictly speaking, they don't, in themselves. there are numerous | non-traditional uses of subforms that don't involve table relationships at | all, or stand the usual setup on its' head. but once you define a | parent/child relationship at the table level, and enforce referential | integrity, and then base a mainform/subform on those parent/child tables, | Access will pretty much demand that you set up it up right, or it won't | work. | | | I have a form with two subforms. Each subform also has a subform. (Each | combination appears on a separate tab of a tab control.) One works, one | doesn't and I cannot find why. The bad one shows all the detail records. | | if you have a subform that shows all the records in the child table, rather | than only the records related to the parent record displayed in the | mainform, then it sounds like you don't have the mainform/subform properly | linked. open the mainform in Design view. click ONCE on the subform, within | the mainform, to select it. in the Properties box, look at the | LinkChildFields and LinkMasterFields properties. the first property should | be set to the name of the foreign key field in the child table (and make | sure that field is included in the subform's RecordSource), and the second | property should be set to the name of the primary key field in the parent | table (again, make sure the primary key field is included in the mainform's | RecordSource. | | hth | | All | the properties seem to be set in a similar manner so I am now looking more | widely. Any tips on what else might be worth looking at would be | appreciated | also. | | TIA | -- | Len | __________________________________________________ ____ | remove nothing for valid email address. | | | | |
#7
|
|||
|
|||
Relationships, back end
Thanks tina,
FYI --- This app tracks loans of medical equipment to disabled kids. I'm not sure exactly why I used the LastMoveID field in tblEquipment. I guess I'll find out once I remove it ;-) The EquipmentNum is a barcode sticker (6 numerals) applied at purchase. Unfortunately a small possibility exists that it may wear off or fall off and a new sticker issued. Also the numbers are not issued sequentially but from a number of different rolls of pre-printed barcode stickers. There's already a MoveDate field in tblMovements. (Will appear in subform and be used for ORDER BY DESC clause.) Location/Movements Relationship ------------------------------- There's already a Location table (tblChild) but it's in an entirely different db created for unrelated purposes but is linked only in this FE, but not linked in this BE; why would you. (Similarly other tables are linked from the Child db eg Regions, Staff.) FE Relationship not made yet so does this prompt any warnings from you for making the relationship in this FE? I assume you would link to the 'MoveTo' field rather than 'From' field - see below. Equipment/Movements Relationship -------------------------------- The movement info is created using a portable barcode scanner so the EquipmentID isn't known then but the barcode is. The text file from the scanner (Barcode, MovedBy, When, From, To) is then imported and the movement records are created. That's why I wanted to use EquipmentNum (rather than ID) as the basis for this relationship. Will using EquipmentNum rather than EquipmentID mean more work or will it mean there will be things to be keep in mind later? (Recording both From and To helps to pick up unrecorded movements.) It sure would be simpler if that small possibility didn't exist and I could make barcode the pk and get rid of the ID field. Your help is very much appreciated. Thanks again. -- Len __________________________________________________ ____ remove nothing for valid email address. "tina" wrote in message ... | suggest the following changes to tables A and C, as | | tblEquipment | EquipmentID (pk) | EquipmentNum | (get rid of the LastMoveID field in this table) | (and btw, a primary key field can be text. if the equipment number assigned | to a given item will never change, and is absolutely unique - if a piece of | equipment breaks down and is replaced, the new piece will get a *new* | equipment number - then you should be able to use it as the pk for this | table, if you want. but you can certainly use a separate field for pk, as | you're now doing.) | | tblMovements | MoveID | EquipmentID (fk from tblEquipment) | MoveTo | | relationship would be | tblEquipment.EquipmentID 1:n tblMovements.EquipmentID | | and btw, i'm guessing that you're tracking the movement of equipment from | location to location, correct? if so, i might have a table listing all | locations, with as much detail describing locations as you need; then | tblMovements would actually be a join table between tblEquipment and | tblLocations, as | | tblMovements | MoveID (pk) | EquipmentID (fk from tblEquipment) | LocationID (fk from tblLocations) | MoveDate | (if you include a move date, you can always find where a piece of equipment | is currently located - it will be the record with the newest date for that | piece of equipment, in tblMovements.) | | hth | | | "Len B" wrote in message | ... | Thanks tina | The FE/BE split is exactly as you said. The link fields looked ok but I'll | look at them again in the light of your definitions. The fact that they | seemed ok led me to look at the relationships as the problem. Yes I meant | creating the 'link lines' and enforcing referential integrity when I said | 'define relationships'. | | Looking at the relationships prompts this question - | The three tables (and fields) concerned are | | A B C | (tbl)Equipment (tbl)Maintenance (tbl)Movements | EquipmentID (PK) MaintenanceID (PK) MoveID (PK) | EquipmentNum (RU) EquipmentID (FK,A) EquipmentNum ??(FK,A)?? | LastMoveID (FK,C) MaintenanceCost MoveTo | PK=primary | FK=foreign | RU=Reqd+Unique (effectively another PK but data type is txt) | | Subform on B works. Relationship is A(1)-B(many)on EquipmentID | | There is a relationship between C(1)-A(many) on MoveID/LastMoveID. | I think I also need one A-C on EquipmentNum but when I try to create | one, access complains that there is already a relationship defined and | offers to delete it. Do I really need the existing relationship or can | I delete it to create the new one? | | -- | Len | __________________________________________________ ____ | remove nothing for valid email address. | "tina" wrote in message | ... | | comments inline. | | | | "Len B" wrote in message | | ... | | Hope this is the appropriate group to ask. | | | | I have two general questions and one a bit more specific. | | (a) Are relationships defined in the back end effective in the front | end? | | | | yes. | | | | (b) Is it preferable to define relationships in FE or BE? | | | | if your BE db is where you store the tables, and the FE db has links to | | those tables (that's the normal BE/FE setup), then we're on the same | page. | | you can "draw the lines" between linked tables, in the FE Relationships | | window, but you can't enforce referential integrity on table links. so | if, | | when you say "define", you mean set the parent/child links AND enforce | | referential integrity, then you must do that to native tables - in other | | words, in the BE db. | | | | (c1) Is it necessary to define relationships for subforms to work or | | | | if you mean "define relationships in the Relationships window", no it's | not | | "necessary". but you should, because defining relationships and | enforcing | | referential integrity is about ensuring the validity of the data. the | fact | | that it's easier to work with mainform/subform setups when those two | things | | are done, is a great by-product, but not the reason for doing it. | | | | (c2) How do relationships affect the working of subforms? | | | | strictly speaking, they don't, in themselves. there are numerous | | non-traditional uses of subforms that don't involve table relationships | at | | all, or stand the usual setup on its' head. but once you define a | | parent/child relationship at the table level, and enforce referential | | integrity, and then base a mainform/subform on those parent/child | tables, | | Access will pretty much demand that you set up it up right, or it won't | | work. | | | | | | I have a form with two subforms. Each subform also has a subform. | (Each | | combination appears on a separate tab of a tab control.) One works, | one | | doesn't and I cannot find why. The bad one shows all the detail | records. | | | | if you have a subform that shows all the records in the child table, | rather | | than only the records related to the parent record displayed in the | | mainform, then it sounds like you don't have the mainform/subform | properly | | linked. open the mainform in Design view. click ONCE on the subform, | within | | the mainform, to select it. in the Properties box, look at the | | LinkChildFields and LinkMasterFields properties. the first property | should | | be set to the name of the foreign key field in the child table (and make | | sure that field is included in the subform's RecordSource), and the | second | | property should be set to the name of the primary key field in the | parent | | table (again, make sure the primary key field is included in the | mainform's | | RecordSource. | | | | hth | | | | All | | the properties seem to be set in a similar manner so I am now looking | more | | widely. Any tips on what else might be worth looking at would be | | appreciated | | also. | | | | TIA | | -- | | Len | | __________________________________________________ ____ | | remove nothing for valid email address. | | | | | | | | | | | | |
#8
|
|||
|
|||
Relationships, back end
Oops,
I've misled you slightly. The Location object is a union select query. It joins the tblChild and another table containing location codes for equipment storage depots. -- Len __________________________________________________ ____ remove nothing for valid email address. "Len B" wrote in message ... | Thanks tina, | FYI | --- | This app tracks loans of medical equipment to disabled kids. | | I'm not sure exactly why I used the LastMoveID field in tblEquipment. | I guess I'll find out once I remove it ;-) | | The EquipmentNum is a barcode sticker (6 numerals) applied at purchase. | Unfortunately a small possibility exists that it may wear off or fall off | and a new sticker issued. Also the numbers are not issued sequentially but | from a number of different rolls of pre-printed barcode stickers. | | There's already a MoveDate field in tblMovements. | (Will appear in subform and be used for ORDER BY DESC clause.) | | Location/Movements Relationship | ------------------------------- | There's already a Location table (tblChild) but it's in an entirely | different db created for unrelated purposes but is linked only in this FE, | but not linked in this BE; why would you. | (Similarly other tables are linked from the Child db eg Regions, Staff.) | FE Relationship not made yet so does this prompt any warnings from you | for making the relationship in this FE? I assume you would link to the | 'MoveTo' field rather than 'From' field - see below. | | Equipment/Movements Relationship | -------------------------------- | The movement info is created using a portable barcode scanner so the | EquipmentID isn't known then but the barcode is. The text file from the | scanner (Barcode, MovedBy, When, From, To) is then imported and the | movement records are created. That's why I wanted to use EquipmentNum | (rather than ID) as the basis for this relationship. Will using | EquipmentNum rather than EquipmentID mean more work or will it mean there | will be things to be keep in mind later? (Recording both From and To helps | to pick up unrecorded movements.) | | It sure would be simpler if that small possibility didn't exist and I | could make barcode the pk and get rid of the ID field. | | Your help is very much appreciated. Thanks again. | | -- | Len | __________________________________________________ ____ | remove nothing for valid email address. | "tina" wrote in message | ... || suggest the following changes to tables A and C, as || || tblEquipment || EquipmentID (pk) || EquipmentNum || (get rid of the LastMoveID field in this table) || (and btw, a primary key field can be text. if the equipment number | assigned || to a given item will never change, and is absolutely unique - if a piece | of || equipment breaks down and is replaced, the new piece will get a *new* || equipment number - then you should be able to use it as the pk for this || table, if you want. but you can certainly use a separate field for pk, as || you're now doing.) || || tblMovements || MoveID || EquipmentID (fk from tblEquipment) || MoveTo || || relationship would be || tblEquipment.EquipmentID 1:n tblMovements.EquipmentID || || and btw, i'm guessing that you're tracking the movement of equipment from || location to location, correct? if so, i might have a table listing all || locations, with as much detail describing locations as you need; then || tblMovements would actually be a join table between tblEquipment and || tblLocations, as || || tblMovements || MoveID (pk) || EquipmentID (fk from tblEquipment) || LocationID (fk from tblLocations) || MoveDate || (if you include a move date, you can always find where a piece of | equipment || is currently located - it will be the record with the newest date for that || piece of equipment, in tblMovements.) || || hth || || || "Len B" wrote in message || ... || Thanks tina || The FE/BE split is exactly as you said. The link fields looked ok but | I'll || look at them again in the light of your definitions. The fact that they || seemed ok led me to look at the relationships as the problem. Yes I | meant || creating the 'link lines' and enforcing referential integrity when I | said || 'define relationships'. || || Looking at the relationships prompts this question - || The three tables (and fields) concerned are || || A B C || (tbl)Equipment (tbl)Maintenance (tbl)Movements || EquipmentID (PK) MaintenanceID (PK) MoveID (PK) || EquipmentNum (RU) EquipmentID (FK,A) EquipmentNum ??(FK,A)?? || LastMoveID (FK,C) MaintenanceCost MoveTo || PK=primary || FK=foreign || RU=Reqd+Unique (effectively another PK but data type is txt) || || Subform on B works. Relationship is A(1)-B(many)on EquipmentID || || There is a relationship between C(1)-A(many) on MoveID/LastMoveID. || I think I also need one A-C on EquipmentNum but when I try to create || one, access complains that there is already a relationship defined and || offers to delete it. Do I really need the existing relationship or can || I delete it to create the new one? || || -- || Len || __________________________________________________ ____ || remove nothing for valid email address. || "tina" wrote in message || ... || | comments inline. || | || | "Len B" wrote in message || | ... || | Hope this is the appropriate group to ask. || | || | I have two general questions and one a bit more specific. || | (a) Are relationships defined in the back end effective in the front || end? || | || | yes. || | || | (b) Is it preferable to define relationships in FE or BE? || | || | if your BE db is where you store the tables, and the FE db has links | to || | those tables (that's the normal BE/FE setup), then we're on the same || page. || | you can "draw the lines" between linked tables, in the FE | Relationships || | window, but you can't enforce referential integrity on table links. so || if, || | when you say "define", you mean set the parent/child links AND enforce || | referential integrity, then you must do that to native tables - in | other || | words, in the BE db. || | || | (c1) Is it necessary to define relationships for subforms to work or || | || | if you mean "define relationships in the Relationships window", no | it's || not || | "necessary". but you should, because defining relationships and || enforcing || | referential integrity is about ensuring the validity of the data. the || fact || | that it's easier to work with mainform/subform setups when those two || things || | are done, is a great by-product, but not the reason for doing it. || | || | (c2) How do relationships affect the working of subforms? || | || | strictly speaking, they don't, in themselves. there are numerous || | non-traditional uses of subforms that don't involve table | relationships || at || | all, or stand the usual setup on its' head. but once you define a || | parent/child relationship at the table level, and enforce referential || | integrity, and then base a mainform/subform on those parent/child || tables, || | Access will pretty much demand that you set up it up right, or it | won't || | work. || | || | || | I have a form with two subforms. Each subform also has a subform. || (Each || | combination appears on a separate tab of a tab control.) One works, || one || | doesn't and I cannot find why. The bad one shows all the detail || records. || | || | if you have a subform that shows all the records in the child table, || rather || | than only the records related to the parent record displayed in the || | mainform, then it sounds like you don't have the mainform/subform || properly || | linked. open the mainform in Design view. click ONCE on the subform, || within || | the mainform, to select it. in the Properties box, look at the || | LinkChildFields and LinkMasterFields properties. the first property || should || | be set to the name of the foreign key field in the child table (and | make || | sure that field is included in the subform's RecordSource), and the || second || | property should be set to the name of the primary key field in the || parent || | table (again, make sure the primary key field is included in the || mainform's || | RecordSource. || | || | hth || | || | All || | the properties seem to be set in a similar manner so I am now | looking || more || | widely. Any tips on what else might be worth looking at would be || | appreciated || | also. || | || | TIA || | -- || | Len || | __________________________________________________ ____ || | remove nothing for valid email address. || | || | || | || | || || || || | | |
#9
|
|||
|
|||
Relationships, back end
comments inline.
"Len B" wrote in message ... Thanks tina, FYI --- This app tracks loans of medical equipment to disabled kids. I'm not sure exactly why I used the LastMoveID field in tblEquipment. I guess I'll find out once I remove it ;-) The EquipmentNum is a barcode sticker (6 numerals) applied at purchase. Unfortunately a small possibility exists that it may wear off or fall off and a new sticker issued. Also the numbers are not issued sequentially but from a number of different rolls of pre-printed barcode stickers. There's already a MoveDate field in tblMovements. (Will appear in subform and be used for ORDER BY DESC clause.) Location/Movements Relationship ------------------------------- There's already a Location table (tblChild) but it's in an entirely different db created for unrelated purposes but is linked only in this FE, but not linked in this BE; why would you. (Similarly other tables are linked from the Child db eg Regions, Staff.) FE Relationship not made yet so does this prompt any warnings from you for making the relationship in this FE? you can't enforce referential integrity in relationships between linked tables, so it's a waste of time to "draw the lines" in the Relationships window in a FE db. you can only truly relate data between two *native* tables that are in the same database. since you're working with linked tables that are native to multiple backend dbs, you'll have to rely on yourself to "enforce" referential integrity in the user interface. it's harder to do, because the system won't prevent you from entering "orphan" data - child data that has no valid parent data. I assume you would link to the 'MoveTo' field rather than 'From' field - see below. i'm not sure what you're referring to here. link to the "MoveTo" field where? if you were using native tables, you'd link tblLocations to both the From and To fields in tblMovements. but as i said above, there's no point setting that relationship in the FE db, and since tblMovements and tblLocations are in different BE dbs, you can't set relationships between the two tables at all. Equipment/Movements Relationship -------------------------------- The movement info is created using a portable barcode scanner so the EquipmentID isn't known then but the barcode is. The text file from the scanner (Barcode, MovedBy, When, From, To) is then imported and the movement records are created. That's why I wanted to use EquipmentNum (rather than ID) as the basis for this relationship. Will using EquipmentNum rather than EquipmentID mean more work or will it mean there will be things to be keep in mind later? (Recording both From and To helps to pick up unrecorded movements.) don't use EquipmentNum, use the primary key field EquipmentID as the foreign key in tblMovements, as i said before. when you import your text file, import it to a temporary table. then write a query that matches the EquipmentNum in the text file with the EquipmentNum in tblEquipment, and include the EquipmentID in the query's output. use that query to create the records in tblMovements, rather than dumping the text file directly into the table. that's how you get the necessary foreign key EquipmentID value into each record in tblMovements, so there's a solid link between that table and tblEquipment. hth It sure would be simpler if that small possibility didn't exist and I could make barcode the pk and get rid of the ID field. Your help is very much appreciated. Thanks again. -- Len __________________________________________________ ____ remove nothing for valid email address. "tina" wrote in message ... | suggest the following changes to tables A and C, as | | tblEquipment | EquipmentID (pk) | EquipmentNum | (get rid of the LastMoveID field in this table) | (and btw, a primary key field can be text. if the equipment number assigned | to a given item will never change, and is absolutely unique - if a piece of | equipment breaks down and is replaced, the new piece will get a *new* | equipment number - then you should be able to use it as the pk for this | table, if you want. but you can certainly use a separate field for pk, as | you're now doing.) | | tblMovements | MoveID | EquipmentID (fk from tblEquipment) | MoveTo | | relationship would be | tblEquipment.EquipmentID 1:n tblMovements.EquipmentID | | and btw, i'm guessing that you're tracking the movement of equipment from | location to location, correct? if so, i might have a table listing all | locations, with as much detail describing locations as you need; then | tblMovements would actually be a join table between tblEquipment and | tblLocations, as | | tblMovements | MoveID (pk) | EquipmentID (fk from tblEquipment) | LocationID (fk from tblLocations) | MoveDate | (if you include a move date, you can always find where a piece of equipment | is currently located - it will be the record with the newest date for that | piece of equipment, in tblMovements.) | | hth | | | "Len B" wrote in message | ... | Thanks tina | The FE/BE split is exactly as you said. The link fields looked ok but I'll | look at them again in the light of your definitions. The fact that they | seemed ok led me to look at the relationships as the problem. Yes I meant | creating the 'link lines' and enforcing referential integrity when I said | 'define relationships'. | | Looking at the relationships prompts this question - | The three tables (and fields) concerned are | | A B C | (tbl)Equipment (tbl)Maintenance (tbl)Movements | EquipmentID (PK) MaintenanceID (PK) MoveID (PK) | EquipmentNum (RU) EquipmentID (FK,A) EquipmentNum ??(FK,A)?? | LastMoveID (FK,C) MaintenanceCost MoveTo | PK=primary | FK=foreign | RU=Reqd+Unique (effectively another PK but data type is txt) | | Subform on B works. Relationship is A(1)-B(many)on EquipmentID | | There is a relationship between C(1)-A(many) on MoveID/LastMoveID. | I think I also need one A-C on EquipmentNum but when I try to create | one, access complains that there is already a relationship defined and | offers to delete it. Do I really need the existing relationship or can | I delete it to create the new one? | | -- | Len | __________________________________________________ ____ | remove nothing for valid email address. | "tina" wrote in message | ... | | comments inline. | | | | "Len B" wrote in message | | ... | | Hope this is the appropriate group to ask. | | | | I have two general questions and one a bit more specific. | | (a) Are relationships defined in the back end effective in the front | end? | | | | yes. | | | | (b) Is it preferable to define relationships in FE or BE? | | | | if your BE db is where you store the tables, and the FE db has links to | | those tables (that's the normal BE/FE setup), then we're on the same | page. | | you can "draw the lines" between linked tables, in the FE Relationships | | window, but you can't enforce referential integrity on table links. so | if, | | when you say "define", you mean set the parent/child links AND enforce | | referential integrity, then you must do that to native tables - in other | | words, in the BE db. | | | | (c1) Is it necessary to define relationships for subforms to work or | | | | if you mean "define relationships in the Relationships window", no it's | not | | "necessary". but you should, because defining relationships and | enforcing | | referential integrity is about ensuring the validity of the data. the | fact | | that it's easier to work with mainform/subform setups when those two | things | | are done, is a great by-product, but not the reason for doing it. | | | | (c2) How do relationships affect the working of subforms? | | | | strictly speaking, they don't, in themselves. there are numerous | | non-traditional uses of subforms that don't involve table relationships | at | | all, or stand the usual setup on its' head. but once you define a | | parent/child relationship at the table level, and enforce referential | | integrity, and then base a mainform/subform on those parent/child | tables, | | Access will pretty much demand that you set up it up right, or it won't | | work. | | | | | | I have a form with two subforms. Each subform also has a subform. | (Each | | combination appears on a separate tab of a tab control.) One works, | one | | doesn't and I cannot find why. The bad one shows all the detail | records. | | | | if you have a subform that shows all the records in the child table, | rather | | than only the records related to the parent record displayed in the | | mainform, then it sounds like you don't have the mainform/subform | properly | | linked. open the mainform in Design view. click ONCE on the subform, | within | | the mainform, to select it. in the Properties box, look at the | | LinkChildFields and LinkMasterFields properties. the first property | should | | be set to the name of the foreign key field in the child table (and make | | sure that field is included in the subform's RecordSource), and the | second | | property should be set to the name of the primary key field in the | parent | | table (again, make sure the primary key field is included in the | mainform's | | RecordSource. | | | | hth | | | | All | | the properties seem to be set in a similar manner so I am now looking | more | | widely. Any tips on what else might be worth looking at would be | | appreciated | | also. | | | | TIA | | -- | | Len | | __________________________________________________ ____ | | remove nothing for valid email address. | | | | | | | | | | | | |
#10
|
|||
|
|||
Relationships, back end
Good Tina.
I understand what you've said. I'm sorry to confuse you. My comment about the MoveTo field as the link was an afterthought that I put under the wrong heading. (My 'see below' comment should have made me realize! D'oh.) I was referring to your concept of making the Movements table a join table between quniLocation and tblEquipment. What I meant was - Which should I link the LocationID field of the select query to, either the Movements.MoveTo or Movements.MoveFrom fields. Since I now realize that the query isn't a native table and only one of its component tables is native, that it is fruitless to 'draw the line' anyway. Right? As for the import process, I didn't intend to import the text file directly into tblMovements but into a temp table, then use VBA to create records in tblMovement by looping through the tblTmpImport, then del tblTmpImport. I don't fully understand your use of the query which effectively adds the EquipmentID field the the temp table. You said "use that query to create the records in tblMovements". Did you mean 'use VBA to create the records from the data in the query' or have I missed the point? If I have correctly understood, how is creating an 'actual' query object better/different from creating a 'virtual' recordset to loop through? I am grateful for your patience, time and expertise. -- Len __________________________________________________ ____ remove nothing for valid email address. "tina" wrote in message ... | comments inline. | | "Len B" wrote in message | ... | Thanks tina, | FYI | --- | This app tracks loans of medical equipment to disabled kids. | | I'm not sure exactly why I used the LastMoveID field in tblEquipment. | I guess I'll find out once I remove it ;-) | | The EquipmentNum is a barcode sticker (6 numerals) applied at purchase. | Unfortunately a small possibility exists that it may wear off or fall off | and a new sticker issued. Also the numbers are not issued sequentially but | from a number of different rolls of pre-printed barcode stickers. | | There's already a MoveDate field in tblMovements. | (Will appear in subform and be used for ORDER BY DESC clause.) | | Location/Movements Relationship | ------------------------------- | There's already a Location table (tblChild) but it's in an entirely | different db created for unrelated purposes but is linked only in this FE, | but not linked in this BE; why would you. | (Similarly other tables are linked from the Child db eg Regions, Staff.) | FE Relationship not made yet so does this prompt any warnings from you | for making the relationship in this FE? | | you can't enforce referential integrity in relationships between linked | tables, so it's a waste of time to "draw the lines" in the Relationships | window in a FE db. you can only truly relate data between two *native* | tables that are in the same database. since you're working with linked | tables that are native to multiple backend dbs, you'll have to rely on | yourself to "enforce" referential integrity in the user interface. it's | harder to do, because the system won't prevent you from entering "orphan" | data - child data that has no valid parent data. | | I assume you would link to the | 'MoveTo' field rather than 'From' field - see below. | | i'm not sure what you're referring to here. link to the "MoveTo" field | where? if you were using native tables, you'd link tblLocations to both the | From and To fields in tblMovements. but as i said above, there's no point | setting that relationship in the FE db, and since tblMovements and | tblLocations are in different BE dbs, you can't set relationships between | the two tables at all. | | | Equipment/Movements Relationship | -------------------------------- | The movement info is created using a portable barcode scanner so the | EquipmentID isn't known then but the barcode is. The text file from the | scanner (Barcode, MovedBy, When, From, To) is then imported and the | movement records are created. That's why I wanted to use EquipmentNum | (rather than ID) as the basis for this relationship. Will using | EquipmentNum rather than EquipmentID mean more work or will it mean there | will be things to be keep in mind later? (Recording both From and To helps | to pick up unrecorded movements.) | | don't use EquipmentNum, use the primary key field EquipmentID as the foreign | key in tblMovements, as i said before. when you import your text file, | import it to a temporary table. then write a query that matches the | EquipmentNum in the text file with the EquipmentNum in tblEquipment, and | include the EquipmentID in the query's output. use that query to create the | records in tblMovements, rather than dumping the text file directly into the | table. that's how you get the necessary foreign key EquipmentID value into | each record in tblMovements, so there's a solid link between that table and | tblEquipment. | | hth | | | It sure would be simpler if that small possibility didn't exist and I | could make barcode the pk and get rid of the ID field. | | Your help is very much appreciated. Thanks again. | | -- | Len | __________________________________________________ ____ | remove nothing for valid email address. | "tina" wrote in message | ... | | suggest the following changes to tables A and C, as | | | | tblEquipment | | EquipmentID (pk) | | EquipmentNum | | (get rid of the LastMoveID field in this table) | | (and btw, a primary key field can be text. if the equipment number | assigned | | to a given item will never change, and is absolutely unique - if a piece | of | | equipment breaks down and is replaced, the new piece will get a *new* | | equipment number - then you should be able to use it as the pk for this | | table, if you want. but you can certainly use a separate field for pk, | as | | you're now doing.) | | | | tblMovements | | MoveID | | EquipmentID (fk from tblEquipment) | | MoveTo | | | | relationship would be | | tblEquipment.EquipmentID 1:n tblMovements.EquipmentID | | | | and btw, i'm guessing that you're tracking the movement of equipment | from | | location to location, correct? if so, i might have a table listing all | | locations, with as much detail describing locations as you need; then | | tblMovements would actually be a join table between tblEquipment and | | tblLocations, as | | | | tblMovements | | MoveID (pk) | | EquipmentID (fk from tblEquipment) | | LocationID (fk from tblLocations) | | MoveDate | | (if you include a move date, you can always find where a piece of | equipment | | is currently located - it will be the record with the newest date for | that | | piece of equipment, in tblMovements.) | | | | hth | | | | | | "Len B" wrote in message | | ... | | Thanks tina | | The FE/BE split is exactly as you said. The link fields looked ok but | I'll | | look at them again in the light of your definitions. The fact that | they | | seemed ok led me to look at the relationships as the problem. Yes I | meant | | creating the 'link lines' and enforcing referential integrity when I | said | | 'define relationships'. | | | | Looking at the relationships prompts this question - | | The three tables (and fields) concerned are | | | | A B C | | (tbl)Equipment (tbl)Maintenance (tbl)Movements | | EquipmentID (PK) MaintenanceID (PK) MoveID (PK) | | EquipmentNum (RU) EquipmentID (FK,A) EquipmentNum ??(FK,A)?? | | LastMoveID (FK,C) MaintenanceCost MoveTo | | PK=primary | | FK=foreign | | RU=Reqd+Unique (effectively another PK but data type is txt) | | | | Subform on B works. Relationship is A(1)-B(many)on EquipmentID | | | | There is a relationship between C(1)-A(many) on MoveID/LastMoveID. | | I think I also need one A-C on EquipmentNum but when I try to create | | one, access complains that there is already a relationship defined and | | offers to delete it. Do I really need the existing relationship or can | | I delete it to create the new one? | | | | -- | | Len | | __________________________________________________ ____ | | remove nothing for valid email address. | | "tina" wrote in message | | ... | | | comments inline. | | | | | | "Len B" wrote in message | | | ... | | | Hope this is the appropriate group to ask. | | | | | | I have two general questions and one a bit more specific. | | | (a) Are relationships defined in the back end effective in the | front | | end? | | | | | | yes. | | | | | | (b) Is it preferable to define relationships in FE or BE? | | | | | | if your BE db is where you store the tables, and the FE db has links | to | | | those tables (that's the normal BE/FE setup), then we're on the same | | page. | | | you can "draw the lines" between linked tables, in the FE | Relationships | | | window, but you can't enforce referential integrity on table links. | so | | if, | | | when you say "define", you mean set the parent/child links AND | enforce | | | referential integrity, then you must do that to native tables - in | other | | | words, in the BE db. | | | | | | (c1) Is it necessary to define relationships for subforms to work | or | | | | | | if you mean "define relationships in the Relationships window", no | it's | | not | | | "necessary". but you should, because defining relationships and | | enforcing | | | referential integrity is about ensuring the validity of the data. | the | | fact | | | that it's easier to work with mainform/subform setups when those two | | things | | | are done, is a great by-product, but not the reason for doing it. | | | | | | (c2) How do relationships affect the working of subforms? | | | | | | strictly speaking, they don't, in themselves. there are numerous | | | non-traditional uses of subforms that don't involve table | relationships | | at | | | all, or stand the usual setup on its' head. but once you define a | | | parent/child relationship at the table level, and enforce | referential | | | integrity, and then base a mainform/subform on those parent/child | | tables, | | | Access will pretty much demand that you set up it up right, or it | won't | | | work. | | | | | | | | | I have a form with two subforms. Each subform also has a subform. | | (Each | | | combination appears on a separate tab of a tab control.) One | works, | | one | | | doesn't and I cannot find why. The bad one shows all the detail | | records. | | | | | | if you have a subform that shows all the records in the child table, | | rather | | | than only the records related to the parent record displayed in the | | | mainform, then it sounds like you don't have the mainform/subform | | properly | | | linked. open the mainform in Design view. click ONCE on the subform, | | within | | | the mainform, to select it. in the Properties box, look at the | | | LinkChildFields and LinkMasterFields properties. the first property | | should | | | be set to the name of the foreign key field in the child table (and | make | | | sure that field is included in the subform's RecordSource), and the | | second | | | property should be set to the name of the primary key field in the | | parent | | | table (again, make sure the primary key field is included in the | | mainform's | | | RecordSource. | | | | | | hth | | | | | | All | | | the properties seem to be set in a similar manner so I am now | looking | | more | | | widely. Any tips on what else might be worth looking at would be | | | appreciated | | | also. | | | | | | TIA | | | -- | | | Len | | | __________________________________________________ ____ | | | remove nothing for valid email address. | | | | | | | | | | | | | | | | | | | | | | | | |
|
Thread Tools | |
Display Modes | |
|
|