Sorting unrelated controls on a report

[TonyWilliams via AccessMonster.com]

I have a report that has 5 calculated controls which are numeric. The report
is based on a query. Is there anyway I can sort the controls into ascending
order each time it is produced? They have all different control names and the
values may differ each time the report is run so I don't see how I can use
the Sorting/Grouping facility.
Many thanks
Tony

 

[Duane Hookom]

I don't have any idea what you mean by "sort" controls. Do you actually want
to sort the records in a report based on a calculated control value in the
report? If so, you can't.

You can only sort by values that can be derived from single records in your
report's record source query. Typically, if you can calculate a value in a
control, you can get the value into the report's record source.

 

[TonyWilliams via AccessMonster.com]

Hi Duane, that's what I thought, I think it's back to the drawing board time.
Thank anyway
Tony

I don't have any idea what you mean by "sort" controls. Do you actually want
to sort the records in a report based on a calculated control value in the
report? If so, you can't.

You can only sort by values that can be derived from single records in your
report's record source query. Typically, if you can calculate a value in a
control, you can get the value into the report's record source.

I have a report that has 5 calculated controls which are numeric. The report
is based on a query. Is there anyway I can sort the controls into ascending

[quoted text clipped - 3 lines]

Many thanks
Tony

 

[KARL DEWEY]

Don't give up just try explaining some more with examples.
--
Build a little, test a little.

Hi Duane, that's what I thought, I think it's back to the drawing board time.
Thank anyway
Tony

I don't have any idea what you mean by "sort" controls. Do you actually want
to sort the records in a report based on a calculated control value in the
report? If so, you can't.

You can only sort by values that can be derived from single records in your
report's record source query. Typically, if you can calculate a value in a
control, you can get the value into the report's record source.

I have a report that has 5 calculated controls which are numeric. The report
is based on a query. Is there anyway I can sort the controls into ascending

[quoted text clipped - 3 lines]

Many thanks
Tony

--
Why don't my grey cells communicate with each as fast as they used to? I hate
getting old!



.

 

[TonyWilliams via AccessMonster.com]

Hi Karl, I don't normally give up but this has got me foxed so here goes:

I have 7 fields which are categories of funding (I'll call them cat1, cat2,
cat3 etc) that hold a percentage value of the percentage split of another
field (txtline) So txtline might be 20.6 of which the value of cat1 is 70,
cat2 is 16 and cat3 is 14, the other 4 values are 0. This means that of the
total of 20.6, 70% is category cat1 and 16% is category cat2 and 14% is cat3.


I then have to calculate what is the value of the categories using this
formula: (txtline * cat1)/100 So this calculation for my example gives me 14,
3 and 3 (the figures are rounde d to the nearest whole number) I'll call
these values Val1, Val2 and Val3 etc

I then calculate the total of my values as Sum(txtline) and Sum(Val1), Sum
(Val2), Sum(Val3) etc

I then calculate the weighted average as (Sum(Val1)/Sum(txtline))*100 for
each category.

The weighted averages are what appears on my report in the controls so I have
7 controls which hold a numeric value which I would like to sort with the
largest first but I can't see any way of sorting them.

Hope you can follow that, I'm not a statistician but the guy who gave me the
forumala is, and that's how he calculates the expected result.

Cheers
Tony

Don't give up just try explaining some more with examples.

Hi Duane, that's what I thought, I think it's back to the drawing board time.
Thank anyway

[quoted text clipped - 13 lines]

 

[Duane Hookom]

Your issue is un-normalized tables. Rather than 7 "cat" fields, you should
have up to 7 records in a related/child table. You can then easily sort the
records in the related table based on the highest to lowest percentage.

You could then use a subreport to display these records either horizontally
or vertically.

If you can't or won't change your table structure, you could use a
normalizing union query.
--
Duane Hookom
Microsoft Access MVP

Hi Karl, I don't normally give up but this has got me foxed so here goes:

I have 7 fields which are categories of funding (I'll call them cat1, cat2,
cat3 etc) that hold a percentage value of the percentage split of another
field (txtline) So txtline might be 20.6 of which the value of cat1 is 70,
cat2 is 16 and cat3 is 14, the other 4 values are 0. This means that of the
total of 20.6, 70% is category cat1 and 16% is category cat2 and 14% is cat3.


I then have to calculate what is the value of the categories using this
formula: (txtline * cat1)/100 So this calculation for my example gives me 14,
3 and 3 (the figures are rounde d to the nearest whole number) I'll call
these values Val1, Val2 and Val3 etc

I then calculate the total of my values as Sum(txtline) and Sum(Val1), Sum
(Val2), Sum(Val3) etc

I then calculate the weighted average as (Sum(Val1)/Sum(txtline))*100 for
each category.

The weighted averages are what appears on my report in the controls so I have
7 controls which hold a numeric value which I would like to sort with the
largest first but I can't see any way of sorting them.

Hope you can follow that, I'm not a statistician but the guy who gave me the
forumala is, and that's how he calculates the expected result.

Cheers
Tony

Don't give up just try explaining some more with examples.

Hi Duane, that's what I thought, I think it's back to the drawing board time.
Thank anyway

[quoted text clipped - 13 lines]

Many thanks
Tony

--
Why don't my grey cells communicate with each as fast as they used to? I hate
getting old!




.

 

[TonyWilliams via AccessMonster.com]

Thanks Duane, I (and I suspect many novice Access users) always struggle with
the question of normailsation. I think I understand the concept but not sure
it applies in this case, but please correct me if I'm wrong.

If I had a record in my main table that held a field where the value could be
any one of a fixed list, then I would put that fixed list in a seperate table
and populate a field in my main table with whatever choice was made from say
a combo box. But in this case, a record in my main table could have a value
in ALL 7 of the various "categories" as what they are doing is effectively
listing what percentage of another field their value represents. So I thought
that I would need 7 fields to store each value for each record.

I hope I've explained that and would welcome any ideas if I've got it wrong.
Regards
Tony

Your issue is un-normalized tables. Rather than 7 "cat" fields, you should
have up to 7 records in a related/child table. You can then easily sort the
records in the related table based on the highest to lowest percentage.

You could then use a subreport to display these records either horizontally
or vertically.

If you can't or won't change your table structure, you could use a
normalizing union query.

Hi Karl, I don't normally give up but this has got me foxed so here goes:

[quoted text clipped - 31 lines]

 

[Duane Hookom]

My opinion stands. I would still have a related table with one record per
category. This would make it much easier (at least for me) to (per your
subject) "Sorting unrelated controls on a report". It's fairly easy to sort
records by value.
--
Duane Hookom
Microsoft Access MVP

Thanks Duane, I (and I suspect many novice Access users) always struggle with
the question of normailsation. I think I understand the concept but not sure
it applies in this case, but please correct me if I'm wrong.

If I had a record in my main table that held a field where the value could be
any one of a fixed list, then I would put that fixed list in a seperate table
and populate a field in my main table with whatever choice was made from say
a combo box. But in this case, a record in my main table could have a value
in ALL 7 of the various "categories" as what they are doing is effectively
listing what percentage of another field their value represents. So I thought
that I would need 7 fields to store each value for each record.

I hope I've explained that and would welcome any ideas if I've got it wrong.
Regards
Tony

Your issue is un-normalized tables. Rather than 7 "cat" fields, you should
have up to 7 records in a related/child table. You can then easily sort the
records in the related table based on the highest to lowest percentage.

You could then use a subreport to display these records either horizontally
or vertically.

If you can't or won't change your table structure, you could use a
normalizing union query.

Hi Karl, I don't normally give up but this has got me foxed so here goes:

[quoted text clipped - 31 lines]

Many thanks
Tony

--
Why don't my grey cells communicate with each as fast as they used to? I hate
getting old!




.

 

[Duane Hookom]

I thought I replied earlier but don't see it. If this is a duplicate then
this just enforces my opinion.

You should have a related table so the records can more easily be displayed
in the report. Normalize, normalize, normalize...

--
Duane Hookom
Microsoft Access MVP

Thanks Duane, I (and I suspect many novice Access users) always struggle with
the question of normailsation. I think I understand the concept but not sure
it applies in this case, but please correct me if I'm wrong.

If I had a record in my main table that held a field where the value could be
any one of a fixed list, then I would put that fixed list in a seperate table
and populate a field in my main table with whatever choice was made from say
a combo box. But in this case, a record in my main table could have a value
in ALL 7 of the various "categories" as what they are doing is effectively
listing what percentage of another field their value represents. So I thought
that I would need 7 fields to store each value for each record.

I hope I've explained that and would welcome any ideas if I've got it wrong.
Regards
Tony

Your issue is un-normalized tables. Rather than 7 "cat" fields, you should
have up to 7 records in a related/child table. You can then easily sort the
records in the related table based on the highest to lowest percentage.

You could then use a subreport to display these records either horizontally
or vertically.

If you can't or won't change your table structure, you could use a
normalizing union query.

Hi Karl, I don't normally give up but this has got me foxed so here goes:

[quoted text clipped - 31 lines]

Many thanks
Tony

--
Why don't my grey cells communicate with each as fast as they used to? I hate
getting old!




.

 

[TonyWilliams via AccessMonster.com]

Thanks Duane, could I prevail on you further then.

Just to go over my problem again this is an extract from a previous post in
this thread where I tried to explain the problem:
I have 7 fields which are categories of funding (I'll call them cat1, cat2,
cat3 etc) that hold a percentage value of the percentage split of another
field (txtline) So txtline might be 20.6 of which the value of cat1 is 70,
cat2 is 16 and cat3 is 14, the other 4 values in this case are 0 but could be
mumbers. This means that of the total of 20.6, 70% is category cat1 and 16%
is category cat2 and 14% is cat3 and 0% for the other categories.

I then have to calculate what is the value of the categories using this
formula: (txtline * cat1)/100 So this calculation for my example gives me 14,
3 and 3 (the figures are rounded to the nearest whole number) I'll call
these values Val1, Val2 and Val3 etc

I then calculate the total of my values as Sum(txtline) and Sum(Val1), Sum
(Val2), Sum(Val3) etc

I then calculate the weighted average as (Sum(Val1)/Sum(txtline))*100 for
each category.

The weighted averages are what appears on my report in the controls so I have
7 controls which hold a numeric value which I would like to sort with the
largest first but I can't see any way of sorting them.

I do realise that I shouldn't start with a form but the people that supply
the data do so on a paper form (!) that has these values.

Could you suggest abetter way of doing this as at the moment my fields for
the 7 values sit in my main table along with the value of txtline.

many thanks
Tony

I thought I replied earlier but don't see it. If this is a duplicate then
this just enforces my opinion.

You should have a related table so the records can more easily be displayed
in the report. Normalize, normalize, normalize...

Thanks Duane, I (and I suspect many novice Access users) always struggle with
the question of normailsation. I think I understand the concept but not sure

[quoted text clipped - 26 lines]

 

[Duane Hookom]

My answer still doesn't change. I don't know what your table or field names
are but let's assume you have:
tblNotNormal
===============
NotID autonumber primary key
txtLine number/double
cat1
cat2
cat3
cat4
cat5
cat6
cat7

IMO, this should be:
tblNormal
===============
NorID autonumber primary key
txtLine number/double

tblCategories
==================
CatID autonumber primary key
Category text title of category

tblNormalCategories (junction table)
====================
NormCatID autonumber primary key (I always have this)
NorID long integer relates to tblNormal.NorID
CatID long integer relates to tblCategories.CatID
NCatPct numeric double to store the percent

This solution allows you to add/remove categories and store percents for 1
or a dozen categories. These can be displayed on a report in almost any
format you want. If you want to view each category percent in columns by
NorID you can create a crosstab.

You should never design a table based on how you want the data to appear on
a form or report. If a form/interface is so critical, you can consider
using some code or whatever. But user-interface doesn't determine table
structures.

Duane Hookom
MS Access MVP

Thanks Duane, could I prevail on you further then.

Just to go over my problem again this is an extract from a previous post
in
this thread where I tried to explain the problem:
I have 7 fields which are categories of funding (I'll call them cat1,
cat2,
cat3 etc) that hold a percentage value of the percentage split of another
field (txtline) So txtline might be 20.6 of which the value of cat1 is 70,
cat2 is 16 and cat3 is 14, the other 4 values in this case are 0 but could
be
mumbers. This means that of the total of 20.6, 70% is category cat1 and
16%
is category cat2 and 14% is cat3 and 0% for the other categories.

I then have to calculate what is the value of the categories using this
formula: (txtline * cat1)/100 So this calculation for my example gives me
14,
3 and 3 (the figures are rounded to the nearest whole number) I'll call
these values Val1, Val2 and Val3 etc

I then calculate the total of my values as Sum(txtline) and Sum(Val1), Sum
(Val2), Sum(Val3) etc

I then calculate the weighted average as (Sum(Val1)/Sum(txtline))*100 for
each category.

The weighted averages are what appears on my report in the controls so I
have
7 controls which hold a numeric value which I would like to sort with the
largest first but I can't see any way of sorting them.

I do realise that I shouldn't start with a form but the people that supply
the data do so on a paper form (!) that has these values.

Could you suggest abetter way of doing this as at the moment my fields for
the 7 values sit in my main table along with the value of txtline.

many thanks
Tony

I thought I replied earlier but don't see it. If this is a duplicate then
this just enforces my opinion.

You should have a related table so the records can more easily be
displayed
in the report. Normalize, normalize, normalize...

Thanks Duane, I (and I suspect many novice Access users) always struggle
with
the question of normailsation. I think I understand the concept but not
sure

[quoted text clipped - 26 lines]

Many thanks
Tony

 

[Duane Hookom]

Seems to me I keep replying but the posts don't show in either the news
reader or web interface. Why not normalize your table structure to create
related records where each record is a value for a single category? That
structure allows you to sort and multiply and calculate etc.

I would not allow a desired screen/form layout determine a table structure.

Duane Hookom
MS Access MVP

Thanks Duane, could I prevail on you further then.

Just to go over my problem again this is an extract from a previous post
in
this thread where I tried to explain the problem:
I have 7 fields which are categories of funding (I'll call them cat1,
cat2,
cat3 etc) that hold a percentage value of the percentage split of another
field (txtline) So txtline might be 20.6 of which the value of cat1 is 70,
cat2 is 16 and cat3 is 14, the other 4 values in this case are 0 but could
be
mumbers. This means that of the total of 20.6, 70% is category cat1 and
16%
is category cat2 and 14% is cat3 and 0% for the other categories.

I then have to calculate what is the value of the categories using this
formula: (txtline * cat1)/100 So this calculation for my example gives me
14,
3 and 3 (the figures are rounded to the nearest whole number) I'll call
these values Val1, Val2 and Val3 etc

I then calculate the total of my values as Sum(txtline) and Sum(Val1), Sum
(Val2), Sum(Val3) etc

I then calculate the weighted average as (Sum(Val1)/Sum(txtline))*100 for
each category.

The weighted averages are what appears on my report in the controls so I
have
7 controls which hold a numeric value which I would like to sort with the
largest first but I can't see any way of sorting them.

I do realise that I shouldn't start with a form but the people that supply
the data do so on a paper form (!) that has these values.

Could you suggest abetter way of doing this as at the moment my fields for
the 7 values sit in my main table along with the value of txtline.

many thanks
Tony

I thought I replied earlier but don't see it. If this is a duplicate then
this just enforces my opinion.

You should have a related table so the records can more easily be
displayed
in the report. Normalize, normalize, normalize...

Thanks Duane, I (and I suspect many novice Access users) always struggle
with
the question of normailsation. I think I understand the concept but not
sure

[quoted text clipped - 26 lines]

Many thanks
Tony

 

[TonyWilliams via AccessMonster.com]

Thanks Duane I understand that now. Based on your structure with the three
tables linked could you describe how I would set up an input form with a
value for txtline and the ability to store 7 categories against that one line?
I'm sorry to keep going on about the form and fully understand your reasoning
but I can't quite see how the form would look to enable me to input values
for 7 categories.

Thanks I really feel as though I'm getting soemwhere now.
Thanks again
Tony

My answer still doesn't change. I don't know what your table or field names
are but let's assume you have:
tblNotNormal
===============
NotID autonumber primary key
txtLine number/double
cat1
cat2
cat3
cat4
cat5
cat6
cat7

IMO, this should be:
tblNormal
===============
NorID autonumber primary key
txtLine number/double

tblCategories
==================
CatID autonumber primary key
Category text title of category

tblNormalCategories (junction table)
====================
NormCatID autonumber primary key (I always have this)
NorID long integer relates to tblNormal.NorID
CatID long integer relates to tblCategories.CatID
NCatPct numeric double to store the percent

This solution allows you to add/remove categories and store percents for 1
or a dozen categories. These can be displayed on a report in almost any
format you want. If you want to view each category percent in columns by
NorID you can create a crosstab.

You should never design a table based on how you want the data to appear on
a form or report. If a form/interface is so critical, you can consider
using some code or whatever. But user-interface doesn't determine table
structures.

Duane Hookom
MS Access MVP

Thanks Duane, could I prevail on you further then.

[quoted text clipped - 51 lines]

 

[Duane Hookom]

To me, the form would resemble the Order Detail entry in Northwind with a
header record (Order) on the main form and detail records (Order Details) in
a subform. I don't know what your header record is even storing but your
details would be the categories and their percents or quantities..

If you or your users are stuck on a horizontal rather than vertical list of
categories then you might need to do some coding to fill unbound controls and
then save them back to a normalized table.

--
Duane Hookom
Microsoft Access MVP

Thanks Duane I understand that now. Based on your structure with the three
tables linked could you describe how I would set up an input form with a
value for txtline and the ability to store 7 categories against that one line?
I'm sorry to keep going on about the form and fully understand your reasoning
but I can't quite see how the form would look to enable me to input values
for 7 categories.

Thanks I really feel as though I'm getting soemwhere now.
Thanks again
Tony

My answer still doesn't change. I don't know what your table or field names
are but let's assume you have:
tblNotNormal
===============
NotID autonumber primary key
txtLine number/double
cat1
cat2
cat3
cat4
cat5
cat6
cat7

IMO, this should be:
tblNormal
===============
NorID autonumber primary key
txtLine number/double

tblCategories
==================
CatID autonumber primary key
Category text title of category

tblNormalCategories (junction table)
====================
NormCatID autonumber primary key (I always have this)
NorID long integer relates to tblNormal.NorID
CatID long integer relates to tblCategories.CatID
NCatPct numeric double to store the percent

This solution allows you to add/remove categories and store percents for 1
or a dozen categories. These can be displayed on a report in almost any
format you want. If you want to view each category percent in columns by
NorID you can create a crosstab.

You should never design a table based on how you want the data to appear on
a form or report. If a form/interface is so critical, you can consider
using some code or whatever. But user-interface doesn't determine table
structures.

Duane Hookom
MS Access MVP

Thanks Duane, could I prevail on you further then.

[quoted text clipped - 51 lines]

Many thanks
Tony

--
Why don't my grey cells communicate with each as fast as they used to? I hate
getting old!



.

 

[TonyWilliams via AccessMonster.com]

Thanks for your help Duane but I think I have to admit defeat, I'm totally
out of my depth having tried to crack this for 6 days now.

I've had a look at the Northwind database and whilst I think I understand the
concept and what is happening there I just can't realte it to what I am doing.
I realise that is probably my inexperience (or the fact that at 65 my brain
cells don't function like they used to) and I really hate being beaten but I
think it's back the drawing board.

Thanks for all your input though it was really appreciated and I'm sorry I
didn't live up to expectations.

So I'll go and try another tack
Cheers
Tony

To me, the form would resemble the Order Detail entry in Northwind with a
header record (Order) on the main form and detail records (Order Details) in
a subform. I don't know what your header record is even storing but your
details would be the categories and their percents or quantities..

If you or your users are stuck on a horizontal rather than vertical list of
categories then you might need to do some coding to fill unbound controls and
then save them back to a normalized table.

Thanks Duane I understand that now. Based on your structure with the three
tables linked could you describe how I would set up an input form with a

[quoted text clipped - 57 lines]

 

[TonyWilliams via AccessMonster.com]

Just one thing Duane. where would I store the value of the category for any
particular record? Wouldn't I need a field in the main table to store the
value selected from the tblcategories, which presumably would be a drop down
combo box on my form?
Am I way off beam?
Tony

To me, the form would resemble the Order Detail entry in Northwind with a
header record (Order) on the main form and detail records (Order Details) in
a subform. I don't know what your header record is even storing but your
details would be the categories and their percents or quantities..

If you or your users are stuck on a horizontal rather than vertical list of
categories then you might need to do some coding to fill unbound controls and
then save them back to a normalized table.

Thanks Duane I understand that now. Based on your structure with the three
tables linked could you describe how I would set up an input form with a

[quoted text clipped - 57 lines]

 

[Duane Hookom]

Apparently you are storing information about categories of funding as they
relate to something. You haven't (at least not in this thread) provided your
table structure and what the funding relates to. Let's just assume these are
projects so your table structure is:

tblProjects
===================
ProjID primary key
ProjName
ProjStartDate
txtLine (this has your total amount)
Cat1
Cat2
--- etc ---

I am simply suggesting you remove all the "Cat" fields from tblProjects and
creating a "child" table with a structure like:

tblProjectFunding
============
ProjFundID
ProjID links to tblProject.ProjID
Category (these might correspond to a value that you once had as cat field
names)
FundPct store the percentage value

You would have a main form based on tblProject and a continuous subform
based on tblProjectFunding. The subform would display 2 columns bound to
Category and FundPct. Category would most likely be a dropdown of
available/possible funding categories. Ideally you would have a small lookup
table created for the user to select a category and then enter the Pct.

If you can't figure this out, please come back with your current table
structure with table and field names as well as what is the purpose of your
table.
--
Duane Hookom
Microsoft Access MVP

Just one thing Duane. where would I store the value of the category for any
particular record? Wouldn't I need a field in the main table to store the
value selected from the tblcategories, which presumably would be a drop down
combo box on my form?
Am I way off beam?
Tony

To me, the form would resemble the Order Detail entry in Northwind with a
header record (Order) on the main form and detail records (Order Details) in
a subform. I don't know what your header record is even storing but your
details would be the categories and their percents or quantities..

If you or your users are stuck on a horizontal rather than vertical list of
categories then you might need to do some coding to fill unbound controls and
then save them back to a normalized table.

Thanks Duane I understand that now. Based on your structure with the three
tables linked could you describe how I would set up an input form with a

[quoted text clipped - 57 lines]

Many thanks
Tony

--
Why don't my grey cells communicate with each as fast as they used to? I hate
getting old!



.

 

[TonyWilliams via AccessMonster.com]

Thanks Duane, I think I need to take a step back and examine what I'm trying
to do and recreate my database along the lines that you have suggested. It
may take me a while but I'll come back to you if I stiumble!
Thanks for your patience
Tony

Apparently you are storing information about categories of funding as they
relate to something. You haven't (at least not in this thread) provided your
table structure and what the funding relates to. Let's just assume these are
projects so your table structure is:

tblProjects
===================
ProjID primary key
ProjName
ProjStartDate
txtLine (this has your total amount)
Cat1
Cat2
--- etc ---

I am simply suggesting you remove all the "Cat" fields from tblProjects and
creating a "child" table with a structure like:

tblProjectFunding
============
ProjFundID
ProjID links to tblProject.ProjID
Category (these might correspond to a value that you once had as cat field
names)
FundPct store the percentage value

You would have a main form based on tblProject and a continuous subform
based on tblProjectFunding. The subform would display 2 columns bound to
Category and FundPct. Category would most likely be a dropdown of
available/possible funding categories. Ideally you would have a small lookup
table created for the user to select a category and then enter the Pct.

If you can't figure this out, please come back with your current table
structure with table and field names as well as what is the purpose of your
table.

Just one thing Duane. where would I store the value of the category for any
particular record? Wouldn't I need a field in the main table to store the

[quoted text clipped - 17 lines]