Dynamic Report with sub-groups

[LeenOoo]

hello,
i've struggling with trying to build a dynamic report. we have a table that has housing sales data for a county. i've set up a form, in hopes of being able to dynamically create/open a report based on what the user selects in the form. the selection choices are area (three cities, the 'subregions' of the major city, or the entire county), the type of housing sale (new or existing structure), the landuse (single family or condo), and the time period.
i've written the code that will write the sql query when the user clicks on the 'see report' button, however i'm not quite sure where to go from here!
i've made a generic report with unbound textboxes in the places where i want to show my info.
now i'm not sure how i can send the info from my query to the report. esp since the query doesn't just return rows, but a count of the number of rows, the average sales price, the mean sales price, the average building square feet and the mean square feet.
and to make things more complicated, if the user selects city1, condos, new and existing for january, i'd like to show something like this:
____________________________________
Housing Sales - January 2007 (title)
City1, County, State (subtitle)

New Condo sales (1st group title)
[countofSales] [AvgSalesPrice] [MedSales] [AveBldgSF] ...

Existing Condo sales (2nd group title)
[countofSales] [AvgSalesPrice] [MedSales] [AveBldgSF] ...

All Condo Sales (3rd group title)
[countofSales] [AvgSalesPrice] [MedSales] [AveBldgSF] ...
____________________________________

thanks!

 

[dhookom]

IMHO you create the reports that your users might want to "publish" in a strictly formatted rendering. Then you create an ad-hoc query writer with the ability to export to a number of different destinations. You can use the query-by-form applet available at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

This applet allows users to build a query and then sent the query to print, Excel, Word,... You only need to import a few objects and then create some master queries with your tables.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[LeenOoo]

Your applet was great, but it won't do as the reports I'm trying to generate will come from multiple queries.
I'm getting slightly closer, by using an append query and union-ing my three parts (new, existing and all sales) into one table. But it won't let me make a report out of this new append query... Can i do it through vba? For example make a generic report then once the user has made their selection i write my queries to create the append query, then assign the controlsource of the textboxes from the generic report to the fields in my append query?
...

 

[dhookom]

I would use my qbf applet and setup one or more datasource query based on the union query. You can't make a report off an action query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[LeenOoo]

i think i must be going about this the wrong way.
here is what my query looks like:

SELECT "SFR" AS Landuse, "New" AS Age, Count(YTDSALES2007.ID) AS CountOfID, Avg(YTDSALES2007.[Sale Price]) AS [AvgOfSale Price], Median("YTDSALES2007","[Sale Price]",'TaxDist="5200" And [LUC at Sale]=20 and new="new" and [Sales Date]>=#1/1/2007# And [Sales Date]<=#3/31/2007# ') AS MedSalePrice, Avg(YTDSALES2007.[Bldg SF]) AS [AvgOfBldg SF], Median("YTDSALES2007","[Bldg SF]",'TaxDist="5200" And [LUC at Sale]=20 AND new="new" and [Sales Date]>=#1/1/2007# And [Sales Date]<=#3/31/2007# ') AS MedBldgSF, Avg(YTDSALES2007.[Price per SF]) AS [AvgOfPrice per SF], Median("YTDSALES2007","[Price per SF]",'TaxDist="5200" And [LUC at Sale]=20 and new="new" and [Sales Date]>=#1/1/2007# And [Sales Date]<=#3/31/2007# ') AS [MedPrice per SF]
FROM YTDSALES2007
WHERE (((YTDSALES2007.TaxDist)="5200") AND ((YTDSALES2007.[LUC at Sale])=20) AND ((YTDSALES2007.New)="new") AND ((YTDSALES2007.[Sales Date])>=#1/1/2007# And (YTDSALES2007.[Sales Date])<=#3/31/2007#));

i make three queries like this: for new='new', new='e' and selection on all the other things without new. then by unioning these three queries together i get the table that i want to show in my report.


Landuse Age CountOfID AvgOfSalePrice MedSalePrice
SFR All 38 1605561.763 1100000
SFR New 1 1423347 1423347
SFR Existing 37 1610486.486 1037500

i also do the same thing for landuse=condo.

if i use your qbf applet, then it looks to me like i have to create a datasource query for each area in question, for sfr and for condo, and for combined. and this is what i was trying to learn how to avoid! there are three cities, plus nine subregions, plus the county.
i've spent way too much time on this already, trying to save time! should i take my table back into excel and write a macro in there to sort out to a new sheet and have that be my report? that's starting to look easier...

 

[dhookom]

If this is a standard published report, I believe I would create a saved report that allowed for different filtering based on user input controls on a form. You may need to use subreports.

Looking at your SQL, I would expect you could create a query that filtered YTDSALES2007 using your criteria but grouping by YTDSALES2007.New. Then re-write the above SQL to select from the query. Send the query into your median function also.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[LeenOoo]

now i have a new problem!

i ended up writing a macro to run through and create a line in a new table for each region, time period, land use type, and age -- filling in fields for each of these, plus the count of the sales, the averages and medians.
from there i have it set up to use your applet, which Rocks!!

but.... i'm having a sorting problem...
my report is set-up with group0 as areaname, group1 as landuse type, then i have a line with age, count, averages, medians.
in the applet i've tried every possible combination of sorting but everytime the lines are not sorted as i would like: by the age field.
the age is either 'New', 'Existing', 'New & Existing'
in the table i even tried inserting a number before these strings so that it was 1New, 2Existing, 3New & Existing but this hasn't helped to get them in this order each time the body is printed.
i thought that maybe it was because of the id field, and that the lines of the body were printed in order of the id, but no.

any ideas?

also, i'm looking for info on sub-reports. i've never used them, and was wondering in what ways are they useful etc, any directions to go for that?

 

[dhookom]

Assuming you are attempting to sort in your report, did you use the report's sorting and grouping dialog? What expression/field did you use? What is the data type of your field? Are you using lookup fields anywhere in your tables?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[LeenOoo]

the report is set up with two levels in the sorting and grouping.
group0 is the areaname, and this is sorted asc. this is a text field.
group1 is the landuse type, and this is sorted asc. this is a numerical field.
next i'd like to sort on age, which is the first column in the body. age is a string field.
what are lookup fields?

 

[dhookom]

We can't see your data or report. Do you have some sample data and how they are currently displayed/sorted in your report and then how you expect them to be sorted?

You mention an ID field so I thought one of your fields in a table might be using the lookup mis-feature.

Subreports are used much like subforms are used.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[LeenOoo]

i'm not sure if there is a way that i could copy in a picture of what my report looks like?? i'll try my best to re-create it here:

Housing Sales -- 1st Quarter 2007

Age [tab][tab][tab][tab][tab]Number [tab]MedianSalesPrice (column headings...)
Incline Village (this is the group0)
Single Family Residence Sales (this is group1)
New & Existing[tab][tab]29[tab][tab]$1,335,690
New [tab][tab][tab][tab][tab] 1[tab][tab]$1,456,000
Existing [tab][tab][tab][tab]28[tab][tab]$...
Condo Sales (this is group1)
Existing [tab][tab][tab][tab]12[tab][tab]$...
New & Existing[tab][tab]15[tab][tab]$335,690
New [tab][tab][tab][tab][tab]3 [tab][tab]$456,000

my report looks something like this, my problem comes from the report body, the part where we see the age, the number of sales and all the other info. these lines seem to sort randomly. the age field actually now contains the values: "1New", "2Existing" and "3All" -- i was hoping that this would help with my sorting, so the text "New", "Existing", and "New & Existing" is placed in a label box over the age textbox (which is invisible) with the following code:

Select Case Me.Age.Value
Case "1New"
    Me.labelAge.Caption = "New"
Case "2Existing"
    Me.labelAge.Caption = "Existing"
Case "3All"
    Me.labelAge.Caption = "New && Existing"
End Select

what i'd like to see every time in the body is New, then Existing then New & Existing.

my report has nothing in the "order by" field of report properties.

 

[dhookom]

Apparently you have two sorting and grouping levels defined in your Sorting and Grouping dialog. You should be able to add another based on an expression like:
=Instr("New Existing New & Existing" ,[Age])
You shouldn't need the numbers in front of the values.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[LeenOoo]

ah ha! that was it! thanks!

now, i'm trying to figure how to do a report with subreports, (i've never used these or sub-forms) maybe i don't even need to do this:
i want to add a graph at the end of my existing report. so i started by creating my graph -- i created a new query for it to single out one major information (i plan on creating three graphs as there are three major informations to show: median sales price, median building square feet and median price per square foot.). then i dragged this graph into the report footer section of my existing report.

i get a blank page between the report body and the graph, and i'm not sure where this is coming from, i made the graph and the original report the same width, same margins.

but, i now see that this cannot work because i put the column headings in the page header section, so they show up also on the graph page.

so then, i tried creating a third report. i put the title in the new report's page header and dragged in the original report into the detail section, and dragged the graph report into the report footer section. there are a couple problems here:
- the area name shows up, but without the formatting that i coded in groupheader0_format.
- the type field (either single family sales or condo) doesn't fill in at all, but it shows up (so a big square with the choosen shading but no text)
- the body of the report does not show up. all the rest of the page, between the type field and the page numbering, is blank.
- a completely blank page prints after each page.

i didn't add any special coding or set-up to the new report. all of the coding and formating is in the existing report and the graph.

from this description can you see anything that i'm doing wrong?

 

[dhookom]

I would go back to prior to creating your third report. If you don't want a section to display in the final page of your report, you can generally use code in the on format event like:

Cancel = Me.[Page] = [Pages]

This assumes you have page numbers in your report.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[LeenOoo]

thanks, that was perfect. i might one day really know what i'm doing in access....
now my boss has asked me to set up a new report, basically the same as what i've already done, but different!
so by changing the query i used the report that i made to do a report for the four major areas, then again changing the query, i printed a report for the 9 subregions that make up one of the major areas.
my boss requested that i make a report that shows the subregions, then the 4 major areas, then some graphs.

what is the best way to go about this? i'm started by creating a subreport for the subregions, and one for the major areas, and i'm trying to combine them by putting them in a third report which contains the title and the footer. i didn't put a source for the new report, and each subreport's source is a new query instead of MyQuery as it had been before. this create something like 48 pages, only showing the page header and footer and the first group title field, but this shows the same value on all the pages.

thanks so much for all your help!

 

[dhookom]

Please start a new thread which will be read by new "eyes". Describe your table structures and how you want your report sorted/grouped. If you need graphs in your report, describe the relationship between the records printed in the report and the graphs.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]