Port Database

[Mr Wheeze]

Hello,

I have a lotus datbase at work and there are far to many conflicts with XP. I am trying to make an access database but keep getting as far as the calculations and then give up.

I have a ships data base. I am trying to calculate totals for each vessels cargo.
Some vessels have more than one cargo. eg A Shell Oils vessel may have Diesel, Unleaded or Leaded Fuel on the one trip.

I have been able to get these totals to work b4 but I forget how I did it. If I calculate Cargo1+Cargo2 + Cargo 3 I get 100200300

The other problem that I have is that the totals do not appear in the corresponding table and so I am not able to use totals in query or report.

eg Form Cargo Cargo Tonnage 100
Cargo 1 Cargo Tonnage 1 200
Cargo 2 Cargo Tonnage 2 300
Cargo Total 100200300
Am I going about this form in the correct way or am I totally going against the grain of access?

Any help would be appreciated.

Thanks

Mr Wheeze

 

[gecko_1]

Are the data type of the numeric fields stored at Text or Number. It looks like they are stored as text so it is joining the strings.

If you are doing the adding in VB, you could try cInt(Cargo1) + cInt(Cargo2) + ..

Todd

 

[Pat Hartman]

Cargo1, cargo2, etc are not defined as numeric. They are defined as text and so the + is acting as a concatenation operator since text fields can't be added together. Change the data type to long or double depending on whether the numeric value is an integer or not.

You have also created a spreadsheet and called it a table. You have a 1-to-many relationship between ship and cargo. You have arbitrarily limited the number of different cargos to 3. In the real world, that is something that could change with the next shipment and you would be forced to change the database design to handle the problem. However, if you normalize the table, whether the ship carries 1 or 1000 different types of cargo is immaterial. Your application will handle it with ease.

Take a look at the Northwinds database (install from your Office CD if you can't find it on your hard drive or do a Google search for a download). This database is a good example of how various relationships work.

 

[Mr Wheeze]

Thank you for you're words of wisdom. I have tried Todd's idea and it works for calculating the form total, although the total rounds up and the table total remains blank. ThankYou, I shall put this into practice once I figure out what i am doing.

Pat, I will have to go back to basics and learn about normalisation. This seems the sensible route to follow.

It is true that a vessel can have more than three commodities and can have a completely different cargo from its previous cargo. This is common in the Lotus database that I touched upon earlier.

If only Microsoft could create normalisation for dummies.

Yeah, I have a vessel table with the details of the vessel which is linked to the shipping database

VesselNo GRT REG LRN LOA
Arduity 1535 Derry 1234566 99
Ben 1235 Derry 1235466 101
Bill 1234 Derry 1235678 78
Bob 1233 De4ry 1234567 65

and a ship table which gives the ship name, plus agent and berth and cargo breakdown.

Should I keep the cargo in a seperate table?

Thanks

Mark S

 

[Pat Hartman]

I will describe the entities you are dealing with.

You have a ship which takes a trip and on this trip, carries 1 or more types of cargo. This is VERY similar in concept to the structure you will see in Northwind for a Customer who places an Order for 1 or more items.