Re: Relationships, back end

comments inline.

"Len B" <gonehome@xxxxxxxxxxxxxxxxxx> wrote in message
news:O9jtgyM3JHA.4412@xxxxxxxxxxxxxxxxxxxxxxx
I'm not sure I trust other users to be creating and executing append
queries.
I'm much happier having them click a button once the file has been
imported.
Or did you mean - set up a permanent append query and have a button run it
and
then delete the temp table.

yes, that's exactly what i meant. though i probably would set up an Import
Specification on the text file and *link* it to the FE db. then write the
query the same, using the linked file directly rather than a temp table. run
the Append query from VBA code in a form, so the user sees only the
interface rather than the nuts 'n bolts.

if you find that you must use a temp table for some reason, rather than the
linked text file directly, i'd favor setting up a separate db on the hard
drive or the server, to hold the (empty) temp table. you can write code to
replace the temp *db* each time you use it, so there's no bloat issue either
in the temp db or in the working FE db.


BTW, before your previous reply I did change the Master/Child links for
the
movements subform to MovementNum and now the movements subform now
displays
only the appropriate movement records plus an 'add new record' (*) row
with
default values. OTOH The Maintenance subform just displays appropriate
maintenance records without the * row. I expect this will remain the case
when
I change the links to MovementID. I am curious as to the differing
behaviours.

--
Len
______________________________________________________
remove nothing for valid email address.
"tina" <nospam@xxxxxxxxxxx> wrote in message
news:UkeSl.27101$d36.8375@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
| comments inline.
|
| "Len B" <gonehome@xxxxxxxxxxxxxxxxxx> wrote in message
| news:OW%2342TE3JHA.1420@xxxxxxxxxxxxxxxxxxxxxxx
| > 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?
|
| okay, looks like you understand creating the SELECT query "which
effectively
| adds the EquipmentID field the the temp table". you're correct, that's
| exactly what you're doing - writing a query that will return a dataset
which
| includes the fields you'll need in order to add the new records to
| tblMovements. once you've done that, open the query in Design view. on
the
| menu bar, select Query | Append, and in the dialog, choose tblMovements
from
| the droplist of tables. if you're not familiar with setting up Append
| queries, read up on it in Help, it's not hard to do in the Design grid.
if
| you have difficulties, post back for specific help.
|
| no point opening opening and looping through a recordset when an Append
| query will do all the work for you.
|
| hth
|
| >
| > I am grateful for your patience, time and expertise.
|
| no problem. a newsgroup is not the best venue to teach techniques and
| troubleshoot problems, but with patience and persistence, we can usually
end
| up communicating well enough to get it done. :)
|
| > --
| > Len
| > ______________________________________________________
| > remove nothing for valid email address.
| > "tina" <nospam@xxxxxxxxxxx> wrote in message
| > news:LLLRl.23712$d36.23208@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
| > | 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.
| > | > | > | >
| > | > | > | >
| > | > | > |
| > | > | > |
| > | > | >
| > | > | >
| > | > |
| > | > |
| > | >
| > | >
| > |
| > |
| >
| >
|
|




.