The Microsoft Jet database engine does not recognise...

[SamMexico via AccessMonster.com]

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

 

[John Spencer]

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]

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

 

[SamMexico via AccessMonster.com]

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

 

[John Spencer]

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