Database engine cannot find the input table or query

[Brendan]

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

 

[RonaldoOneNil]

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")

 

[Jerry Whittle]

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.

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

 

[KARL DEWEY]

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

 

[Brendan]

@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

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.

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