A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

The Microsoft Jet database engine does not recognise...



 
 
Thread Tools Display Modes
  #1  
Old May 26th, 2010, 10:49 AM posted to microsoft.public.access.queries
SamMexico via AccessMonster.com
external usenet poster
 
Posts: 26
Default The Microsoft Jet database engine does not recognise...

Hi everyone,

I've been helped out on here and managed to get a query working exactly how I
wanted although now I can't seem to generate a chart from said query. The
query below shows the cumulative values over paticular regions:

SELECT Format([Date of Consent],"mmm yyyy") AS Month_Year, Data.Region, Count
(Data.[Date of Consent]) AS CountMonthYear, (SELECT Count([XX].[Date of
Consent]) FROM Data AS [XX] WHERE Format([XX].[Date of Consent],"yyyymm")
=Format([Data].[Date of Consent],"yyyymm")) AS Cumulative
FROM Data
WHERE (((Data.Region) In ("Leicester","Nottingham","Northampton","Kettering ",
"Derby")))
GROUP BY Format([Date of Consent],"mmm yyyy"), Data.Region, Format([Date of
Consent],"yyyymm")
HAVING (((Count(Data.[Date of Consent]))0) AND (((SELECT Count([XX].[Date of
Consent]) FROM Data AS [XX] WHERE Format([XX].[Date of Consent],"yyyymm")
=Format([Data].[Date of Consent],"yyyymm")))0))
ORDER BY Format([Date of Consent],"yyyymm");

But when I create the chart I get this error message: 'The Microsoft Jet
database engine does not recognise '[Data].[Date of Consent]' as a valid
field name or expression''.

I believe thi shas something to do with the query parameters if searching on
here is anything to go by but I'm a novice and need it spelled out ot me.

If anyone could help that would be great

Sam

--
Message posted via http://www.accessmonster.com

  #2  
Old May 26th, 2010, 01:52 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default The Microsoft Jet database engine does not recognise...

SELECT Format([Date of Consent],"mmm yyyy") AS Month_Year
, Data.Region
, Count(Data.[Date of Consent]) AS CountMonthYear
, (SELECT Count([XX].[Date of Consent])
FROM Data AS [XX]
WHERE Format([XX].[Date of Consent],"yyyymm")=
Format([Data].[Date of Consent],"yyyymm")) AS Cumulative
FROM Data
WHERE (((Data.Region) In ("Leicester","Nottingham","Northampton","Kettering ",
"Derby")))
GROUP BY Format([Date of Consent],"mmm yyyy"), Data.Region, Format([Date of
Consent],"yyyymm")
HAVING (((Count(Data.[Date of Consent]))0) AND (((SELECT Count([XX].[Date of
Consent]) FROM Data AS [XX] WHERE Format([XX].[Date of Consent],"yyyymm")
=Format([Data].[Date of Consent],"yyyymm")))0))
ORDER BY Format([Date of Consent],"yyyymm");

The query does not return any column named '[Data].[Date of Consent]'. It
returns columns named Month_Year, Region, CountMonthYear, and Cumulative.

If the chart is based on the query then those are the only "fields" available
to use in the chart.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

SamMexico via AccessMonster.com wrote:
Hi everyone,

I've been helped out on here and managed to get a query working exactly how I
wanted although now I can't seem to generate a chart from said query. The
query below shows the cumulative values over paticular regions:

SELECT Format([Date of Consent],"mmm yyyy") AS Month_Year, Data.Region, Count
(Data.[Date of Consent]) AS CountMonthYear, (SELECT Count([XX].[Date of
Consent]) FROM Data AS [XX] WHERE Format([XX].[Date of Consent],"yyyymm")
=Format([Data].[Date of Consent],"yyyymm")) AS Cumulative
FROM Data
WHERE (((Data.Region) In ("Leicester","Nottingham","Northampton","Kettering ",
"Derby")))
GROUP BY Format([Date of Consent],"mmm yyyy"), Data.Region, Format([Date of
Consent],"yyyymm")
HAVING (((Count(Data.[Date of Consent]))0) AND (((SELECT Count([XX].[Date of
Consent]) FROM Data AS [XX] WHERE Format([XX].[Date of Consent],"yyyymm")
=Format([Data].[Date of Consent],"yyyymm")))0))
ORDER BY Format([Date of Consent],"yyyymm");

But when I create the chart I get this error message: 'The Microsoft Jet
database engine does not recognise '[Data].[Date of Consent]' as a valid
field name or expression''.

I believe thi shas something to do with the query parameters if searching on
here is anything to go by but I'm a novice and need it spelled out ot me.

If anyone could help that would be great

Sam

  #3  
Old May 27th, 2010, 10:00 AM posted to microsoft.public.access.queries
SamMexico via AccessMonster.com
external usenet poster
 
Posts: 26
Default The Microsoft Jet database engine does not recognise...

Forgive my ignorance but how would I produce a bar chart that has the month
and year along the x-axis and the number of entries along the y-axis? There
would be 3 bars within each month; one for Nottingham, one for Leicester and
one for the Total.

I have created this graph in Excel but I'm just not very good at using Access,
the reason I would like it in Access (2003) is so that it will automatically
update.

Thanks,

Sam

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201005/1

  #4  
Old May 27th, 2010, 10:01 AM posted to microsoft.public.access.queries
SamMexico via AccessMonster.com
external usenet poster
 
Posts: 26
Default The Microsoft Jet database engine does not recognise...

Forgive my ignorance but how would I produce a bar chart that has the month
and year along the x-axis and the number of entries along the y-axis? There
would be 3 bars within each month; one for Nottingham, one for Leicester and
one for the Total.

I have created this graph in Excel but I'm just not very good at using Access,
the reason I would like it in Access (2003) is so that it will automatically
update.

Thanks,

Sam

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201005/1

  #5  
Old May 27th, 2010, 02:49 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default The Microsoft Jet database engine does not recognise...

Sorry, not something I am very familiar with. You might try posting in
reports - Duane Hookom is pretty darn good with all aspects of reporting.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

SamMexico via AccessMonster.com wrote:
Forgive my ignorance but how would I produce a bar chart that has the month
and year along the x-axis and the number of entries along the y-axis? There
would be 3 bars within each month; one for Nottingham, one for Leicester and
one for the Total.

I have created this graph in Excel but I'm just not very good at using Access,
the reason I would like it in Access (2003) is so that it will automatically
update.

Thanks,

Sam

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:39 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.