query is acting up

[kwooten]

i have a linked table query that is acting up. I updated my table like I always do. I have a query with the linked data and some expressions. That query runs fine. Then I have another query built off this first query with another table attached. I went to run the query and I get a data type mismatch in criteria expression in the second query. I have 1 expression that is an IIF formula based on an expression from the 1st query. If I change expression to group by the query runs but then my report doesnt run.. what is wrong with the query?

[June7]

Mismatch data type. You are trying to join tables on a value generated by an expression? An expression often results in a string value which will cause incompatibility with a number field.

Show the SQL statements for analysis.

[kwooten]

this is the first time i have had a problem with it. My data table has dates so i converted a date to a name and year "MONTH FILLED: MonthName(Month([FILLED])) & " " & DatePart("yyyy",[FILLED])".. In the 2nd query I had an expression "MONTH DATA: IIf([MONTH FILLED]="NOVEMBER 2011",[TTF],Null)".. In a report i was counting the month data (to get occurances in that month) and also avg(month data) to get the average TTF for that particular month.

[kwooten]

query 1:

SELECT TTF.[Job : Cost Center], TTF.[Job : PCN #], TTF.[External Recruiter : Full Name: First Last], TTF.[Internal Recruiter : Full Name: First Last], IIf([Applicant Flow Status]="HIRED INTERNALLY",IIf([Internal Recruiter : Full Name: First Last]="ASSOCIATE RECRUITER",[External Recruiter : Full Name: First Last],[Internal Recruiter : Full Name: First Last]),IIf([External Recruiter : Full Name: First Last]=" ",[Internal Recruiter : Full Name: First Last],IIf([External Recruiter : Full Name: First Last]="NO EXTERNAL",[Internal Recruiter : Full Name: First Last],[External Recruiter : Full Name: First Last]))) AS RECRUITER, TTF.[Applicant Flow Status], TTF.[Time-to-Fill Since Creation (AVG)], TTF.[Last APPROVED], TTF.[Last Offer: Background Check Initiated (Sent to Lexis Nexis)], TTF.[First Hired: Hired / Trigger Feed to Humanic (Phase II)], TTF.[Last Hired: New Hire EFT & W4], TTF.[Last Hired: New Hire Payroll Feed], TTF.[First Hired: Hired Internally], IIf([Applicant Flow Status]="HIRED INTERNALLY",[First Hired: Hired Internally],IIf([Last Offer: Background Check Initiated (Sent to Lexis Nexis)] Is Null,[First Hired: Hired / Trigger Feed to Humanic (Phase II)],[Last Offer: Background Check Initiated (Sent to Lexis Nexis)])) AS FILLED, MonthName(Month([FILLED])) & " " & DatePart("yyyy",[FILLED]) AS [MONTH FILLED], IIf([Applicant Flow Status]="HIRED INTERALLY ",Null,IIf([Last Offer: Background Check Initiated (Sent to Lexis Nexis)] Is Null,[First Hired: Hired / Trigger Feed to Humanic (Phase II)]-[Last APPROVED],[Last Offer: Background Check Initiated (Sent to Lexis Nexis)]-[Last APPROVED])) AS [EXTERNAL TIME TO FILL], IIf([Applicant Flow Status]="HIRED INTERNALLY",[First Hired: Hired Internally]-[Last APPROVED],Null) AS [INTERNAL TIME TO FILL], IIf([Applicant Flow Status]="HIRED INTERNALLY",[First Hired: Hired Internally]-[Last APPROVED],IIf([Last Offer: Background Check Initiated (Sent to Lexis Nexis)] Is Null,[First Hired: Hired / Trigger Feed to Humanic (Phase II)]-[Last APPROVED],[Last Offer: Background Check Initiated (Sent to Lexis Nexis)]-[Last APPROVED])) AS TTF
FROM TTF
GROUP BY TTF.[Job : Cost Center], TTF.[Job : PCN #], TTF.[External Recruiter : Full Name: First Last], TTF.[Internal Recruiter : Full Name: First Last], TTF.[Applicant Flow Status], TTF.[Time-to-Fill Since Creation (AVG)], TTF.[Last APPROVED], TTF.[Last Offer: Background Check Initiated (Sent to Lexis Nexis)], TTF.[First Hired: Hired / Trigger Feed to Humanic (Phase II)], TTF.[Last Hired: New Hire EFT & W4], TTF.[Last Hired: New Hire Payroll Feed], TTF.[First Hired: Hired Internally]
ORDER BY IIf([Applicant Flow Status]="HIRED INTERNALLY",IIf([Internal Recruiter : Full Name: First Last]="ASSOCIATE RECRUITER",[External Recruiter : Full Name: First Last],[Internal Recruiter : Full Name: First Last]),IIf([External Recruiter : Full Name: First Last]=" ",[Internal Recruiter : Full Name: First Last],IIf([External Recruiter : Full Name: First Last]="NO EXTERNAL",[Internal Recruiter : Full Name: First Last],[External Recruiter : Full Name: First Last])));

[kwooten]

query 2:

SELECT [TIME TO FILL].[Job : PCN #], RECRUITERS.[Recruiter Org], [TIME TO FILL].RECRUITER, [TIME TO FILL].[MONTH FILLED], [TIME TO FILL].[EXTERNAL TIME TO FILL], [TIME TO FILL].[INTERNAL TIME TO FILL], [TIME TO FILL].TTF, IIf([MONTH FILLED]="NOVEMBER 2011",[TTF],Null) AS [MONTH DATA]
FROM [TIME TO FILL] LEFT JOIN RECRUITERS ON [TIME TO FILL].RECRUITER = RECRUITERS.[Recruiter ]
GROUP BY [TIME TO FILL].[Job : PCN #], RECRUITERS.[Recruiter Org], [TIME TO FILL].RECRUITER, [TIME TO FILL].[MONTH FILLED], [TIME TO FILL].[EXTERNAL TIME TO FILL], [TIME TO FILL].[INTERNAL TIME TO FILL], [TIME TO FILL].TTF;

[admessing]

Since your post is extrememly hard to read in the previous format...I have cleaned it up for the rest of us to view:

Query 1:

 
SELECT 
TTF.[Job : Cost Center], 
TTF.[Job : PCN #], 
TTF.[External Recruiter : Full Name: First Last], 
TTF.[Internal Recruiter : Full Name: First Last], 
IIf([Applicant Flow Status]="HIRED INTERNALLY",
IIf([Internal Recruiter : Full Name: First Last]="ASSOCIATE RECRUITER",[External Recruiter : Full Name: First Last],[Internal Recruiter : Full Name: First Last]),
IIf([External Recruiter : Full Name: First Last]=" ",[Internal Recruiter : Full Name: First Last],IIf([External Recruiter : Full Name: First Last]="NO EXTERNAL",[Internal Recruiter : Full Name: First Last],[External Recruiter : Full Name: First Last]))) AS RECRUITER, 
TTF.[Applicant Flow Status], TTF.[Time-to-Fill Since Creation (AVG)], TTF.[Last APPROVED], 
TTF.[Last Offer: Background Check Initiated (Sent to Lexis Nexis)], 
TTF.[First Hired: Hired / Trigger Feed to Humanic (Phase II)], 
TTF.[Last Hired: New Hire EFT & W4], 
TTF.[Last Hired: New Hire Payroll Feed], 
TTF.[First Hired: Hired Internally], 
IIf([Applicant Flow Status]="HIRED INTERNALLY",[First Hired: Hired Internally],
IIf([Last Offer: Background Check Initiated (Sent to Lexis Nexis)] Is Null,[First Hired: Hired / Trigger Feed to Humanic (Phase II)],[Last Offer: Background Check Initiated (Sent to Lexis Nexis)])) 
AS FILLED, MonthName(Month([FILLED])) & " " & DatePart("yyyy",[FILLED]) AS [MONTH FILLED], 
IIf([Applicant Flow Status]="HIRED INTERALLY ",Null,IIf([Last Offer: Background Check Initiated (Sent to Lexis Nexis)] Is Null,[First Hired: Hired / Trigger Feed to Humanic (Phase II)]-[Last APPROVED],[Last Offer: Background Check Initiated (Sent to Lexis Nexis)]-[Last APPROVED])) AS [EXTERNAL TIME TO FILL], 
IIf([Applicant Flow Status]="HIRED INTERNALLY",[First Hired: Hired Internally]-[Last APPROVED],Null) AS [INTERNAL TIME TO FILL], 
IIf([Applicant Flow Status]="HIRED INTERNALLY",[First Hired: Hired Internally]-[Last APPROVED],
IIf([Last Offer: Background Check Initiated (Sent to Lexis Nexis)] Is Null,[First Hired: Hired / Trigger Feed to Humanic (Phase II)]-[Last APPROVED],[Last Offer: Background Check Initiated (Sent to Lexis Nexis)]-[Last APPROVED])) AS TTF
FROM TTF
GROUP BY TTF.[Job : Cost Center], 
TTF.[Job : PCN #], 
TTF.[External Recruiter : Full Name: First Last], 
TTF.[Internal Recruiter : Full Name: First Last], 
TTF.[Applicant Flow Status], 
TTF.[Time-to-Fill Since Creation (AVG)], 
TTF.[Last APPROVED], 
TTF.[Last Offer: Background Check Initiated (Sent to Lexis Nexis)], 
TTF.[First Hired: Hired / Trigger Feed to Humanic (Phase II)], 
TTF.[Last Hired: New Hire EFT & W4], 
TTF.[Last Hired: New Hire Payroll Feed], TTF.[First Hired: Hired Internally]
ORDER BY IIf([Applicant Flow Status]="HIRED INTERNALLY",
IIf([Internal Recruiter : Full Name: First Last]="ASSOCIATE RECRUITER",[External Recruiter : Full Name: First Last],[Internal Recruiter : Full Name: First Last]),
IIf([External Recruiter : Full Name: First Last]=" ",[Internal Recruiter : Full Name: First Last],IIf([External Recruiter : Full Name: First Last]="NO EXTERNAL",[Internal Recruiter : Full Name: First Last],[External Recruiter : Full Name: First Last])));

[admessing]

Here is Query 2 Cleaned up:

 
SELECT 
[TIME TO FILL].[Job : PCN #], 
RECRUITERS.[Recruiter Org], 
[TIME TO FILL].RECRUITER, 
[TIME TO FILL].[MONTH FILLED], 
[TIME TO FILL].[EXTERNAL TIME TO FILL], 
[TIME TO FILL].[INTERNAL TIME TO FILL], 
[TIME TO FILL].TTF,
IIf([MONTH FILLED]="NOVEMBER 2011",[TTF],Null) AS [MONTH DATA]
FROM [TIME TO FILL] LEFT JOIN RECRUITERS ON [TIME TO FILL].RECRUITER = RECRUITERS.[Recruiter ]
GROUP BY [TIME TO FILL].[Job : PCN #], 
RECRUITERS.[Recruiter Org], 
[TIME TO FILL].RECRUITER, 
[TIME TO FILL].[MONTH FILLED], 
[TIME TO FILL].[EXTERNAL TIME TO FILL], 
[TIME TO FILL].[INTERNAL TIME TO FILL], 
[TIME TO FILL].TTF;

[kwooten]

The first query runs with no problem... it is the second one giving me the problems. Specifically with the "MONTH DATA" expression.

[kwooten]

I made the IIF statement in query 1 and it still ran fine. I opened query 2, which is based on query 1, and dropped the "MONTH DATA" field into the query and I got the data mismatch again... Why would it work in query 1 but not query 2??

[June7]

I tried to replicate this issue with my data. Can't. The IIf in my second query works fine. Would have to work with your project and data to further analyse issue.

[kwooten]

I have attached a mock of my database.

[kwooten]

anyone have any ideas?

[kwooten]

I changed the TTF query (query 1) to a "Make Table" query. I made the table then redid query 2 and everything ran fine.. Im not sure what is wrong with the normal "Select" queries. Something is wrong with the "Month Filled" field.

[June7]

I don't understand why you have the queries as aggregate (group by) queries. The TTF table has 5383 records. The aggregate Time To Fill query has 5371 records. What is the grouping supposed to accomplish? Make these regular SELECT queries and the mismatch error doesn't happen.

The join of Time to Fill query to the Recruiters table results in 5377 records. Some TTF table records don't have a recruiter so because of the INNER jointype some records are dropped.

Why are you saving names in the TTF table instead of the Recruiter ID? What if you have two Jane Smith? Unlikely but not impossible. The point is that using names as keys is risky.

[kwooten]

they only say "Group By" because of the expressions... i thought.

These queries are Select queries.

I dont want records with no recruiter. This is a recruiter report. We dont have double name recruiters.

How would you do it differently on DB I posted?