G
Gary Morgan (ConcordiaFreight)
Hi, i am currently writing an update for my database. I need to get the DB to
automatically calculate some figures but the fields which need to be in the
formula are in two different tables.
This is wot the update in the database is for: We have currently started
doing storage as a side line to our normal business. Now we could have a 100
pallets come into our warehouse which is classed as one job, but each pallet
has to hav its info stored so that when the customer phones up and gives us a
reference number for a pallet we can do a simple search and straight away
find where the pallet is stored within our warehouse.
I have created a new table to store the pallet data in, which runs in line
with the current table. Heres wot each of the tables do:
1. daybook table (stores the main job data)
2. Pallet table (stores pallet details which are linked to a job number
which is stored in the daybook table)
Heres the fields which are used within the tables.
daybook table
our ref (primary key)
rate per pallet
total days storage
charge out
Pallet Table
pallet ref
racking ID
Date released
Days Stored
Example of Data Stored:
Job Data
Our ref:
Customer Ref: (Customers ref number for the number of pallets we are storing)
Date: (Date the pallets were received in)
Contact: (Customer Contact)
Nop: (Number Of Pallets, This is the amount of pallets stored)
Weight:
rate per pallet:
Total days Stored:
Charge Out:[days stored]*[rate per pallet]
Pallet data (There will be multiple's of these to the Job Data)
Our ref: (this ref will have multiple pallets stored within the job)
pallet ref: (the individual pallet which is stored)
racking ID: (location of pallet)
date released:
Days Stored:
Formulas needed
the 1st part would be to find out the number of days in which the 1 pallet
has been stored for.
so to give the "Days Stored" u would hav to minus "date released" from the
"date". But this has to be done over 2 tables.
if they where in the same table i could just use this calc in a txt box
[date released]-[date] but since they are dif tables i dont know how to get
the database to look for the details. so i would need it to:
look at "date released" in "pallet table" minus "date" in "daybook Table" =
days stored.
This would have to be done for every pallet which has been stored. Now we
could hav 100 pallets which is stored under 1 "Our Ref" since they had all
came into the warehouse at the same time. Therefore once the indiviual pallet
storage days has been calculated it would have to calculate the total days
storage for all pallets stored under the 1 "our ref"
I am not sure how this would be done, i think maybe a query with a filter so
that it asks the user wot "our ref" is needed to be calculated. That way then
the Db would know that all "Days Stored" for 1 "Our ref" needs to be added
together.
once the total number of days storage has been calculated then i can just
use the simple multiplication formula to calculate the Charge Out.
So it would look like this:
Job Data
Our ref: 1245
Customer Ref: bas3006
Date: 06/06/05
Contact: .......
Nop: 1
Weight: 62kg
rate per pallet: 1.60
Total days Stored: Formula need to go here
Charge Out:[Total days stored]*[rate per pallet]
Pallet data
Our ref: 1245
pallet ref: 123456
racking ID: 005a
date released: 07/06/05
Days Stored: formula needed here
sorry to bombard u with this
if u could help it would be much appreciated, i feel like i am banging my
head on a brick wall, i know wot is needed but i dont know how to do it. very
annoying.
thanks
Gary
automatically calculate some figures but the fields which need to be in the
formula are in two different tables.
This is wot the update in the database is for: We have currently started
doing storage as a side line to our normal business. Now we could have a 100
pallets come into our warehouse which is classed as one job, but each pallet
has to hav its info stored so that when the customer phones up and gives us a
reference number for a pallet we can do a simple search and straight away
find where the pallet is stored within our warehouse.
I have created a new table to store the pallet data in, which runs in line
with the current table. Heres wot each of the tables do:
1. daybook table (stores the main job data)
2. Pallet table (stores pallet details which are linked to a job number
which is stored in the daybook table)
Heres the fields which are used within the tables.
daybook table
our ref (primary key)
rate per pallet
total days storage
charge out
Pallet Table
pallet ref
racking ID
Date released
Days Stored
Example of Data Stored:
Job Data
Our ref:
Customer Ref: (Customers ref number for the number of pallets we are storing)
Date: (Date the pallets were received in)
Contact: (Customer Contact)
Nop: (Number Of Pallets, This is the amount of pallets stored)
Weight:
rate per pallet:
Total days Stored:
Charge Out:[days stored]*[rate per pallet]
Pallet data (There will be multiple's of these to the Job Data)
Our ref: (this ref will have multiple pallets stored within the job)
pallet ref: (the individual pallet which is stored)
racking ID: (location of pallet)
date released:
Days Stored:
Formulas needed
the 1st part would be to find out the number of days in which the 1 pallet
has been stored for.
so to give the "Days Stored" u would hav to minus "date released" from the
"date". But this has to be done over 2 tables.
if they where in the same table i could just use this calc in a txt box
[date released]-[date] but since they are dif tables i dont know how to get
the database to look for the details. so i would need it to:
look at "date released" in "pallet table" minus "date" in "daybook Table" =
days stored.
This would have to be done for every pallet which has been stored. Now we
could hav 100 pallets which is stored under 1 "Our Ref" since they had all
came into the warehouse at the same time. Therefore once the indiviual pallet
storage days has been calculated it would have to calculate the total days
storage for all pallets stored under the 1 "our ref"
I am not sure how this would be done, i think maybe a query with a filter so
that it asks the user wot "our ref" is needed to be calculated. That way then
the Db would know that all "Days Stored" for 1 "Our ref" needs to be added
together.
once the total number of days storage has been calculated then i can just
use the simple multiplication formula to calculate the Charge Out.
So it would look like this:
Job Data
Our ref: 1245
Customer Ref: bas3006
Date: 06/06/05
Contact: .......
Nop: 1
Weight: 62kg
rate per pallet: 1.60
Total days Stored: Formula need to go here
Charge Out:[Total days stored]*[rate per pallet]
Pallet data
Our ref: 1245
pallet ref: 123456
racking ID: 005a
date released: 07/06/05
Days Stored: formula needed here
sorry to bombard u with this
if u could help it would be much appreciated, i feel like i am banging my
head on a brick wall, i know wot is needed but i dont know how to do it. very
annoying.
thanks
Gary