How to 'zerorise' a field

[graveyard]

hi people, i have a problem here which i hope you can enlighten me abt.

my database has duplicates due to different currencies used. The currency value is not duplicated but the weight is. sorry if i am confusing you so its better that i give an example.

consignment ID [12345] Weight [100] Currency [USD] Cost [300]
consignment ID [12345] Weight [100] Currency [AUD] Cost [800]

An item with consignment ID [12345] is paid in 2 currencies (USD and AUD). The different currency causes record to be duplicated. the Weight is duplicate but not cost. So, I need to remove one of the weight so it reflects the actual weight which is [100] and i need to add together USD300 + AUD800 and convert to EURO.


i am ok with the currency conversion and i divide the weight by 2 in the query so i can get the actual weight which is 100. Below is what i have from the query

consignment ID [12345] Weight [50] Currency [USD] Cost [300]
consignment ID [12345] Weight [50] Currency [AUD] Cost [800]

it seems as though 50(KG) is charged in USD while 50(KG) is charged in AUD which is not the case.

Can someone advise me how to adjust my query so i get the following:

consignment ID [12345] Weight [100] Currency [USD] Cost [300]
consignment ID [12345] Weight [0] Currency [AUD] Cost [800]

so user know the 2nd record is duplicated (since they know it is impossible to have an item weighing 0 and cost something).

that means i need to "Zerorise" the "weight" field of 2nd record without affecting the weight value of the 1st record.

Pls advise. Thanks!!!

 

[jdraw]

Does "my database" mean that you designed it?
You may want to show us a picture of your tables and relationships, then ask for advice on the design.

Have you considered a table for relating currencies? Or using an exchange rate database service? Having duplicate records because of the currency involved seems to be a design shortcoming. What happens when you start dealing withCanadian Dollars, Euros and Yen? Will you have replicated data, and then have to remove values from multiple records?

 

[graveyard]

hi jdraw, yes i designed the database. sure, i am not sure how to show all the access tables queries here as its quite messy but i did a slide which summarises the whole thing. pls find attachment - as noted in it, the records are duplicated when CCR cons and CCR costs tables (right at beginning) are combined to create "Match Consignment detials with cost query".

i have a few tables in my database which contain reference of exchange rate of all teh currencies vs EURO.

Yes, as long as the consignment is traded at more than one currency (cost in USD and AUD in this example), the records will be duplicated. i do not need to combine the records into one (i figured out if i remove the 'USD" and the "AUD" field, the records will be collapsed into one [consignment ID [12345] Weight [100] New Currency [EU 800] ) but i need to derive at what is depicted in the attachment (See "ideal results should be : ......." at bottom of the slide. the 2nd line is evident that it is a duplicate since weight = 0 and the user is aware.

Sorry if this is unclear because i really hope this is not confusing. If it is, pls let me know so i can try to make it clearler.

 

[John Big Booty]

I'd be inclined to break that down into two tables, one would be;

TBL_ConsignmentHeader
ConsignmentID (PK)
ActualWeight
ChargeableWeight
ConsignmentDate
ClientID (FK)
CarrierID (FK)
(and any other pieces of date that are relevant to this section)


The other would be;
TBL_ConsignmentDetails
ConsignmentID (FK)
ChargeType (FK)
ConsignmentCharge
CurrencyID (FK)

 

[gemma-the-husky]

I would not be inclined to zeroise the fields

What you really need is to separate the unique consignment numbers in which you are interested, and then accumulate date from them.

As JBB says, the repeating groups (ie Id and weight) indicate that the data is no longer properly normalised - but I think you might get round it, as described above.

note that it might be quite tricky to determine which of the duplicates gets zeroised - is there always a USD. If mot, how do you decide which one to zeroise?

 

[graveyard]

I would not be inclined to zeroise the fields

What you really need is to separate the unique consignment numbers in which you are interested, and then accumulate date from them.

As JBB says, the repeating groups (ie Id and weight) indicate that the data is no longer properly normalised - but I think you might get round it, as described above.

note that it might be quite tricky to determine which of the duplicates gets zeroised - is there always a USD. If mot, how do you decide which one to zeroise?

oh, i think i forgot to mention that's what i did. thats why in the attachment i put up earlier - there are 2 queries (duplicate consol) this contains the duplicated consignment numbers which have different currencies charged to each number and also (non duplicated consol) which contain unique numbers (non duplicates), i use make table query on the non duplicated and append the duplicates to form the "consolidated ccr raw" table.

Ohh it is quite easy to determine. only the weight is duplicated but not the costs. So i just need to remove one of the weight (since the value is the same it doesnt matter which one i remove from) - the costs wont be affected as if i sum up both records, the costs will add up nicely and the weight will be correct (e.g. 200kg + 0 = 200kg)

Any idea how to proceed?

 

[graveyard]

I'd be inclined to break that down into two tables, one would be;

TBL_ConsignmentHeader
ConsignmentID (PK)
ActualWeight
ChargeableWeight
ConsignmentDate
ClientID (FK)
CarrierID (FK)
(and any other pieces of date that are relevant to this section)


The other would be;
TBL_ConsignmentDetails
ConsignmentID (FK)
ChargeType (FK)
ConsignmentCharge
CurrencyID (FK)

hi john, sorry i dont quite get this. what is FK and PK? would you mind explaining. thanks

 

[John Big Booty]

PK is a Primary Key and FK is Foreign Key

 

[gemma-the-husky]

well the best solution is to move to properly normalised tables

you need a consignment header

- consignmentId, datres, shipper etc , etc, and WEIGHT in there

and then consigment details
- US, $800
- Aus $300


by arbitrarily setting some consignment details to zero, you could start tieing yourself in knots, as you need to find the designated "master" detail to find the data relating to the consignment.


-----
if you are desperate to do it your way, i think you would need a query sorted by consigment number, and then iterate it with code. Take the first item, and leave the qty intact, and clear the details for any other items. But you need to be careful, as inserting extra lines might mess up the order, and you might lose all the quantity information.

the other way, is maybe to have a query that selects the max weight by consignment no, and then you only get 1 weight per consignment.

Seriously, depending how critical this is, it may be worth dealing with the normalisation issue, rather than trying to come up with ways of handling un-normalised data.

 

[graveyard]

thanks, johnbigbotty and gemma the husky! I will work on the normalised mode and at the same time, trying to refine my current query to show just one weight value.
Sounds like heaps of workout but you guys have given me great advice!