Please help my job depends on this (1 Viewer)

[masteripper]

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
I have attached a sample for anyone is interested

 

[bodvoc]

You won't like this

Wotcha

I had a similar problem a while back...

First up, split your table down... i.e. A Table for address, a Table for work. Link both tables together. You may even want to split it further by having outside work, inside work etc... What ever Link them all.

Then write your query too include all the tables, use a refresh or requery in the script and Bobs yer uncle...!

I found that Access doesn't like Table that are to big.. My prob was with a medical record db and dealing with 250k records a day.. the above fixed it for me.. Alot of work.. I know.. but it is about design...

 

[masteripper]

thanks a lot for the reply
Please download my sample database to see if it is similar to yours maybe you can help me

 

[bodvoc]

Hi again

Yep, did down load it, and that is the way to go.. but I had problems due to missing activex code....

On yours, create a button via the wizard and get it to do a requery of your data or a refresh.. then copy the code from the on click event and paste it into where you call your print job.. i.e. the print button on screen... thats the trick I used with mine.. probably not the right way to do it, but it works... it may help with your project... If I get time tonight, I will try to knock you up a db that should do it.. but be brave, make a back up of your db and have a go...

Hope the boss doesn't sack you...

 

[raskew]

Wasn’t able to open your example (only have Access 97 currently
loaded—you’re liable to get more responses if you save an example
in Access 97 since more responders will be able to view it).

Even without seeing it I’d have to venture a guess that
database design is your worst enemy.

The statements
1) “Every record has about 40 checkboxes
about works each house is needed...” –and-
2) “I have a form based on a rather huge table with about
100 fields probably they will get more on the way...

are pretty good indicators you’re using a relational database
as you would an Excel spreadsheet—‘going wide’
(i.e., adding new fields each time a new service is needed)
rather than ‘going deep’ (adding a new record.)

Consider this scenario:

tblHomes (details about the property):
HomeID – autonumber (PK)
Address – Text
...other details describing the home

tblSchedule (details about construction tasks):
ScheduleID – autonumber (PK)
HomeID – long
ServiceID – long
ProjStartDate – date/time (projected start date)
ProjEndDate – date/time (projected end date)
EndDate – date/time (actual completion date)
Remarks – text

Now set a one-to-many relationship between these two
tables based on HomeID.

Add a lookup table, tblServices:
ServiceID – autonumber (PK) |
Service – text

it would look something like this:
ServiceID Service
1 | Site Prep
2 | Foundation
3 | Framing
4 | Insulation

etc.

With those three tables, the form wizard will build a
form/sub-form that will allow you to track each step of the process.

Were it me, I'd steer-clear of the check-box approach and
go with actual dates since check-boxes tell you absolutely
nothing about the timeliness of the process. For example,
say the boss now wants to evaluate the timeliness of
various subcontractors. With check-boxes we haven’t a clue,
only that the job got done at sometime (it could have been 2
weeks ahead of schedule or 3 months after).

We can use the tblSchedule | Enddate to determine if a task
has been completed (if it’s blank/null, the task is still open).

I’ve attached a little (Access 97) zip file to demonstrate how
you might restructure your design to achieve a more
normalized approach.

 

[Rich]

Just curious, exactly how many forum's "experts/apprentices" are building this application for you?

 

[masteripper]

Thanks for the replies

Thanks everybody for the replies
I uploaded a 97 version of my database
PS i must confess i have posted my problem to several forums but i am really desperate.i am not kidding i am joking i am not going to sell my database i just want to show something to my boss in order to keep my job ; it is so hard to be unemployed

 

[masteripper]

One note to raskew

hi my friend
Please download the 97 version of the database
There is a pretty good point of using checkboxes in order to have a swift view of all the works
The use of dates is used in the full database

 

[mark curtis]

masteripper,

r u employed as an access developer?

if u r not y does your boss say he will sack u?

 

[masteripper]

i am not an access developer i am a network technician
Unfotunately my boss gave me this peculiar job of following the progress of the company's works and i just want to do something in order to 1st. get the job done at its best 2nd. impress him a bit
Unfortunately there are not too many jobs in the area i live and the company i work is one of the best so i want to do whatever is needed to stick.I have big problems finding a suitable work
Anyway

 

[Cosmos75]

I wonder what happened?

 

[mark curtis]

looks like he got the sack???

 

[Friday]

possibly he didn't stick?

 

[Vassago]

Someone could email him and ask. But I'd be afraid of understanding the reply.

 

[mark curtis]

Stop it!! my sides are splitting!!!!

 

[masteripper]

unfortunately i got fired but don't worry

unfortunately i got fired but don't worry it has nothing to do with this program my boss was "out of this world".
Anyway i would like to thanks everyone and once more i am looking for job

 

[mission2java_78]

Re: unfortunately i got fired but don't worry

unfortunately i got fired but don't worry it has nothing to do with this program my boss was "out of this world".
Anyway i would like to thanks everyone and once more i am looking for job

I could not help but laugh when I saw this thread...
No offense to you losing your job..but this thread was hillarious..here I am reading the first post as to what you need..and reading everyone's reply..especially the end saying "what happened to this guy". Then I click the link to go to page #2 and all of a sudden i see "Guys I got fired"..I couldnt stop laffing...not because you got fired just the way you put it.
Anyhow..good luck with finding a new job. Try posting your resume on monster.com

Later

 

[Uncle Gizmo]

Try posting your resume on monster.com

Try and avoid jobs that require database though.

 

[nelson]

hmmm didn't you do any Access programming when you are @ Univ ? ....hehehe goodluck on looking for a job !!!