Best way ... ?

[Black Knight]

Please could someone help me out here.

I'm involved with a project at work and we're using Access 97 to store the
data.

I don't think the 'expert' who set it up really knows what he's doing
becuase we are manually copying the same data into to several tables etc
rather than having all this done through one or more forms and make it more
automatic.

Basically its just a table with various fields (yeah I know they're all like
that !)

I've loads of questions but firstly I need help with the main problem

There's a field called 'MASTERLOOP'
It's not unique

There's another field called 'LOOPTAG'
This is unique

The way the data is recorded is as follows (example only) ........ you will
see how the two fields are related

MASTERLOOP | LOOP TAG |

12-X-123 12-XA-123
12-X-123 12-XB-123
12-X-123 12-XC-123
19-P-345 19-PA-123
19-P-345 19-PB-123
99-T-789 99-TA-789
99-T-789 99-TB-789
99-T-789 99-TC-789

and so on .....

So for each MASTERLOOP reference there could be several records associated
with it.

How do I make a form that displays the one MASTERLOOP reference number but
also displays all the associated records in a sub form one at a time ?

Like this

Record 1
-------------------------------------------------
12-X-123
-------------------------------------------------
12-XA-123
-------------------------------------------------

Record 2
-------------------------------------------------
12-X-123
-------------------------------------------------
12-XB-123
-------------------------------------------------

Record 3
-------------------------------------------------
12-X-123
-------------------------------------------------
12-XC-123
-------------------------------------------------


Also how do I set a form up to enter a new MASTERLOOP reference number and
then allow me to add as many records as are associated with that one number
?

Sorry its long-winded - I was told Access will make it easy !!!

 

[Guest]

I certainly concur with your view regarding the original developer's
'expertise'. The short answer here is that you can delete the MasterLoop
field from the table; its completely redundant as its value can be computed
from the LoopTag field.

Taking things as the stand, however, as its all in one table you don't need
a subform at all; it can be done in a simple form in continuous form view by
putting controls bound to the MasterLoop and LoopTag fields one above the
other in the detail section.

To add new records you can use the same form. You would have to enter the
MasterLoop values each time, however, though you could semi-automate it so
that the first new value entered after opening the form is automatically
entered into subsequent new records while the form is still open. If you
want to change to a new MasterLoop value you'd then have to edit the value
entered by default. The edited value would then become the default while the
form is still open. To do this you'd put the following code in the form's
AfterInsert event procedure:

Me.MasterLoop.Defaultvalue = """" & Me.MasterLoop & """"

This is very much a Band-Aid solution to a fundamentally wrong design,
however. If the rest of the design of the database mirrors this level of
incompetence you'd be well advised to go back to square one and rebuild it
from scratch. Existing data would probably still be transferable into a
properly designed application, and is not usually as difficult to do as might
be imagined. Even if its wrongly stored at present provided its consistently
wrong then it should be possible to get it out and into a well structured
logical model.

Ken Sheridan
Stafford, England

 

[Jeff Boyce]

In addition to Ken's suggestions, remember to make a backup copy before you
start changing things.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Black Knight]

I certainly concur with your view regarding the original developer's
'expertise'. The short answer here is that you can delete the MasterLoop
field from the table; its completely redundant as its value can be
computed
from the LoopTag field.

Taking things as the stand, however, as its all in one table you don't
need
a subform at all; it can be done in a simple form in continuous form view
by
putting controls bound to the MasterLoop and LoopTag fields one above the
other in the detail section.

To add new records you can use the same form. You would have to enter the
MasterLoop values each time, however, though you could semi-automate it so
that the first new value entered after opening the form is automatically
entered into subsequent new records while the form is still open. If you
want to change to a new MasterLoop value you'd then have to edit the value
entered by default. The edited value would then become the default while
the
form is still open. To do this you'd put the following code in the form's
AfterInsert event procedure:

Me.MasterLoop.Defaultvalue = """" & Me.MasterLoop & """"

This is very much a Band-Aid solution to a fundamentally wrong design,
however. If the rest of the design of the database mirrors this level of
incompetence you'd be well advised to go back to square one and rebuild it
from scratch. Existing data would probably still be transferable into a
properly designed application, and is not usually as difficult to do as
might
be imagined. Even if its wrongly stored at present provided its
consistently
wrong then it should be possible to get it out and into a well structured
logical model.

Thanks Ken

How would I 'compute' the MASTERLOOP field in the table ?

 

[Guest]

In a form or report you could have an unbound text box with a ControlSource of:

=Left([LoopTag],4) & Right([LoopTag],4)

You'd no longer need to set the Defaultvalue property for this control of
course as once you enter the LoopTag value the unbound control would show the
computed value.

In a query you'd do it in a similar way:

SELECT Left(LoopTag,4) & Right(LoopTag,4) AS MasterLoop, LoopTag
FROM YourTable;

To do this in query design view enter the following in the 'field' row of a
blank column:

MasterLoop: Left([LoopTag],4) & Right([LoopTag],4)

An Alternative approach would be to have the MasterLoop field as at present
in the table, plus a Tag field with just the A,B,C etc. You'd then compute
the MasterLoop with:

=Left([LoopTag],4) & [Tag] & Right([LoopTag],4)

The MasterLoop and Tag columns should be the composite primary key of the
table or otherwise indexed uniquely. You could then still set the
MasterLoop's DefaultValue as I described and you'd just need to enter A,B,C
in the Tag field when entering a new record until you wish to change the
MasterLoop value to a new one.

I should emphasise, however, that this is probably far from the right way to
do things. I suspect that the table really needs decomposing into at least
two tables, one with one row per MasterLoop value and another referencing it,
containing a column of the Tag values. Without knowing what the entity types
being modelled are, however, its not possible to be definitive about this.

Ken Sheridan
Stafford, England

 

[Black Knight]

In a form or report you could have an unbound text box with a
ControlSource of:

=Left([LoopTag],4) & Right([LoopTag],4)

You'd no longer need to set the Defaultvalue property for this control of
course as once you enter the LoopTag value the unbound control would show
the
computed value.

In a query you'd do it in a similar way:

SELECT Left(LoopTag,4) & Right(LoopTag,4) AS MasterLoop, LoopTag
FROM YourTable;

To do this in query design view enter the following in the 'field' row of
a
blank column:

MasterLoop: Left([LoopTag],4) & Right([LoopTag],4)

An Alternative approach would be to have the MasterLoop field as at
present
in the table, plus a Tag field with just the A,B,C etc. You'd then
compute
the MasterLoop with:

=Left([LoopTag],4) & [Tag] & Right([LoopTag],4)

The MasterLoop and Tag columns should be the composite primary key of the
table or otherwise indexed uniquely. You could then still set the
MasterLoop's DefaultValue as I described and you'd just need to enter
A,B,C
in the Tag field when entering a new record until you wish to change the
MasterLoop value to a new one.

I should emphasise, however, that this is probably far from the right way
to
do things. I suspect that the table really needs decomposing into at
least
two tables, one with one row per MasterLoop value and another referencing
it,
containing a column of the Tag values. Without knowing what the entity
types
being modelled are, however, its not possible to be definitive about this.

Ken Sheridan
Stafford, England

Ken

Thank you so much for your replies and your help.

I've managed to concatenate each field as you suggested using a query and
then making a form from the query.

I think the reason for the MASTERLOOP field to be typed in is because its
used in other tables.

If this field is derived using a query or a form then the other tables would
not work properly ?

If I can be so bold I can give further information if you were able to spare
your valuable time to help out.

Many Thanks

 

[Guest]

It would still be possible to reference the key of another table even without
the MasterLoop column; you'd use the same expression as is used to compute
the value from the LoopTag value. However the best way would be the
alternative I suggested, of keeping the MasterLoop column and having a Tag
column with just the A,B,C etc, and then computing theLoopTag value. This
also eliminates the redundancy, which is important because it can otherwise
leave the door open to update anomalies. With the present setup it is
possible to have a LoopTag value and a MasterLoop value in the same row with
the common elements differing. By elimination the redundancy this is no
longer possible.

You can easily populate a Tag column from the existing data with a simple
UPDATE query once you've added the Tag column to the table definition:

UPDATE YourTable
SET Tag = MID(LoopTag,5,1);

Once you are satisfied that the tag column contains the correct values the
LoopTag column can be deleted from the table definition. The MasterLoop and
Tag columns should then be (together) indexed uniquely, which you do from the
View|Indexes menu item in table design view.

BTW I got the expression wrong for computing the LoopTag; it should have
been the following of course:

=Left([MasterLoop],4) & [Tag] & Right(MasterLoop],4)

Feel free to post any more questions you have on this.

Ken Sheridan
Stafford, England