Results 1 to 4 of 4
  1. #1
    Datamulcher is offline Novice
    Windows 10 Access 2007
    Join Date
    Apr 2016
    Location
    Victoria, Canada
    Posts
    26

    Exclamation Selecting a value from lookup table CHANGES THE LOOKUP TABLE & the values for other fields!~

    OK. This is weird. Likely an incorrect join, but it is making zero sense to me....

    So. I have a form (which runs off of the query below) for viewing wage data for different occupations. This is in a database which Coders use to clean and code the data.

    Think of it like:

    JOB TITLE
    - PAY - PAY BASIS
    Welder 13.00 /hour
    Chef 20,000 /year
    Laborer 15,000 /day

    Part of the Coders' job is to fix errors such as that Laborer above who is making 15,000 a day. Obviously this would be a yearly salary since a construction worker wouldn't make $15,000 an hour.
    Of course there is a lookup table for the pay basis.
    Pay PayDesc DirectionOfConversion
    1 /Hour --->
    2 /Year
    3 /Piece Work_or_Commission ?
    4 Don’t Know
    5 /Month
    6 /day <---

    Right, everything looks easy and reasonable so far.
    Now the occupations are grouped by Company. So the welders, carpenters and electricians for a construction company are grouped together.

    The weird problem I am having is that if I change that laborer's pay basis to "/year" by selecting this from the combo box two really strange things happen!
    1. ALL the occupations for this company suddenly change to "/year" for pay basis (even though I do not see how this would be linked!)
    2. THE LOOKUP TABLE IS CHANGED! After this action it looks like this:


    Pay PayDesc DirectionOfConversion
    1 /Hour --->
    2
    2
    3 /Piece Work_or_Commission ?
    4 Don’t Know
    5 /Month
    6 /day <---


    Soooooooooooo.... Not only is every occupation for the company changed and not just the selected ReferenceID one, but the lookup table is changing!

    Here is the code for the underlying query:

    Code:
           SELECT extracted_data_main.[_telkey]                                AS Telkey,
           extracted_data_main.apelkey                                             AS Apelkey,
           extracted_data_master_not_occs.aupld_business_name,
           [coded cases].codercomments,
           [coded cases].noc_code,
           Iif([noc_code] IS NOT NULL, Dlookup("[noc4desc]", "lookupnoc4digit",
                                       "[noc4]=" & [noc_code]))          AS  NOC_Description,
           extracted_data_main.ab1title                                 AS Job_Title
           ,
           extracted_data_main.ab2desc                                AS       [Job Description],
           Iif([aprevious_ab1title] IS NOT NULL, "update survey", NULL) AS       Indicates_Update_Survey,
           extracted_data_main.aprevious_noc                       AS       NOC_CODE_2013,
           extracted_data_main.ab1noc                                AS       Respondent_NOC,
           extracted_data_main.aqb5_avr                             AS       Average_Wage,
           extracted_data_main.aqb5_strt                            AS       Starting_Wage,
           extracted_data_main.aqb5_top                            AS Top_Wage,
           extracted_data_main.ab4hours,
           extracted_data_main.ab10notes                                AS       [Interviewer Notes],
           extracted_data_master_not_occs.qa2industry,
           [coded cases].firstcodedby,
           [coded cases].firstcodedtime,
           [coded cases].lastcodedby,
           [coded cases].lastcodedtime,
           Iif(extracted_data_main.datasource = 2, "data entry", Iif(
           extracted_data_main.datasource = 3, "excel sheet", NULL))    AS SOURCE,
           extracted_data_main.ab10notes,
           extracted_data_master_not_occs.aupld_contact,
           extracted_data_master_not_occs.aupld_city,
           extracted_data_master_not_occs.aqa3activity,
           tblexclude_from_coding.f1,
           extracted_data_main.b5allpay,
           lookuppaybasis.paydesc                                         AS AvgBasis,
           extracted_data_main.b5spay,
           LookupPayBasis_1.paydesc                                     AS StartBasis,
           extracted_data_main.b5tpay,
           LookupPayBasis_2.paydesc                                     AS TopBasis
    FROM   (((tblexclude_from_coding
              RIGHT JOIN (extracted_data_master_not_occs
                          INNER JOIN (extracted_data_main
                                      LEFT JOIN [coded cases]
                                             ON extracted_data_main.[_telkey] =
                                                [coded cases].telkey)
                                  ON extracted_data_master_not_occs.telkey =
                                     extracted_data_main.apelkey)
                      ON tblexclude_from_coding.f1 = extracted_data_main.ab1title)
             LEFT JOIN lookuppaybasis
                    ON extracted_data_main.b5allpay = lookuppaybasis.pay)
            LEFT JOIN lookuppaybasis AS LookupPayBasis_1
                   ON extracted_data_main.b5spay = LookupPayBasis_1.pay)
           LEFT JOIN lookuppaybasis AS LookupPayBasis_2
                  ON extracted_data_main.b5tpay = LookupPayBasis_2.pay
    WHERE  ( ( ( extracted_data_main.[_telkey] ) IS NOT NULL )
             AND ( ( tblexclude_from_coding.f1 ) IS NULL ) );
    'legend':

    extracted_data_main is all the occupations.
    extracted_data_master_not_occs is the company information. (name, phone etc)
    lookuppaybasis is the "offending" lookup table.
    coded cases is a continually generated list of cases which have been coded, and what they are coded to.
    tblexclude_from_coding is a list of job titles that can be done by update query so it is excluded from being in the form for the coders.
    A NOC_CODE is the occupational code. Mine would be 1254 "social science research assistant (except university)". This is the main thing they are coding the occupations to.
    as a visual aid- here is the query structure:



    Click image for larger version. 

Name:	Capture.PNG 
Views:	10 
Size:	45.1 KB 
ID:	28963

    [the problem existed when I had only one lookup table linked here. It also exists when I use what Access labels a "1" join or "2" join on the lookup tables with a "3" join creating an "ambiguous outer join".]

    Anyone know what is going on? im at a wall with this.............................................. .................................................. ..

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Can you post a copy of you db with some sample data?

  3. #3
    Datamulcher is offline Novice
    Windows 10 Access 2007
    Join Date
    Apr 2016
    Location
    Victoria, Canada
    Posts
    26
    I know that would be helpful but its >gig in size and a government project with personal data sooooooooooo - not permitted (and I wouldn't be at all surprised if my tech people were snooping on my web activities).
    I'll see if I can make a version with the tables gutted of any actual data, with random information and structure intact. Mayyybe

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    you get the '2' because you've set the wrong column to be the bound one. It appears you're getting the autonumber field for year (2).
    As for the first issue, if you bind the control to a query or table field (i.e. you have set a control source property) and you allow changes to what the control displays (this is a combo's row source property) then you will alter the value for that field in at least the current record. If you have set relationships with cascade updates, the changes will propagate throughout the tables according to the defined relationships.

    Sounds like you need to either research how to build/use Access forms or explain the process much further than you have, because given all the form possibilities (Allow Edits/Deletions/Additons/Data Entry options), sub forms and the like, you might need a different setup. Sometimes combos should not have control sources and changes should be effected via queries or sql statements in code.

    Sorry to say I can't bring myself to examine the code you posted 'cause it's all over the place. It doesn't explain what event it's related to either, so I'm not sure it helps.
    Might you have a normalization issue as well? I don't see anything that justifies having the same PK in several tables, plus the need to join the same table multiple times is not a good sign.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Lookup table values
    By haywam59 in forum Access
    Replies: 2
    Last Post: 01-23-2016, 10:16 AM
  2. Selecting multiple elements from a lookup table
    By JRohde in forum SharePoint
    Replies: 1
    Last Post: 11-10-2015, 12:21 PM
  3. Replies: 4
    Last Post: 04-25-2015, 10:29 PM
  4. Replies: 3
    Last Post: 06-28-2013, 01:59 PM
  5. table lookup / null field values, help!
    By benjammin in forum Forms
    Replies: 3
    Last Post: 07-27-2011, 01:56 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums