 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / March 2006Best way ... ?
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 !!! 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 > Please could someone help me out here. > [quoted text clipped - 67 lines] > > Sorry its long-winded - I was told Access will make it easy !!! >I certainly concur with your view regarding the original developer's > 'expertise'. The short answer here is that you can delete the MasterLoop [quoted text clipped - 31 lines] > 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 ? 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 > Thanks Ken > > How would I 'compute' the MASTERLOOP field in the table ? > In a form or report you could have an unbound text box with a > ControlSource of: [quoted text clipped - 44 lines] > 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 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 > Ken > [quoted text clipped - 13 lines] > > Many Thanks In addition to Ken's suggestions, remember to make a backup copy before you start changing things. Regards Jeff Boyce Microsoft Office/Access MVP > Please could someone help me out here. > [quoted text clipped - 67 lines] > > Sorry its long-winded - I was told Access will make it easy !!! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.