Re: Relationships, back end

Tech-Archive recommends: Fix windows errors by optimizing your registry

comments inline.

"Len B" <gonehome@xxxxxxxxxxxxxxxxxx> wrote in message
news:ucOJirg2JHA.5816@xxxxxxxxxxxxxxxxxxxxxxx
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" <nospam@xxxxxxxxxxx> wrote in message
news:_D5Rl.259522$4m1.232607@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
| 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" <gonehome@xxxxxxxxxxxxxxxxxx> wrote in message
| news:usVjJlV2JHA.1864@xxxxxxxxxxxxxxxxxxxxxxx
| > 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" <nospam@xxxxxxxxxxx> wrote in message
| > news:nwLQl.16281$d36.436@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
| > | comments inline.
| > |
| > | "Len B" <gonehome@xxxxxxxxxxxxxxxxxx> wrote in message
| > | news:%232gR4gO2JHA.1420@xxxxxxxxxxxxxxxxxxxxxxx
| > | > 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.
| > | >
| > | >
| > |
| > |
| >
| >
|
|




.

Relevant Pages

  • Re: Update field from one table with value of field in another table
    ... tblMeters and and tblEquipment and this "description" value is a string; ... the same EquipmentID but not the same Description. ... WHERE M.MeterDescription = 'Hour Meter'; ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Relationships, back end
    ... EquipmentID (fk from tblEquipment) ... and then base a mainform/subform on those parent/child ... Each subform also has a subform. ...
    (microsoft.public.access.tablesdbdesign)
  • Database Design
    ... MembersEquipmentID ... EquipmentID (Combo from tblEquipment) ... the EquipmentTypeID Combo in tblMembersEquipment to vary depending on ...
    (microsoft.public.access.gettingstarted)
  • Re: Database Design
    ... Your field "Name" in tblMember has to be changed because you used a reserved ... MembersEquipmentID ... EquipmentID (Combo from tblEquipment) ...
    (microsoft.public.access.gettingstarted)
  • Re: Relationships, back end
    ... Which should I link the LocationID field of the select query to, ... the records in tblMovements". ... adds the EquipmentID field the the temp table". ... |> from a number of different rolls of pre-printed barcode stickers. ...
    (microsoft.public.access.tablesdbdesign)