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.

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

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.

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

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.
|
|
|
|

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.
|
|
|
|

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.
| |
| |
| |
| |
|
|
|
|

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.
|| |
|| |
|| |
|| |
||
||
||
||
|
|

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.
| |
| |
| |
| |
|
|
|
|

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.
| | |
| | |
| | |
| | |
| |
| |
| |
| |
|
|
|
|