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

    Database advice needed. (2000)

    Hi,
    I've attached a simplified database that I need some advice on. I haven't got to produce complicated reports or statistics but I have got to display data to the user in a specific way.
    On FrmBoxes a list of 144 boxes are displayed. These boxes are cabinets that contain a filing system.
    I need to have the user to be able to hover over each box and a pop up needs to be displayed with all records in TblArchive that have a Box number relating to that box.
    In relation to the list of names in the pop up I would like them to be colour coded in some way. For instance, if a file has been temporarily moved then the user will go into the database and check a box (I haven't done this bit yet!) on that entry. On the pop up that entry will be displayed in purple text.
    I would also like to be able to change the colour of the actual box dependant upon whether there are any spaces in in that box (or filing cabinet). Currently I am working under the premise that all boxes are full. When the files date expires they get binned. Thus making a space in say box 42. This box would then turn yellow, instead of grey. I'd also like the boxes to be a different colour if red.

    I had dabbled with the idea of giving each file a density number (i.e. how thick it is), say 1 for a standard, 5 for a extra thick. But then that would mean going through all the existing files, over 1000............so I won't be delving into that. But this would have been a mre accurate way of filing varying thicknesses of files in a sctructured space............
    Basically, is this possible????? And if so how?
    Attached Files Attached Files

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

    Re: Database advice needed. (2000)

    Hi Nigel,
    My gut reaction is that the approach you suggest is fraught with peril (overstating the case a bit). For one thing, how do you know that there will never be more than 144 boxes? My concern is the number of objects on the form, as well as the number of fields in the query that such a design would require. Access works well when you have many records with a few fields, but not so well when you have a few records with many properties. Two specific limits I would worry about are the maximum number of fields in a query (255) and the maximum number of objects on a form over its lifetime (754). Also, the use of the mouse hover method, as opposed to the mouse click, is very likely to be problematic. Firstly, the Control Tip feature of Access forms uses this kind of logic internally. Secondly, there is no hover event per se, so you would have to use the mouse move event, and then create some complex logic to determine whether the mouse had hovered long enough to open the detail form for that box.

    If you can describe the user problem you are trying to solve, someone may be able to suggest a better alternative - if your user is insisting on a specific method of solving the problem then I think you have several challenges.
    Wendell

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

    Re: Database advice needed. (2000)

    The form will only ever hold 144 boxes hence no concern from my behalf that the boxes will develop into extortianate values.
    The criteria set out was that the current system that has files placed into box numbers would be displayed in a user friendly way. i.e. the 144 onscreen boxes. The user would then be able to tell from a glance as to whether that box was full and what was in that box by concentrating the mouse over it.
    Essentially I would create a database that is for arguments sake full (in the sense of physical material). The contents of the database would be held over 144 boxes, or essentially entries on a table. So I would have a TblBoxes with 144 entries, linked to TblData with the actual file information in. Each entry in TblData is given a box number.
    When the user produces a report to detail whether the file destruction data was less than now(), i.e. expired, the user would find those file and delete the data from the database. This would then show all spaces that had been created by the removal of these files.
    I was also planning to do search fields so that the user could specify which box a file was in............and so on.

    The problem with the current system is that the filing is poor and that when a file is removed it is problematic to locate at a later date as to whether or not that box now has a space available. Otherwise the current system is unable to track any file removal and its effect on available space in a specific box.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Database advice needed. (2000)

    Since you give no indication of how to determine when a box is full, I don't know how to handle that.

    The attached version demonstrates a way of displaying pop-up information.
    Attached Files Attached Files

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

    Re: Database advice needed. (2000)

    I would tackle the project using a rather different paradigm. I would start with a complete inventory of each box, then do the sort of aging process you described to get rid of the dead wood, and make an assumption that a box could hold on average a set number of files. Then I would randomly assign any new folder a number from 1 to 144, and have the user file the folder in that box. We actually built a document management system with an imaging process and did it more or less in that fashion. When a box gets over full because of a fat folder, simply ask for another filing location. Also, in general I would expect very little need to simply see the contents of the box - it's a random mixture and therefore not very interesting. The real need to to find a specific folder, or to find folders to be destroyed. While the boxes user interface is interesting, I think you would find it used very seldom as long as you let the computer figure out the box the folder should be put in. Just my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>.
    Wendell

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

    Re: Database advice needed. (2000)

    Hi Hans,
    Yet again thanks for your input.
    I see the problems faced with this approach to the database as stated by Wendell's post to this query. I think that I will be working on the premise that all the boxes will be full from the start, possible with each box having a true/false value that I can then alter once all the data has been transferred from the current excel sheet. As the files differ in size the only way that I can see for the boxes to be marked as full is too have an actual checkbox, or similar, to indicate this. The problem lays with the thickness of the files and that obviously not all are the same.
    The display of the actually box numbers seems slightly wasteful, but this is what has been requested. I suppose it is an instantaneous indication of where the spaces are. So I'll stick with it.
    I do however think that essentially all the user will generally do is to actually search the database. This would be done by URN, defendant or victim.
    How would I change the actual colouring of the box dependant upon whether the box value is true or false (in the sense of full).
    All a bit messy..................

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Database advice needed. (2000)

    The form you designed uses command buttons; you can't change the background colour of command buttons (that is determined by a setting in the user's control panel), but you can change the properties of the caption (font, size, colour). You'd have to loop through the command buttons in the On Load event of the form, and set the caption properties based on the status of the "Full" check box.

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

    Re: Database advice needed. (2000)

    Would it be more practicable to redesign the whole thing. I just think that the premise off all the boxes and having a caption box is a bit messy. I confess that I was unsure of what limitations this would have.
    The problem lays that essentially the physical filing is layed out in a impracticable way. They are filed in the order of destruction date. The destruction date is determined by the length of a custodial sentence. Therefore a file sentenced today may have the same destruction date as one finalised 6 years ago. Therefore they've found that they are having to move large amounts of files around into the dates that have just expired in order to fit in the new destruction dates.
    I believe the best way forward would be as long as the file has a box recorded against it, it wouldn't really matter where that file was put. The accuracy of the physical cabinets and their numbered boxes are set in stone and therefore accurate.
    The user is still required to have a visual aid to determine which boxes have spaces (however small) so that they can simply file them away without having to go through the whole filing system................... <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45>

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Database advice needed. (2000)

    I think Wendell's approach is best. If you have a yes/no field or any other way of indicating whether a box is full or not, the application can assign an available box number to a file. There is no real need for a visual representation.

    If you really want the visual representation, you could use labels instead of command buttons. You can set both the foreground (text) and background (fill) colour for labels.

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

    Re: Database advice needed. (2000)

    If the users absolutely insist that they have to make the decision about which box a folder is to be filed in, rather than using command buttons, you could simply use a rectangle instead of a command button, and then float a label above it. In that way you could change the background color of the "button" - make it raised so it looks more or less like a button. But I think Hans and I agree that you will spend hours and hours of time trying to design a clever and complex user interface (and probably encounter all sorts of problems and challenges) which in the long run the users probably won't like, where if you change the "business process" a bit, a much simpler design will make the user's life better all around.
    Wendell

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

    Re: Database advice needed. (2000)

    The problem is that a user may remove a file that is say 14cm thick, and need to place a file 40cm thick in its place. This is why I feel that unless a stock take is physically taken of the individual file size and box location then the method of automatically assigning a box number will be somewhat unpredictable. We can have a single file that take up three boxes, and one that is 5mm thick.......plus if this was done it would rely to much on the user determining a file density based on their assumption.
    With regards to the similar database that you have created, how did you get around this issue?

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

    Re: Database advice needed. (2000)

    Hi Hans,
    Just as a bit of a tangent but still relating to my redesigning of this form, I've been having a look at your reply at <post#=325625>post 325625</post#> regarding searching of records in a database. Presumably this would work in relation to my database, albeit slightly tweaked in the appropriate places.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Database advice needed. (2000)

    A good way to create a search function on a form is to put a combo box on the form with Control Wizards on. The third option of the wizard is to use the combo box to find records; after you have answered a few questions, the wizard will create the necessary code for you.

Posting Permissions

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