Results 1 to 14 of 14
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access database - I'm stuck (2000)

    Hi,
    I have an Access database(zipped at 168K) that I was hoping I could mail to some kind soul. Its too big to attach to this post!
    What I need to be able to do with this database is to have a seperate table for either defendants or witness.
    Currently the database is set up so that only one witness per defendant can be entered. Against the witness is then subforms that record media that arrives in relation to that defendant and when that media is sent to third parties and then returned.
    The problem with the current database is that only one witness can be recorded per defendant, in reality this is not the case as there are normally several witnesses per defendant. The tracking of the media is fine and I would like to be able to keep it like this including all automatic entries and reference numbers for media stock.
    I would also like to be able to do a form previous to the database that allows the user to search for preious entries thus avoiding duplication. And then either go to the entry to amend/add or add a new entry from scratch.
    And finally, there is a calendar form in the database that I can apparently link to textbox's that need a date entered. This would be on a double click......I haven't a clue how to do this.
    Can anyone spare the time just to peruse over the database and give me a hand.
    Thanks,
    Armitageshanks

  2. #2
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access database - I'm stuck (2000)

    Theres quite a lot in there, but there shouldnt be any problem to achieve what you want to do.

    1st though, can you try and 'compact database' to see if the size can be changed. Go to Tools, Database utilities.

    [img]/forums/images/smilies/smile.gif[/img]
    Thanks,

    pmatz

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access database - I'm stuck (2000)

    Thanks, its now 101k.......what a sod

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access database - I'm stuck (2000)

    That's just over the limit, but you might try importing everything (all objects) into a new empty database, and then zip that database. Compacting usually isn't quite as effective as doing the import. If that doesn't do the trick you could delete some of the forms/reports, or alternatively some of the data, but representative data usually makes things easier to understand.
    Wendell

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access database - I'm stuck (2000)

    Or as Hans has suggested in the past, save it in previous version, possibly a97

    HTH

    John

  6. #6
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access database - I'm stuck (2000)

    Finally got it down to under 1 hundred. Any one fancy a look???
    Thanks
    Attached Files Attached Files

  7. #7
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access database - I'm stuck (2000)

    Hello.

    I've had a look at the database, here's my suggestions: -

    If you want more than one witness you'll have to add a new table for the witness details, use the ID from the primary table to identify the witness relationship, this will then give you a one defendant to many witness relationship. Getting data into the table could then be accomplished by adding a sub form to the current input form.

    To search the database for previous entries create a new form and drop a combo box on it, use the wizard and select the 'find a record that......' option on the wizard, make sure you set the not in list property or people will be able to add records this way.

    If this makes sense good, if not tell me where it doesn't and I'll rephrase my answer.

    I could possibly do the job, but if I did you wouldn't get to learn a whole new set of tools to play with, Access is actually quite fun.

    One last point, if the fact you can't find the database window is stopping you then go to Tools>Startup and check the view database window, restart the database and you're in.

    HTH

    Ian

  8. #8
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access database - I'm stuck (2000)

    Hi,
    Thanks for the reply, this does make sense that all the information would be on one form alone.
    Unfortunately I'm not 100% sure how to do what you have said. I've only been basically trained on Access and I'm now expected to be able to do complex databases..........it's annoying because I can dance around excel but access ...........oh noooooooo.
    Presumably I create a table with just the witness details in, this is then duplicated in the sense of a form and then this form is as a subform in the main form.
    The problem that I can foresee with this (and I might be wrong) is the table currently generates automatic entries for the witness and the media that relates to them. Therefore when you click add, choose ..6 videos, it then displays 6 entries each with their own reference numbers. These can then be marked 'In' or 'Out'. What I wouldn't know how to do is to enable the subform to act in a similar way as currently this all relates to the main form.
    Any suggestions?
    Thanks

  9. #9
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access database - I'm stuck (2000)

    OK, here go, basic Access 101.....

    If you put the current form into design view you will see it comprises a main form and two sub forms. The sub forms take their data from tables on the 'many' side of the one to many relationship set from the 'main' table. This will let you check the data source form the form's properties.

    Having had a better look at the database I'm curious as to why you want to make these changes, while in database best practice having a single record of the defendant and many records of the witnesses is the way forwards, it does not really effect the volume of data you will be storing.

    If you are set on the change I think it might actually be easier to add a new table for the defendant, as the interview data appears to be specific to the witness, you would then add a single field to the current 'main' table to allow storeage of the unique ID from the defendant table.

    Some of the code in the current system indicates that this is part of a larger entity, or was conceived as such. The on current code for the data entry form in particular seems to 'looking' for information from a form other than the one that opens it.

    I will try and produce an example database, but I'm not making any promises as time is a little tight.

    As for learning Access I've never had a lesson in my life, what I know has been learnt due to various managers making 'unreasonable' demands.... Fortunately people like Hans and Charlotte provide very good and easily understood help, much better than anything you'll get from me!!!!

    Ian

    Ian

  10. #10
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access database - I'm stuck (2000)

    Hi,
    Thanks for the help.
    In theory all that I will need to be able to do is keep the form the same way that it is now but allow the database to give the user the ability to enter mutiple witnesses in relation to the same defendant without re-entering the data everytime a new witness needs to be entered.
    Being pretty inexperienced in any form of access design I'm not too sure how to alter the current database but essentially keeping its core properties. I fear that I would mess the current properties up so that entries are not duplicated in the case of the amount of media.
    My guess, from what you've indicated is that a seperate table is to be created for the defendants as this will not directly affect any of the properties of the main form.
    This data once entered would then link its URN over to the current main form and so on and so forth. I presume that a relationship will need to be created between the defendant id and the witness id , presumably forced referential.
    Would the defendant form be best outside of the main form? I suppose that reply would be how long is a bit of string..............but generally I would guess that databases work better the easier the layout is for the user.............I also would guess that this way would make it easier for the user to enter searches for existing 'cases' and thus edit them or delete.
    As you can see my inexperience is shining through...............but generally layout is somewhat muddled in my mind, and apart from the technical specifications on how to accomplish it without messing around too much with the original configuration i can see what needs to be done.
    Thanks for your tolerance

  11. #11
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access database - I'm stuck (2000)

    Your last post has me worried about exactly where this sytem is 'going'. Do you have any clear information regarding the final scope of the project? Any flow charts or other data that would help to indicate the intended scope?

    I've rather leapt at this and am now worried the project has a scope I've missed out. Before starting any project of my own I make sure I have the final scope of the project outlined, even if that is alrady known to be beyond what will be accomplished during the stage I'm involved in. Then interview the users to ensure I have a full and detailed process map for the system. If you are starting out to modify this system then I think you ought to sit down with your boss and define teh scopem then chat with your co workers to get to understand exactly what they expect from the system.

  12. #12
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access database - I'm stuck (2000)

    Well, as Ian said, there is no other thing but to make a separate table.
    You _can_ add several fields to your table (Witness1, Witness2, etc.) if you know you aren't going to have any more witnesses than, say 5, but that is not Pure and Good, and gets _real_ ugly fast. That's the bad way. The best way is to have your case table, your Witness or "person" table, and a join table between the two -- which contains the case ID, the person ID, and what role that person has.
    Then it's up to you to get forms wrapped around those tables, the witness subform inserted in the main case form, etc. You'll probably want to take a couple of quick classes in Access. A lot of local places offer 1-day intermediate and advanced courses, and that might be helpful. -- thx
    Pat

  13. #13
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access database - I'm stuck (2000)

    The way the database will need to work is that Cases arrive within the system where there is one defendant. Videos come in to the office in relation to that case with witness interviews on them. There can be an undetermined amount of witnesses, from 1 to probably 24/25 maximum (but this is not set in stone). The videos/audios will come in at seperate times so therefore need to be added to each witness (or defendant depending upon how you look at it) as they come in. That's why the database information will need to be amended. The scenario may also be that further witnesses are found and therefore they will need to be added at a later date as well as their media type and quantity. The actual entry will not need to be deleted once the case has been finalised as it will effectively be a record kept for approximately 5/6 years.
    Hence why The defendant will need to have a seperate table to the witness due to the volume of witnesses to that person. However little or large.
    That is essentially what the user of the database will be doing throughout the history of the case, registering the defendant, entering the witness/es, then record media type and quantity, then record and movement the media makes (i.e. to external parties on a date, returned 2 weeks later). Adding new witnesses and adding new media when they come in, and then recording the media movement........and so on. The reasoning behind this accuracy is the fact that the videos are interviews and relate to allegations of child abuse, and therefore due to their delicate subject all media involving the children needs to be kept track off with some form of audit trail to their movements.
    I've attached the basis of what I'm trying to work on. I've added a tbldefendant, then defform with the subform being the current form attached previously to this post. This is how I had envisioned the table looking. The only problem with the this form is that I can't seem to be able to get the URN in the tblinterview to carry across from the tbl defendant. I also cannot get the add media control to work, when I choose the amount of media it comes up with 'No'.
    I've also had to delete the calendar frm which I would like to incorporate into this form as in the original post. This I have no idea how to do.
    Any suggestions?
    Thanks.
    Attached Files Attached Files

  14. #14
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access database - I'm stuck (2000)

    I haven't had the time to view your updated database, this evening maybe, but here's a suggestion. Turn the current form into a sub form, use a combo box to control and add the data about defendants. A combo box has two useful event functions, the first one being on update, the second being on not in list. You can combine these to check whether it's a current defendant or a new one and carry out an action. The actions are these, if the defendant exists then find the data relevant to the defendant in the subform (create a 'training' form and use the button wizard to get an idea of the code to use), if the defendant does not exist then open create a new record and place the defendant table unique ID in the relevant child tables.

    If you haven't done that flow chart yet and prepared a spec yet then try to do it, it really is worth its weight in gold once you start to modify stuff. I learnt this the hard way by wasting a lot of my time.. You can use Excel to prepare a very useful flow chart, so no difficulties there.

    Ian

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •