Query not working when combined with other queries

[prpmtn]

Ok, I’m lost. I have been working on this for over a week and can’t find the solution or figure it out. :banghead: I have three queries built from two unrelated tables (DataFile and tblBLS).
DataFile:
ContractorName
NAICS
Year1
Year2
Year3

tblBLS:
NAICS
BLSYear (this includes 4 years of data, separated by NAICS )
TCIR

The queries are used to pull information from both tables based on year (qryBLS1: DataFile.Year1=tblBLS.BLSYear, qryBLS2: DataFile.Year2=tblBLS.BLSYear, and qryBLS3: DataFile.Year3=tblBLS.BLSYear) and perform calculations (there are more fields in each table, but the primary data is pulled by year and matching NAICS; these variables are listed as numbers in the tables). I’m sure there are easier ways, but after struggling for a couple of weeks, I just decided to split out my one gargantuan (I love that word) query into three, pool them together into another query to use a report. Everything works great until Year1 does not match any BLSYear. This is fine for the individual queries, but when I try to pull the three together for the report, I get no data (since qryBLS1 is not working). Any thoughts on how I can still get the other two queries to pull information, even if the one is not producing results? I can put “Data not available” on the report, but I can’t get anything to load (sometimes the DataFile information is more up to date than the BLS data – It could lag one year behind). Here is the SQL for the query not working:
SELECT DISTINCT DataFile.ContractorName, DataFile.Year1, DataFile.Year2, DataFile.Year3,
IIf([NC]=[NC2] And [DataFile]![Year1]=[tblBLS]![BLSYear],[tblBLS]![TCIR],"") AS BTCIR1,
IIf([NC]=[NC2] And [DataFile]![Year2]=[tblBLS]![BLSYear],[tblBLS]![TCIR],"") AS BTCIR2,
IIf([NC]=[NC2] And [DataFile]![Year3]=[tblBLS]![BLSYear],[tblBLS]![TCIR],"") AS BTCIR3,
Left([DataFile]![NAICS],3) AS nc,
Left([tblBLS]![NAICS],3) AS nc2, *
FROM DataFile, tblBLS
WHERE (((DataFile.ContractorName)=[Forms]![frmReports]![cboContractorName]) AND ((DataFile.Year1)=[tblBLS]![BLSYear]) AND ((Left([DataFile]![NAICS],3))=Left([tblBLS]![NAICS],3) And (Left([DataFile]![NAICS],3))=Left([tblBLS]![NAICS],3) And (Left([DataFile]![NAICS],3))=Left([tblBLS]![NAICS],3)));
I tried changing the AND statements to OR and got more records than I bargained for! I also tried to remove the DataFile.Year requirments all together and again, ended up with beaucoup records. Any help would be appreciated. Thank you.

 

[plog]

Kinda got it, but need sample data to cement it. Can you provide 3 sets of sample data:

A. Sample data from DataFile
B. Sample data from tblBLS
C. What your query should return when you feed it A and B

Be sure to include table and field names and enough data to cover all cases that are giving you issues.

 

[prpmtn]

How about I just attach the whole bloody thing? Here it is...none of this stuff is confidential (I just made up the data in the tables to have stuff to play with). Sorry...forgot to mention that I'm working in A2007. Any help would be greatly appreciated....thank you.

 

[plog]

That's fine for A & B, but I still need C. What do you expect to result from that sample data. I don't want words, don't try and explain it to me, I want data.

 

[prpmtn]

The query should take the input from the user on the "reports" form combo box (contractor name), compare Year1, Year2 and Year3 as well as the first three numbers of the NAICS code in the DataFile (by contractor name) to the BLSYear and first three numbers of the NAICS code in the tblBLS table. If the years and codes match, the query should return the TCIR codes from the tblBLS table associated with those two variables (basically comparing the Contractors data to the BLS data) for each of the three years. Everything works great until the years do not match (such as the Year1 data is not yet updated in the tblBLS). Does that make sense?

 

[plog]

Wow, that is the exact opposite that I asked for.

I don't know how to make myself more clear than I did in post #4. If you need further explanation, see post #2.

 

[prpmtn]

Sorry...I was reading your reply on my phone and mis-read the entire sentence structure. Is this what you mean by "data"?
DataFile.Year1 = 2013,2012,2011,2010
tblBLS.BLSYear = 2012,2011,2010
tblBLS.TCIR = 2.8, 2.9, 0.8, 3.2,1.7
tblBLS.NACIS=111,112,113,115,213,238,252
DataFile.NAICS = 11193, 11364, 21399, 238419, 252384

If DataFile.Year1=tblBLS.BLSYear and left(DataFile.NAICS,3) = left(tlbBLS.NAICS,3) then
BCTIR1=tblBLS.TCIR
BCTIR is the information I need to retrieve. Thank you. Sorry again for the misunderstanding.

 

[plog]

Nope. See post #2.

Think of me as Google maps--provide a starting point (beginning data) and and ending point (expected data) and I will make the route between the two. I don't care that you think you need to drive on main street for 200 yards or that you keep making a wrong left turn just after Cincinnati or that your friend said he crossed a red bridge when he made the trip. All I want is starting data and ending data.

 

[prpmtn]

Thanks for your help, but I have decided to go a different route and validate the data match on entry into the table. This negates the issue with the query. Thanks again.