Need guidance (1 Viewer)

[KevlarKev]

Hi all,

I am looking for some advice if possible on a project management database.

I am currently speccing this out at the moment and getting some tables built. My big issue comes when I get to billing and invoicing.

To give you a headsup, the company I am building this for does well site analysis on oil rigs. When they do a project, the project can (and often does) have more than one well. When it comes to invoicing and billing, i need to have something in place when the invoice covers all of the wells in the project.

I have already created a well table and linked that to my projects table. However, do any of you have any idea how I could amalagamate any wells selected into one billing/invoice?

Thanks!

 

[plog]

Sounds pretty straight forward--you should bill/invoice a project. From there you should pull in all wells that fall under it.

If you don't think that will work, explain why and post some data.

 

[KevlarKev]

Hi Plog,

I will break this down in steps so I hope you can see my prediciment better.

1. The company has a budget per project which covers each individual area such as sample analysis, sample preperation, collection and human expenses such as food etc. They have a realtively good idea at outset of each of these areas and how many samples etc they are going to take.

2. The company wants to be able to produce a monthly invoice for the client showing what has been achieved that month e.g. 20/100 samples taken.

3. The company wants a project manager to be able to generate this invoice every month and update the database with the amount of samples etc.

4. All of the invoices / updaets need to subtract from the totals initially stated.

5. Each of the areas billed should be broken down into individual wells so for eg. if a project had five wells and ten samples were done in one month, the invoice / update needs to show how many were done on each well to add up to 10.

Help!

 

[plog]

Dive in somewhere and then post what you have. Or search the internet for some samples and possibly derive your database from those samples.

 

[KevlarKev]

Thanks plog. I got stuck in as suggested and started mapping it out. Now run into a relationship issue I could do with your assistance on again if you would be so kind.

I have the project table which stores main project info and has a project number as the pk.

I have two staff tables, one for scientists and one for technicians. Scientists have scientistid as the pk and technician has technicianid as the pk.

I want to be able to set up the relationship between these the three tables.

One project can have many scientists and technicians working on it and many technicians and scientists can have many projects they are assigned to.

I realise that I need some link tables which I think should be scientists assigned to project's and technicians assigned to project's but I'm drawing a blank b because isn't the that still a many to many relationship?

Suggestions would be very much appreciated!!!

 

[jdraw]

I suggest you create a data model. It will help focus your issues.
see
http:/www.databaseanswers.org for a variety of models -- there probably is not 1 single model to do what you need, but you may be able to take pieces/concepts from a few to help build your own.

also, you may find the procedure laid out at this link will help you build the Model/Entity Relationship Diagram.
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

 

[plog]

I have two staff tables,

You probably only need 1 staff table, then a field within that table to designate the role of the person (scientist, technician, etc). Do your staff tables have the same layout?

I think should be scientists assigned to project's and technicians assigned to project's but I'm drawing a blank b because isn't the that still a many to many relationship?

Yes it is, and what's wrong with that? Its called a Junction table (http://en.wikipedia.org/wiki/Junction_table). Within that table you store IDs from two tables (ProjectID and StaffID) creating a link between multiple projects and multiple people where each record defines a person's role on a particular project.

 

[jdraw]

See https://www.youtube.com/watch?v=7XstSSyG8fw

for a video on Many to Many/Junction table