Civil Jobs tracking database in 2003

[MoHntr]

Hello everyone,

I have been trying to figure this out for a couple weeks. I have finally decided to turn to someone who knows.
I want this database to do the following. Track jobs by a unique job number (pk). Within the jobs I want to be able to show several pieces of equipment on the site. Same with employees, and be able to move the equipment and employees to a different job, or better to keep a record of where they have been. Clients for the jobs seem easy enough since there is only one client per job number.
Not sure if I have enough knowlege to even explain this correctly. I seem to be able to do everything except put more than one employee or piece of equipment on a single job. I have deleted alot of stuff I had in the database to try and make it easier to look at for anyone willing to help. I'm afraid I may have deleted too much.

Thanks for any help you can offer.

File attached - I hope.

MoHntr

 

[MoHntr]

Trying to upload the database. Not sure why it is over 4 meg. Only have a few records in it. Still working on uploading.... UPLOADED! I had to start over. I just recreated the tables and auto forms. I don't know why, but my original db is over 4 meg.... has no more info in it than the one I just created and it is only 500KB.

 

[MoHntr]

Hello again,

I was wondering if I asked this in the wrong forum, or if everyone just thinks I am beyond help?

Maybe if someone could just help me with one part of my problem I could figure out the rest. How can I show more than one person on a job? I would like to do it with a subform I think.

Thanks,

MoHntr

 

[georgedwilkinson]

I answered your question on your other thread. You really don't need to post on the same topic on more than one thread.

A possible reason why you haven't had an answer is that your description is a little verbose and doesn't really indicate what you're having problems with.

The thing you are describing is a many to many relationship. Each job can have many employees and each employee can be involved in many jobs. Same with equipment, and so forth. You implement a many to many relationship with a table in between the 2 tables holding the base data, called a junction or associative table.

HTH.

 

[MoHntr]

Thank you for the reply in both forums. Sorry for the double question.

MoHntr

 

[Lightwave]

Hi there MoHntr not sure whether this has been answered or not and not seen the other thread so forgive me if I'm repeating points made.

Breaking it down to a very simple level I would probably have five main tables

Table 1 - Job
Table 2 - Equipment
Table 3 - Personnell
Table 4 - Equipment on Job
Table 5 - Personnell on Job

I would envisage Table - Job having fields like
ID - autonumber unique
Sitename
Town
Postcode (I'm uk based)
Client
Grid ref - (can be useful if you want to link up to a GIS / Google Earth Later)

Table 2 and 3 would be similar except Equipment and Personnell centric.

Table 4 and 5 look like tables you haven't previously identified.
Table 4 for instance would be
IDUnique
IDLink (this will tie in to the job number)
IDEquipment (this will be referenced to the equipment table)
Start date on site
Finish date on site

Table5PersonnellonJob
IDUnique
IDLink (again this will tie into the job number
IDPersonnell ( this will be referenced to the personnell table)
Start date on site
Finish date on site

The above structure should mean that you only ever have one record of each job person and equipment. But equipment can re-appear on the different sites as jobs progress. By going back to a particular job you should be able to see what equipment was on what job when. Likewise with personnell . This has the advantage that if set up correctly when you change details of the personnell this could feed through to the jobs. Eg - if a person's contact number changes - changing it in the individuals record may allow it to update automatically on the record (depending on how you implement the structure).

Access is excellent for fixed asset registers. I use them a lot at work for project management of planning applications. I have a similar structure and the sites are linked through to Google Earth. Instead of equipment I have planning applications so an individual site can have many planning applications. But there are main players for each site which are equivalent to your personnell. Those main players (my term) could be doing anything from plumbing and construction to surveying environmental impact assessment.

Good project to get your teeth into

 

[MoHntr]

Wow, thank you lightwave. I am going to study your post and try to get this thing figured out. It is definately more complex than I expected. Great post thanks again.

MoHntr

 

[Lightwave]

MoH

Here's a really simple demonstration

Unzip and then open F001 form

Access 2003

 

[MoHntr]

Lightwave,

Thank you so much. That is exactly what I needed to see. I know you said it is basic, but I suppose I have been trying to make it much more difficult than needed.
I will disect your sample to learn from it, then build from it.


MoHntr

 

[MoHntr]

Lightwave,

I was wondering just how you obtain the geographical information for your program? Do you enter the coordinates manually? I have hidden the links in your sample database, but they have peaked my interest. Thank you.

MoHntr

 

[Lightwave]

Ah yes got a bit carried away there.

Yes I enter them manually.

I think you will struggle to adapt your database to using the code listed though because its getting towards the harder end of VB programming and there is quite a lot to adapt to make it work for an individual database.

I've kind of forgotten how to do it myself! But you could try adapting the code in the below thread. Might be one to think about for the future.

http://www.access-programmers.co.uk/forums/showthread.php?t=151797

 

[MoHntr]

Yes, it may be a bit more than I need at the time. Once again thank you for your help and assistance. I have imported data and modified your sample database you had shown me. May I call it my own with many implimented changes? I really should have asked before now. When I imported my tables and re created the relationships I linked one of them incorrectly. Mr. B. pointed out my error in the "General" forums.
It has a very nice structure for me to learn from.

Sincerely,

MoHntr

 

[vbaInet]

I have imported data and modified your sample database you had shown me. May I call it my own with many implimented changes? I really should have asked before now.

Copywright. Hehe!

 

[Lightwave]

The minute you understand the concept of junctions and associative tables then it will effectively be your own because chances are you could make one up for anyone else.

It is probably a good thing that you made a mistake as it will have helped your understanding. Another important thing to understand is that the links are to Unique ID references. This means that subsequent to linking records to jobs the details of the individual items personnel and vehicles can be altered how you like and these will automatically feed through to the forms. Probably more important with personnel because things like a persons numbers who they work for tend to change more frequently than I suspect the attributes of machinery

Another nice little technique is the use of combo boxes referenced to the IDs which show the names of the individuals although storing the ID field. It took me a while to learn that one.

Best of luck with it.