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 |
#11
|
|||
|
|||
Charts / Queries
I used that in the query and when i went to use the chart wizard that is what
it cam up with. I will just try to paste the sql from the query and just paste it in the sql for the chart to see if that works. What you suggested work just fine it was the chart that gave me someting different. "Duane Hookom" wrote: What happened to my suggestion? SELECT (Format([DATE TO PRINT ROOM],"mmm"" '""yy")) AS MMMYY, Count(*) AS [Count] FROM [BILLING LOG] WHERE [DATE TO PRINT ROOM] Between Forms!date_frm!srtdate AND Forms!date_frm!endDate GROUP BY Format([DATE TO PRINT ROOM],"mmm"" '""yy"), Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1 ORDER BY Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1 -- Duane Hookom Microsoft Access MVP "Erik" wrote: this is the row source for the chart: SELECT [MMMYY],Sum([Count]) AS [SumOfCount] FROM [orders_completed] GROUP BY [MMMYY]; "Duane Hookom" wrote: Check your Row Source property. The order displayed in the datasheet of this sql should be the order the chart displays. If you don't figure this out, come back with the Row Source property. -- Duane Hookom Microsoft Access MVP "Erik" wrote: Ok I got that and understand thanks, but now i have a problem with the chart that i am creating. When I create the chart it still shows the dates out of order, any cues? Thanks so much! "Duane Hookom" wrote: Make sure your form is open with dates entered and change your Row Source query to: SELECT (Format([DATE TO PRINT ROOM],"mmm"" '""yy")) AS MMMYY, Count(*) AS [Count] FROM [BILLING LOG] WHERE [DATE TO PRINT ROOM] Between Forms!date_frm!srtdate AND Forms!date_frm!endDate GROUP BY Format([DATE TO PRINT ROOM],"mmm"" '""yy"), Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1 ORDER BY Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1; This assumes your field name is [Date To Print Room]. -- Duane Hookom Microsoft Access MVP "Erik" wrote: my form: date_frm srtdate enddate my sql: SELECT (Format([DATE TO PRINT ROOM],"mmm"" '""yy")) AS Expr1, Count(*) AS [Count] FROM [BILLING LOG] GROUP BY (Format([DATE TO PRINT ROOM],"mmm"" '""yy")), (Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1) HAVING ((Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1)); "Duane Hookom" wrote: Create your form with text boxes and provide us with the names like: frmSelectDates txtStart txtEnd Provide us with your current SQL view like: Select Format([SomeDateField],"mmm-yyyy"), Count(*) as NumOf From tblYourTable Group By Format([SomeDateField],"mmm-yyyy"), Format([SomeDateField],"yyyymm") Order By Format([SomeDateField],"yyyymm"); -- Duane Hookom Microsoft Access MVP "Erik" wrote: Thank you for your response. I still am very confused. I Have a table [billing log] and I created a query that has the dates from the [billing log] formatted to show the month-year example jan-07 and the second column in the query to count the number of records that fall in that month. I have it showing the right amounts and dates. The problem i am having is how to group by year and month and how to incorperate the text boxes on the form that you wrote back about. "Duane Hookom" wrote: The first task is to create a form with a couple text boxes for you to enter the beginning and ending dates. Then create a query that groups by Year and Month and calculates some value. Then use the report chart wizard based on the query. The Row Source of the chart control is the key to what displays in your chart. -- Duane Hookom Microsoft Access MVP "Erik" wrote: I am trying to create my first chart in a report. I would like to know how to set the data in the x axis to show the month-year example Jan-07. I would like it to ask when to start the first month and the last month and then put it in order of the month and year. I don't know if this makes sense. If not maybe i can try to explain it a different way. Thanks for any help. |
#12
|
|||
|
|||
Charts / Queries
The wizards sometimes muck up your results. That's why you often need to
manually reset some properties especially Row Sources. -- Duane Hookom Microsoft Access MVP "Erik" wrote: I used that in the query and when i went to use the chart wizard that is what it cam up with. I will just try to paste the sql from the query and just paste it in the sql for the chart to see if that works. What you suggested work just fine it was the chart that gave me someting different. "Duane Hookom" wrote: What happened to my suggestion? SELECT (Format([DATE TO PRINT ROOM],"mmm"" '""yy")) AS MMMYY, Count(*) AS [Count] FROM [BILLING LOG] WHERE [DATE TO PRINT ROOM] Between Forms!date_frm!srtdate AND Forms!date_frm!endDate GROUP BY Format([DATE TO PRINT ROOM],"mmm"" '""yy"), Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1 ORDER BY Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1 -- Duane Hookom Microsoft Access MVP "Erik" wrote: this is the row source for the chart: SELECT [MMMYY],Sum([Count]) AS [SumOfCount] FROM [orders_completed] GROUP BY [MMMYY]; "Duane Hookom" wrote: Check your Row Source property. The order displayed in the datasheet of this sql should be the order the chart displays. If you don't figure this out, come back with the Row Source property. -- Duane Hookom Microsoft Access MVP "Erik" wrote: Ok I got that and understand thanks, but now i have a problem with the chart that i am creating. When I create the chart it still shows the dates out of order, any cues? Thanks so much! "Duane Hookom" wrote: Make sure your form is open with dates entered and change your Row Source query to: SELECT (Format([DATE TO PRINT ROOM],"mmm"" '""yy")) AS MMMYY, Count(*) AS [Count] FROM [BILLING LOG] WHERE [DATE TO PRINT ROOM] Between Forms!date_frm!srtdate AND Forms!date_frm!endDate GROUP BY Format([DATE TO PRINT ROOM],"mmm"" '""yy"), Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1 ORDER BY Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1; This assumes your field name is [Date To Print Room]. -- Duane Hookom Microsoft Access MVP "Erik" wrote: my form: date_frm srtdate enddate my sql: SELECT (Format([DATE TO PRINT ROOM],"mmm"" '""yy")) AS Expr1, Count(*) AS [Count] FROM [BILLING LOG] GROUP BY (Format([DATE TO PRINT ROOM],"mmm"" '""yy")), (Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1) HAVING ((Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1)); "Duane Hookom" wrote: Create your form with text boxes and provide us with the names like: frmSelectDates txtStart txtEnd Provide us with your current SQL view like: Select Format([SomeDateField],"mmm-yyyy"), Count(*) as NumOf From tblYourTable Group By Format([SomeDateField],"mmm-yyyy"), Format([SomeDateField],"yyyymm") Order By Format([SomeDateField],"yyyymm"); -- Duane Hookom Microsoft Access MVP "Erik" wrote: Thank you for your response. I still am very confused. I Have a table [billing log] and I created a query that has the dates from the [billing log] formatted to show the month-year example jan-07 and the second column in the query to count the number of records that fall in that month. I have it showing the right amounts and dates. The problem i am having is how to group by year and month and how to incorperate the text boxes on the form that you wrote back about. "Duane Hookom" wrote: The first task is to create a form with a couple text boxes for you to enter the beginning and ending dates. Then create a query that groups by Year and Month and calculates some value. Then use the report chart wizard based on the query. The Row Source of the chart control is the key to what displays in your chart. -- Duane Hookom Microsoft Access MVP "Erik" wrote: I am trying to create my first chart in a report. I would like to know how to set the data in the x axis to show the month-year example Jan-07. I would like it to ask when to start the first month and the last month and then put it in order of the month and year. I don't know if this makes sense. If not maybe i can try to explain it a different way. Thanks for any help. |
#13
|
|||
|
|||
Charts / Queries
Thanks so much it works great. Just one more thing. I don't think that the
date group by and order by are correct. If i type in the start date on the form as 1/1/07 and the end date of 12/31/07 it starts feb 07 it does not show jan. I guess i do not need to create the query i just need to create the sql in the chart. Thanks again! you were a big help. "Duane Hookom" wrote: The wizards sometimes muck up your results. That's why you often need to manually reset some properties especially Row Sources. -- Duane Hookom Microsoft Access MVP "Erik" wrote: I used that in the query and when i went to use the chart wizard that is what it cam up with. I will just try to paste the sql from the query and just paste it in the sql for the chart to see if that works. What you suggested work just fine it was the chart that gave me someting different. "Duane Hookom" wrote: What happened to my suggestion? SELECT (Format([DATE TO PRINT ROOM],"mmm"" '""yy")) AS MMMYY, Count(*) AS [Count] FROM [BILLING LOG] WHERE [DATE TO PRINT ROOM] Between Forms!date_frm!srtdate AND Forms!date_frm!endDate GROUP BY Format([DATE TO PRINT ROOM],"mmm"" '""yy"), Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1 ORDER BY Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1 -- Duane Hookom Microsoft Access MVP "Erik" wrote: this is the row source for the chart: SELECT [MMMYY],Sum([Count]) AS [SumOfCount] FROM [orders_completed] GROUP BY [MMMYY]; "Duane Hookom" wrote: Check your Row Source property. The order displayed in the datasheet of this sql should be the order the chart displays. If you don't figure this out, come back with the Row Source property. -- Duane Hookom Microsoft Access MVP "Erik" wrote: Ok I got that and understand thanks, but now i have a problem with the chart that i am creating. When I create the chart it still shows the dates out of order, any cues? Thanks so much! "Duane Hookom" wrote: Make sure your form is open with dates entered and change your Row Source query to: SELECT (Format([DATE TO PRINT ROOM],"mmm"" '""yy")) AS MMMYY, Count(*) AS [Count] FROM [BILLING LOG] WHERE [DATE TO PRINT ROOM] Between Forms!date_frm!srtdate AND Forms!date_frm!endDate GROUP BY Format([DATE TO PRINT ROOM],"mmm"" '""yy"), Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1 ORDER BY Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1; This assumes your field name is [Date To Print Room]. -- Duane Hookom Microsoft Access MVP "Erik" wrote: my form: date_frm srtdate enddate my sql: SELECT (Format([DATE TO PRINT ROOM],"mmm"" '""yy")) AS Expr1, Count(*) AS [Count] FROM [BILLING LOG] GROUP BY (Format([DATE TO PRINT ROOM],"mmm"" '""yy")), (Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1) HAVING ((Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1)); "Duane Hookom" wrote: Create your form with text boxes and provide us with the names like: frmSelectDates txtStart txtEnd Provide us with your current SQL view like: Select Format([SomeDateField],"mmm-yyyy"), Count(*) as NumOf From tblYourTable Group By Format([SomeDateField],"mmm-yyyy"), Format([SomeDateField],"yyyymm") Order By Format([SomeDateField],"yyyymm"); -- Duane Hookom Microsoft Access MVP "Erik" wrote: Thank you for your response. I still am very confused. I Have a table [billing log] and I created a query that has the dates from the [billing log] formatted to show the month-year example jan-07 and the second column in the query to count the number of records that fall in that month. I have it showing the right amounts and dates. The problem i am having is how to group by year and month and how to incorperate the text boxes on the form that you wrote back about. "Duane Hookom" wrote: The first task is to create a form with a couple text boxes for you to enter the beginning and ending dates. Then create a query that groups by Year and Month and calculates some value. Then use the report chart wizard based on the query. The Row Source of the chart control is the key to what displays in your chart. -- Duane Hookom Microsoft Access MVP "Erik" wrote: I am trying to create my first chart in a report. I would like to know how to set the data in the x axis to show the month-year example Jan-07. I would like it to ask when to start the first month and the last month and then put it in order of the month and year. I don't know if this makes sense. If not maybe i can try to explain it a different way. Thanks for any help. |
#14
|
|||
|
|||
Charts / Queries
I'm not sure from your reply if you need more assistance. If you do, come
back with your actual Row Source etc. -- Duane Hookom Microsoft Access MVP "Erik" wrote: Thanks so much it works great. Just one more thing. I don't think that the date group by and order by are correct. If i type in the start date on the form as 1/1/07 and the end date of 12/31/07 it starts feb 07 it does not show jan. I guess i do not need to create the query i just need to create the sql in the chart. Thanks again! you were a big help. "Duane Hookom" wrote: The wizards sometimes muck up your results. That's why you often need to manually reset some properties especially Row Sources. -- Duane Hookom Microsoft Access MVP "Erik" wrote: I used that in the query and when i went to use the chart wizard that is what it cam up with. I will just try to paste the sql from the query and just paste it in the sql for the chart to see if that works. What you suggested work just fine it was the chart that gave me someting different. "Duane Hookom" wrote: What happened to my suggestion? SELECT (Format([DATE TO PRINT ROOM],"mmm"" '""yy")) AS MMMYY, Count(*) AS [Count] FROM [BILLING LOG] WHERE [DATE TO PRINT ROOM] Between Forms!date_frm!srtdate AND Forms!date_frm!endDate GROUP BY Format([DATE TO PRINT ROOM],"mmm"" '""yy"), Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1 ORDER BY Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1 -- Duane Hookom Microsoft Access MVP "Erik" wrote: this is the row source for the chart: SELECT [MMMYY],Sum([Count]) AS [SumOfCount] FROM [orders_completed] GROUP BY [MMMYY]; "Duane Hookom" wrote: Check your Row Source property. The order displayed in the datasheet of this sql should be the order the chart displays. If you don't figure this out, come back with the Row Source property. -- Duane Hookom Microsoft Access MVP "Erik" wrote: Ok I got that and understand thanks, but now i have a problem with the chart that i am creating. When I create the chart it still shows the dates out of order, any cues? Thanks so much! "Duane Hookom" wrote: Make sure your form is open with dates entered and change your Row Source query to: SELECT (Format([DATE TO PRINT ROOM],"mmm"" '""yy")) AS MMMYY, Count(*) AS [Count] FROM [BILLING LOG] WHERE [DATE TO PRINT ROOM] Between Forms!date_frm!srtdate AND Forms!date_frm!endDate GROUP BY Format([DATE TO PRINT ROOM],"mmm"" '""yy"), Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1 ORDER BY Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1; This assumes your field name is [Date To Print Room]. -- Duane Hookom Microsoft Access MVP "Erik" wrote: my form: date_frm srtdate enddate my sql: SELECT (Format([DATE TO PRINT ROOM],"mmm"" '""yy")) AS Expr1, Count(*) AS [Count] FROM [BILLING LOG] GROUP BY (Format([DATE TO PRINT ROOM],"mmm"" '""yy")), (Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1) HAVING ((Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1)); "Duane Hookom" wrote: Create your form with text boxes and provide us with the names like: frmSelectDates txtStart txtEnd Provide us with your current SQL view like: Select Format([SomeDateField],"mmm-yyyy"), Count(*) as NumOf From tblYourTable Group By Format([SomeDateField],"mmm-yyyy"), Format([SomeDateField],"yyyymm") Order By Format([SomeDateField],"yyyymm"); -- Duane Hookom Microsoft Access MVP "Erik" wrote: Thank you for your response. I still am very confused. I Have a table [billing log] and I created a query that has the dates from the [billing log] formatted to show the month-year example jan-07 and the second column in the query to count the number of records that fall in that month. I have it showing the right amounts and dates. The problem i am having is how to group by year and month and how to incorperate the text boxes on the form that you wrote back about. "Duane Hookom" wrote: The first task is to create a form with a couple text boxes for you to enter the beginning and ending dates. Then create a query that groups by Year and Month and calculates some value. Then use the report chart wizard based on the query. The Row Source of the chart control is the key to what displays in your chart. -- Duane Hookom Microsoft Access MVP "Erik" wrote: I am trying to create my first chart in a report. I would like to know how to set the data in the x axis to show the month-year example Jan-07. I would like it to ask when to start the first month and the last month and then put it in order of the month and year. I don't know if this makes sense. If not maybe i can try to explain it a different way. Thanks for any help. |
#15
|
|||
|
|||
Charts / Queries
Yes I have a couple of things that have happen over lunch that i would like
to ask you. 1) this is the row source for my chart: SELECT (Format([DATE TO PRINT ROOM],"mmm"" '""yy")) AS MMMYY, Count(*) AS [Count] FROM [BILLING LOG] WHERE ((([BILLING LOG].[DATE TO PRINT ROOM]) Between [Forms]![date_frm]![srtdate] And [Forms]![date_frm]![endDate])) GROUP BY Format([DATE TO PRINT ROOM],"mmm"" '""yy"), Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1 ORDER BY Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1; When i enter the dates in the form that I created and open the report to view the chart it is missing the first month that i type in. 2.when the chart opens the pages keep going and going and going. It seems that when you go to the next page it repeats the query over and shows the same chart. 3. the last thing i wanted to ask you was instead of using the form to enter the dates can i have it ask you for the dates like a perameter? "Duane Hookom" wrote: I'm not sure from your reply if you need more assistance. If you do, come back with your actual Row Source etc. -- Duane Hookom Microsoft Access MVP "Erik" wrote: Thanks so much it works great. Just one more thing. I don't think that the date group by and order by are correct. If i type in the start date on the form as 1/1/07 and the end date of 12/31/07 it starts feb 07 it does not show jan. I guess i do not need to create the query i just need to create the sql in the chart. Thanks again! you were a big help. "Duane Hookom" wrote: The wizards sometimes muck up your results. That's why you often need to manually reset some properties especially Row Sources. -- Duane Hookom Microsoft Access MVP "Erik" wrote: I used that in the query and when i went to use the chart wizard that is what it cam up with. I will just try to paste the sql from the query and just paste it in the sql for the chart to see if that works. What you suggested work just fine it was the chart that gave me someting different. "Duane Hookom" wrote: What happened to my suggestion? SELECT (Format([DATE TO PRINT ROOM],"mmm"" '""yy")) AS MMMYY, Count(*) AS [Count] FROM [BILLING LOG] WHERE [DATE TO PRINT ROOM] Between Forms!date_frm!srtdate AND Forms!date_frm!endDate GROUP BY Format([DATE TO PRINT ROOM],"mmm"" '""yy"), Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1 ORDER BY Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1 -- Duane Hookom Microsoft Access MVP "Erik" wrote: this is the row source for the chart: SELECT [MMMYY],Sum([Count]) AS [SumOfCount] FROM [orders_completed] GROUP BY [MMMYY]; "Duane Hookom" wrote: Check your Row Source property. The order displayed in the datasheet of this sql should be the order the chart displays. If you don't figure this out, come back with the Row Source property. -- Duane Hookom Microsoft Access MVP "Erik" wrote: Ok I got that and understand thanks, but now i have a problem with the chart that i am creating. When I create the chart it still shows the dates out of order, any cues? Thanks so much! "Duane Hookom" wrote: Make sure your form is open with dates entered and change your Row Source query to: SELECT (Format([DATE TO PRINT ROOM],"mmm"" '""yy")) AS MMMYY, Count(*) AS [Count] FROM [BILLING LOG] WHERE [DATE TO PRINT ROOM] Between Forms!date_frm!srtdate AND Forms!date_frm!endDate GROUP BY Format([DATE TO PRINT ROOM],"mmm"" '""yy"), Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1 ORDER BY Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1; This assumes your field name is [Date To Print Room]. -- Duane Hookom Microsoft Access MVP "Erik" wrote: my form: date_frm srtdate enddate my sql: SELECT (Format([DATE TO PRINT ROOM],"mmm"" '""yy")) AS Expr1, Count(*) AS [Count] FROM [BILLING LOG] GROUP BY (Format([DATE TO PRINT ROOM],"mmm"" '""yy")), (Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1) HAVING ((Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1)); "Duane Hookom" wrote: Create your form with text boxes and provide us with the names like: frmSelectDates txtStart txtEnd Provide us with your current SQL view like: Select Format([SomeDateField],"mmm-yyyy"), Count(*) as NumOf From tblYourTable Group By Format([SomeDateField],"mmm-yyyy"), Format([SomeDateField],"yyyymm") Order By Format([SomeDateField],"yyyymm"); -- Duane Hookom Microsoft Access MVP "Erik" wrote: Thank you for your response. I still am very confused. I Have a table [billing log] and I created a query that has the dates from the [billing log] formatted to show the month-year example jan-07 and the second column in the query to count the number of records that fall in that month. I have it showing the right amounts and dates. The problem i am having is how to group by year and month and how to incorperate the text boxes on the form that you wrote back about. "Duane Hookom" wrote: The first task is to create a form with a couple text boxes for you to enter the beginning and ending dates. Then create a query that groups by Year and Month and calculates some value. Then use the report chart wizard based on the query. The Row Source of the chart control is the key to what displays in your chart. -- Duane Hookom Microsoft Access MVP "Erik" wrote: I am trying to create my first chart in a report. I would like to know how to set the data in the x axis to show the month-year example Jan-07. I would like it to ask when to start the first month and the last month and then put it in order of the month and year. I don't know if this makes sense. If not maybe i can try to explain it a different way. Thanks for any help. |
#16
|
|||
|
|||
Charts / Queries
1) Are you using American date formats? Is [DATE TO PRINT ROOM] a date field?
2) I expect you have a record source specified for your report that you can remove if you only want the single chart 3) I won't let you use parameter prompts ;-) -- Duane Hookom Microsoft Access MVP "Erik" wrote: Yes I have a couple of things that have happen over lunch that i would like to ask you. 1) this is the row source for my chart: SELECT (Format([DATE TO PRINT ROOM],"mmm"" '""yy")) AS MMMYY, Count(*) AS [Count] FROM [BILLING LOG] WHERE ((([BILLING LOG].[DATE TO PRINT ROOM]) Between [Forms]![date_frm]![srtdate] And [Forms]![date_frm]![endDate])) GROUP BY Format([DATE TO PRINT ROOM],"mmm"" '""yy"), Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1 ORDER BY Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1; When i enter the dates in the form that I created and open the report to view the chart it is missing the first month that i type in. 2.when the chart opens the pages keep going and going and going. It seems that when you go to the next page it repeats the query over and shows the same chart. 3. the last thing i wanted to ask you was instead of using the form to enter the dates can i have it ask you for the dates like a perameter? "Duane Hookom" wrote: I'm not sure from your reply if you need more assistance. If you do, come back with your actual Row Source etc. -- Duane Hookom Microsoft Access MVP "Erik" wrote: Thanks so much it works great. Just one more thing. I don't think that the date group by and order by are correct. If i type in the start date on the form as 1/1/07 and the end date of 12/31/07 it starts feb 07 it does not show jan. I guess i do not need to create the query i just need to create the sql in the chart. Thanks again! you were a big help. "Duane Hookom" wrote: The wizards sometimes muck up your results. That's why you often need to manually reset some properties especially Row Sources. -- Duane Hookom Microsoft Access MVP "Erik" wrote: I used that in the query and when i went to use the chart wizard that is what it cam up with. I will just try to paste the sql from the query and just paste it in the sql for the chart to see if that works. What you suggested work just fine it was the chart that gave me someting different. "Duane Hookom" wrote: What happened to my suggestion? SELECT (Format([DATE TO PRINT ROOM],"mmm"" '""yy")) AS MMMYY, Count(*) AS [Count] FROM [BILLING LOG] WHERE [DATE TO PRINT ROOM] Between Forms!date_frm!srtdate AND Forms!date_frm!endDate GROUP BY Format([DATE TO PRINT ROOM],"mmm"" '""yy"), Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1 ORDER BY Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1 -- Duane Hookom Microsoft Access MVP "Erik" wrote: this is the row source for the chart: SELECT [MMMYY],Sum([Count]) AS [SumOfCount] FROM [orders_completed] GROUP BY [MMMYY]; "Duane Hookom" wrote: Check your Row Source property. The order displayed in the datasheet of this sql should be the order the chart displays. If you don't figure this out, come back with the Row Source property. -- Duane Hookom Microsoft Access MVP "Erik" wrote: Ok I got that and understand thanks, but now i have a problem with the chart that i am creating. When I create the chart it still shows the dates out of order, any cues? Thanks so much! "Duane Hookom" wrote: Make sure your form is open with dates entered and change your Row Source query to: SELECT (Format([DATE TO PRINT ROOM],"mmm"" '""yy")) AS MMMYY, Count(*) AS [Count] FROM [BILLING LOG] WHERE [DATE TO PRINT ROOM] Between Forms!date_frm!srtdate AND Forms!date_frm!endDate GROUP BY Format([DATE TO PRINT ROOM],"mmm"" '""yy"), Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1 ORDER BY Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1; This assumes your field name is [Date To Print Room]. -- Duane Hookom Microsoft Access MVP "Erik" wrote: my form: date_frm srtdate enddate my sql: SELECT (Format([DATE TO PRINT ROOM],"mmm"" '""yy")) AS Expr1, Count(*) AS [Count] FROM [BILLING LOG] GROUP BY (Format([DATE TO PRINT ROOM],"mmm"" '""yy")), (Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1) HAVING ((Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1)); "Duane Hookom" wrote: Create your form with text boxes and provide us with the names like: frmSelectDates txtStart txtEnd Provide us with your current SQL view like: Select Format([SomeDateField],"mmm-yyyy"), Count(*) as NumOf From tblYourTable Group By Format([SomeDateField],"mmm-yyyy"), Format([SomeDateField],"yyyymm") Order By Format([SomeDateField],"yyyymm"); -- Duane Hookom Microsoft Access MVP "Erik" wrote: Thank you for your response. I still am very confused. I Have a table [billing log] and I created a query that has the dates from the [billing log] formatted to show the month-year example jan-07 and the second column in the query to count the number of records that fall in that month. I have it showing the right amounts and dates. The problem i am having is how to group by year and month and how to incorperate the text boxes on the form that you wrote back about. "Duane Hookom" wrote: The first task is to create a form with a couple text boxes for you to enter the beginning and ending dates. Then create a query that groups by Year and Month and calculates some value. Then use the report chart wizard based on the query. The Row Source of the chart control is the key to what displays in your chart. -- Duane Hookom Microsoft Access MVP "Erik" wrote: I am trying to create my first chart in a report. I would like to know how to set the data in the x axis to show the month-year example Jan-07. I would like it to ask when to start the first month and the last month and then put it in order of the month and year. I don't know if this makes sense. If not maybe i can try to explain it a different way. Thanks for any help. |
#17
|
|||
|
|||
Charts / Queries
I got it to work! I tried creating the chart on a form instead of the report
and it works just fine now. I will stick with that for now. I really appreciate all that you have done for me. Thanks a bunch the answers: 1)yes 2)yes 3)darne I am not sure why but i trust you. "Duane Hookom" wrote: 1) Are you using American date formats? Is [DATE TO PRINT ROOM] a date field? 2) I expect you have a record source specified for your report that you can remove if you only want the single chart 3) I won't let you use parameter prompts ;-) -- Duane Hookom Microsoft Access MVP "Erik" wrote: Yes I have a couple of things that have happen over lunch that i would like to ask you. 1) this is the row source for my chart: SELECT (Format([DATE TO PRINT ROOM],"mmm"" '""yy")) AS MMMYY, Count(*) AS [Count] FROM [BILLING LOG] WHERE ((([BILLING LOG].[DATE TO PRINT ROOM]) Between [Forms]![date_frm]![srtdate] And [Forms]![date_frm]![endDate])) GROUP BY Format([DATE TO PRINT ROOM],"mmm"" '""yy"), Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1 ORDER BY Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1; When i enter the dates in the form that I created and open the report to view the chart it is missing the first month that i type in. 2.when the chart opens the pages keep going and going and going. It seems that when you go to the next page it repeats the query over and shows the same chart. 3. the last thing i wanted to ask you was instead of using the form to enter the dates can i have it ask you for the dates like a perameter? "Duane Hookom" wrote: I'm not sure from your reply if you need more assistance. If you do, come back with your actual Row Source etc. -- Duane Hookom Microsoft Access MVP "Erik" wrote: Thanks so much it works great. Just one more thing. I don't think that the date group by and order by are correct. If i type in the start date on the form as 1/1/07 and the end date of 12/31/07 it starts feb 07 it does not show jan. I guess i do not need to create the query i just need to create the sql in the chart. Thanks again! you were a big help. "Duane Hookom" wrote: The wizards sometimes muck up your results. That's why you often need to manually reset some properties especially Row Sources. -- Duane Hookom Microsoft Access MVP "Erik" wrote: I used that in the query and when i went to use the chart wizard that is what it cam up with. I will just try to paste the sql from the query and just paste it in the sql for the chart to see if that works. What you suggested work just fine it was the chart that gave me someting different. "Duane Hookom" wrote: What happened to my suggestion? SELECT (Format([DATE TO PRINT ROOM],"mmm"" '""yy")) AS MMMYY, Count(*) AS [Count] FROM [BILLING LOG] WHERE [DATE TO PRINT ROOM] Between Forms!date_frm!srtdate AND Forms!date_frm!endDate GROUP BY Format([DATE TO PRINT ROOM],"mmm"" '""yy"), Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1 ORDER BY Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1 -- Duane Hookom Microsoft Access MVP "Erik" wrote: this is the row source for the chart: SELECT [MMMYY],Sum([Count]) AS [SumOfCount] FROM [orders_completed] GROUP BY [MMMYY]; "Duane Hookom" wrote: Check your Row Source property. The order displayed in the datasheet of this sql should be the order the chart displays. If you don't figure this out, come back with the Row Source property. -- Duane Hookom Microsoft Access MVP "Erik" wrote: Ok I got that and understand thanks, but now i have a problem with the chart that i am creating. When I create the chart it still shows the dates out of order, any cues? Thanks so much! "Duane Hookom" wrote: Make sure your form is open with dates entered and change your Row Source query to: SELECT (Format([DATE TO PRINT ROOM],"mmm"" '""yy")) AS MMMYY, Count(*) AS [Count] FROM [BILLING LOG] WHERE [DATE TO PRINT ROOM] Between Forms!date_frm!srtdate AND Forms!date_frm!endDate GROUP BY Format([DATE TO PRINT ROOM],"mmm"" '""yy"), Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1 ORDER BY Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1; This assumes your field name is [Date To Print Room]. -- Duane Hookom Microsoft Access MVP "Erik" wrote: my form: date_frm srtdate enddate my sql: SELECT (Format([DATE TO PRINT ROOM],"mmm"" '""yy")) AS Expr1, Count(*) AS [Count] FROM [BILLING LOG] GROUP BY (Format([DATE TO PRINT ROOM],"mmm"" '""yy")), (Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1) HAVING ((Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1)); "Duane Hookom" wrote: Create your form with text boxes and provide us with the names like: frmSelectDates txtStart txtEnd Provide us with your current SQL view like: Select Format([SomeDateField],"mmm-yyyy"), Count(*) as NumOf From tblYourTable Group By Format([SomeDateField],"mmm-yyyy"), Format([SomeDateField],"yyyymm") Order By Format([SomeDateField],"yyyymm"); -- Duane Hookom Microsoft Access MVP "Erik" wrote: Thank you for your response. I still am very confused. I Have a table [billing log] and I created a query that has the dates from the [billing log] formatted to show the month-year example jan-07 and the second column in the query to count the number of records that fall in that month. I have it showing the right amounts and dates. The problem i am having is how to group by year and month and how to incorperate the text boxes on the form that you wrote back about. "Duane Hookom" wrote: The first task is to create a form with a couple text boxes for you to enter the beginning and ending dates. Then create a query that groups by Year and Month and calculates some value. Then use the report chart wizard based on the query. The Row Source of the chart control is the key to what displays in your chart. -- Duane Hookom Microsoft Access MVP "Erik" wrote: I am trying to create my first chart in a report. I would like to know how to set the data in the x axis to show the month-year example Jan-07. I would like it to ask when to start the first month and the last month and then put it in order of the month and year. I don't know if this makes sense. If not maybe i can try to explain it a different way. Thanks for any help. |
#18
|
|||
|
|||
Charts / Queries
Parameter prompts:
-Don't allow default values -Can't use combo or list boxes -Can only be seen one at at time -Can't be tested for validity -Aren't retained for another usage (another report or query) -Won't be found in most well-constructed application -- Duane Hookom Microsoft Access MVP "Erik" wrote: I got it to work! I tried creating the chart on a form instead of the report and it works just fine now. I will stick with that for now. I really appreciate all that you have done for me. Thanks a bunch the answers: 1)yes 2)yes 3)darne I am not sure why but i trust you. "Duane Hookom" wrote: 1) Are you using American date formats? Is [DATE TO PRINT ROOM] a date field? 2) I expect you have a record source specified for your report that you can remove if you only want the single chart 3) I won't let you use parameter prompts ;-) -- Duane Hookom Microsoft Access MVP "Erik" wrote: Yes I have a couple of things that have happen over lunch that i would like to ask you. 1) this is the row source for my chart: SELECT (Format([DATE TO PRINT ROOM],"mmm"" '""yy")) AS MMMYY, Count(*) AS [Count] FROM [BILLING LOG] WHERE ((([BILLING LOG].[DATE TO PRINT ROOM]) Between [Forms]![date_frm]![srtdate] And [Forms]![date_frm]![endDate])) GROUP BY Format([DATE TO PRINT ROOM],"mmm"" '""yy"), Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1 ORDER BY Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1; When i enter the dates in the form that I created and open the report to view the chart it is missing the first month that i type in. 2.when the chart opens the pages keep going and going and going. It seems that when you go to the next page it repeats the query over and shows the same chart. 3. the last thing i wanted to ask you was instead of using the form to enter the dates can i have it ask you for the dates like a perameter? "Duane Hookom" wrote: I'm not sure from your reply if you need more assistance. If you do, come back with your actual Row Source etc. -- Duane Hookom Microsoft Access MVP "Erik" wrote: Thanks so much it works great. Just one more thing. I don't think that the date group by and order by are correct. If i type in the start date on the form as 1/1/07 and the end date of 12/31/07 it starts feb 07 it does not show jan. I guess i do not need to create the query i just need to create the sql in the chart. Thanks again! you were a big help. "Duane Hookom" wrote: The wizards sometimes muck up your results. That's why you often need to manually reset some properties especially Row Sources. -- Duane Hookom Microsoft Access MVP "Erik" wrote: I used that in the query and when i went to use the chart wizard that is what it cam up with. I will just try to paste the sql from the query and just paste it in the sql for the chart to see if that works. What you suggested work just fine it was the chart that gave me someting different. "Duane Hookom" wrote: What happened to my suggestion? SELECT (Format([DATE TO PRINT ROOM],"mmm"" '""yy")) AS MMMYY, Count(*) AS [Count] FROM [BILLING LOG] WHERE [DATE TO PRINT ROOM] Between Forms!date_frm!srtdate AND Forms!date_frm!endDate GROUP BY Format([DATE TO PRINT ROOM],"mmm"" '""yy"), Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1 ORDER BY Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1 -- Duane Hookom Microsoft Access MVP "Erik" wrote: this is the row source for the chart: SELECT [MMMYY],Sum([Count]) AS [SumOfCount] FROM [orders_completed] GROUP BY [MMMYY]; "Duane Hookom" wrote: Check your Row Source property. The order displayed in the datasheet of this sql should be the order the chart displays. If you don't figure this out, come back with the Row Source property. -- Duane Hookom Microsoft Access MVP "Erik" wrote: Ok I got that and understand thanks, but now i have a problem with the chart that i am creating. When I create the chart it still shows the dates out of order, any cues? Thanks so much! "Duane Hookom" wrote: Make sure your form is open with dates entered and change your Row Source query to: SELECT (Format([DATE TO PRINT ROOM],"mmm"" '""yy")) AS MMMYY, Count(*) AS [Count] FROM [BILLING LOG] WHERE [DATE TO PRINT ROOM] Between Forms!date_frm!srtdate AND Forms!date_frm!endDate GROUP BY Format([DATE TO PRINT ROOM],"mmm"" '""yy"), Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1 ORDER BY Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1; This assumes your field name is [Date To Print Room]. -- Duane Hookom Microsoft Access MVP "Erik" wrote: my form: date_frm srtdate enddate my sql: SELECT (Format([DATE TO PRINT ROOM],"mmm"" '""yy")) AS Expr1, Count(*) AS [Count] FROM [BILLING LOG] GROUP BY (Format([DATE TO PRINT ROOM],"mmm"" '""yy")), (Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1) HAVING ((Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1)); "Duane Hookom" wrote: Create your form with text boxes and provide us with the names like: frmSelectDates txtStart txtEnd Provide us with your current SQL view like: Select Format([SomeDateField],"mmm-yyyy"), Count(*) as NumOf From tblYourTable Group By Format([SomeDateField],"mmm-yyyy"), Format([SomeDateField],"yyyymm") Order By Format([SomeDateField],"yyyymm"); -- Duane Hookom Microsoft Access MVP "Erik" wrote: Thank you for your response. I still am very confused. I Have a table [billing log] and I created a query that has the dates from the [billing log] formatted to show the month-year example jan-07 and the second column in the query to count the number of records that fall in that month. I have it showing the right amounts and dates. The problem i am having is how to group by year and month and how to incorperate the text boxes on the form that you wrote back about. "Duane Hookom" wrote: The first task is to create a form with a couple text boxes for you to enter the beginning and ending dates. Then create a query that groups by Year and Month and calculates some value. Then use the report chart wizard based on the query. The Row Source of the chart control is the key to what displays in your chart. -- Duane Hookom Microsoft Access MVP "Erik" wrote: I am trying to create my first chart in a report. I would like to know how to set the data in the x axis to show the month-year example Jan-07. I would like it to ask when to start the first month and the last month and then put it in order of the month and year. I don't know if this makes sense. If not maybe i can try to explain it a different way. Thanks for any help. |
|
Thread Tools | |
Display Modes | |
|
|