Subreport or Subform in a report

[hockey8837]

Hello,

I have a report, run off of a wildcard parameter query for dates, which lists all upcoming work (trees to be planted) needed for a contractor; based off of the date scheduled (planting date/time of year). So, the user can type the date into the prompt and pull all trees for that planting date in the report.

The report is basically each location and the species throughout the city.

I need a summary section at the bottom of the report that will count display each tree species and how many are in to be planted from the above locations. This is so the contractor will know how many to order of each species.

How do I extract this info? COUNT just gives me the total for all trees in the report. I'm trying to figure out if I need to do a subform or a subreport? If so, which, and how to set it up so that it will work from the parameter query without getting 2 prompts for the same thing when opening the report.

Thanks!

 

[Trevor G]

I would look at using a totals query so you can be specific about the trees and totals required, then drag the query into the Report Footer (Yes needs to go in the Report if they need to have a print out etc) and it should bind to the query and report.

 

[hockey8837]

I would look at using a totals query so you can be specific about the trees and totals required, then drag the query into the Report Footer (Yes needs to go in the Report if they need to have a print out etc) and it should bind to the query and report.

So add the totals line in the query the report is based off of (the parameter query)? Or, create a 2nd query just with species and totals, and put that in the footer?

 

[Trevor G]

Second query in the Report Footer

 

[hockey8837]

So I put the new query (contents= Species, and CountofSpecies) into the report footer, and it only gives me the first species and total, not all of them.

But, if I open up the subform independently, it shows me all trees in the DB and counts properly. I tried moving the 'Species' and 'CountofSpecies' around in the subform-to the headers, footers, details etc. None showed me all tree species, only the first. This is what I'd run into before, and wasn't sure where to go from here.

 

[Trevor G]

Can you upload a sample of the database, make sure the size is below 2mg, so if you need to compact it then zip it before uploading

Let me know the table name, query name's and report name to play with.

 

[hockey8837]

Can you upload a sample of the database, make sure the size is below 2mg, so if you need to compact it then zip it before uploading

Let me know the table name, query name's and report name to play with.

Here's the DB.

Primary Table: Tree Work
Queries: ContractorWorkReport & TreeSpeciesToPlant
Report: CDBGforCITYContractorWorkRpt & TreeSpeciesToPlant subreport

 

[Trevor G]

The issue seems to be that the subreport wants to bind itself to linked fields, what I chose to do is to was open the report in design view then select the edge of the sub report, and looked at the properties and look for the wizard then I made it not linked the list then appears, but it will be a list of all of the trees, so I would advice that the second query becomes a make table based on the list that is selected from the 1st query then change the sub report to display the temp table that what you get the correct list.

You would then combine the process to run the make table query then run preview of the report.

If you need help let me know and I will look to fix this into your database over the weekend.

 

[hockey8837]

Yes, I'd appreciate the help. This seems just a little over my head; I've done all the tweaking I am capable. I will keep playing with it, too, but even if you could get me most of the way, I could probably adjust it from there.

 

[Trevor G]

Ok I will do this for you, I will give you some detailed instructions on how I corrected the report to help you in case you need to do it again. It will be done over the weekend unless I can find sometime on Friday.

I am working in London tomorrow so it's a big day of catching trains etc very early and late home.

I will also upload the finished database.

 

[hockey8837]

Thank you so much! No rush!

 

[Trevor G]

I have attached a copy of your database which will run the report with the sub report working.

I have created a form named frmSample and on it I have placed a combo box which shows the dates in it, there is also a command button which has an embbed macro and if you look at the design of the macro you will see that it is switching the warnings off (so you don't get prompts), it is then opening the queries and report, I have used a make table query to get the totals of the trees based on the query that runs the report, in the report I have reset the subreport to the temp table ztblSpecies and taken of the Master and Child links so you can see the totals. The report also has a text box which will show you the date that has been selected.

If this does help then you can add the features to your main form, you would have to adjust a few things.

As an extra advisor for you, you should follow the recommended naming convesions as it will help others find things in your database when things have to be amended or created.

Here is some links to help you understand them.

http://www.acc-technology.com/namconv.htm

http://www.databasedev.co.uk/naming_conv.html

 

[hockey8837]

Thank you so much for taking the time to put this together! I'll have to play around with it to see how everything is working, but it looks great so far.

Thanks again!

 

[hockey8837]

Hi,
I've been trying to play around with the new feature you worked on for me, and I'm having some trouble. When I try to move (copied and pasted) the button to another form (like, the Report Generator form), I'm running into a few problems.

First: when I click the button, I get an error message which says 'Enter parameter value: Forms!frmSample!cboTreeDate' But, looking at the properties, it looks like my combo box is still named the same, but I can't figure out where it's naming the form frmSample to change it to the new form name.

Second: Though I can select a date in the drop down (cboTreeDate), the report which opens after has no data; i.e. it is blank. It's the correct report, but no info in it. If I open the same thing through your frmSample, it works and looks great.

Could you explain what I need to change if I want to move this button, and also my next step will be to create a similar button, but that launches a report for different funding (report ARRAdateandzip).

Thanks!

I have attached a copy of your database which will run the report with the sub report working.

I have created a form named frmSample and on it I have placed a combo box which shows the dates in it, there is also a command button which has an embbed macro and if you look at the design of the macro you will see that it is switching the warnings off (so you don't get prompts), it is then opening the queries and report, I have used a make table query to get the totals of the trees based on the query that runs the report, in the report I have reset the subreport to the temp table ztblSpecies and taken of the Master and Child links so you can see the totals. The report also has a text box which will show you the date that has been selected.

If this does help then you can add the features to your main form, you would have to adjust a few things.

As an extra advisor for you, you should follow the recommended naming convesions as it will help others find things in your database when things have to be amended or created.

Here is some links to help you understand them.

http://www.acc-technology.com/namconv.htm

http://www.databasedev.co.uk/naming_conv.html

 

[Trevor G]

What you need to do is look back at the query which generates the report, I have set the criteria for the date to go to the form, if you have copied the date field to your main form then you will have to reset the criteria to point to that form rather than my sample, that will correct your report and all should run smoothly.

So rather than the criteria going to frmSample it will need to point to the Navigate form.

 

[hockey8837]

Thanks, I think I've got it now. I didn't realize you'd added the line in the query for the report.

Now, the trick is to see if I can duplicate this method for other reports. Wish me luck!