Problem in crossing datas and tables. This problem looks like the battleship game

[matteozz]

Hello everyone,



unfortunately I have several problems in carrying out a job, so I have many doubts before to present everything clearly.

I have a value that comes from some of the formulas set out above, let's call WEIGHT. Then I have another value, the CAP.

WEIGHT must be associated with the weight ranges: up to 100, 300, 500, 1000, 2000, 2500, 3000, 4000, 5000.

The CAP will instead be assigned to a particular area: ZONE 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11. In the same area can be assigned more CAP.

The end of the weight determines a cost in relation to the area.
Suppose that the 300 is up to 300 and the ZONE 7 (because for example the cap 41 corresponds to that area), in this case you will find a value that should be determined previously, we have made € 10; if the range is 1000 and the ZONE 1, the value found will be different. A little 'as the baleship game!

I am confused about how to create tables with data and to ensure that they relate properly and with all parameters!

Thank you all for the tremendous help

[ranman256]

tZone table

ZONE , CAP
1, 500
2, 300
3, 2000

but still not sure what youre doing with Weight.

tWeightReadings:
zone, weight
1, 200
1, 250
1, 300

is this it?

[matteozz]

It's very simple... take a look to this scheme
Having the WEIGHT and the CAP I 'd like to determine the green highlight number (3.00 €)

[orange]

@matteoz

Can you clearly describe the associated business in simple English?
What exactly do these WEIGHT, ZONE, CAP , AREA and COST represent?

It seems to me that your confusion is based on a lack of clear definition of the issue.
Hats off to ranman if he has guessed correctly, or understood your requirement.

[matteozz]

Ok, I'm sorry but I'm not a database expert and my English is really poor...
I'll try to be the most clear I can.

I want to determine some costs because I work for a transport company. So, the problem is that I have a data, which is the weight of the goods, for example 300 Kg.
To determine this final cost to apply, I have to look at a table where there are some crossing datas.
In addition to the first data (300 Kg), I have the CAP (the Italian for ZIP code).

I also have a table where every ZIP is associated to his ZONE (It's a group of ZIPs of a geographic area) and I have some ranges of weight...
What I want to do is to determine to which range the weight correspond (300's range), and the zone of the ZIP code. In doing this i can obtain the cost...

[orange]

Your English is quite good ---certainly sufficient for communication.
I understand Weight (kg). But what is the unit of measure associated with CAP?

[ranman256]

In Access your data will be stored like this:

ZONE	CAP
ZONE1	41
ZONE1	50
ZONE1	36
ZONE2	74
ZONE2	65
ZONE3	22
RANGE	ZONE	AMT
100	ZONE1	2.00
100	ZONE2	11.00
300	ZONE1	3.00
300	ZONE2	12.00

[June7]

CAP is like ZIP code - a postal identifier? This is a postal weight rate chart? Given a weight and CAP, lookup the rate? Normalized structure suggested by ranman should make this easier to accomplish. There are ways to deal with non-normalized structure but why bother if normalizing makes life easier?

[orange]

I agree with ranman and June -- normalized structure ( as always with rdbms) will make life easier, and queries, calculations possible.

[matteozz]

In Access your data will be stored like this:

ZONE	CAP
ZONE1	41
ZONE1	50
ZONE1	36
ZONE2	74
ZONE2	65
ZONE3	22
RANGE	ZONE	AMT
100	ZONE1	2.00
100	ZONE2	11.00
300	ZONE1	3.00
300	ZONE2	12.00

Thank yiu for your reply!

So, I'll create two table like you show me. In doing this, I hope to will be able to creare some realtions between theme and the obtain the final reasult. This won't be an easy work but I'm gonna try