Access DB Question

tjbarr

st_fip fip_code sic var_code bls1975 1975_supr
06 06013 01 1 194
06 06013 01 2 2086607
06 06013 02 1 0 s
06 06013 02 2 0 s

Please help. I would like to combine sic codes one 01 and 02 together by var_code. However if one of them has an s in 1975_supr then I want to update the other one to S and update the bls1975 field to zero.

e.g. for fip_code= 06013 Var_code=1, sic 01=194 in the Bls_1975 column and null in the 1975_supr column, but sic02=0 in the Bls_1975 column and and "s" in the 1975_supr column.
Therefore I want to update sic 01 var_code=1 in the bls_1975 column to 0 and the bls_1975 Column to "s".

I have 25 years of this so I need a loop in vb to do this in a module. I tried to do it in Access but I had to do too much manually. Can someone help?

barand

I've attached an access mdb with a query which should do what you want - provided I understood correctly 🙂

Have a copy of your table before attempting the update query - suggest you import it into the attached database and name the table 'sic', replacing the small one already there.

hth

(Apologies to PHP users for being OT, but if anyone is interested, the query contained in the file is

UPDATE Sic INNER JOIN Sic AS Sic_1 ON (Sic.var_code = Sic_1.var_code)
AND (Sic.fip_code = Sic_1.fip_code) SET Sic.[1975_supr] = [Sic_1].[1975_supr], Sic.bls1975 = [Sic_1].[bls1975]
WHERE (((Sic.[1975_supr]) Is Null)
AND ((Sic.sic)=1) AND ((Sic_1.sic)=2))

)

barand

And here's the attachment

tjbarr

Thanks Very Much. It was simple but I made it complicated. I have 25 Years of this - Do I need to do each year one at a time?

barand

If each year has a different column name, yes.

Has the 1st rule of normalisation (remove repeating data) been broken here ?

tjbarr

Yes, but only because this is a web application that exports out to an excel spreadsheet with only 256 columns allowed

tjbarr

What would be the changes if I was combining 3 or more sic's