Calculation from tables (2000)

Topic

New Reply

Hi Guys,
I’m having this problem with an access database in relation to calculations. Its been really bugging me for weeks.
Yet, it does seem simple. It involves calculation. The below is what i’m trying to do.

1. There are two forms that are of concern, frmQuotation and frmManifest,
on frmManifest, there are are 2 textfields, Weight and Amount. The Amount is calculated according to the Weight,
The calculation table is stored in frmQuotation (tblQuotation & tblZoneRates). Now looking at the frmQuotation, we have
Zone, Type, 1stWeight, 1stRate, AddWeight, AddRate, Surcharge.
These fields refer to the calculation process for each zone, eg ADL is Adelaide. Type means Parcel or Document (P or D), 1stWeight
means if it weighs less then 0.5g, it will be charged at $15 (1stRate), any subsequent weight value of 500grams after that would add $14.
And the end result would be output into the amount field in the frmManifest. This all depends on the zone, as you can see, each zone has a special price.
By default, all rates for shipments would be calculated from the PublishedRates2003 account.
However, if the customer account (QuotationID is customeraccount) has a different rate for a specific zone, then that would override the default rate.
If you can help me out, it would be soo appreciated. I’m ripping out my hairs, aagghhh.

Reply | Quote

Replies

I’m . How do I get the Zone and Type information for a record in tblManifest? The Destination field contains numbers for some records, text for others, there is also a Terminal field, which is mostly empty, and Content is mostly empty too.

Oh, and by the way, I assume that you mean 0.5 kg, not 0.5g.

Reply | Quote

Zone in tblManifest would be retrieved from tblZoneList, it could also be added into frmManifest if need be. In frmManifest, ‘Content’ is the same thing as ‘Type’.
Apologies for the confusion in the data, I was experimenting with queries to see which works best. Only to leave me baffled.
As my design flaw has lapsed and need assistance on it. Please help me.
yep, your right, 0.5kg is correct.
Regards
Mark

Reply | Quote

Please clean up your design before asking us to solve your problems. You shouldn’t expect us to do the cleaning for you. So:
– Make Destination into a number field, and populate it with valid values.
– Decide whether you want to store Type/Content as numbers (1 or 2, as in tblManifest) or as text (“D” or “P”, as in tblZoneRates); be consistent and modify the design and data accordingly. Provide a valid value for Content in all records in tblManifest.
– Remove database objects not needed for this problem. qryManifestCN can’t be opened in this version, neither can frmServiceZone, so scrap them.
– Try to remove other inconsistencies.
Then repost the database.

Reply | Quote

Please clean up your design before asking us to solve your problems. You shouldn’t expect us to do the cleaning for you. So:
– Make Destination into a number field, and populate it with valid values.
– Decide whether you want to store Type/Content as numbers (1 or 2, as in tblManifest) or as text (“D” or “P”, as in tblZoneRates); be consistent and modify the design and data accordingly. Provide a valid value for Content in all records in tblManifest.
– Remove database objects not needed for this problem. qryManifestCN can’t be opened in this version, neither can frmServiceZone, so scrap them.
– Try to remove other inconsistencies.
Then repost the database.

Reply | Quote

Zone in tblManifest would be retrieved from tblZoneList, it could also be added into frmManifest if need be. In frmManifest, ‘Content’ is the same thing as ‘Type’.
Apologies for the confusion in the data, I was experimenting with queries to see which works best. Only to leave me baffled.
As my design flaw has lapsed and need assistance on it. Please help me.
yep, your right, 0.5kg is correct.
Regards
Mark

Reply | Quote

I’m . How do I get the Zone and Type information for a record in tblManifest? The Destination field contains numbers for some records, text for others, there is also a Terminal field, which is mostly empty, and Content is mostly empty too.

Oh, and by the way, I assume that you mean 0.5 kg, not 0.5g.

Reply | Quote