Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    May 2003
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Please help!my job is depending on this (Access 2002)

    Hi to everybody
    I know it is looking a bit dramatic the title of my post but i am not joking!
    I have this problem and i really don't have a clue how to solve it
    And by the way i can really describe it because i don't know which method i should use
    So lets go to the problem and hopefully my boss won't fire me(I am not joking i am 100% honest about this it is all a matter of time)
    I have a form based on a rather huge table with about 100 filelds probably they will get more on the way
    I will put 63 records this week (the records are about some houses my company is building; each home a separate record ok?) and the problems is
    Every record has about 40 checkboxes about works each house is needed(for example house1 needs painting,heating,plumbing etc , house2 needs plumbing,heating,fences etc and the list goes on) and to get the information straight these works are standards( i split them in 2 parts i)works to be done - ii)works that this week are going to get done)
    So for this week i will have for all these 63 houses a perfect report to give to my boss(he will see that house1 has for example 5 works undergoing and about 3 to get done and this goes the same fot all the others) but the problems arise for next week ---->
    Next week i will have some data for example for house14 that plumbing is done and we are going to make the heating and this is the big question how when i put this new record for house 14 (with all these checkboxes i mentioned) and for the sake of example lets say that i have data for 15 houses how i will get :
    1ST my new records when i pick a house that has an old rcord(i don't know if i am making myself completly understood eg house14 on this week has 3 works undergoing and 5 more to come
    house14 next week has completed 2 of the 3 works last week and now has 6 works to get done) i want to come will all the old values present(for example i have checked plumbing and electricals for this week i want them to be checked again when i move to the next record so i get the information if they need to be still checked or not) and
    2ND my records who had no update(for example house nothing was done in house21 it is still the same works for next week as it has this week and no progress was made) are copied to the next week(sorry for the examples but i am really i a very bad situation and i am desperate to say the least again one more example:
    week20 house3 has plumbing,heating,roof .......
    week20 house15 has heating,roof,painting....
    week20 house35 has painting,tiles,fences....

    lets move to next week
    week21 house3 & house15 &house35 when i select them as new records for week 21 come with all the old values in the checkboxes but i have made progress with house3 and its plumbing so i uncheck plumbing and similar for house15 for its roof but now i a new job e.g fencesso i check fences and house35 stays as it was with no changes.
    So when i go to print my report for week21 i will have an updated house3 & house15 but house35 carries the old values and my report has all these 63 records i mentioned before
    Please bear in mind that anytime these 63 records can easily go up or down(a new house or house that although was ready to get build the owner change it mind)
    As i said before i can really describe what i need and i have very little knowledge of access and vba but i surely as hell want to keep my job
    If anyone can help me i would be much obliged to say the least
    PS sorry for the length of this post and for the many examples but when someone is deperate is desperate
    In order to show the nature of the problem please download my sample databas
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Please help!my job is depending on this (Access 2002)

    I don't know if you built this or someone else did, but the design is going to cause you grief sooner rather than later. You have a table for ContractDeal, which contains a link to the hypercontract, so why are you repeating the hypercontract field in the WorkProgress and House tables? The idea of relational design is that you enter data in one place and then use key fields to link tables together in queries, etc., in order to see the related data. You need a table to point to the individual house project, but without all those fields for week and types of work. *Those* fields belong in a separate table joined to the table containing the work project. Then you can have records in the latter table for each week of the project while still only having a single record for the project itself. Actually, I would take it a step further and have a field in the "week" table for Week, WorkType and Status, in addition to a foreign key to the WorkProgress table. Then instead of 40 yes/no fields in a record, you would have as many records as you might need to describe the worktype and status for an individual house/week. Your forms would need to be completely rebuilt using subforms (assuming you want to use bound forms) but your data will be cleaner and more reliable and your tables and forms won't need to be changed whenever you come up with a new worktype.

    As far as losing your job goes, if keeping it requires you to build Access databases, then I would suggest you do some fast studying on relational concepts to prevent building structures that will be impossible to maintain over time.

    There is something about your forms that my system dislikes because I keep getting ActiveX messages when those arrow buttons are clicked (it may be the terrible name assigned to the project itself or it may be corruption) and parameter messages when I open the workprogress form.
    Charlotte

  3. #3
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Please help!my job is depending on this (Access 2002)

    I didn't open your attachment, because just from your description I completely agree with Charlotte. You need to restructure your data. Instead of having multiple records for the same house, just have a header record that provides a key for each house, and a therefore only record per house. Then I personally would create three tables. The first two would be 'lists', one for the type of work, and one for the status. (ie, table 1 would have Plumbing, Heating, A/C, etc., Table 2 would have Started, InProcess, Completed...or soemthing along those lines....) The third table would have the keys from the first two tables for the first two fields, then a date field, then a comment field. Then, in your reports, just query the main house table, for any record that isn't completed, and then query the third table I mentioned for the status of all steps.

  4. #4
    New Lounger
    Join Date
    May 2003
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Please help!my job is depending on this (Access 2002)

    thanks you guys for the replying
    I am studying hard all these you are saying in your replies
    PS sorry if my database causes activex problems i only wih i knew what to do in order to make it flawless(probably because i use access xp GR)

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

    Re: Please help!my job is depending on this (Access 2002)

    It appears to me you are trying to construct a project management system in Access. Have you looked at using Microsoft Project instead? Agreed that your situation is quite complex and might ultimately drive Project over the edge, but it is designed to do reports that show you what progress has been made on a given project - in your case each house could be a project or a development might be a project, and each house in it a subproject. The advantage of Project is that it lets you create predecessor and successor relationships - ie you can't paint the interior of a house until it has been drywalled, and you don't want to drywall until the rough plumbing and electric has been done. There are more sophisticated planning tools like Primavera too - and people have integrated Project with Access when they wanted the reporting tools of Project, but had more data than it could reasonably handle.

    Just some food for thought - you may be too far down the Access path to consider switching, but you have a huge task ahead of you from what I can see.
    Wendell

  6. #6
    New Lounger
    Join Date
    May 2003
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Please help!my job is depending on this (Access 2002)

    microsoft project??????
    I will give it a look;personally i have never heard it before
    Thanks anyway and i think as you said that this what i need although i have never heard it
    Thans maybe i am lucky

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Please help!my job is depending on this (Access 2002)

    Access XP has nothing to do with it. I opened the database in Access XP, and I work in that version daily. Your database has a different problem.
    Charlotte

  8. #8
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Please help!my job is depending on this (Access 2002)

    MasterRipper

    I don't think I can, or many others can solve your issue in such a short amount of time.
    A couple of things you need to be asking yourself.

    1.What is the job description you have been employed.
    2.Are you capable of this kind of task.

    A good idea would be to sit down with your boss and ask him these questions.
    If your boss wants a database to control these scheduling problems, then he has to be a little more sympathetic to the overall task.
    Firstly I would try and put all your idea's on paper and see where you're coming from.

    Detail all your requirements and start from scratch.
    You need to , as said before , do some studying before taking on the task.
    I'm sure, with working with your boss, if he can see it working on paper, you can then start a couple of idea's working with Access from that point.
    Your boss will only be impressed if he can see progress.
    Keep things simple to start with, and try some of the wizards already available.
    You'll learn quite a lot from these, and they'll help you along until you sharpen your skills.

    I'm sorry if advice is the only thing that can be given, but listen do.

    We wish you all the best in your forth coming task.

  9. #9
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Please help!my job is depending on this (Access 2002)

    Sorry for the late message, but Microsoft Project, a scheduling tool, can assist you in tracking your projects quite easily. You can set each house as a project as suggested by Wendell and then consolidate them or add all of the houses in one project. Your tasks such as pour slab, framing, electrical, plumbing, roofing..... can all be set up as tasks to be done. There is no limit on the tasks you can add (well there is, but you would never reach the limit). You would then track the project by inserting a percent complete and could run reports to get a review of work to be done, work that is late, completed, etc. This may not be as "pretty" as the checkboxes in access, but will get the job done.

    Just my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>

    Good Luck.
    Regards,

    Gary
    (It's been a while!)

  10. #10
    New Lounger
    Join Date
    May 2003
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Please help!my job is depending on this (Access 2002)

    thanks everyone for the replies.i will have a look at microsoft project if it can put up the tasks iwant it to do

Posting Permissions

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