uniquely identifying sum of values

Waffeltje

Registered User.
Local time
Today, 09:39
Joined
Aug 12, 2014
Messages
11
Hi,

I'm designing a database which is used for calculating prices per transporter. Each transporter is assigned a unique code (in table tblCarriers) and can transport from a specific country. When a transporter can transport from country, this country has to be linked to this specific transporter.

In the current design each transporter is assigned a value of power two, so {1,2,4,8,......,n} in tblCarriers. Table tblZones consists of all countries and contains one column called departurePossible. When a transporter can transport from a country, the value which is assigned to that specific transporter of power two is added to the departurePossible column.

If ((tblZones.departurePossible.value And tblCarriers.value) = tblCarriers.value)
Then
departurePossible = True

This works, but the values of power two grow exponentially which isn't favorable. Does anyone has other ideas for managing this kind of problems?

Thanks in advance.
 
Like this?

Code:
CREATE TABLE CarrierDeparturePossible
(CarrierCode VARCHAR(4) NOT NULL,
CountryCode VARCHAR(3) NOT NULL,
PRIMARY KEY (CarrierCode, CountryCode));
 
Ummm, assign an incremental number (1, 2, 3, 4...) to each transporter?

What am I missing?
 
The case is, when multiple (up to hundreds of) carriers transport from one specific country, how can I derive from this one field in the ZonesWorld table which carriers belong to this value.

Say; the database stores one hundred carriers and 15 out of these 100 transport from the UK. Each carrier possesses an unique value, and this value is added to the DeparturePossible field in the ZonesWorld table. Then; how can I derive which carriers transport from the UK, based on the summed value in the ZonesWorld table. As already explained, I currently work with powers of two and check whether the unique carrier value is covered by the summed value in ZonesWorld table, but this grows exponentially. The method is something like this: (sorry for the messed up link, I can't post links yet with only 8 posts..)

This is actual a mathematical problem and not specifically a VBA Access problem, but I can't get an answer to this problem.

Thanks anyway.
 
Buttonmoon and plog have given answers to your question, but you have provided very limited info regarding the business this database is going to support.
How about giving us an overview in plain English of WHAT your proposed database is about? Readers can only post focused answers once they understand WHAT you are trying to accomplish. Then, they may offers several methods/approaches as to HOW that WHAT can be done. The WHAT always comes before the HOW.

A jpg of your relationships window would also help put your current info into context. You can zip the jpg if necessary.

Good luck.
 
So, you want to have one field be able to store multiple values? That's not how databases are to work. Sounds like you need a junction table (http://en.wikipedia.org/wiki/Junction_table).

A junction table defines a many to many relationship (one transporters can go to multiple countries and one country can have multiple transporters). If that's the case a junction table is the way to go. First you would assign an autonumber primary key to both the Transporter and the Country table. Then you create a table to hold both of those IDs when a transporter goes to a country:

TransporterCountries
ID_Transporter, ID_Country
1, 3
1, 7
2, 3
2, 9

So for Country=3, you don't create some encoding system to create a number that means Transporter=1 & Transporter=2 are assigned to it; you add 2 records to TransporterCountries which lets you define the same relationship.
 
I suspect OP is referring to bit masking - google that term, plenty of references. But there is hardly any point in doing it like that since price of storage is virtually nil, so just go with #8 -bit masking is too much hassle with little benefit.
 

Users who are viewing this thread

Back
Top Bottom