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!
- ALL the occupations for this company suddenly change to "/year" for pay basis (even though I do not see how this would be linked!)
- 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:
'legend':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 ) );
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:
[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.............................................. .................................................. ..