Ideas on courier databases

[Switchwork]

Ideas on courier databases.?? HELP ME!!!

Hello basically I would like some of you to give me ideas on how to design a database for a courier company. I'd like to know how you people that have experience with these things would go about designing a database that is not like any sample database out there. See I have looked for sample databases and they all have orders and things and that is not how you need one for a courier company.

Basically you need the normal regular tables like Customers and Employees but instead of Orders you have Job details. The problem I have is these are the fields I have in my JOB DETAILS table.

JobNo (PK) auto
REFNo (num)
CustomerID (FK)
CompanyName
EmpNo
JobDate
PickUp
Destination
Weight
NOP (Number Of Pallets)
EmpLastName
DriversEarnings
JobCost
ExtraCosts
TotalCost
POD (Proof Of Delivery)
TimePODReceived
InvoiceSent
InvoicePaid
Notes.

Basically I was wondering whether to split this table into Jobs with all the relevant details like Pickup etc and then have JobDetails that has POD, Weight, Invoice stuff in??

Then when It comes to the form putting the Jobdetails Fields into a Tab subform type thing?? Does anyone think that this will work?

I currently have all those fields in one table and displayed on one form so thats why I would like some advice and information and Tips on how some of you would go about designing a database for a courier company. I am still also trying to work out which way to keep track of my invoicing as at the moment I am doing it on paper and TRUST ME IT TAKES A HELL OF A LONG TIME""

So if anyone can help me and give me some points on how some of you might do it I would love you all forever.

PLEASE PLEASE PLEASE PLEASE HEKP ME!!!

RESPECT

 

[Mile-O]

Re: Ideas on courier databases.?? HELP ME!!!

CustomerID (FK)
CompanyName

TotalCost

Would there be any need for the CompanyName if the CustomerID is already included.

And why store the total costs when you have all the other costs?

TRUST ME IT TAKES A HELL OF A LONG TIME""

PLEASE PLEASE PLEASE PLEASE HEKP ME!!!

RESPECT

And what's with all these capitals and punctuation marks?

 

[Switchwork]

Yes I Know!!!!

Hello and thanx for answering me on this one!!

That is one of the questions I wanted answering.
Do I have to have both the CustomerID and CompanyName?
So I should just have one or the other?

And the TotalCost field I need to generate the invoice reports. See the JobCost and the ExtraCosts add together to make the TotalCost. And in the Invoice Report the TotalCost is used in a calculation to calculate the VAT and get the GRAND TOTAL.
Thats why I need to have the TOTAL COST as well as all the other costs. Also I need to sometimes see how much a job was including Extra costs when a customer queries a job!!

Anyway Should I use two seperate tables for the job details then?? What do you think??
There is a lot of fields in this table. Isn't there?

Anyway any advice would be of benefit to my cause
And about the punctuation I just got carried away with the Help Me business!!

Thanx for pointing the OTT out!

 

[Mile-O]

Re: Yes I Know!!!!

Do I have to have both the CustomerID and CompanyName?

I take it that Companies are, in fact, the customers in question.

If you have a Customers table:

i.e.

CustomerID
CustomerName
CustomerPhone
CustomerEmail
etc, etc, etc


...then you only need CustomerID in your jobs table

 

[Mile-O]

Re: Yes I Know!!!!

And the TotalCost field I need to generate the invoice reports. See the JobCost and the ExtraCosts add together to make the TotalCost. And in the Invoice Report the TotalCost is used in a calculation to calculate the VAT and get the GRAND TOTAL.
Thats why I need to have the TOTAL COST as well as all the other costs. Also I need to sometimes see how much a job was including Extra costs when a customer queries a job!!

You've identified a need for a new table: tblCosts

A one-to-many as one job can have many costs.

And maybe a further detailing CostTypes.