"Nurture" the graph

  • Thread starter Thread starter Sev
  • Start date Start date
S

Sev

I have monthly data in queries. One query to each month.
In form, trough combobox, I need to choose the monthly
query to "nurture" the graph in subform.
I would like help, because I don´t know the best way to do
this.
(And I never saw any sample)
Any idea is welcome.
Sev
 
Sev,

To begin with, it looks like you have started off with a poor design; a
separate queruy for each month shouldn't be necessary, you should have one
query with a month filter.

Now, getting to the particular question: once you have changed your design
to a single query, "feed" the graph from it, and make the month criterion in
the query read directly from the combo on the form
(Forms![FormName]![ComboName]), and the job is done.

HTH,
Nikos

I have monthly data in queries. One query to each month.
In form, trough combobox, I need to choose the monthly
query to "nurture" the graph in subform.
I would like help, because I don´t know the best way to do
this.
(And I never saw any sample)
Any idea is welcome.
Sev
 
Thanks for your replay.

I need to have Crosstab queries to calc values and I think
isn't possible to filter the Date.

Therefore, I have one query for month and I have in Date
Column:

Between (#01/02/2001#) And (#28/02/2001#)

I don't know solution for this...

Thanks.
Sev
 
Sev,

If you want monthly columns in your crosstab, then use a calculated field in
your query like:

Period: Format([fldDate]), "yyyy-mm")

It will work beautifully as Column Header in a crosstab, and sort correctly.

Note: in the above example I have assumed your date field to be fldDate. I
understand your field is actually named Date; it would be advisable to
change the field name to something else, as Date is a reserved keyword in
Access, and you may eventually run into problems in your design as a result
of the confusion (yes, Access can be confused under similar conditions).

HTH,
Nikos

Thanks for your replay.

I need to have Crosstab queries to calc values and I think
isn't possible to filter the Date.

Therefore, I have one query for month and I have in Date
Column:

Between (#01/02/2001#) And (#28/02/2001#)

I don't know solution for this...

Thanks.
Sev
 
Nikos,

Thanks for your solution, but, lamently I have "dd-mmm-yy".
It is more complicated...
Sev
-----Original Message-----
Sev,

If you want monthly columns in your crosstab, then use a calculated field in
your query like:

Period: Format([fldDate]), "yyyy-mm")

It will work beautifully as Column Header in a crosstab, and sort correctly.

Note: in the above example I have assumed your date field to be fldDate. I
understand your field is actually named Date; it would be advisable to
change the field name to something else, as Date is a reserved keyword in
Access, and you may eventually run into problems in your design as a result
of the confusion (yes, Access can be confused under similar conditions).

HTH,
Nikos

Thanks for your replay.

I need to have Crosstab queries to calc values and I think
isn't possible to filter the Date.

Therefore, I have one query for month and I have in Date
Column:

Between (#01/02/2001#) And (#28/02/2001#)

I don't know solution for this...

Thanks.
Sev
-----Original Message-----
Sev,

To begin with, it looks like you have started off with a poor design; a
separate queruy for each month shouldn't be necessary, you should have one
query with a month filter.

Now, getting to the particular question: once you have changed your design
to a single query, "feed" the graph from it, and make the month criterion in
the query read directly from the combo on the form
(Forms![FormName]![ComboName]), and the job is done.

HTH,
Nikos

I have monthly data in queries. One query to each month.
In form, trough combobox, I need to choose the monthly
query to "nurture" the graph in subform.
I would like help, because I don´t know the best way to do
this.
(And I never saw any sample)
Any idea is welcome.
Sev


.
Click to expand...


.
Click to expand...
 
Sev,

There is definitely a solution, if you care to explain! What is your table
like, and what are you trying to achieve? Where do you have "dd-mmm-yy", in
the table or as column headers in the crosstab?

Nikos

Nikos,

Thanks for your solution, but, lamently I have "dd-mmm-yy".
It is more complicated...
Sev
-----Original Message-----
Sev,

If you want monthly columns in your crosstab, then use a calculated field in
your query like:

Period: Format([fldDate]), "yyyy-mm")

It will work beautifully as Column Header in a crosstab, and sort correctly.

Note: in the above example I have assumed your date field to be fldDate. I
understand your field is actually named Date; it would be advisable to
change the field name to something else, as Date is a reserved keyword in
Access, and you may eventually run into problems in your design as a result
of the confusion (yes, Access can be confused under similar conditions).

HTH,
Nikos

Thanks for your replay.

I need to have Crosstab queries to calc values and I think
isn't possible to filter the Date.

Therefore, I have one query for month and I have in Date
Column:

Between (#01/02/2001#) And (#28/02/2001#)

I don't know solution for this...

Thanks.
Sev
-----Original Message-----
Sev,

To begin with, it looks like you have started off with a poor design; a
separate queruy for each month shouldn't be necessary, you should have one
query with a month filter.

Now, getting to the particular question: once you have changed your design
to a single query, "feed" the graph from it, and make the month criterion in
the query read directly from the combo on the form
(Forms![FormName]![ComboName]), and the job is done.

HTH,
Nikos

I have monthly data in queries. One query to each month.
In form, trough combobox, I need to choose the monthly
query to "nurture" the graph in subform.
I would like help, because I don´t know the best way to do
this.
(And I never saw any sample)
Any idea is welcome.
Sev


.
Click to expand...


.
Click to expand...
 
Thanks for your reply.

Perhaps my ambition is to be great, but...

Table1:
Param, Text, 50
Day, Medium Date, dd-mmm-yy
Value, Single, Standard,2

In the limit what I intend it is to have a query to choose
Month to "feed" the graph in one form to present graphs
with the 30 days of the Month choosed MonthYear.

One more time, many thanks.
Sev
-----Original Message-----
Sev,

There is definitely a solution, if you care to explain! What is your table
like, and what are you trying to achieve? Where do you have "dd-mmm-yy", in
the table or as column headers in the crosstab?

Nikos

Nikos,

Thanks for your solution, but, lamently I have "dd-mmm- yy".
It is more complicated...
Sev
-----Original Message-----
Sev,

If you want monthly columns in your crosstab, then use a calculated field in
your query like:

Period: Format([fldDate]), "yyyy-mm")

It will work beautifully as Column Header in a crosstab, and sort correctly.

Note: in the above example I have assumed your date field to be fldDate. I
understand your field is actually named Date; it would be advisable to
change the field name to something else, as Date is a reserved keyword in
Access, and you may eventually run into problems in your design as a result
of the confusion (yes, Access can be confused under similar conditions).

HTH,
Nikos

Thanks for your replay.

I need to have Crosstab queries to calc values and I think
isn't possible to filter the Date.

Therefore, I have one query for month and I have in Date
Column:

Between (#01/02/2001#) And (#28/02/2001#)

I don't know solution for this...

Thanks.
Sev
-----Original Message-----
Sev,

To begin with, it looks like you have started off with a poor design; a
separate queruy for each month shouldn't be necessary, you should have one
query with a month filter.

Now, getting to the particular question: once you have changed your design
to a single query, "feed" the graph from it, and make
Click to expand...
the
month criterion in
the query read directly from the combo on the form
(Forms![FormName]![ComboName]), and the job is done.

HTH,
Nikos

I have monthly data in queries. One query to each month.
In form, trough combobox, I need to choose the monthly
query to "nurture" the graph in subform.
I would like help, because I don´t know the best way to do
this.
(And I never saw any sample)
Any idea is welcome.
Sev


.
Click to expand...


.
Click to expand...


.
Click to expand...
 
Sev,

Now we're getting somewhere. No need for a separate query by month, a single
query will do it. I'll assume the following names, and you'll have to
substitute the real ones:

Form name: Form1
Add two combo boxes: cboMonth, cboYear

Type the year values like:
2002, 2003, 2004 etc. in the rowsource property of cboYear (having set its
rowsource type to Value List)
For cboMonth: make a lookup table (called, say, tblMonths) with two fields,
like:
Mnth# MnthName
1 January
2 February
....
....
12 December

Make cboMonth 's rowsource the table, selecting both columns (fields), set
the Column Count property to 2, and the Column width property to something
like 0;3 so the first column (month number) does not show in the drop-down
list. Leave the Bound column property to the default 1 (so the combo "shows"
the month name, but returns the number!).

Now, make a query on Table1 (to base the graph on) like you did for a given
month, but instead of typing in criteria for the date like you did, do the
following:
Add two calculated fields:
fltrMonth: Month([Day])
fltrYear: Year([Day])

and type in these criteria respectively:

Forms![Form1]!cboMonth
Forms![Form1]!cboYear

So, the query reads the selected Month and Year from the combos on the form
everytime you run it! All you need is to have the form open and valid
selections in the combos. You can put the combos on the same form that hosts
the graph, and have the graph display the new data immediately as you change
the selection in the combos. Just be careful to use the right form and
control names in the expressions.

Note: Day is also an Access reserved keyword, so it would be advisable to
change your field name to something different.

HTH,
Nikos


Thanks for your reply.

Perhaps my ambition is to be great, but...

Table1:
Param, Text, 50
Day, Medium Date, dd-mmm-yy
Value, Single, Standard,2

In the limit what I intend it is to have a query to choose
Month to "feed" the graph in one form to present graphs
with the 30 days of the Month choosed MonthYear.

One more time, many thanks.
Sev
-----Original Message-----
Sev,

There is definitely a solution, if you care to explain! What is your table
like, and what are you trying to achieve? Where do you have "dd-mmm-yy", in
the table or as column headers in the crosstab?

Nikos

Nikos,

Thanks for your solution, but, lamently I have "dd-mmm- yy".
It is more complicated...
Sev
-----Original Message-----
Sev,

If you want monthly columns in your crosstab, then use a calculated field in
your query like:

Period: Format([fldDate]), "yyyy-mm")

It will work beautifully as Column Header in a crosstab, and sort correctly.

Note: in the above example I have assumed your date field to be fldDate. I
understand your field is actually named Date; it would be advisable to
change the field name to something else, as Date is a reserved keyword in
Access, and you may eventually run into problems in your design as a result
of the confusion (yes, Access can be confused under similar conditions).

HTH,
Nikos

Thanks for your replay.

I need to have Crosstab queries to calc values and I think
isn't possible to filter the Date.

Therefore, I have one query for month and I have in Date
Column:

Between (#01/02/2001#) And (#28/02/2001#)

I don't know solution for this...

Thanks.
Sev
-----Original Message-----
Sev,

To begin with, it looks like you have started off with a poor design; a
separate queruy for each month shouldn't be necessary, you should have one
query with a month filter.

Now, getting to the particular question: once you have changed your design
to a single query, "feed" the graph from it, and make
Click to expand...
the
month criterion in
the query read directly from the combo on the form
(Forms![FormName]![ComboName]), and the job is done.

HTH,
Nikos

I have monthly data in queries. One query to each month.
In form, trough combobox, I need to choose the monthly
query to "nurture" the graph in subform.
I would like help, because I don´t know the best way to do
this.
(And I never saw any sample)
Any idea is welcome.
Sev


.
Click to expand...


.
Click to expand...


.
Click to expand...
 
Exactly..., Nikos.

Good solution, for my problem.
(I wait to be capable to implement it)
Have you a good week-end and good luck.

Many thanks.
Sev
-----Original Message-----
Sev,

Now we're getting somewhere. No need for a separate query by month, a single
query will do it. I'll assume the following names, and you'll have to
substitute the real ones:

Form name: Form1
Add two combo boxes: cboMonth, cboYear

Type the year values like:
2002, 2003, 2004 etc. in the rowsource property of cboYear (having set its
rowsource type to Value List)
For cboMonth: make a lookup table (called, say, tblMonths) with two fields,
like:
Mnth# MnthName
1 January
2 February
....
....
12 December

Make cboMonth 's rowsource the table, selecting both columns (fields), set
the Column Count property to 2, and the Column width property to something
like 0;3 so the first column (month number) does not show in the drop-down
list. Leave the Bound column property to the default 1 (so the combo "shows"
the month name, but returns the number!).

Now, make a query on Table1 (to base the graph on) like you did for a given
month, but instead of typing in criteria for the date like you did, do the
following:
Add two calculated fields:
fltrMonth: Month([Day])
fltrYear: Year([Day])

and type in these criteria respectively:

Forms![Form1]!cboMonth
Forms![Form1]!cboYear

So, the query reads the selected Month and Year from the combos on the form
everytime you run it! All you need is to have the form open and valid
selections in the combos. You can put the combos on the same form that hosts
the graph, and have the graph display the new data immediately as you change
the selection in the combos. Just be careful to use the right form and
control names in the expressions.

Note: Day is also an Access reserved keyword, so it would be advisable to
change your field name to something different.

HTH,
Nikos


Thanks for your reply.

Perhaps my ambition is to be great, but...

Table1:
Param, Text, 50
Day, Medium Date, dd-mmm-yy
Value, Single, Standard,2

In the limit what I intend it is to have a query to choose
Month to "feed" the graph in one form to present graphs
with the 30 days of the Month choosed MonthYear.

One more time, many thanks.
Sev
-----Original Message-----
Sev,

There is definitely a solution, if you care to explain! What is your table
like, and what are you trying to achieve? Where do you have "dd-mmm-yy", in
the table or as column headers in the crosstab?

Nikos

Nikos,

Thanks for your solution, but, lamently I have "dd-mmm- yy".
It is more complicated...
Sev
-----Original Message-----
Sev,

If you want monthly columns in your crosstab, then use a calculated field in
your query like:

Period: Format([fldDate]), "yyyy-mm")

It will work beautifully as Column Header in a crosstab, and sort correctly.

Note: in the above example I have assumed your date
Click to expand...
field
to be fldDate. I
understand your field is actually named Date; it would
Click to expand...
be
advisable to
change the field name to something else, as Date is a reserved keyword in
Access, and you may eventually run into problems in your design as a result
of the confusion (yes, Access can be confused under similar conditions).

HTH,
Nikos

Thanks for your replay.

I need to have Crosstab queries to calc values and I think
isn't possible to filter the Date.

Therefore, I have one query for month and I have in Date
Column:

Between (#01/02/2001#) And (#28/02/2001#)

I don't know solution for this...

Thanks.
Sev

-----Original Message-----
Sev,

To begin with, it looks like you have started off with a
poor design; a
separate queruy for each month shouldn't be necessary,
you should have one
query with a month filter.

Now, getting to the particular question: once you have
changed your design
to a single query, "feed" the graph from it, and make the
month criterion in
the query read directly from the combo on the form
(Forms![FormName]![ComboName]), and the job is done.

HTH,
Nikos

message
I have monthly data in queries. One query to each month.
In form, trough combobox, I need to choose the monthly
query to "nurture" the graph in subform.
I would like help, because I don´t know the best way to do
this.
(And I never saw any sample)
Any idea is welcome.
Sev


.



.
Click to expand...


.
Click to expand...


.
Click to expand...
 
Back
Top