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 |
#1
|
|||
|
|||
Chart monthly...Help Needed Please....
Dear Duane Hookom ..yes it is working.....i should thank you...
so...Thank you very much..... Regards Ismail "Duane Hookom" wrote: Set the join property to include all the records from your recordset of all dates. Set the criteria under the AllDates field to limit the dates to your desired output. -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... O..Very interesting your Query...really useful....not only for my present problem... but i am sorry to ask too much from u...... now i have my query which gives me data(upto 14 days,total sales amount) and ur query which qives me..all the dates...could u please give me some help how can i make query..which gives me all the dates in month..but total sales amount will be upto 14 days...i mean remaining 16 days i will have empty..in total sales amount field...... thanks "Duane Hookom" wrote: Creating a table of all dates isn't that difficult. If you want to use a table with only 10 records: Create a table "tblOneToNine" with a single field "Num" of numeric data type. Enter in values 0, 1, 2, 3, ...9 Create a query with sql of: SELECT Date()- ([tblOneToNine].[Num]*1000+ [tblOneToNine_1].[Num]*100+ [tblOneToNine_2].[Num]*10+ [tblOneToNine_3].[Num]) AS AllDates FROM tblOneToNine, tblOneToNine AS tblOneToNine_1, tblOneToNine AS tblOneToNine_2, tblOneToNine AS tblOneToNine_3; You can save this query which will result in all dates from 9999 days ago to today. -- Duane Hookom MS Access MVP "ismail" wrote in message ... Please Please.......i see the monthly chart for all months..of 2003, 2004,2005...etc i feel practically difficult to have separate table which has dates of all months of all years.....please reply..... Regards "Duane Hookom" wrote: You might want to have a table of all dates that you can use in your row source query. Join the date fields with a join that includes all the dates from your table of all dates. -- Duane Hookom MS Access MVP "ismail" wrote in message ... could u please have look into this problem again... my row source is SELECT (Format([dateofS],"Short Date")) AS Expr1, Sum(chart2_act_petQ.petrol) AS SumOfpetrol FROM chart2_act_petQ GROUP BY (Format([dateofS],"Short Date")); and if i have sales data only upto 14 days...in the table..but in my like to see numbers upto 30 or 31 or 28 depends upon the current month... could u please explain me in more details..what to do in query...Please.. "Duane Hookom" wrote: Modify the Row Source of the chart control to return the data like you want in your chart. -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... I have chart ..it will give me monthly sales details....X axis is Dates...like 1.2.3....31 Y axis is Amounts....dynamic chart...i use only one chart for all 12 months.. if i have sales ..only for 14 days...in chart i would like to see the line upto 14.. and number of days should be there like 30 or 31 depends upon the month. but now i see the chart shows 1.2.3....14 and sales line goes upto end of the chart. i would like to see in chart ...all the days..but the line should stop at 14th day. Thanks in Advance. |
#2
|
|||
|
|||
O...! Please remember this problem again......this query doesnt give me
all the dates in the current month....except current month..it is ok... but i need "all the dates in the current month".. please help.....i found that in the last moment... thanks & Regards "ismail" wrote: Dear Duane Hookom ..yes it is working.....i should thank you... so...Thank you very much..... Regards Ismail "Duane Hookom" wrote: Set the join property to include all the records from your recordset of all dates. Set the criteria under the AllDates field to limit the dates to your desired output. -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... O..Very interesting your Query...really useful....not only for my present problem... but i am sorry to ask too much from u...... now i have my query which gives me data(upto 14 days,total sales amount) and ur query which qives me..all the dates...could u please give me some help how can i make query..which gives me all the dates in month..but total sales amount will be upto 14 days...i mean remaining 16 days i will have empty..in total sales amount field...... thanks "Duane Hookom" wrote: Creating a table of all dates isn't that difficult. If you want to use a table with only 10 records: Create a table "tblOneToNine" with a single field "Num" of numeric data type. Enter in values 0, 1, 2, 3, ...9 Create a query with sql of: SELECT Date()- ([tblOneToNine].[Num]*1000+ [tblOneToNine_1].[Num]*100+ [tblOneToNine_2].[Num]*10+ [tblOneToNine_3].[Num]) AS AllDates FROM tblOneToNine, tblOneToNine AS tblOneToNine_1, tblOneToNine AS tblOneToNine_2, tblOneToNine AS tblOneToNine_3; You can save this query which will result in all dates from 9999 days ago to today. -- Duane Hookom MS Access MVP "ismail" wrote in message ... Please Please.......i see the monthly chart for all months..of 2003, 2004,2005...etc i feel practically difficult to have separate table which has dates of all months of all years.....please reply..... Regards "Duane Hookom" wrote: You might want to have a table of all dates that you can use in your row source query. Join the date fields with a join that includes all the dates from your table of all dates. -- Duane Hookom MS Access MVP "ismail" wrote in message ... could u please have look into this problem again... my row source is SELECT (Format([dateofS],"Short Date")) AS Expr1, Sum(chart2_act_petQ.petrol) AS SumOfpetrol FROM chart2_act_petQ GROUP BY (Format([dateofS],"Short Date")); and if i have sales data only upto 14 days...in the table..but in my like to see numbers upto 30 or 31 or 28 depends upon the current month... could u please explain me in more details..what to do in query...Please.. "Duane Hookom" wrote: Modify the Row Source of the chart control to return the data like you want in your chart. -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... I have chart ..it will give me monthly sales details....X axis is Dates...like 1.2.3....31 Y axis is Amounts....dynamic chart...i use only one chart for all 12 months.. if i have sales ..only for 14 days...in chart i would like to see the line upto 14.. and number of days should be there like 30 or 31 depends upon the month. but now i see the chart shows 1.2.3....14 and sales line goes upto end of the chart. i would like to see in chart ...all the days..but the line should stop at 14th day. Thanks in Advance. |
#3
|
|||
|
|||
If you have a query that doesn't show what you want it to show, you should
reply with information about your tables along with your SQL view. -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... O...! Please remember this problem again......this query doesnt give me all the dates in the current month....except current month..it is ok... but i need "all the dates in the current month".. please help.....i found that in the last moment... thanks & Regards "ismail" wrote: Dear Duane Hookom ..yes it is working.....i should thank you... so...Thank you very much..... Regards Ismail "Duane Hookom" wrote: Set the join property to include all the records from your recordset of all dates. Set the criteria under the AllDates field to limit the dates to your desired output. -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... O..Very interesting your Query...really useful....not only for my present problem... but i am sorry to ask too much from u...... now i have my query which gives me data(upto 14 days,total sales amount) and ur query which qives me..all the dates...could u please give me some help how can i make query..which gives me all the dates in month..but total sales amount will be upto 14 days...i mean remaining 16 days i will have empty..in total sales amount field...... thanks "Duane Hookom" wrote: Creating a table of all dates isn't that difficult. If you want to use a table with only 10 records: Create a table "tblOneToNine" with a single field "Num" of numeric data type. Enter in values 0, 1, 2, 3, ...9 Create a query with sql of: SELECT Date()- ([tblOneToNine].[Num]*1000+ [tblOneToNine_1].[Num]*100+ [tblOneToNine_2].[Num]*10+ [tblOneToNine_3].[Num]) AS AllDates FROM tblOneToNine, tblOneToNine AS tblOneToNine_1, tblOneToNine AS tblOneToNine_2, tblOneToNine AS tblOneToNine_3; You can save this query which will result in all dates from 9999 days ago to today. -- Duane Hookom MS Access MVP "ismail" wrote in message ... Please Please.......i see the monthly chart for all months..of 2003, 2004,2005...etc i feel practically difficult to have separate table which has dates of all months of all years.....please reply..... Regards "Duane Hookom" wrote: You might want to have a table of all dates that you can use in your row source query. Join the date fields with a join that includes all the dates from your table of all dates. -- Duane Hookom MS Access MVP "ismail" wrote in message ... could u please have look into this problem again... my row source is SELECT (Format([dateofS],"Short Date")) AS Expr1, Sum(chart2_act_petQ.petrol) AS SumOfpetrol FROM chart2_act_petQ GROUP BY (Format([dateofS],"Short Date")); and if i have sales data only upto 14 days...in the table..but in my like to see numbers upto 30 or 31 or 28 depends upon the current month... could u please explain me in more details..what to do in query...Please.. "Duane Hookom" wrote: Modify the Row Source of the chart control to return the data like you want in your chart. -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... I have chart ..it will give me monthly sales details....X axis is Dates...like 1.2.3....31 Y axis is Amounts....dynamic chart...i use only one chart for all 12 months.. if i have sales ..only for 14 days...in chart i would like to see the line upto 14.. and number of days should be there like 30 or 31 depends upon the month. but now i see the chart shows 1.2.3....14 and sales line goes upto end of the chart. i would like to see in chart ...all the days..but the line should stop at 14th day. Thanks in Advance. |
#4
|
|||
|
|||
You gave me this....
-------------------------------------------------------------- Creating a table of all dates isn't that difficult. If you want to use a table with only 10 records: Create a table "tblOneToNine" with a single field "Num" of numeric data type. Enter in values 0, 1, 2, 3, ...9 Create a query with sql of: SELECT Date()- ([tblOneToNine].[Num]*1000+ [tblOneToNine_1].[Num]*100+ [tblOneToNine_2].[Num]*10+ [tblOneToNine_3].[Num]) AS AllDates FROM tblOneToNine, tblOneToNine AS tblOneToNine_1, tblOneToNine AS tblOneToNine_2, tblOneToNine AS tblOneToNine_3; You can save this query which will result in all dates from 9999 days ago to today. ------------------------------------------------------------------------ if you run this ...you get dates...upto today....that is 23/08/2005 but i need dates upto 31/08/2005......that is it.....no confusion.... if this query doesnt do...you may give me some other query? Please reply.. Thanks... "Duane Hookom" wrote: If you have a query that doesn't show what you want it to show, you should reply with information about your tables along with your SQL view. -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... O...! Please remember this problem again......this query doesnt give me all the dates in the current month....except current month..it is ok... but i need "all the dates in the current month".. please help.....i found that in the last moment... thanks & Regards "ismail" wrote: Dear Duane Hookom ..yes it is working.....i should thank you... so...Thank you very much..... Regards Ismail "Duane Hookom" wrote: Set the join property to include all the records from your recordset of all dates. Set the criteria under the AllDates field to limit the dates to your desired output. -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... O..Very interesting your Query...really useful....not only for my present problem... but i am sorry to ask too much from u...... now i have my query which gives me data(upto 14 days,total sales amount) and ur query which qives me..all the dates...could u please give me some help how can i make query..which gives me all the dates in month..but total sales amount will be upto 14 days...i mean remaining 16 days i will have empty..in total sales amount field...... thanks "Duane Hookom" wrote: Creating a table of all dates isn't that difficult. If you want to use a table with only 10 records: Create a table "tblOneToNine" with a single field "Num" of numeric data type. Enter in values 0, 1, 2, 3, ...9 Create a query with sql of: SELECT Date()- ([tblOneToNine].[Num]*1000+ [tblOneToNine_1].[Num]*100+ [tblOneToNine_2].[Num]*10+ [tblOneToNine_3].[Num]) AS AllDates FROM tblOneToNine, tblOneToNine AS tblOneToNine_1, tblOneToNine AS tblOneToNine_2, tblOneToNine AS tblOneToNine_3; You can save this query which will result in all dates from 9999 days ago to today. -- Duane Hookom MS Access MVP "ismail" wrote in message ... Please Please.......i see the monthly chart for all months..of 2003, 2004,2005...etc i feel practically difficult to have separate table which has dates of all months of all years.....please reply..... Regards "Duane Hookom" wrote: You might want to have a table of all dates that you can use in your row source query. Join the date fields with a join that includes all the dates from your table of all dates. -- Duane Hookom MS Access MVP "ismail" wrote in message ... could u please have look into this problem again... my row source is SELECT (Format([dateofS],"Short Date")) AS Expr1, Sum(chart2_act_petQ.petrol) AS SumOfpetrol FROM chart2_act_petQ GROUP BY (Format([dateofS],"Short Date")); and if i have sales data only upto 14 days...in the table..but in my like to see numbers upto 30 or 31 or 28 depends upon the current month... could u please explain me in more details..what to do in query...Please.. "Duane Hookom" wrote: Modify the Row Source of the chart control to return the data like you want in your chart. -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... I have chart ..it will give me monthly sales details....X axis is Dates...like 1.2.3....31 Y axis is Amounts....dynamic chart...i use only one chart for all 12 months.. if i have sales ..only for 14 days...in chart i would like to see the line upto 14.. and number of days should be there like 30 or 31 depends upon the month. but now i see the chart shows 1.2.3....14 and sales line goes upto end of the chart. i would like to see in chart ...all the days..but the line should stop at 14th day. Thanks in Advance. |
#5
|
|||
|
|||
You should be able to figure out how to add a specific number to a
field/column in a query.... -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... You gave me this.... -------------------------------------------------------------- Creating a table of all dates isn't that difficult. If you want to use a table with only 10 records: Create a table "tblOneToNine" with a single field "Num" of numeric data type. Enter in values 0, 1, 2, 3, ...9 Create a query with sql of: SELECT Date()- ([tblOneToNine].[Num]*1000+ [tblOneToNine_1].[Num]*100+ [tblOneToNine_2].[Num]*10+ [tblOneToNine_3].[Num]) AS AllDates FROM tblOneToNine, tblOneToNine AS tblOneToNine_1, tblOneToNine AS tblOneToNine_2, tblOneToNine AS tblOneToNine_3; You can save this query which will result in all dates from 9999 days ago to today. ------------------------------------------------------------------------ if you run this ...you get dates...upto today....that is 23/08/2005 but i need dates upto 31/08/2005......that is it.....no confusion.... if this query doesnt do...you may give me some other query? Please reply.. Thanks... "Duane Hookom" wrote: If you have a query that doesn't show what you want it to show, you should reply with information about your tables along with your SQL view. -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... O...! Please remember this problem again......this query doesnt give me all the dates in the current month....except current month..it is ok... but i need "all the dates in the current month".. please help.....i found that in the last moment... thanks & Regards "ismail" wrote: Dear Duane Hookom ..yes it is working.....i should thank you... so...Thank you very much..... Regards Ismail "Duane Hookom" wrote: Set the join property to include all the records from your recordset of all dates. Set the criteria under the AllDates field to limit the dates to your desired output. -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... O..Very interesting your Query...really useful....not only for my present problem... but i am sorry to ask too much from u...... now i have my query which gives me data(upto 14 days,total sales amount) and ur query which qives me..all the dates...could u please give me some help how can i make query..which gives me all the dates in month..but total sales amount will be upto 14 days...i mean remaining 16 days i will have empty..in total sales amount field...... thanks "Duane Hookom" wrote: Creating a table of all dates isn't that difficult. If you want to use a table with only 10 records: Create a table "tblOneToNine" with a single field "Num" of numeric data type. Enter in values 0, 1, 2, 3, ...9 Create a query with sql of: SELECT Date()- ([tblOneToNine].[Num]*1000+ [tblOneToNine_1].[Num]*100+ [tblOneToNine_2].[Num]*10+ [tblOneToNine_3].[Num]) AS AllDates FROM tblOneToNine, tblOneToNine AS tblOneToNine_1, tblOneToNine AS tblOneToNine_2, tblOneToNine AS tblOneToNine_3; You can save this query which will result in all dates from 9999 days ago to today. -- Duane Hookom MS Access MVP "ismail" wrote in message ... Please Please.......i see the monthly chart for all months..of 2003, 2004,2005...etc i feel practically difficult to have separate table which has dates of all months of all years.....please reply..... Regards "Duane Hookom" wrote: You might want to have a table of all dates that you can use in your row source query. Join the date fields with a join that includes all the dates from your table of all dates. -- Duane Hookom MS Access MVP "ismail" wrote in message ... could u please have look into this problem again... my row source is SELECT (Format([dateofS],"Short Date")) AS Expr1, Sum(chart2_act_petQ.petrol) AS SumOfpetrol FROM chart2_act_petQ GROUP BY (Format([dateofS],"Short Date")); and if i have sales data only upto 14 days...in the table..but in my like to see numbers upto 30 or 31 or 28 depends upon the current month... could u please explain me in more details..what to do in query...Please.. "Duane Hookom" wrote: Modify the Row Source of the chart control to return the data like you want in your chart. -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... I have chart ..it will give me monthly sales details....X axis is Dates...like 1.2.3....31 Y axis is Amounts....dynamic chart...i use only one chart for all 12 months.. if i have sales ..only for 14 days...in chart i would like to see the line upto 14.. and number of days should be there like 30 or 31 depends upon the month. but now i see the chart shows 1.2.3....14 and sales line goes upto end of the chart. i would like to see in chart ...all the days..but the line should stop at 14th day. Thanks in Advance. |
#6
|
|||
|
|||
Actually after trying ...many times...i could not found solution...
"Duane Hookom" wrote: You should be able to figure out how to add a specific number to a field/column in a query.... -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... You gave me this.... -------------------------------------------------------------- Creating a table of all dates isn't that difficult. If you want to use a table with only 10 records: Create a table "tblOneToNine" with a single field "Num" of numeric data type. Enter in values 0, 1, 2, 3, ...9 Create a query with sql of: SELECT Date()- ([tblOneToNine].[Num]*1000+ [tblOneToNine_1].[Num]*100+ [tblOneToNine_2].[Num]*10+ [tblOneToNine_3].[Num]) AS AllDates FROM tblOneToNine, tblOneToNine AS tblOneToNine_1, tblOneToNine AS tblOneToNine_2, tblOneToNine AS tblOneToNine_3; You can save this query which will result in all dates from 9999 days ago to today. ------------------------------------------------------------------------ if you run this ...you get dates...upto today....that is 23/08/2005 but i need dates upto 31/08/2005......that is it.....no confusion.... if this query doesnt do...you may give me some other query? Please reply.. Thanks... "Duane Hookom" wrote: If you have a query that doesn't show what you want it to show, you should reply with information about your tables along with your SQL view. -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... O...! Please remember this problem again......this query doesnt give me all the dates in the current month....except current month..it is ok... but i need "all the dates in the current month".. please help.....i found that in the last moment... thanks & Regards "ismail" wrote: Dear Duane Hookom ..yes it is working.....i should thank you... so...Thank you very much..... Regards Ismail "Duane Hookom" wrote: Set the join property to include all the records from your recordset of all dates. Set the criteria under the AllDates field to limit the dates to your desired output. -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... O..Very interesting your Query...really useful....not only for my present problem... but i am sorry to ask too much from u...... now i have my query which gives me data(upto 14 days,total sales amount) and ur query which qives me..all the dates...could u please give me some help how can i make query..which gives me all the dates in month..but total sales amount will be upto 14 days...i mean remaining 16 days i will have empty..in total sales amount field...... thanks "Duane Hookom" wrote: Creating a table of all dates isn't that difficult. If you want to use a table with only 10 records: Create a table "tblOneToNine" with a single field "Num" of numeric data type. Enter in values 0, 1, 2, 3, ...9 Create a query with sql of: SELECT Date()- ([tblOneToNine].[Num]*1000+ [tblOneToNine_1].[Num]*100+ [tblOneToNine_2].[Num]*10+ [tblOneToNine_3].[Num]) AS AllDates FROM tblOneToNine, tblOneToNine AS tblOneToNine_1, tblOneToNine AS tblOneToNine_2, tblOneToNine AS tblOneToNine_3; You can save this query which will result in all dates from 9999 days ago to today. -- Duane Hookom MS Access MVP "ismail" wrote in message ... Please Please.......i see the monthly chart for all months..of 2003, 2004,2005...etc i feel practically difficult to have separate table which has dates of all months of all years.....please reply..... Regards "Duane Hookom" wrote: You might want to have a table of all dates that you can use in your row source query. Join the date fields with a join that includes all the dates from your table of all dates. -- Duane Hookom MS Access MVP "ismail" wrote in message ... could u please have look into this problem again... my row source is SELECT (Format([dateofS],"Short Date")) AS Expr1, Sum(chart2_act_petQ.petrol) AS SumOfpetrol FROM chart2_act_petQ GROUP BY (Format([dateofS],"Short Date")); and if i have sales data only upto 14 days...in the table..but in my like to see numbers upto 30 or 31 or 28 depends upon the current month... could u please explain me in more details..what to do in query...Please.. "Duane Hookom" wrote: Modify the Row Source of the chart control to return the data like you want in your chart. -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... I have chart ..it will give me monthly sales details....X axis is Dates...like 1.2.3....31 Y axis is Amounts....dynamic chart...i use only one chart for all 12 months.. if i have sales ..only for 14 days...in chart i would like to see the line upto 14.. and number of days should be there like 30 or 31 depends upon the month. but now i see the chart shows 1.2.3....14 and sales line goes upto end of the chart. i would like to see in chart ...all the days..but the line should stop at 14th day. Thanks in Advance. |
#7
|
|||
|
|||
SELECT 100+Date()-
([tblOneToNine].[Num]*1000+ [tblOneToNine_1].[Num]*100+ [tblOneToNine_2].[Num]*10+ [tblOneToNine_3].[Num]) AS AllDates FROM tblOneToNine, tblOneToNine AS tblOneToNine_1, tblOneToNine AS tblOneToNine_2, tblOneToNine AS tblOneToNine_3; This will give you about 100 days into the future. -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... Actually after trying ...many times...i could not found solution... "Duane Hookom" wrote: You should be able to figure out how to add a specific number to a field/column in a query.... -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... You gave me this.... -------------------------------------------------------------- Creating a table of all dates isn't that difficult. If you want to use a table with only 10 records: Create a table "tblOneToNine" with a single field "Num" of numeric data type. Enter in values 0, 1, 2, 3, ...9 Create a query with sql of: SELECT Date()- ([tblOneToNine].[Num]*1000+ [tblOneToNine_1].[Num]*100+ [tblOneToNine_2].[Num]*10+ [tblOneToNine_3].[Num]) AS AllDates FROM tblOneToNine, tblOneToNine AS tblOneToNine_1, tblOneToNine AS tblOneToNine_2, tblOneToNine AS tblOneToNine_3; You can save this query which will result in all dates from 9999 days ago to today. ------------------------------------------------------------------------ if you run this ...you get dates...upto today....that is 23/08/2005 but i need dates upto 31/08/2005......that is it.....no confusion.... if this query doesnt do...you may give me some other query? Please reply.. Thanks... "Duane Hookom" wrote: If you have a query that doesn't show what you want it to show, you should reply with information about your tables along with your SQL view. -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... O...! Please remember this problem again......this query doesnt give me all the dates in the current month....except current month..it is ok... but i need "all the dates in the current month".. please help.....i found that in the last moment... thanks & Regards "ismail" wrote: Dear Duane Hookom ..yes it is working.....i should thank you... so...Thank you very much..... Regards Ismail "Duane Hookom" wrote: Set the join property to include all the records from your recordset of all dates. Set the criteria under the AllDates field to limit the dates to your desired output. -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... O..Very interesting your Query...really useful....not only for my present problem... but i am sorry to ask too much from u...... now i have my query which gives me data(upto 14 days,total sales amount) and ur query which qives me..all the dates...could u please give me some help how can i make query..which gives me all the dates in month..but total sales amount will be upto 14 days...i mean remaining 16 days i will have empty..in total sales amount field...... thanks "Duane Hookom" wrote: Creating a table of all dates isn't that difficult. If you want to use a table with only 10 records: Create a table "tblOneToNine" with a single field "Num" of numeric data type. Enter in values 0, 1, 2, 3, ...9 Create a query with sql of: SELECT Date()- ([tblOneToNine].[Num]*1000+ [tblOneToNine_1].[Num]*100+ [tblOneToNine_2].[Num]*10+ [tblOneToNine_3].[Num]) AS AllDates FROM tblOneToNine, tblOneToNine AS tblOneToNine_1, tblOneToNine AS tblOneToNine_2, tblOneToNine AS tblOneToNine_3; You can save this query which will result in all dates from 9999 days ago to today. -- Duane Hookom MS Access MVP "ismail" wrote in message ... Please Please.......i see the monthly chart for all months..of 2003, 2004,2005...etc i feel practically difficult to have separate table which has dates of all months of all years.....please reply..... Regards "Duane Hookom" wrote: You might want to have a table of all dates that you can use in your row source query. Join the date fields with a join that includes all the dates from your table of all dates. -- Duane Hookom MS Access MVP "ismail" wrote in message ... could u please have look into this problem again... my row source is SELECT (Format([dateofS],"Short Date")) AS Expr1, Sum(chart2_act_petQ.petrol) AS SumOfpetrol FROM chart2_act_petQ GROUP BY (Format([dateofS],"Short Date")); and if i have sales data only upto 14 days...in the table..but in my like to see numbers upto 30 or 31 or 28 depends upon the current month... could u please explain me in more details..what to do in query...Please.. "Duane Hookom" wrote: Modify the Row Source of the chart control to return the data like you want in your chart. -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... I have chart ..it will give me monthly sales details....X axis is Dates...like 1.2.3....31 Y axis is Amounts....dynamic chart...i use only one chart for all 12 months.. if i have sales ..only for 14 days...in chart i would like to see the line upto 14.. and number of days should be there like 30 or 31 depends upon the month. but now i see the chart shows 1.2.3....14 and sales line goes upto end of the chart. i would like to see in chart ...all the days..but the line should stop at 14th day. Thanks in Advance. |
#8
|
|||
|
|||
Thank you verymuch........it solved my problem.........i put 9000 instead of
100.. i have a another question in forms can i ask here... Regards "Duane Hookom" wrote: SELECT 100+Date()- ([tblOneToNine].[Num]*1000+ [tblOneToNine_1].[Num]*100+ [tblOneToNine_2].[Num]*10+ [tblOneToNine_3].[Num]) AS AllDates FROM tblOneToNine, tblOneToNine AS tblOneToNine_1, tblOneToNine AS tblOneToNine_2, tblOneToNine AS tblOneToNine_3; This will give you about 100 days into the future. -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... Actually after trying ...many times...i could not found solution... "Duane Hookom" wrote: You should be able to figure out how to add a specific number to a field/column in a query.... -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... You gave me this.... -------------------------------------------------------------- Creating a table of all dates isn't that difficult. If you want to use a table with only 10 records: Create a table "tblOneToNine" with a single field "Num" of numeric data type. Enter in values 0, 1, 2, 3, ...9 Create a query with sql of: SELECT Date()- ([tblOneToNine].[Num]*1000+ [tblOneToNine_1].[Num]*100+ [tblOneToNine_2].[Num]*10+ [tblOneToNine_3].[Num]) AS AllDates FROM tblOneToNine, tblOneToNine AS tblOneToNine_1, tblOneToNine AS tblOneToNine_2, tblOneToNine AS tblOneToNine_3; You can save this query which will result in all dates from 9999 days ago to today. ------------------------------------------------------------------------ if you run this ...you get dates...upto today....that is 23/08/2005 but i need dates upto 31/08/2005......that is it.....no confusion.... if this query doesnt do...you may give me some other query? Please reply.. Thanks... "Duane Hookom" wrote: If you have a query that doesn't show what you want it to show, you should reply with information about your tables along with your SQL view. -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... O...! Please remember this problem again......this query doesnt give me all the dates in the current month....except current month..it is ok... but i need "all the dates in the current month".. please help.....i found that in the last moment... thanks & Regards "ismail" wrote: Dear Duane Hookom ..yes it is working.....i should thank you... so...Thank you very much..... Regards Ismail "Duane Hookom" wrote: Set the join property to include all the records from your recordset of all dates. Set the criteria under the AllDates field to limit the dates to your desired output. -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... O..Very interesting your Query...really useful....not only for my present problem... but i am sorry to ask too much from u...... now i have my query which gives me data(upto 14 days,total sales amount) and ur query which qives me..all the dates...could u please give me some help how can i make query..which gives me all the dates in month..but total sales amount will be upto 14 days...i mean remaining 16 days i will have empty..in total sales amount field...... thanks "Duane Hookom" wrote: Creating a table of all dates isn't that difficult. If you want to use a table with only 10 records: Create a table "tblOneToNine" with a single field "Num" of numeric data type. Enter in values 0, 1, 2, 3, ...9 Create a query with sql of: SELECT Date()- ([tblOneToNine].[Num]*1000+ [tblOneToNine_1].[Num]*100+ [tblOneToNine_2].[Num]*10+ [tblOneToNine_3].[Num]) AS AllDates FROM tblOneToNine, tblOneToNine AS tblOneToNine_1, tblOneToNine AS tblOneToNine_2, tblOneToNine AS tblOneToNine_3; You can save this query which will result in all dates from 9999 days ago to today. -- Duane Hookom MS Access MVP "ismail" wrote in message ... Please Please.......i see the monthly chart for all months..of 2003, 2004,2005...etc i feel practically difficult to have separate table which has dates of all months of all years.....please reply..... Regards "Duane Hookom" wrote: You might want to have a table of all dates that you can use in your row source query. Join the date fields with a join that includes all the dates from your table of all dates. -- Duane Hookom MS Access MVP "ismail" wrote in message ... could u please have look into this problem again... my row source is SELECT (Format([dateofS],"Short Date")) AS Expr1, Sum(chart2_act_petQ.petrol) AS SumOfpetrol FROM chart2_act_petQ GROUP BY (Format([dateofS],"Short Date")); and if i have sales data only upto 14 days...in the table..but in my like to see numbers upto 30 or 31 or 28 depends upon the current month... could u please explain me in more details..what to do in query...Please.. "Duane Hookom" wrote: Modify the Row Source of the chart control to return the data like you want in your chart. -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... I have chart ..it will give me monthly sales details....X axis is Dates...like 1.2.3....31 Y axis is Amounts....dynamic chart...i use only one chart for all 12 months.. if i have sales ..only for 14 days...in chart i would like to see the line upto 14.. and number of days should be there like 30 or 31 depends upon the month. but now i see the chart shows 1.2.3....14 and sales line goes upto end of the chart. i would like to see in chart ...all the days..but the line should stop at 14th day. Thanks in Advance. |
#9
|
|||
|
|||
If you have a new question, you should start a new thread.
-- Duane Hookom MS Access MVP "ismail" wrote in message ... Thank you verymuch........it solved my problem.........i put 9000 instead of 100.. i have a another question in forms can i ask here... Regards "Duane Hookom" wrote: SELECT 100+Date()- ([tblOneToNine].[Num]*1000+ [tblOneToNine_1].[Num]*100+ [tblOneToNine_2].[Num]*10+ [tblOneToNine_3].[Num]) AS AllDates FROM tblOneToNine, tblOneToNine AS tblOneToNine_1, tblOneToNine AS tblOneToNine_2, tblOneToNine AS tblOneToNine_3; This will give you about 100 days into the future. -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... Actually after trying ...many times...i could not found solution... "Duane Hookom" wrote: You should be able to figure out how to add a specific number to a field/column in a query.... -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... You gave me this.... -------------------------------------------------------------- Creating a table of all dates isn't that difficult. If you want to use a table with only 10 records: Create a table "tblOneToNine" with a single field "Num" of numeric data type. Enter in values 0, 1, 2, 3, ...9 Create a query with sql of: SELECT Date()- ([tblOneToNine].[Num]*1000+ [tblOneToNine_1].[Num]*100+ [tblOneToNine_2].[Num]*10+ [tblOneToNine_3].[Num]) AS AllDates FROM tblOneToNine, tblOneToNine AS tblOneToNine_1, tblOneToNine AS tblOneToNine_2, tblOneToNine AS tblOneToNine_3; You can save this query which will result in all dates from 9999 days ago to today. ------------------------------------------------------------------------ if you run this ...you get dates...upto today....that is 23/08/2005 but i need dates upto 31/08/2005......that is it.....no confusion.... if this query doesnt do...you may give me some other query? Please reply.. Thanks... "Duane Hookom" wrote: If you have a query that doesn't show what you want it to show, you should reply with information about your tables along with your SQL view. -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... O...! Please remember this problem again......this query doesnt give me all the dates in the current month....except current month..it is ok... but i need "all the dates in the current month".. please help.....i found that in the last moment... thanks & Regards "ismail" wrote: Dear Duane Hookom ..yes it is working.....i should thank you... so...Thank you very much..... Regards Ismail "Duane Hookom" wrote: Set the join property to include all the records from your recordset of all dates. Set the criteria under the AllDates field to limit the dates to your desired output. -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... O..Very interesting your Query...really useful....not only for my present problem... but i am sorry to ask too much from u...... now i have my query which gives me data(upto 14 days,total sales amount) and ur query which qives me..all the dates...could u please give me some help how can i make query..which gives me all the dates in month..but total sales amount will be upto 14 days...i mean remaining 16 days i will have empty..in total sales amount field...... thanks "Duane Hookom" wrote: Creating a table of all dates isn't that difficult. If you want to use a table with only 10 records: Create a table "tblOneToNine" with a single field "Num" of numeric data type. Enter in values 0, 1, 2, 3, ...9 Create a query with sql of: SELECT Date()- ([tblOneToNine].[Num]*1000+ [tblOneToNine_1].[Num]*100+ [tblOneToNine_2].[Num]*10+ [tblOneToNine_3].[Num]) AS AllDates FROM tblOneToNine, tblOneToNine AS tblOneToNine_1, tblOneToNine AS tblOneToNine_2, tblOneToNine AS tblOneToNine_3; You can save this query which will result in all dates from 9999 days ago to today. -- Duane Hookom MS Access MVP "ismail" wrote in message ... Please Please.......i see the monthly chart for all months..of 2003, 2004,2005...etc i feel practically difficult to have separate table which has dates of all months of all years.....please reply..... Regards "Duane Hookom" wrote: You might want to have a table of all dates that you can use in your row source query. Join the date fields with a join that includes all the dates from your table of all dates. -- Duane Hookom MS Access MVP "ismail" wrote in message ... could u please have look into this problem again... my row source is SELECT (Format([dateofS],"Short Date")) AS Expr1, Sum(chart2_act_petQ.petrol) AS SumOfpetrol FROM chart2_act_petQ GROUP BY (Format([dateofS],"Short Date")); and if i have sales data only upto 14 days...in the table..but in my like to see numbers upto 30 or 31 or 28 depends upon the current month... could u please explain me in more details..what to do in query...Please.. "Duane Hookom" wrote: Modify the Row Source of the chart control to return the data like you want in your chart. -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... I have chart ..it will give me monthly sales details....X axis is Dates...like 1.2.3....31 Y axis is Amounts....dynamic chart...i use only one chart for all 12 months.. if i have sales ..only for 14 days...in chart i would like to see the line upto 14.. and number of days should be there like 30 or 31 depends upon the month. but now i see the chart shows 1.2.3....14 and sales line goes upto end of the chart. i would like to see in chart ...all the days..but the line should stop at 14th day. Thanks in Advance. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Fan charts | Dean | Charts and Charting | 7 | May 30th, 2005 11:51 AM |
Transparent Excel chart backgrounds | Peggy | Charts and Charting | 1 | September 5th, 2004 04:41 AM |
Chart menu visible property | Sandy V | Charts and Charting | 8 | May 17th, 2004 01:39 PM |
Point Labeling and Chart Plotting help needed | tacotime | Charts and Charting | 1 | May 14th, 2004 08:05 PM |
Simple? Help Needed with Excel Chart | DKD | Charts and Charting | 2 | February 1st, 2004 06:32 AM |