Intelligent Comparison based Update - Access / VBA -

-

need intelligently perform updates on access table. expert vba / intelligent thinking required.

table1 (for reference only) companycode     text regioncategory  number (1-99) regioncount     number(0 - 25000)  table2 invoicenumber       number companycode         text     numrows             number regioncode          fourdigitnumber confirmationremark  y / n

ourobjective put yes or no in 'confirmationremark' column. rules :

1.select invoicenumbers have 2 rows table2 , different regioncode. these have same companycode. regioncategory first 2 digits of regioncode.

2.for these 2 invoices - difference between 2 regioncategory must greater two.

3.lookup regioncount , table1

decision making : comparing 2 invoices different regioncodes. idea , invoice higher regioncount 1 marked yes.

1.the difference between regioncount must considerable. 'considerable' - trying determine right number. let take 500 now.

2.the invoice lower region count - should have regioncount - 0 (bestcase) or very low. if invoice lower region count has high regioncount value > 200 , cannot conclude.

3.numrows , prefered 1 or lesser other. comparison , not mandatory , hence shall have provision not check this. mark other invoice 'n'

you have many ways approach type of complex update.

if lucky, may able craft sql update statement can include changes, have resort combination of select queries , custom vba filter them based on results of calculations or lookups involving other data.

a few hints

  • often, tend think problem in terms of 'what steps data match criteria'.
    sometimes, though, it's easier turn problem on head , instead ask 'what steps data not match criteria'.
    because in case result boolean, true or false, set confirmationremark field true records , update should set false, instead of other way around.

  • break down each step (as did) , try find simplest select query return data need step. if step complex, break down further.

  • compose broken down select statements build more complex query tends toward goal.

  • once have gone far can, either construct update table2 set confirmationremark=true invoicenumber in (select invoicenumber ....) or use vba go through recordset of results complext select statement , more checks before update field in code.

some issues

unfortunately, despite efforts document situation, there not enough details help:

  • you not mention primary keys (from say, seems table2 have multiple records identical invoicenumber)

  • the type of data dealing not obvious. should include sample of data , identify ones should end-up confirmationremark set.

  • your problem localised, meaning specific of value else, although think bit more details question of interest, if show example of how approach complex data updates in access.


Comments

Post a Comment

Popular posts from this blog

Hatching array of circles in AutoCAD using c# -

-
i beginner in autocad plugins .i have created array of circle using following code. now, have hatch circles in array .how that??how hatch circles @ once? moreover, how can return hatch method , use hatch objects.sorry language.thanks in advance. [assembly: commandclass(typeof(beamsection.class1))] namespace beamsection { public class class1 { //class1 obj = new class1(); [commandmethod("beamatsupport")] public void addlightweightpolyline() { // current document , database document acdoc = application.documentmanager.mdiactivedocument; database accurdb = acdoc.database; promptpointresult ppr = acdoc.editor.getpoint("\nselect starting point "); var ucs = acdoc.editor.currentusercoordinatesystem; point3d startingpt = ppr.value.transformby(ucs); list<double> radius = new list<double>(); radius.add(0.5);
Read more

ios - UITEXTFIELD InputView Uipicker not working in swift -

-
hello trying pop uipickerview view programmatically when user clicks on textfield. have tried doesn't doing anything. isn't working. nothing happening when click textfield class userprofiletableviewcontroller: uitableviewcontroller,uitextfielddelegate,uipickerviewdelegate,uipickerviewdatasource { var itempicker: uipickerview! = uipickerview() @iboutlet weak var gendertxtfield: uitextfield! var gender = ["male","female"] override func viewdidload() { super.viewdidload() gendertxtfield.delegate = self itempicker!.delegate = self itempicker!.datasource = self itempicker!.backgroundcolor = uicolor.blackcolor() self.gendertxtfield.inputview = itempicker } func numberofcomponentsinpickerview(pickerview: uipickerview) -> int{ return 1 } // returns # of rows in each component.. func pickerview(pickerview: uipickerview, numberofrowsincomponent component:
Read more

Python Pig Latin Translator -

-
so have been working on @ codecademy.com while trying learn python. don't know if blind , can't see why won't work or what. here's code won't let me use. function print variable if had typed in , print "empty" if didn't type anything. print "welcome english pig latin translator!" original = raw_input("what's name?") if len(original) > 0 print original else print "empty" if len(original) > 0 else the above lines contains syntax errors. try: if len(original) > 0: else: note added colons, ( : ).
Read more