The Microsoft Jet database engine does not recognise...
in [Queries]
|
From: SamMexico via AccessMonster.com on 26 May 2010 05:49 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
From: John Spencer on 26 May 2010 08:52 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 >
From: SamMexico via AccessMonster.com on 27 May 2010 05:00 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/Forums.aspx/access-queries/201005/1
From: SamMexico via AccessMonster.com on 27 May 2010 05:01 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/Forums.aspx/access-queries/201005/1
From: John Spencer on 27 May 2010 09:49 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 >
|
Pages: 1 Prev: Convert string value to numeric value Next: Shift several columns in table |