Oil well allocation

[sean88]

Dear all,

I am trying to use Access to build a database that handles equipment for several oil wells but I am not sure the best way to designate the equipment.

Essentially I would like to make a table for each piece of equipment with fields as below:

Flowmeter table:

flowmeter type; serial number; date ordered; location;

Pressure Gauge table:

gauge type; serial number; date ordered; location;

etc.

These pieces of equipment then need to be assigned to a well which will have a name eg. Well 1; Well 2; Well 3; etc.
I was thinking there would be one table which would control the kit assigned via an input form.

The wells will often have several "pressure gauges" or "flowmeters" of different types or serial numbers assigned to the same well.

This information will all need to be queried in order to view all of the equipment assigned to a certain well as well as a query which can search for a serial number and show which well it is assigned to.



I have made a few attempts at this (not particularly Access savvy) and have had problems in how best to lay everything out.

Any advice would be greatly appreciated.

Regards,

Sean

[orange]

Do yourself a favor. Work through this tutorial to get a better handle on table and database design concepts.
Then apply what you have experienced and learned to your own situation. It will be the best use of an hour or so
before you start your project.

Then write a 5-6 line description of "WHAT" this proposed database is about in plain English. Look at the tutorial for an example.
Then, come back to the forum with any issues.
Also, do not use names with embedded spaces -- it will led to numerous syntax issues. Use only alphanumeric characters and "_" (underscore).
Good luck.

[sean88]

Very helpful tutorial!!

Thank you

[sean88]

Hi Orange,

I have found your tutorials online to be of great use and I have been able to build the foundations of my database quite successfully so far.

I do have a bit of a stumbling block - perhaps you may be able to offer some assistance?

The aim of the database is to control the inventory of many bits of equipment and to allow them to be allocated for certain jobs (ie to allocate a job number to each piece of equipment).

Each item has a unique serial number which identifies it; I am trying to figure out how to show in access that one piece of equipment has been attached to another piece of equipment which then may be attached to another bit of equipment.

Hopefully my example illustrates this, please imagine a car where a tyre is attached to a wheel which is then attached to the axle.

Job number to build wheel arrangement: 1234

Item 1. Tyre - Serial Number: 1111
Item 2. Wheel - Serial Number: 2222
Item 3. Axle - Serial Number: 3333

Sorry if this analogy doesn't help very much. Any advice is much appreciated!!

Regards,

Sean

[orange]

Hmmm, sounds like you have it backwards.

You issue in your words is

many bits of equipment and to allow them to be allocated for certain jobs

Your approach

trying to figure out how to show in access that one piece of equipment has been attached to another piece of equipment which then may be attached to another bit of equipment

I think allocating equip to Job is the approach to take

1 Job may have 0 or many pieces of Equip
Each piece of Equip has a unique identifier (so same piece of Equip can not be on 2 or more jobs at any time)

Jobs--->Equip

Job 300 --->Equip  12345
Job 300---->Equip  23123

Are you concerned about Equip at Jobs, or Equip related to Equip?

[sean88]

This is exactly right:

1 Job may have 0 or many pieces of Equip
Each piece of Equip has a unique identifier (so same piece of Equip can not be on 2 or more jobs at any time)

Jobs--->Equip

Job 300 --->Equip  12345
Job 300---->Equip  23123

For one job, there will be several bits of equipment - each identified by their serial number. I think i understand this relationship.

My problem is that I would like to be able to show combinations of certain parts.

i.e.

Job 300 ---> Part number 1234: serial 5555 (an item in the database)
Job 300 ---> Part number 7891: serial 6767 (another item in the database)
Job 300 ---> Part number 1234: serial 6666 (the same component as serial 5555 but a unique item identified by its serial number)

I am happy with this stage and can construct a query to search "all parts associated with Job 300" or "search all part numbers 1234"
However, i would like to find a way of showing that certain parts are combined so:

[Part number 7891: serial 6767] is taken to the workshop and attached to [Part number 1234: serial 6666] - both parts still retain their serial numbers and there is no new serial number created to signify a combination of parts.

If i run a query for "serial 6767" is there a way of showing that this part has now been attached to "serial 6666" and vice versa?

Please let me know if I am not being very clear!

Thank you in advance,

Sean

[orange]

Not unless you tell or can tell from some info in the database.
You could have a configurations file that indicates what pieces fit together. Then, based on part numbers and part types, and the configurations file, you could make a strong educated guess of that particular "attached set uo".