automating update query (1 Viewer)

New_to_this

New member
Local time
Today, 16:37
Joined
Nov 20, 2018
Messages
7
HI,

I want to automate to below script which i normally manually run to check any file imported into a access database, it just needs to check the postcode and update a new column with a flag to show it matches.

UPDATE [UK A19EDA] SET [UK A19EDA].[UK Postcode] = "Y"

WHERE ((([UK A19EDA].Postcode) Like Like "AL*" Or Like "BN*" Or Like "CM*" Or Like "CT*" Or Like "GU*" Or Like "HP*" Or Like "LU*" Or Like "ME*" Or Like "MK*" Or Like "OX*" Or Like "PO*" Or Like "RG*" Or Like "RH*" Or Like "SG*" Or Like "SL*" Or Like "SO*" Or Like "SS*" Or Like "TN*" or br* or cr* or da* or e#* or ec* or en* or en* or ha* or ig* or kt* or n#* or nw* or rm* or se* or sm* or sw* or tw* or ub* or w#* or wc* or wd* or Like "DH*" Or Like "DL*" Or Like "HG*" Or Like "HU*" Or Like "LS*" Or Like "NE*" Or Like "SR*" Or Like "TS*" Or Like "WF*" Or Like "YO*" or Like "BB*" Or Like "BD*" Or Like "BL*" Or Like "CA*" Or Like "CH*" Or Like "CW*" Or Like "FY*" Or Like "HD*" Or Like "HX*" Or Like "L#*" Or Like "LA*" Or Like "M#*" Or Like "OL*" Or Like "PR*" Or Like "SK*" Or Like "WA*" Or Like "WN*" or Cb* or co* or de* or dn* or ip* or le* or ln* or ng* or nr* or pe* or s#* or B#* or cv* or dy* or hr* or nn* or st* or tf* or wr* or ws* or wv* or Like "BA*" Or Like "BH*" Or Like "BS*" Or Like "DT*" Or Like "EX*" Or Like "GL*" Or Like "PL*" Or Like "SN*" Or Like "SP*" Or Like "TA*" Or Like "TQ*" Or Like "TR*" or like bt* or Ab* or dd* or dg* or eh* or fk* or g#* or hs* or iv* or ka* or kw* or ky* or ml* or pa* or ph* or td* or ze* or Like "CF*" Or Like "LD*" Or Like "LL*" Or Like "NP*" Or Like "SA*" Or Like "SY*" or like “JE*” or like “GY*” or like “IM*” ));

would be most grateful for any assistance, im quite new to automatic procedures so please explain in simple terms!!

thanks
 

Minty

AWF VIP
Local time
Today, 23:37
Joined
Jul 26, 2013
Messages
10,355
I think it would be much easier to use a table of postcode districts and then use that and
the portion of your supplied postcode up to the space as the join. If it's match then update your flag.

If another postcode district arrives you simply add it to your postcode area list.
 

New_to_this

New member
Local time
Today, 16:37
Joined
Nov 20, 2018
Messages
7
thanks Minty, I'm not sure what your saying, I could generate a table of all UK 4 byte postcodes (up to the break) and then format the file I need to flag to allow matching but the problem I have is people who would need to run this in my absence aren't access savvy at all so just want them to be able to import the file and run the query.

with your suggestion how would i go about this bearing in mind the files i need to flag will all have different names moving forward and want to make this a one button query for my non access savvy colleagues
 

Minty

AWF VIP
Local time
Today, 23:37
Joined
Jul 26, 2013
Messages
10,355
Well the automating of it is fairly straight forward, assuming your file is always in the same format.

You can make a command button on a form, that with the use of a file picker, will perform an import and then run your query all in one fell swoop.

I use a list box and populate the files in a specific location for this type of thing. Then select the file and press the action button to do the necessary processing.

Each step is not overly difficult, just break it down to individual steps.
 

Bullschmidt

Freelance DB Developer
Local time
Today, 18:37
Joined
May 9, 2019
Messages
40
Or if didn't want to make many changes you could have a macro called AutoExec which would get run each time the database is opened and in there have a RunSQL command with your existing SQL statement.
 
Last edited:

Users who are viewing this thread

Top Bottom