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