Comparing data from month to month (1 Viewer)

[MattioMatt]

I have a crosstab query which I am using my forms record source.

The query itself looks like this:

Month | Low | Medium | High | Very High |
February 2018 0 5 1 2
January 2018 0 4 2 6

I've used the statuses as column headers as that is what I wanted my text boses to be populated with. So I'm using the headers as the control sources of my text boxes on the form.

The form I've built has a combobox to select the month I'd like to view data for. However what I'd like to do is be able to have two combox boxes and some more text boxes so you can view data for one month and then compare it against another month. Is there a way of doing that? Can I get this to work in a query to then use on the form?

I hope I'm explaining what I'm trying to well enough.

 

[isladogs]

Use the months selected in the two combos as filter criteria for your query
Something similar to

Forms!formname.combo1 or Forms!Formname.combo2

That will give you two records

 

[MattioMatt]

Hi Ridders,

Thanks for you reply! I'm struggling to understand how that would work on a form. Perhaps this is more a form forum query.

As I have 4 text boxes which have the control sources of Low, Medium, High, Very High - I want to have another 4 text boxes so I can compare side by side on the form, but obviously I only have "one set" of control sources to use, so if I set the additional 4 text boxes to the same control sources then the values mirror each other.
I was wondering if there is a way to do something in the querey so I have a total of 8 control sources that I can use to bound my text boxes to.

The image below is something I'm looking to do.

 

[isladogs]

Hi Ridders,

Thanks for you reply! I'm struggling to understand how that would work on a form. Perhaps this is more a form forum query.

As I have 4 text boxes which have the control sources of Low, Medium, High, Very High - I want to have another 4 text boxes so I can compare side by side on the form, but obviously I only have "one set" of control sources to use, so if I set the additional 4 text boxes to the same control sources then the values mirror each other.
I was wondering if there is a way to do something in the querey so I have a total of 8 control sources that I can use to bound my text boxes to.

The image below is something I'm looking to do.

I was assuming you would use the filtered crosstab query with the two records as the record source for a subform

If you want it displayed like your picture there is no point using a crosstab.
In fact its unhelpful for this outcome.

Instead create an aggregate query where you group by month and by the field containing low, high etc and then count that same field. Now use that query as the basis for two subforms with the combos and 4 textboxes

 

[MattioMatt]

So if I keep the crosstab query, you mentioned the subform, would I be right in assuming your thoughts were to have a subform on the main form? Both forms have the record source of my query, one combobox updates the main form and then the other updates the subform? Not sure I understand the two records as the record source for a subform

 

[isladogs]

So if I keep the crosstab query, you mentioned the subform, would I be right in assuming your thoughts were to have a subform on the main form? Both forms have the record source of my query, one combobox updates the main form and then the other updates the subform? Not sure I understand the two records as the record source for a subform

By definition a subform is contained on a parent form.

If you keep the crosstab, the subform will look exactly like the crosstab with each month as one record. In that case, the two combos will be on the main form and once both are selected use code to update the subform.

Or scrap the crosstab and use an aggregate query as previously described with the two subform layout.

Your choice. Both will work but only by using the right type of query in each case

 

[MattioMatt]

Thanks Ridders!

You've been most helpful, I'm truly grateful, the main reason for staying with the crosstab is that Low, Medium, High, Very High are the row headings so I get the option to set the text box control sources to those fields. So I have Month, Low, High, Medium, High, Very High

If I use an aggregate query then I only have the Month, Status, Count.

 

[isladogs]

Thanks Ridders!

You've been most helpful, I'm truly grateful, the main reason for staying with the crosstab is that Low, Medium, High, Very High are the row headings so I get the option to set the text box control sources to those fields. So I have Month, Low, High, Medium, High, Very High

If I use an aggregate query then I only have the Month, Status, Count.

As I said its up to you.

Personally I'd go with the crosstab anyway as originally planned

However, if using the aggregate query the values for the Status field will be Low, Medium, High, Very High so you can set the label captions to those & use several DLookup functions to get the count values for the textboxes

 

[arnelgp]

Use unbound textbixes and on the AfterUpate of the combo fill the values of the corresponding textbox using Dlookup against the xtab.