If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Database engine cannot find the input table or query
Hi
It would be great if someone can look at this sql please. The query runs and brings back the correct data, but every time I run it, I get the error "The Mic.. Access database engine cannot find the input table or query 'tblReferrals.[Referral Assigned To]'. Make sure it exists...spelled correctly." TRANSFORM Count(tblReferrals.ID) AS CountOfID SELECT tblReferrals.[Referral Assigned To], DCount("tblReferrals.[Service 1] + tblReferrals.[Service 2] + tblReferrals.[Service 3] + tblReferrals.[Service 4]","tblReferrals.[Referral Assigned To]","tblReferrals.[Service 1]=True" & "tblReferrals.[Service 2]=True" & "tblReferrals.[Service 3]=True" & "tblReferrals.[Service 4]=True") FROM tblReferrals GROUP BY tblReferrals.[Referral Assigned To] PIVOT Format([Referral Assigned Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec"); I have spent lots of time searching for answers. I have checked the spelling of the name. But it's a mystery to me why it runs but still has the error. In the results table there is a field created called Expr1 that has no data in it. Thank you -- Brendan Adelaide, Australia Office Professional 2007 on Windows XP I always search the forum before posting a question |
#2
|
|||
|
|||
Database engine cannot find the input table or query
I can't believe the query works because the DCount part in your statement is
wrong. It should be DCount("[Service 1] + [Service 2] + [Service 3] + [Service 4]","tblReferrals","[Service 1]=True And [Service 2]=True And [Service 3]=True And [Service 4]=True") "Brendan" wrote: Hi It would be great if someone can look at this sql please. The query runs and brings back the correct data, but every time I run it, I get the error "The Mic.. Access database engine cannot find the input table or query 'tblReferrals.[Referral Assigned To]'. Make sure it exists...spelled correctly." TRANSFORM Count(tblReferrals.ID) AS CountOfID SELECT tblReferrals.[Referral Assigned To], DCount("tblReferrals.[Service 1] + tblReferrals.[Service 2] + tblReferrals.[Service 3] + tblReferrals.[Service 4]","tblReferrals.[Referral Assigned To]","tblReferrals.[Service 1]=True" & "tblReferrals.[Service 2]=True" & "tblReferrals.[Service 3]=True" & "tblReferrals.[Service 4]=True") FROM tblReferrals GROUP BY tblReferrals.[Referral Assigned To] PIVOT Format([Referral Assigned Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec"); I have spent lots of time searching for answers. I have checked the spelling of the name. But it's a mystery to me why it runs but still has the error. In the results table there is a field created called Expr1 that has no data in it. Thank you -- Brendan Adelaide, Australia Office Professional 2007 on Windows XP I always search the forum before posting a question |
#3
|
|||
|
|||
Database engine cannot find the input table or query
Does this return the proper data? If so, create your crosstab from it as a
named query. SELECT tblReferrals.[Referral Assigned To], DCount("tblReferrals.[Service 1] + tblReferrals.[Service 2] + tblReferrals.[Service 3] + tblReferrals.[Service 4]","tblReferrals.[Referral Assigned To]","tblReferrals.[Service 1]=True" & "tblReferrals.[Service 2]=True" & "tblReferrals.[Service 3]=True" & "tblReferrals.[Service 4]=True") FROM tblReferrals It's probably coming up with the Expr1 as you haven't created an alias for the rather strange DCount. Speaking of which, your data isn't properly normalized. Instead of Service 1, Service 2, etc. across (like a spreadsheet), you should have the data going down - possibly in another table. Ask yourself this: What happens when someone wants to start tracking Service 5? I bet that you'll need to modify many of your queries, forms, and reports! Look how clean things are with a table structure something like below. Notice that you could add all the Services that you want without changing the structure. Referrals ReferralAssignedTo ServiceNumber John 1 John 2 Jim 1 Jane 1 Tom 1 John 3 Select ReferralAssignedTo, Count(ServiceNumber) From Referrals Group By ReferralAssignedTo ; John 3 Jim 1 Jane 1 Tom 1 -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Brendan" wrote: Hi It would be great if someone can look at this sql please. The query runs and brings back the correct data, but every time I run it, I get the error "The Mic.. Access database engine cannot find the input table or query 'tblReferrals.[Referral Assigned To]'. Make sure it exists...spelled correctly." TRANSFORM Count(tblReferrals.ID) AS CountOfID SELECT tblReferrals.[Referral Assigned To], DCount("tblReferrals.[Service 1] + tblReferrals.[Service 2] + tblReferrals.[Service 3] + tblReferrals.[Service 4]","tblReferrals.[Referral Assigned To]","tblReferrals.[Service 1]=True" & "tblReferrals.[Service 2]=True" & "tblReferrals.[Service 3]=True" & "tblReferrals.[Service 4]=True") FROM tblReferrals GROUP BY tblReferrals.[Referral Assigned To] PIVOT Format([Referral Assigned Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec"); I have spent lots of time searching for answers. I have checked the spelling of the name. But it's a mystery to me why it runs but still has the error. In the results table there is a field created called Expr1 that has no data in it. Thank you -- Brendan Adelaide, Australia Office Professional 2007 on Windows XP I always search the forum before posting a question |
#4
|
|||
|
|||
Database engine cannot find the input table or query
In the results table there is a field created called Expr1 that has no data
in it. Your DCount is doing that as it does not have a field name alias like this -- DCount( ......... ) AS Alias_Name -- Build a little, test a little. "Brendan" wrote: Hi It would be great if someone can look at this sql please. The query runs and brings back the correct data, but every time I run it, I get the error "The Mic.. Access database engine cannot find the input table or query 'tblReferrals.[Referral Assigned To]'. Make sure it exists...spelled correctly." TRANSFORM Count(tblReferrals.ID) AS CountOfID SELECT tblReferrals.[Referral Assigned To], DCount("tblReferrals.[Service 1] + tblReferrals.[Service 2] + tblReferrals.[Service 3] + tblReferrals.[Service 4]","tblReferrals.[Referral Assigned To]","tblReferrals.[Service 1]=True" & "tblReferrals.[Service 2]=True" & "tblReferrals.[Service 3]=True" & "tblReferrals.[Service 4]=True") FROM tblReferrals GROUP BY tblReferrals.[Referral Assigned To] PIVOT Format([Referral Assigned Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec"); I have spent lots of time searching for answers. I have checked the spelling of the name. But it's a mystery to me why it runs but still has the error. In the results table there is a field created called Expr1 that has no data in it. Thank you -- Brendan Adelaide, Australia Office Professional 2007 on Windows XP I always search the forum before posting a question |
#5
|
|||
|
|||
Database engine cannot find the input table or query
@Ronaldo Thank you for tidying up the code. I had made it very clunky it
seems and can learn from that. @Jerry Thank you too. The [Service #] fields in the main table are text fields rather than numbers, each single client has up to 3 and never more than 4. It's based on the Contact database template in Access. @Karl Thank you for the reminder about creating an alias. -- Brendan Adelaide, Australia Office Professional 2007 on Windows XP I always search the forum before posting a question "KARL DEWEY" wrote: In the results table there is a field created called Expr1 that has no data in it. Your DCount is doing that as it does not have a field name alias like this -- DCount( ......... ) AS Alias_Name -- Build a little, test a little. "Brendan" wrote: Hi It would be great if someone can look at this sql please. The query runs and brings back the correct data, but every time I run it, I get the error "The Mic.. Access database engine cannot find the input table or query 'tblReferrals.[Referral Assigned To]'. Make sure it exists...spelled correctly." TRANSFORM Count(tblReferrals.ID) AS CountOfID SELECT tblReferrals.[Referral Assigned To], DCount("tblReferrals.[Service 1] + tblReferrals.[Service 2] + tblReferrals.[Service 3] + tblReferrals.[Service 4]","tblReferrals.[Referral Assigned To]","tblReferrals.[Service 1]=True" & "tblReferrals.[Service 2]=True" & "tblReferrals.[Service 3]=True" & "tblReferrals.[Service 4]=True") FROM tblReferrals GROUP BY tblReferrals.[Referral Assigned To] PIVOT Format([Referral Assigned Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec"); I have spent lots of time searching for answers. I have checked the spelling of the name. But it's a mystery to me why it runs but still has the error. In the results table there is a field created called Expr1 that has no data in it. Thank you -- Brendan Adelaide, Australia Office Professional 2007 on Windows XP I always search the forum before posting a question |
Thread Tools | |
Display Modes | |
|
|