Too many querries

[Andy]

Hi;

Is there a simpler way to do the following?

A portion of our database records items sold. There are 6 categories,
"Clothing", "Stationary", Etc. Each category contains from 3 to 30 items.
The report returns the sub-total price, (the item's name is displayed in the
control's label).

I have created a query that returns the correct information using the
following expressions:

ClothingPrc: IIf([qryProdBuyTbl]![ProdBuyCatagoryName]="Clothing" And
[qryProdBuyTbl]![ProdBuySubCategoryName]="Shirt",[qryProdBuyTbl]![ProdBuyUni
tsBought]*[qryProdBuyTbl]![ProdBuyUnitPrice]," ")

StationaryPrc: IIf([qryProdBuyTbl]![ProdBuyCatagoryName]="Stationary" And
[qryProdBuyTbl]![ProdBuySubCategoryName]="Pens",[qryProdBuyTbl]![ProdBuyUnit
sBought]*[qryProdBuyTbl]![ProdBuyUnitPrice]," ")

ETC, ETC, ETC.

The obstacle I'm encountering is that each item prints on a separate report
page. In other words one page displays the price of the shirts, another for
shoes, a third for pens etc etc etc. The remainder of each page is blank.

I believe the reason it is doing this is because of the "blank lines" in the
query.

"Hide Duplicates" is not an appropriate answer because we have some products
that are the same price.

The only solution I can see at this time is creating a separate query for
each Sub-Category but that isn't practical. Too many queries would be
needed.

Anyone have a suggestion?

Thanks in advanced.

Andy

 

[Steve Schapel]

Andy,

Part of the problem is possibly due to the " " as the FalsePart
argument of your IIf expressions. Why do you want to put in a "space"
for all fields?

Anyway, that's beside the point, really, because I would suggest you
try a completely different tack. I haven't 100% grasped what the data
is in qryProdBuyTbl or how you want your report to turn out. But see
what it gives you, and see if you can use it instead, if you do
this...
Make a query based on qryProdBuyTbl.
Add the ProdBuyCatagoryName and ProdBuySubCategoryName fields to the
query design grid.
Enter a calculated field in the next column, like this...
Prc: [ProdBuyUnitsBought]*[ProdBuyUnitPrice]
Make it a Totals Query (select Totals from the View menu).
In the Totals row of the grid, leave Group By in the first 2 columns,
and enter First in the Totals row of the Prc column (or maybe it
should be Sum, I don't know without seeing more about your data).
Use this query as the basis of your report.

Apologies in advance if this is off-beam, based on trying to imagine
what you are really doing. But if I am off-beam, I would nevertheless
feel sure that there is a less complicated way of doing it than
running a separate IIf() function for each data category.

- Steve Schapel, Microsoft Access MVP

 

[Andy]

Steve;

Your suggestion gave me information that I wish I had months ago, would have
saved me a lot of work on another part of the database. Still need some
more help with this portion.

What I'm working on requires that the report conforms to a rigid government
form. I used the first examples of clothing sales to help simplify the
explanation. From your response I know you need more information, so here
it is.

As stated before the report contains 6 catagories and from 3 to 30 items
each. Following the example of clothing sales, the report is similar to
this:

Clothing (ProdBuyCatagoryName)
Shirts (ProdBuySubCategoryName) then the Price is displayed next to it.
(The buyer can choose several different types of shirts listed in a
Combobox, and the Total for all the shirts MUST be displayed next to the
ProdBuySubCategoryName.)

The Combobox contains normally carried brands, (Hanes, Fruit of the Loom,
Etc.), and it also includes an "Other" brand.
The normally carried brand name is listed on the report, the total of the
Price*AmountPurchased must be returned next to that particular brand, and
the "Other" must list it's brand name and it's total price next to it.

In some Catagories there are as many as 6 "Others", in some there is only 1
"Other".

Any suggestions to get me on the correct path to a solution?

Thank You!

Andy

Andy,

Part of the problem is possibly due to the " " as the FalsePart
argument of your IIf expressions. Why do you want to put in a "space"
for all fields?

Anyway, that's beside the point, really, because I would suggest you
try a completely different tack. I haven't 100% grasped what the data
is in qryProdBuyTbl or how you want your report to turn out. But see
what it gives you, and see if you can use it instead, if you do
this...
Make a query based on qryProdBuyTbl.
Add the ProdBuyCatagoryName and ProdBuySubCategoryName fields to the
query design grid.
Enter a calculated field in the next column, like this...
Prc: [ProdBuyUnitsBought]*[ProdBuyUnitPrice]
Make it a Totals Query (select Totals from the View menu).
In the Totals row of the grid, leave Group By in the first 2 columns,
and enter First in the Totals row of the Prc column (or maybe it
should be Sum, I don't know without seeing more about your data).
Use this query as the basis of your report.

Apologies in advance if this is off-beam, based on trying to imagine
what you are really doing. But if I am off-beam, I would nevertheless
feel sure that there is a less complicated way of doing it than
running a separate IIf() function for each data category.

- Steve Schapel, Microsoft Access MVP

Hi;

Is there a simpler way to do the following?

A portion of our database records items sold. There are 6 categories,
"Clothing", "Stationary", Etc. Each category contains from 3 to 30 items.
The report returns the sub-total price, (the item's name is displayed in the
control's label).

I have created a query that returns the correct information using the
following expressions:

ClothingPrc: IIf([qryProdBuyTbl]![ProdBuyCatagoryName]="Clothing" And
[qryProdBuyTbl]![ProdBuySubCategoryName]="Shirt",[qryProdBuyTbl]![ProdBuyUn

i

tsBought]*[qryProdBuyTbl]![ProdBuyUnitPrice]," ")

StationaryPrc: IIf([qryProdBuyTbl]![ProdBuyCatagoryName]="Stationary" And
[qryProdBuyTbl]![ProdBuySubCategoryName]="Pens",[qryProdBuyTbl]![ProdBuyUni

t

sBought]*[qryProdBuyTbl]![ProdBuyUnitPrice]," ")

ETC, ETC, ETC.

The obstacle I'm encountering is that each item prints on a separate report
page. In other words one page displays the price of the shirts, another for
shoes, a third for pens etc etc etc. The remainder of each page is blank.

I believe the reason it is doing this is because of the "blank lines" in the
query.

"Hide Duplicates" is not an appropriate answer because we have some products
that are the same price.

The only solution I can see at this time is creating a separate query for
each Sub-Category but that isn't practical. Too many queries would be
needed.

Anyone have a suggestion?

Thanks in advanced.

Andy

 

[Steve Schapel]

Andy,

I am happy to look at this in more detail with you, but it would help
if you could give me an outline of the table/fields structure of the
data we are dealing with, and maybe a fuller example of the output
required for the report.

In the meantime, here is an idea which might be pertinent... You
don't have to do all the data manipulation work in the query. If you
have a query that gives you:
Category SubCategory Brand Price*Amount
.... then you can base your report on this query as is, and use the
report's Sorting and Grouping facility to get the desired result. For
examply, make a Group Header for the Category and SubCategory fields.
In the SubCategory Header section of the report, you will then have
the ProdBuySubCategoryName and an unbound textbox with controlsource
set to =Sum([Prc]) which will show the total for all (e.g.) shirts.

- Steve Schapel, Microsoft Access MVP

 

[Andy]

Steve;

Thank you for replies. Your suggestions are getting me closer to a
solution.

I implimented you last suggestion and it is real close to the answer needed.
It does give all of the answers correctly, but I am having difficulty w/the
report's layout.

You asked for an outline of the Table/Field structure, I consider what I am
working on confidential, not secretive but confidential. Corporate piracy
is one of our concerns. For that reason, if it is acceptable to you, I will
continue to use the clothing analogy.

The tables consist of the "Main" table, (Purchaser's Name/Address/Etc), a
"Personal Info" table, (Place of Employment/Family background/Etc.), a
"Shopping History" table, (Products previously purchased at other
stores/Highest price paid/Etc.) and a "Products purchased from our store"
table, (Coats/Hats/Shirts/Shoes/Etc).

All of the tables have a one-to-many relationship using the Main table's ID.

The report needed has a mandated layout. It cannot vary in any way, or it
will be rejected.

The report is generated to one, and only one page, 8.5" X 14". To stay
within the limitations of the vast amount of possible printers I set the
margins to 7/10s of an inch all around, (which as you are most likely aware
will print on 90% of the printers on the market), with no headers or
footers. (tried it and it bleeds to next page).

The layout of this report is:

Customer Name/Address

Catagories:
Hats:
Stetson, (fixed, (Label's
caption))..........................................(Selling Price*Qty.
Purchased)
Other, (variable name that is generated by bound control)
Other, (variable name that is generated by bound control)......(Total of
both "Others" Selling Price*Qty. Purchased)
Sub-Total Hats ..................Amt.

Shirts:
Arrow, (fixed, (Label's
caption))...........................................(Selling Price*Qty.
Purchased)
Van Hussen, (fixed, (Label's
caption))...................................(Selling Price*Qty. Purchased)
Other, (variable name that is generated by bound control)......(Selling
Price*Qty. Purchased)
Other, (variable name that is generated by bound control)......(Selling
Price*Qty. Purchased)
Other, (variable name that is generated by bound control)......(Selling
Price*Qty. Purchased)
Other, (variable name that is generated by bound control)......(Selling
Price*Qty. Purchased)
Sub-Total Shirts..................Amt.

Total of all Catagories..................Amt.

Using your last suggestions I removed the space at the end of the
expression, (" "). I was also able to get the total selling price as it
should be. What I couldn't achieve was placing those answers in the proper
position in the report.

Tried using unbound controls: =IIf([ProdBuyCatagoryName]="Clothing" And
[ProdBuySubCategoryName]="Shirt",[Prc],""). Answer for that example was
displayed on page 3.

Even tried creating a second report and refering to the control on the
original report. No Go.

Steve; I am not attempting to get you to finish my work. What I am doing is
finishing a project that I have been working on for an extraordinarily long
period of time.

Thank you again for your previous replys and thank you for reading this.

Andy

Andy,

I am happy to look at this in more detail with you, but it would help
if you could give me an outline of the table/fields structure of the
data we are dealing with, and maybe a fuller example of the output
required for the report.

In the meantime, here is an idea which might be pertinent... You
don't have to do all the data manipulation work in the query. If you
have a query that gives you:
Category SubCategory Brand Price*Amount
... then you can base your report on this query as is, and use the
report's Sorting and Grouping facility to get the desired result. For
examply, make a Group Header for the Category and SubCategory fields.
In the SubCategory Header section of the report, you will then have
the ProdBuySubCategoryName and an unbound textbox with controlsource
set to =Sum([Prc]) which will show the total for all (e.g.) shirts.

- Steve Schapel, Microsoft Access MVP

Steve;

Your suggestion gave me information that I wish I had months ago, would have
saved me a lot of work on another part of the database. Still need some
more help with this portion.

What I'm working on requires that the report conforms to a rigid government
form. I used the first examples of clothing sales to help simplify the
explanation. From your response I know you need more information, so here
it is.

As stated before the report contains 6 catagories and from 3 to 30 items
each. Following the example of clothing sales, the report is similar to
this:

Clothing (ProdBuyCatagoryName)
Shirts (ProdBuySubCategoryName) then the Price is displayed next to it.
(The buyer can choose several different types of shirts listed in a
Combobox, and the Total for all the shirts MUST be displayed next to the
ProdBuySubCategoryName.)

The Combobox contains normally carried brands, (Hanes, Fruit of the Loom,
Etc.), and it also includes an "Other" brand.
The normally carried brand name is listed on the report, the total of the
Price*AmountPurchased must be returned next to that particular brand, and
the "Other" must list it's brand name and it's total price next to it.

In some Catagories there are as many as 6 "Others", in some there is only 1
"Other".

Any suggestions to get me on the correct path to a solution?

Thank You!

Andy

Andy,

Part of the problem is possibly due to the " " as the FalsePart
argument of your IIf expressions. Why do you want to put in a "space"
for all fields?

Anyway, that's beside the point, really, because I would suggest you
try a completely different tack. I haven't 100% grasped what the data
is in qryProdBuyTbl or how you want your report to turn out. But see
what it gives you, and see if you can use it instead, if you do
this...
Make a query based on qryProdBuyTbl.
Add the ProdBuyCatagoryName and ProdBuySubCategoryName fields to the
query design grid.
Enter a calculated field in the next column, like this...
Prc: [ProdBuyUnitsBought]*[ProdBuyUnitPrice]
Make it a Totals Query (select Totals from the View menu).
In the Totals row of the grid, leave Group By in the first 2 columns,
and enter First in the Totals row of the Prc column (or maybe it
should be Sum, I don't know without seeing more about your data).
Use this query as the basis of your report.

Apologies in advance if this is off-beam, based on trying to imagine
what you are really doing. But if I am off-beam, I would nevertheless
feel sure that there is a less complicated way of doing it than
running a separate IIf() function for each data category.

- Steve Schapel, Microsoft Access MVP


Hi;

Is there a simpler way to do the following?

A portion of our database records items sold. There are 6 categories,
"Clothing", "Stationary", Etc. Each category contains from 3 to 30 items.
The report returns the sub-total price, (the item's name is displayed in the
control's label).

I have created a query that returns the correct information using the
following expressions:

ClothingPrc: IIf([qryProdBuyTbl]![ProdBuyCatagoryName]="Clothing" And
[qryProdBuyTbl]![ProdBuySubCategoryName]="Shirt",[qryProdBuyTbl]![ProdBuyUni
tsBought]*[qryProdBuyTbl]![ProdBuyUnitPrice]," ")

StationaryPrc: IIf([qryProdBuyTbl]![ProdBuyCatagoryName]="Stationary" And
[qryProdBuyTbl]![ProdBuySubCategoryName]="Pens",[qryProdBuyTbl]![ProdBuyUnit
sBought]*[qryProdBuyTbl]![ProdBuyUnitPrice]," ")

ETC, ETC, ETC.

The obstacle I'm encountering is that each item prints on a separate report
page. In other words one page displays the price of the shirts, another for
shoes, a third for pens etc etc etc. The remainder of each page is blank.

I believe the reason it is doing this is because of the "blank lines" in the
query.

"Hide Duplicates" is not an appropriate answer because we have some products
that are the same price.

The only solution I can see at this time is creating a separate query for
each Sub-Category but that isn't practical. Too many queries would be
needed.

Anyone have a suggestion?

Thanks in advanced.

Andy

 

[Steve Schapel]

Andy,

I'm not sure what is not working for you, but here are a couple of
questions...

1. Have you checked that the total of the left + right page margins,
as defined under File|Page Setup, plus the width of the report as
designed, is less than the width of the paper?

2. Have you used the report's Sorting & Grouping dialog to define a
ProdBuyCatagoryName Footer and a ProdBuySubCategoryName Footer? If
so, have you then got an unbound textbox in both of these Footer
sections with control source set to =Sum([Prc]) ? (This assumes Prc
is the name of a calculated field in the query that the report is
based on, resulting from [Selling Price]*[Qty Purchased] or some such,
so Prc should therefore, I think, be in the Detail section of the
report, whereas it looks like you are recalculating these again in the
Detail section?)

We'll get there eventually!

- Steve Schapel, Microsoft Access MVP

Steve;

Thank you for replies. Your suggestions are getting me closer to a
solution.

I implimented you last suggestion and it is real close to the answer needed.
It does give all of the answers correctly, but I am having difficulty w/the
report's layout.

You asked for an outline of the Table/Field structure, I consider what I am
working on confidential, not secretive but confidential. Corporate piracy
is one of our concerns. For that reason, if it is acceptable to you, I will
continue to use the clothing analogy.

The tables consist of the "Main" table, (Purchaser's Name/Address/Etc), a
"Personal Info" table, (Place of Employment/Family background/Etc.), a
"Shopping History" table, (Products previously purchased at other
stores/Highest price paid/Etc.) and a "Products purchased from our store"
table, (Coats/Hats/Shirts/Shoes/Etc).

All of the tables have a one-to-many relationship using the Main table's ID.

The report needed has a mandated layout. It cannot vary in any way, or it
will be rejected.

The report is generated to one, and only one page, 8.5" X 14". To stay
within the limitations of the vast amount of possible printers I set the
margins to 7/10s of an inch all around, (which as you are most likely aware
will print on 90% of the printers on the market), with no headers or
footers. (tried it and it bleeds to next page).

The layout of this report is:

Customer Name/Address

Catagories:
Hats:
Stetson, (fixed, (Label's
caption))..........................................(Selling Price*Qty.
Purchased)
Other, (variable name that is generated by bound control)
Other, (variable name that is generated by bound control)......(Total of
both "Others" Selling Price*Qty. Purchased)
Sub-Total Hats ..................Amt.

Shirts:
Arrow, (fixed, (Label's
caption))...........................................(Selling Price*Qty.
Purchased)
Van Hussen, (fixed, (Label's
caption))...................................(Selling Price*Qty. Purchased)
Other, (variable name that is generated by bound control)......(Selling
Price*Qty. Purchased)
Other, (variable name that is generated by bound control)......(Selling
Price*Qty. Purchased)
Other, (variable name that is generated by bound control)......(Selling
Price*Qty. Purchased)
Other, (variable name that is generated by bound control)......(Selling
Price*Qty. Purchased)
Sub-Total Shirts..................Amt.

Total of all Catagories..................Amt.

Using your last suggestions I removed the space at the end of the
expression, (" "). I was also able to get the total selling price as it
should be. What I couldn't achieve was placing those answers in the proper
position in the report.

Tried using unbound controls: =IIf([ProdBuyCatagoryName]="Clothing" And
[ProdBuySubCategoryName]="Shirt",[Prc],""). Answer for that example was
displayed on page 3.

Even tried creating a second report and refering to the control on the
original report. No Go.

Steve; I am not attempting to get you to finish my work. What I am doing is
finishing a project that I have been working on for an extraordinarily long
period of time.

Thank you again for your previous replys and thank you for reading this.

Andy

 

[Andy]

Steve;

Thank you again for working with me.

In answer to your questions:
Re: Margins: They are 7/10" T/B/L/R. I was informed that using 7/10" all
around would allow the report to print on perhaps 90% of the printers on the
market today. This is important.
The page size is 8.5"X14" and it must print on one page and one page only.

This report's controls are .15", (point-one-five of an inch). There are so
many mandatory controls that the report takes up the entire area of the
page. I know this through trial and error.
The font is Book Antiqua 8 point. The font was the smallest of the
"standard" fonts that should be available on most machines.

Re: Query construction and Sorting/Grouping and Headers/Footers.
Yes. I've tried that, not getting correct results.
The Query, using: ProdBuyCatagoryName and ProdBuySubCategoryName fields and
Prc: [ProdBuyUnitsBought]*[ProdBuyUnitPrice] with either "First" or "Sum"
does not total all "Arrow" shirts, (short sleve, long sleve, pull over), but
shows total of all short sleeves, then total of all long sleeves, Etc.)

When a Header or Footer is added. The report generates 2 pages, the first
showing the Header, the second showing the actual report. Even set all
controls in the Header to "Visible=False".

Also tried creating unbound controls in the report that pulled from the
controls in the Header:
ArrowPrc: IIf([ProdBuyCatagoryName]="Shirt" And
[ProdBuySubCategoryName]="Arrow",[Prc],""), the report generated 95 pages
with one amount on each page.

Expanding on an earlier example of the report's mandated structure:

Customer Name/Address

Catagories:
Hats:
Stetson, (fixed, (Label's caption Shows
"Stetson")).....................................(SellingPrice*QtyPurchased)
Other, (variable name that is generated by bound control)
Other, (variable name that is generated by bound control)......(Total of
both "Others" SellingPrice*QtyPurchased)

Sub-Total Hats ..................Amt=[Stetson]+[Other]

There may be 2,3, or 4 Stetson hats but they all must total on that one
line.
The "Others" lines in this instance need to be limited to the first 2
entries in the input form, and after the word "Other" a bound control must
genterate the name of each product purchased.

Shirts:
Arrow, (fixed, (Label's
caption))...........................................(SellingPrice*QtyPurchas
ed)
Van Hussen, (fixed, (Label's
caption))...................................(SellingPrice*QtyPurchased)
Other, (variable name that is generated by bound
control)......(SellingPrice*QtyPurchased)
Other, (variable name that is generated by bound
control)......(SellingPrice*QtyPurchased)
Other, (variable name that is generated by bound
control)......(SellingPrice*QtyPurchased)
Other, (variable name that is generated by bound
control)......(SellingPrice*QtyPurchased)
Sub-Total
Shirts..................Amt=[Arrow]+[VanHussen]+[Other1]+[Other2]+[Other3]+[
Other4]+[Other5]

Again there may be 2,3, or 4 Arrow shirts, (short sleve, long sleve, pull
over), but they all must total on that one line.
And for this Catagory after the word "Other" a bound control must generate
the name of the product purchased with an individual amount for each line.
With a limit of 5 others and 5 individual amounts, may be none but must be
limited to 5

Total of all Catagories..................Amt.

Steve; I realize that I need to learn how to "talk" with an "MVP", meaning I
might call the total "Amt" where you may call it "Sum". If this is the case
please inform me.

Thank You Once Again.
Andy

Andy,

I'm not sure what is not working for you, but here are a couple of
questions...

1. Have you checked that the total of the left + right page margins,
as defined under File|Page Setup, plus the width of the report as
designed, is less than the width of the paper?

2. Have you used the report's Sorting & Grouping dialog to define a
ProdBuyCatagoryName Footer and a ProdBuySubCategoryName Footer? If
so, have you then got an unbound textbox in both of these Footer
sections with control source set to =Sum([Prc]) ? (This assumes Prc
is the name of a calculated field in the query that the report is
based on, resulting from [Selling Price]*[Qty Purchased] or some such,
so Prc should therefore, I think, be in the Detail section of the
report, whereas it looks like you are recalculating these again in the
Detail section?)

We'll get there eventually!

- Steve Schapel, Microsoft Access MVP

Steve;

Thank you for replies. Your suggestions are getting me closer to a
solution.

I implimented you last suggestion and it is real close to the answer needed.
It does give all of the answers correctly, but I am having difficulty w/the
report's layout.

You asked for an outline of the Table/Field structure, I consider what I am
working on confidential, not secretive but confidential. Corporate piracy
is one of our concerns. For that reason, if it is acceptable to you, I will
continue to use the clothing analogy.

The tables consist of the "Main" table, (Purchaser's Name/Address/Etc), a
"Personal Info" table, (Place of Employment/Family background/Etc.), a
"Shopping History" table, (Products previously purchased at other
stores/Highest price paid/Etc.) and a "Products purchased from our store"
table, (Coats/Hats/Shirts/Shoes/Etc).

All of the tables have a one-to-many relationship using the Main table's ID.

The report needed has a mandated layout. It cannot vary in any way, or it
will be rejected.

The report is generated to one, and only one page, 8.5" X 14". To stay
within the limitations of the vast amount of possible printers I set the
margins to 7/10s of an inch all around, (which as you are most likely aware
will print on 90% of the printers on the market), with no headers or
footers. (tried it and it bleeds to next page).

The layout of this report is:

Customer Name/Address

Catagories:
Hats:
Stetson, (fixed, (Label's
caption))..........................................(Selling Price*Qty.
Purchased)
Other, (variable name that is generated by bound control)
Other, (variable name that is generated by bound control)......(Total of
both "Others" Selling Price*Qty. Purchased)
Sub-Total Hats ..................Amt.

Shirts:
Arrow, (fixed, (Label's
caption))...........................................(Selling Price*Qty.
Purchased)
Van Hussen, (fixed, (Label's
caption))...................................(Selling Price*Qty. Purchased)
Other, (variable name that is generated by bound control)......(Selling
Price*Qty. Purchased)
Other, (variable name that is generated by bound control)......(Selling
Price*Qty. Purchased)
Other, (variable name that is generated by bound control)......(Selling
Price*Qty. Purchased)
Other, (variable name that is generated by bound control)......(Selling
Price*Qty. Purchased)
Sub-Total Shirts..................Amt.

Total of all Catagories..................Amt.

Using your last suggestions I removed the space at the end of the
expression, (" "). I was also able to get the total selling price as it
should be. What I couldn't achieve was placing those answers in the proper
position in the report.

Tried using unbound controls: =IIf([ProdBuyCatagoryName]="Clothing" And
[ProdBuySubCategoryName]="Shirt",[Prc],""). Answer for that example was
displayed on page 3.

Even tried creating a second report and refering to the control on the
original report. No Go.

Steve; I am not attempting to get you to finish my work. What I am doing is
finishing a project that I have been working on for an extraordinarily long
period of time.

Thank you again for your previous replys and thank you for reading this.

Andy

 

[Steve Schapel]

Andy,

Regarding the page dimensions, so, the width of the report in design
view has to a maximum of 7.1" i.e. 8.5-(0.7*2), is this correct?

Don't worry about the pedantries of using the correct word. But I
think there is a principle which you need to grasp. Which is that you
have to use the Sorting and Grouping facility of the report to get the
desired result. Probably the Detail section of the report should not
be Visible. I am not in a position to analyse what you have done via
the detail you have provided. I would need to see the actual report
and the actual data to do it on that level. Not that I'm offering...
we will get there eventually. But I can definitely tell you that
anything that looks like IIf([ProdBuyCatagoryName]="Shirt" And
[ProdBuySubCategoryName]="Arrow",[Prc],"") or anything that looks like
[Arrow]+[VanHussen]+[Other1]+[Other2]+[Other3]+[Other4]+[Other5] will
not work. The basic concept is that your query should provide you
with all your data in the form of:
ProdBuyCatagoryName ProdBuySubCategoryName Prc
(where prc = [ProdBuyUnitsBought]*[ProdBuyUnitPrice])
(Is this the same as SellingPrice*QtyPurchased, or his this another
"layer" to the overall picture?)
On your report, you need to have unbound textboxes with the
controlsource set to =Sum([Prc]) and these need to be placed as
required in the appropriate Group Footer sections of the report. You
used the example of the Arrow shirts. Andy, if Shirt is data in the
ProdBuyCatagoryName field, and Arrow is data in the
ProdBuySubCategoryName field, then if you put the
ProdBuySubCategoryName control on your report in the
ProdBuySubCategoryName Footer section, and the textbox with
=Sum([Prc]) in the ProdBuySubCategoryName Footer section, then this
*should* give you the total price of Arrow Shirts. If it doesn't, it
doesn't mean the concept is wrong. The concept is right... don't
abandon this idea. It's just that something has been missed in the
communication between you and me, and we need to fine-tune somewhere,
but the basic method is correct.

- Steve Schapel, Microsoft Access MVP

Steve;

Thank you again for working with me.

In answer to your questions:
Re: Margins: They are 7/10" T/B/L/R. I was informed that using 7/10" all
around would allow the report to print on perhaps 90% of the printers on the
market today. This is important.
The page size is 8.5"X14" and it must print on one page and one page only.

This report's controls are .15", (point-one-five of an inch). There are so
many mandatory controls that the report takes up the entire area of the
page. I know this through trial and error.
The font is Book Antiqua 8 point. The font was the smallest of the
"standard" fonts that should be available on most machines.

Re: Query construction and Sorting/Grouping and Headers/Footers.
Yes. I've tried that, not getting correct results.
The Query, using: ProdBuyCatagoryName and ProdBuySubCategoryName fields and
Prc: [ProdBuyUnitsBought]*[ProdBuyUnitPrice] with either "First" or "Sum"
does not total all "Arrow" shirts, (short sleve, long sleve, pull over), but
shows total of all short sleeves, then total of all long sleeves, Etc.)

When a Header or Footer is added. The report generates 2 pages, the first
showing the Header, the second showing the actual report. Even set all
controls in the Header to "Visible=False".

Also tried creating unbound controls in the report that pulled from the
controls in the Header:
ArrowPrc: IIf([ProdBuyCatagoryName]="Shirt" And
[ProdBuySubCategoryName]="Arrow",[Prc],""), the report generated 95 pages
with one amount on each page.

Expanding on an earlier example of the report's mandated structure:

Customer Name/Address

Catagories:
Hats:
Stetson, (fixed, (Label's caption Shows
"Stetson")).....................................(SellingPrice*QtyPurchased)
Other, (variable name that is generated by bound control)
Other, (variable name that is generated by bound control)......(Total of
both "Others" SellingPrice*QtyPurchased)

Sub-Total Hats ..................Amt=[Stetson]+[Other]

There may be 2,3, or 4 Stetson hats but they all must total on that one
line.
The "Others" lines in this instance need to be limited to the first 2
entries in the input form, and after the word "Other" a bound control must
genterate the name of each product purchased.

Shirts:
Arrow, (fixed, (Label's
caption))...........................................(SellingPrice*QtyPurchas
ed)
Van Hussen, (fixed, (Label's
caption))...................................(SellingPrice*QtyPurchased)
Other, (variable name that is generated by bound
control)......(SellingPrice*QtyPurchased)
Other, (variable name that is generated by bound
control)......(SellingPrice*QtyPurchased)
Other, (variable name that is generated by bound
control)......(SellingPrice*QtyPurchased)
Other, (variable name that is generated by bound
control)......(SellingPrice*QtyPurchased)
Sub-Total
Shirts..................Amt=[Arrow]+[VanHussen]+[Other1]+[Other2]+[Other3]+[
Other4]+[Other5]

Again there may be 2,3, or 4 Arrow shirts, (short sleve, long sleve, pull
over), but they all must total on that one line.
And for this Catagory after the word "Other" a bound control must generate
the name of the product purchased with an individual amount for each line.
With a limit of 5 others and 5 individual amounts, may be none but must be
limited to 5

Total of all Catagories..................Amt.

Steve; I realize that I need to learn how to "talk" with an "MVP", meaning I
might call the total "Amt" where you may call it "Sum". If this is the case
please inform me.

Thank You Once Again.
Andy

 

[Andy]

Steve;

You are correct. The maximum width of the report in design view is 7.1" and
the maximum lenght is 12.5653" with controls or labels occupying the entire
length. I am not permitted to vary that structure in anyway.

As you instructed the query contains only:
ProdBuyCatagoryName ProdBuySubCategoryName and
Prc (where prc = [ProdBuyUnitsBought]*[ProdBuyUnitPrice])
(SellingPrice*QtyPurchased is the same, I missed typed).

The report has a ProdBuySubCategoryName Footer and contains the
[ProdBuySubCategoryName] and =Sum([Prc]) controls and they do show the
proper totals.
YAHOO!

What I have yet to learn is how do get those totals and the
ProdBuySubCategoryName, (where needed for the "Others"), displayed in the
proper places on the report.

The layout of the report, (that must be followed without variation), isn't
in alpabetical nor numeric order, (Shirts/Hats/Pants, 1.00/5.00/2.00)

There are 6 ProdBuyCatagoryName(s) with upto 60 ProdBuySubCategoryName(s).
The table at this moment contains 48 records.

I did experiment with the following.

Knowing that the query must remain as described above, thought perhaps if an
unbound control was placed in the Detail Section containing:
Arrow (where Arrow =IIf([ProdBuySubCategoryName]="Arrow",[Prc],"")
it would allow for correct placement of the total.

The report displayed the answer about halfway down page 2, (page 1 was blank
when the controls in the Footer were set to Visible=False).

[Arrow] placed in the Footer displayed 2/3 down on page 2 and in
ProdBuySubCategoryName Header 1/3 down page 2.

And one attempt generated 1,440 pages. Got rid of that quick.

Have you overcome something like this before?

Oh, Steve seen your also helping people in the Access Forms newsgroup.
Brought a smile to my face.

Thank You so much for your help. What you have thought is greatly
appreciated.

Andy

Andy,

Regarding the page dimensions, so, the width of the report in design
view has to a maximum of 7.1" i.e. 8.5-(0.7*2), is this correct?

Don't worry about the pedantries of using the correct word. But I
think there is a principle which you need to grasp. Which is that you
have to use the Sorting and Grouping facility of the report to get the
desired result. Probably the Detail section of the report should not
be Visible. I am not in a position to analyse what you have done via
the detail you have provided. I would need to see the actual report
and the actual data to do it on that level. Not that I'm offering...
we will get there eventually. But I can definitely tell you that
anything that looks like IIf([ProdBuyCatagoryName]="Shirt" And
[ProdBuySubCategoryName]="Arrow",[Prc],"") or anything that looks like
[Arrow]+[VanHussen]+[Other1]+[Other2]+[Other3]+[Other4]+[Other5] will
not work. The basic concept is that your query should provide you
with all your data in the form of:
ProdBuyCatagoryName ProdBuySubCategoryName Prc
(where prc = [ProdBuyUnitsBought]*[ProdBuyUnitPrice])
(Is this the same as SellingPrice*QtyPurchased, or his this another
"layer" to the overall picture?)
On your report, you need to have unbound textboxes with the
controlsource set to =Sum([Prc]) and these need to be placed as
required in the appropriate Group Footer sections of the report. You
used the example of the Arrow shirts. Andy, if Shirt is data in the
ProdBuyCatagoryName field, and Arrow is data in the
ProdBuySubCategoryName field, then if you put the
ProdBuySubCategoryName control on your report in the
ProdBuySubCategoryName Footer section, and the textbox with
=Sum([Prc]) in the ProdBuySubCategoryName Footer section, then this
*should* give you the total price of Arrow Shirts. If it doesn't, it
doesn't mean the concept is wrong. The concept is right... don't
abandon this idea. It's just that something has been missed in the
communication between you and me, and we need to fine-tune somewhere,
but the basic method is correct.

- Steve Schapel, Microsoft Access MVP

Steve;

Thank you again for working with me.

In answer to your questions:
Re: Margins: They are 7/10" T/B/L/R. I was informed that using 7/10" all
around would allow the report to print on perhaps 90% of the printers on the
market today. This is important.
The page size is 8.5"X14" and it must print on one page and one page only.

This report's controls are .15", (point-one-five of an inch). There are so
many mandatory controls that the report takes up the entire area of the
page. I know this through trial and error.
The font is Book Antiqua 8 point. The font was the smallest of the
"standard" fonts that should be available on most machines.

Re: Query construction and Sorting/Grouping and Headers/Footers.
Yes. I've tried that, not getting correct results.
The Query, using: ProdBuyCatagoryName and ProdBuySubCategoryName fields and
Prc: [ProdBuyUnitsBought]*[ProdBuyUnitPrice] with either "First" or "Sum"
does not total all "Arrow" shirts, (short sleve, long sleve, pull over), but
shows total of all short sleeves, then total of all long sleeves, Etc.)

When a Header or Footer is added. The report generates 2 pages, the first
showing the Header, the second showing the actual report. Even set all
controls in the Header to "Visible=False".

Also tried creating unbound controls in the report that pulled from the
controls in the Header:
ArrowPrc: IIf([ProdBuyCatagoryName]="Shirt" And
[ProdBuySubCategoryName]="Arrow",[Prc],""), the report generated 95 pages
with one amount on each page.

Expanding on an earlier example of the report's mandated structure:

Customer Name/Address

Catagories:
Hats:
Stetson, (fixed, (Label's caption Shows
"Stetson")).....................................(SellingPrice*QtyPurchased)
Other, (variable name that is generated by bound control)
Other, (variable name that is generated by bound control)......(Total of
both "Others" SellingPrice*QtyPurchased)

Sub-Total Hats ..................Amt=[Stetson]+[Other]

There may be 2,3, or 4 Stetson hats but they all must total on that one
line.
The "Others" lines in this instance need to be limited to the first 2
entries in the input form, and after the word "Other" a bound control must
genterate the name of each product purchased.

Shirts:
Arrow, (fixed, (Label's
caption))...........................................(SellingPrice*QtyPurcha

s

ed)
Van Hussen, (fixed, (Label's
caption))...................................(SellingPrice*QtyPurchased)
Other, (variable name that is generated by bound
control)......(SellingPrice*QtyPurchased)
Other, (variable name that is generated by bound
control)......(SellingPrice*QtyPurchased)
Other, (variable name that is generated by bound
control)......(SellingPrice*QtyPurchased)
Other, (variable name that is generated by bound
control)......(SellingPrice*QtyPurchased)
Sub-Total
Shirts..................Amt=[Arrow]+[VanHussen]+[Other1]+[Other2]+[Other3]+

[

Other4]+[Other5]

Again there may be 2,3, or 4 Arrow shirts, (short sleve, long sleve, pull
over), but they all must total on that one line.
And for this Catagory after the word "Other" a bound control must generate
the name of the product purchased with an individual amount for each line.
With a limit of 5 others and 5 individual amounts, may be none but must be
limited to 5

Total of all Catagories..................Amt.

Steve; I realize that I need to learn how to "talk" with an "MVP", meaning I
might call the total "Amt" where you may call it "Sum". If this is the case
please inform me.

Thank You Once Again.
Andy

 

[Steve Schapel]

Andy,

Making progress, do you think?

Well, I usually work in the sequence of get the correct data first,
and then tidy up placement second!

Please be aware also, that what you are undertaking with this report
is definitely not a trivial process, and even the most experienced
Access report builders would take a fair bit of time and effort to get
it right. Also be aware that trying to make a data-driven report come
out precisely like a paper document is sometimes next to impossible.

The way I am reading this situation now, is that you have been trying
to put in a separate control on the report for each of the "Other"
types of shirts, hats, etc. What you need to aim for is for each of
these "Other" to be represented in your data in the same way that the
Arrow Shirts are represented, so the data you require for these
categories also comes out on the report via controls in a group
footer.

As regards the customised sort order, as far as I know there is only
one solution to this... If you already have a table that lists all
the subcategories and categories, use this. If not, you will need to
make such a table. And then you will need to add another field to the
table where you enter numbers or letters in the order you want it
sorted by. This table will have to be added to the report's query.
And the sort field in this table will need to be used in the report's
Sorting and Grouping to create the group headers.

Have you figured this yet?... A control with controlsource =Sum([Prc])
in the ProdBuySubCategoryName Footer will show the total amount for
each SubCategory, eg Arrow. A control with controlsource =Sum([Prc])
in the ProdBuyCategoryName Footer will show the total amount for each
Category, eg Shirts. A control with controlsource =Sum([Prc]) in the
Report Footer will show the total amount for all Categories. Etc.

And of course the textboxes in the group footer sections need to be
the required height, with no "white space" above them or below them in
the report section. And as I said before, if I understand your data
structure correctly, the Detail section of the report should have its
Visible property set to No.

So, a few more random thoughts, hopefully will help.

- Steve Schapel, Microsoft Access MVP

Steve;

You are correct. The maximum width of the report in design view is 7.1" and
the maximum lenght is 12.5653" with controls or labels occupying the entire
length. I am not permitted to vary that structure in anyway.

As you instructed the query contains only:
ProdBuyCatagoryName ProdBuySubCategoryName and
Prc (where prc = [ProdBuyUnitsBought]*[ProdBuyUnitPrice])
(SellingPrice*QtyPurchased is the same, I missed typed).

The report has a ProdBuySubCategoryName Footer and contains the
[ProdBuySubCategoryName] and =Sum([Prc]) controls and they do show the
proper totals.
YAHOO!

What I have yet to learn is how do get those totals and the
ProdBuySubCategoryName, (where needed for the "Others"), displayed in the
proper places on the report.

The layout of the report, (that must be followed without variation), isn't
in alpabetical nor numeric order, (Shirts/Hats/Pants, 1.00/5.00/2.00)

There are 6 ProdBuyCatagoryName(s) with upto 60 ProdBuySubCategoryName(s).
The table at this moment contains 48 records.

I did experiment with the following.

Knowing that the query must remain as described above, thought perhaps if an
unbound control was placed in the Detail Section containing:
Arrow (where Arrow =IIf([ProdBuySubCategoryName]="Arrow",[Prc],"")
it would allow for correct placement of the total.

The report displayed the answer about halfway down page 2, (page 1 was blank
when the controls in the Footer were set to Visible=False).

[Arrow] placed in the Footer displayed 2/3 down on page 2 and in
ProdBuySubCategoryName Header 1/3 down page 2.

And one attempt generated 1,440 pages. Got rid of that quick.

Have you overcome something like this before?

Oh, Steve seen your also helping people in the Access Forms newsgroup.
Brought a smile to my face.

Thank You so much for your help. What you have thought is greatly
appreciated.

Andy

 

[Andy]

Steve I thank you for all your help.

Andy

Andy,

Making progress, do you think?

Well, I usually work in the sequence of get the correct data first,
and then tidy up placement second!

Please be aware also, that what you are undertaking with this report
is definitely not a trivial process, and even the most experienced
Access report builders would take a fair bit of time and effort to get
it right. Also be aware that trying to make a data-driven report come
out precisely like a paper document is sometimes next to impossible.

The way I am reading this situation now, is that you have been trying
to put in a separate control on the report for each of the "Other"
types of shirts, hats, etc. What you need to aim for is for each of
these "Other" to be represented in your data in the same way that the
Arrow Shirts are represented, so the data you require for these
categories also comes out on the report via controls in a group
footer.

As regards the customised sort order, as far as I know there is only
one solution to this... If you already have a table that lists all
the subcategories and categories, use this. If not, you will need to
make such a table. And then you will need to add another field to the
table where you enter numbers or letters in the order you want it
sorted by. This table will have to be added to the report's query.
And the sort field in this table will need to be used in the report's
Sorting and Grouping to create the group headers.

Have you figured this yet?... A control with controlsource =Sum([Prc])
in the ProdBuySubCategoryName Footer will show the total amount for
each SubCategory, eg Arrow. A control with controlsource =Sum([Prc])
in the ProdBuyCategoryName Footer will show the total amount for each
Category, eg Shirts. A control with controlsource =Sum([Prc]) in the
Report Footer will show the total amount for all Categories. Etc.

And of course the textboxes in the group footer sections need to be
the required height, with no "white space" above them or below them in
the report section. And as I said before, if I understand your data
structure correctly, the Detail section of the report should have its
Visible property set to No.

So, a few more random thoughts, hopefully will help.

- Steve Schapel, Microsoft Access MVP

Steve;

You are correct. The maximum width of the report in design view is 7.1" and
the maximum lenght is 12.5653" with controls or labels occupying the entire
length. I am not permitted to vary that structure in anyway.

As you instructed the query contains only:
ProdBuyCatagoryName ProdBuySubCategoryName and
Prc (where prc = [ProdBuyUnitsBought]*[ProdBuyUnitPrice])
(SellingPrice*QtyPurchased is the same, I missed typed).

The report has a ProdBuySubCategoryName Footer and contains the
[ProdBuySubCategoryName] and =Sum([Prc]) controls and they do show the
proper totals.
YAHOO!

What I have yet to learn is how do get those totals and the
ProdBuySubCategoryName, (where needed for the "Others"), displayed in the
proper places on the report.

The layout of the report, (that must be followed without variation), isn't
in alpabetical nor numeric order, (Shirts/Hats/Pants, 1.00/5.00/2.00)

There are 6 ProdBuyCatagoryName(s) with upto 60 ProdBuySubCategoryName(s).
The table at this moment contains 48 records.

I did experiment with the following.

Knowing that the query must remain as described above, thought perhaps if an
unbound control was placed in the Detail Section containing:
Arrow (where Arrow =IIf([ProdBuySubCategoryName]="Arrow",[Prc],"")
it would allow for correct placement of the total.

The report displayed the answer about halfway down page 2, (page 1 was blank
when the controls in the Footer were set to Visible=False).

[Arrow] placed in the Footer displayed 2/3 down on page 2 and in
ProdBuySubCategoryName Header 1/3 down page 2.

And one attempt generated 1,440 pages. Got rid of that quick.

Have you overcome something like this before?

Oh, Steve seen your also helping people in the Access Forms newsgroup.
Brought a smile to my face.

Thank You so much for your help. What you have thought is greatly
appreciated.

Andy

 

[Andy]

Steve;

Found this using MSKB:
http://support.microsoft.com/default.aspx?scid=kb;en-us;208979&Product=acc2000

It follows on your information regarding using Footers for returning the
correct information.

If you ever running for "MVP of the Year" you got my vote.

Thank You!

Andy

Steve I thank you for all your help.

Andy

Andy,

Making progress, do you think?

Well, I usually work in the sequence of get the correct data first,
and then tidy up placement second!

Please be aware also, that what you are undertaking with this report
is definitely not a trivial process, and even the most experienced
Access report builders would take a fair bit of time and effort to get
it right. Also be aware that trying to make a data-driven report come
out precisely like a paper document is sometimes next to impossible.

The way I am reading this situation now, is that you have been trying
to put in a separate control on the report for each of the "Other"
types of shirts, hats, etc. What you need to aim for is for each of
these "Other" to be represented in your data in the same way that the
Arrow Shirts are represented, so the data you require for these
categories also comes out on the report via controls in a group
footer.

As regards the customised sort order, as far as I know there is only
one solution to this... If you already have a table that lists all
the subcategories and categories, use this. If not, you will need to
make such a table. And then you will need to add another field to the
table where you enter numbers or letters in the order you want it
sorted by. This table will have to be added to the report's query.
And the sort field in this table will need to be used in the report's
Sorting and Grouping to create the group headers.

Have you figured this yet?... A control with controlsource =Sum([Prc])
in the ProdBuySubCategoryName Footer will show the total amount for
each SubCategory, eg Arrow. A control with controlsource =Sum([Prc])
in the ProdBuyCategoryName Footer will show the total amount for each
Category, eg Shirts. A control with controlsource =Sum([Prc]) in the
Report Footer will show the total amount for all Categories. Etc.

And of course the textboxes in the group footer sections need to be
the required height, with no "white space" above them or below them in
the report section. And as I said before, if I understand your data
structure correctly, the Detail section of the report should have its
Visible property set to No.

So, a few more random thoughts, hopefully will help.

- Steve Schapel, Microsoft Access MVP

Steve;

You are correct. The maximum width of the report in design view is

7.1"

and

the maximum lenght is 12.5653" with controls or labels occupying the entire
length. I am not permitted to vary that structure in anyway.

As you instructed the query contains only:
ProdBuyCatagoryName ProdBuySubCategoryName and
Prc (where prc = [ProdBuyUnitsBought]*[ProdBuyUnitPrice])
(SellingPrice*QtyPurchased is the same, I missed typed).

The report has a ProdBuySubCategoryName Footer and contains the
[ProdBuySubCategoryName] and =Sum([Prc]) controls and they do show the
proper totals.
YAHOO!

What I have yet to learn is how do get those totals and the
ProdBuySubCategoryName, (where needed for the "Others"), displayed in the
proper places on the report.

The layout of the report, (that must be followed without variation), isn't
in alpabetical nor numeric order, (Shirts/Hats/Pants, 1.00/5.00/2.00)

There are 6 ProdBuyCatagoryName(s) with upto 60 ProdBuySubCategoryName(s).
The table at this moment contains 48 records.

I did experiment with the following.

Knowing that the query must remain as described above, thought perhaps

if

an

unbound control was placed in the Detail Section containing:
Arrow (where Arrow =IIf([ProdBuySubCategoryName]="Arrow",[Prc],"")
it would allow for correct placement of the total.

The report displayed the answer about halfway down page 2, (page 1 was blank
when the controls in the Footer were set to Visible=False).

[Arrow] placed in the Footer displayed 2/3 down on page 2 and in
ProdBuySubCategoryName Header 1/3 down page 2.

And one attempt generated 1,440 pages. Got rid of that quick.

Have you overcome something like this before?

Oh, Steve seen your also helping people in the Access Forms newsgroup.
Brought a smile to my face.

Thank You so much for your help. What you have thought is greatly
appreciated.

Andy