Analyze multiple fields of actual vs. estimated expenses

[klallen810]

Hi,

I am trying to find the best way to compare estimated expenses to actual expenses in Access. I am working with vessel and port call information, so each port call has a series of costs associated with it. I have two tables, each with multiple fields (around 100). Each of the fields contains a cost type that goes to a particular cost code. Each row of the table is associated with one particular port call. The call is estimated right after it happens, but the invoices are not all recieved for up to three months. Once all of the invoices for this port call have been recieved, we enter the actual expenses for the call. I am trying to find the best way to analyze the costs through finding the variances for one particular port call for each code, and also for multiple port calls to see which cost item has the largest/smallest variance for further investigation into how to estimate it more accurlately. I am thinking that I may have to move into Excel to do this, but would like to stay in Access if possible. Any ideas??

 

[Guus2005]

You have two tables with approx. 100 fields? You may have a normalisation problem.
Typically this happens when data is read into an Access table. After which you need to use queries to build your relational database.

There is not a problem that you can't solve with Access.

There is a simple query that you can run, when your database is properly normalised, to get the result you want:

select Id, EstCost - Nz(ActCost,0) as DiffCost from Table1

This query shows the difference between the estimated cost and the actual cost.
When the actual cost is unknown and the field is still NULL, 0 is assumed.

HTH

 

[klallen810]

So for the EstCost portion, would I need to name every field that I have an estimated cost for? This is what I am trying to avoid since there are so many fields. Each port call has its own unique Vessel ID. If I could do something like what you said, but without having to name each field that would be great. I have attached a database with two tables, tActuals and tEstimated. I haven't recieved any actual invoices yet, so i just copied some of the estimated fields and changed some numbers for something to go with. I am trying to think of a way to better normalize the tables, but I can't think of anything that would be too helpful, but my brain might just be shot. Hopefully the table data helps! Thanks!!

 

[Guus2005]

If your data is imported from an excel spreadsheet (or any other datasource) and this is a onetime thing then you can create a solution based on the tables you have in your database.
If your data is part of a system which you will use in the future then you need to normalise your tables!
If you don't, creating a simple query is hard, creating a difficult query will be impossible, slow, unreadable and very hard to debug.

In the database provided you need to determine the primary key for your tables. It is not the VesselID. You mentioned that that changes in every port. Important is that you choose your own PK for one particular vessel. To select that vessel you need to know the name and the correct spelling of that name. e.g. MSC TOMOKO has four different VesselID's.
This is all part of the normalisation proces. I am assuming that you will end up with the following tables: Vessel, Voyage, Port, Country, Load.

Vessel hold everything (name, Id, MaxLoad, ...) for a certain vessel
Voyage holds an Id for the Vessel, a port, departure and arrival date.
Port holds all available ports
Country holds all countries, Id, abbreviation and fullnames
Load holds vessel id, Voyage Id and what it is carrying and how much

I'll leave you with that.

There are a lot of weights stored in your tables. I can't determine which are estimated and which are not and which you are interested in.

You need to connect the two tables together using the unique id's

SELECT tActuals.TOWAGE, tEstimated.TOWAGE, [tActuals].[TOWAGE]-[tEstimated].[TOWAGE] AS DiffTowage
FROM tActuals INNER JOIN tEstimated ON (tActuals.VOYAGE = tEstimated.VOYAGE) AND (tActuals.[VESSEL ID] = tEstimated.[VESSEL ID]);

In the above query id have connected the two tables using an inner join. Those records which do not have an actual records are not shown due to the INNER JOIN.
I have joined the tables to their unique keys which IMHO are VOYAGE and VESSELID. It can be something totally different but i leave that up to you.

Am i making any sense?

HTH

 

[klallen810]

Yes, that definately helps. I will work on normalizing and implementing the query as stated above and if I have any further questions I will let you know. Thank you!!