Related Events

[Guest]

I am fairly new to ACCESS but here goes. I have a table with the field names
DockCode, BargeName, ProductCode, BargeTicket

BargeTicket is my primary key because it follows the loading and unloading
event, it is also on several other tables that follow the barges from their
loading, unloading, transfer, delays and other.

My question is this from time to time we will load a barge and something
will happen and the products will be unloaded onto a different barge. We have
a field for Load, Unload, Transfer (these are YES and NO fields) that are
made so we can see what is happening on this BargeTicket every event has a
BargeTicket from loading at the Dock to unloading at our Dock

With every event having a different BargeTicket how can I relate the
unloading of one Barge to the other Barge so that we can track the product
movement so that when one ticket is called up all three (first loading,
pumping to the other barge and the other barge ticket) tickets will appear or
be referenced to each other.

Would adding a couple of field to put the previous ticket and the other
BargeTicket in and create a relationship work, would a query or report be
better. I think I will have to add the extra fields reguardless of whatelse I
do.

What started out as a simple data base is turning into a monster becasue now
everyone (managers) is getting ideas of how we can better us it to track
events but they don't have the time to help set it up (knowledge or
willingness to learn) but need the results so it is being passed down to me
because our upper company managment is pushing record retention so I thought
instead of the EXCEL spreadsheets why not go with ACCESS and now I am stuck
with setting this up and doing my job. But I like the challenge and as the
old chief said to Clint Eastwood "endeavor to purservere" so I will with the
help of others.

THANK YOU even if there is no answer.

 

[Allen Browne]

Instead of 3 yes/no fields for Load, Unload, and Transfer, can I suggest
using one Number field, where you enter one of these numbers:
1 Load
2 Transfer into
-1 Unload
-2 Transfer from

The positive numbers indicate cargo going into the vessel, and the negative
numbers indicate cargo being removed. If you wish to then link the transfers
to each other, you could add another Number field where you store the value
of the matching record's primary key, but only for the transfer.

 

[Guest]

Allen I like the idea of a number for the activity so I removed the previous
fields and replaced them with a "Activity" field as you suggested but I am
using 1, 2, 3, 4 just for the ease of entry, I also add a field for the
"LinkingTicket" so we can enter the other barges Ticket Number.

Now how do I get these two together in a query

 

[Guest]

In my LinkingTicket field criteria of my query I put NOT NULL and I am able
to get all of the barges that have a LinkingTicket field with an entry in it
but how do i get them to match up because they are listed down the page not
in an order so we are having to match BargeTicket with the LinkingTicket of
the other barge to get the two tickets matched.

 

[Allen Browne]

I'm not exactly sure what you are aiming for (a report?), but you could add
a 2nd copy of the same table to this query. Now drag the the LinkingTicket
of one table (in the upper pane of query design), and drop it onto the
BargeTicket of the other.

Where there is a transfer, this would allow you to display the details of
where it came from and where it went to, all in one record.

Your numers (1-4) are fine. The numbers I suggested were positive for
produce added to the barge, and negative for produce removed from it. By
using the sign of the field, you can then determine the difference (if you
ever need to.)

 

[Guest]

The barge product is tracked from the time it enters our tank farm until it
leaves. When we have a product movement to or from a barge we have to know
where it came from and where it went to, hours it took for the transfer (we
are charged for the movement in hours it took to move the product and in turn
we charge the customer by the same total of hours), we have to count how many
barges were handled @ our dock, the number of products both total for all and
a total of individual (we had over 1500 barges with 11 different products we
have to know how many of ech was handled) as well as track the delays we have
in the transfers because they are charged to the party that caused the delay.

All of these movements are sent to at least 8 different people who either
charge customer, know where the product is (what barge, tank), order
movements, track what we are being charged and others who I don't what they
do with the info so I was thinking reports and queries would probably be
easier at seeing the over all picture of what is happening.

We use EXCEL to do all this and they were becoming cumbersome because of the
sheer amount of them we were accumilating, so hopfully as I work getting my
database set up we will be able to faze out the spreadsheet and I believe
that we can import them into the database if we need to.

Thanks for all the help because I know somewhat about EXCEL but I'm a newbe
to ACCESS.

 

[Allen Browne]

This is probably a bigger question than can be handled in the newsgroups. It
consists of how to build a relational design for your data + how to build
the interface to manage it, so really it's about building an entire app.

There is not really one answer: you know your data, so we can only offer
suggstions based on the info you provide. Ultimately you will have to build
something that covers *all* your requirements - well beyond what we can do
here.

The relational structure needs to take into account that cargo is loaded
from a vessel/dock into another vessel/dock. That includes the possibility
of vessel-to-vessel transfers. You will therefore need a structure where
vessels and docks are in the same table. The Transaction table will then
have fields like this:
TransactionID primary key
SourceID the vessel or dock the goods came from
TargetID the vessel or dock the goods moved to
CargoTypeID what was moved
Quantity how much was moved
UnitID what the quantity is measured in.
StartDateTime when the lading/unlading began
Duration how long the lading/unlading took

This transaction table will have 2 relations to the Vessel/Dock table.
There may be further complexities such as:
- managing holds within a vessel
- managing storage areas within a dock
- managing docks within a port
- multiple lading/unlading shifts within a transaction
- managing lost goods (containers lost at sea, evaporated fluid, etc.)
- managing journeys and waypoints, manifests, dangerous goods, customs, and
compliance with all the legislation of all countries along the routes.

But the point is that this table answers your question about how to tie one
to the other.