Merging Tables

[Chippie]

I have a table called Customer. In it there are similar records with one value Ship/Bill set to either Ship or Bill. This is obviously duplicating many entries in my DB. All the data is in one table, how do I join it so that I have 2 extra fields Ship and Bill with a tick box in.

If one record has a ship in it, and another exactly the same record has bill in it, how do I merge these into one record.

Any help would be really appreciated. It's killing my head.

 

[Sawatzky]

Dear Chippie,

please tell me whether your customers table has a seperate primary key and what is the criterion to identify a specific customer.

My first thought was to use access' assistant for table analysation, but I am not sure whether it works like I expect it to.

regards astrid

 

[Chippie]

Sawatzky, Customer is just one huge table. I have multiple entries of records with the only difference being that in one particular field the value can either be SHIP or BILL. I blame the rubbish legacy system the data was ripped from. Anyway, not all Customer SHIP and BILL, some just SHIP. The BILL and SHIP addresses are the same. The Custome rtable does not have a Primary key. I can easierly stick on an ID, but that won't help. The only critera that canbe searched is the address. This is, to an extent, unique to eahc client.

I have now stripped the table into 2 tables, SHIP AND BILL. I am now going to try to merge the two, but am unsure how.

Thankyou in anticipation

 

[Sawatzky]

Dear chippie,

to examine the problem I did the following
create 2 tables
adrbill and adrship
with the following structure
customer text
street text
type text (to take ship resp. bill)

then I made a third table

having a primary key on customer AND street

customer text
street text
ship yes / NO
bill yes / NO

with this query I insert all customers of the table adrship into adrbillship

INSERT INTO adrbillship ( cutomer, street, ship )
SELECT adrship.cutomer, adrship.street, IIf(Not IsNull([type]),True,False) AS ship
FROM adrship;

and now comes the quick and dirty part

with this query I insert all customers of the table adrship into adrbillship if they are not allready inserted

INSERT INTO adrbillship ( cutomer, street,bill )
SELECT adrbill.cutomer, adrbill.street, IIf(Not IsNull([type]),True,False) AS bill
FROM adrbill;

this is quick and dirty as access will only insert those that are not inserted yet, as the others are dismissed because of the primary key violation (shame on me)

the remaining records are normally written in a autocreated Table as failure or error table,
which you now can use to update the records which were in adrship and also in adrbill.

if this table is not created, I could use this update query instead:

UPDATE adrbillship INNER JOIN adrbill ON (adrbillship.street = adrbill.street) AND (adrbillship.cutomer = adrbill.cutomer) SET adrbillship.bill = IIf(Not IsNull([adrbill]![type]),True,False);


HTH

regards Astrid

 

[Chippie]

Excellent, thankyou very much. I tried on some trial data before I step into the big golliath. The only thin I wondered is how do I create 2 primary keys in access. The customer name, and street can be used many times in my table, and are therefore not able to be primary keys. Trying your method not using primary keys creates a problem. The records are created correctly, but I think coz of the primary keys, records are dumplicated. Therefore if a customer who is billing is added, then the same customer is added shipping 2 records will be created. One with just bill and the other with both bill and ship.

Can you delete duplicate entries without creating another table ?

Thankyou for all your help.

 

[Sawatzky]

Dear chippie

<<The only thin I wondered is how do I create 2 primary keys in access. >>

in the ciew where you see the structure of your table , you mark all columns you want in your primary key and than you click the little golden key icon.

HTH
regards Astrid