Need help with Summary Report

[tony7ana]

Hi All,

I have been trying to figure our how to run a report in Access 2007 for the last 2 days and have not been able to format it to the way that I want.

Currently I have a table that looks like this:

...............Date ........... Produced ........NextWeekProductionType
Machine A 07/01/2012 ...50 boxes ........Bags
Machine A 08/04/2012 ....85 boxes .......Cases
Machine B 07/08/2012 ....65 bags .........Paper
Machine B 07/18/2012 ....83 bags ..........Pens

I want a report that prompts that user for a week begin date and a week end date. In this case, if the user enters begin date 07/01/2012 and end date 08/04/2012, then I want the report to look as follows:

Report
Week Begin Date: 07/01/2012
Week End Date: 08/04/2012

Produced
50 boxes
85 boxes
65 bags
83 bags

Next week production
bags
cases
papers pens


I dont want the machine name to be referenced, it seems everytime i try to run the report, it summarizes as a line item which i do not want. I have tried running a query but its not allowing me to run a report off the query.

Any help is appreciated and thanks in advance!!!!

 

[vbaInet]

First of all you need a form that you will use for entering the parameters. The form will contain two textboxes and a command button. If your textboxes are named txtStartDate and txtEndDate, put the following in the Criteria row under the Date field in your query:

BETWEEN Forms!FormName!txtStartDate AND Forms!FormName!txtEndDate

Where FormName should be substituted for the name of your form.

Once you've done all that, let me know and we'll guide you through the final phase.

 

[tony7ana]

First of all you need a form that you will use for entering the parameters. The form will contain two textboxes and a command button. If your textboxes are named txtStartDate and txtEndDate, put the following in the Criteria row under the Date field in your query:

BETWEEN Forms!FormName!txtStartDate AND Forms!FormName!txtEndDate

Where FormName should be substituted for the name of your form.

Once you've done all that, let me know and we'll guide you through the final phase.

Thanks vbaInet. I have a form created that has a start date and and end date. I have placed the code in your post in my query. I appreciate your input, what are the next steps?

 

[vbaInet]

I would imagine you have also created the report. So in the Control Source of each of your textboxes on the report you just need to put the reference to the relevant textboxes on the form, just like we did in the query.

In the Click event of the button, use the OpenReport command.

 

[tony7ana]

Thanks vbaInet, the report is now running but I have one small issue. The report is showing an individual page in the report for each form entry.

For example,

The report is showing this

produced: 50 boxes
Next week production type: bags

produced:85 boxes
Next week production type: cases

I want the report to show this:

Produced:
50 boxes
85 boxes

Next week prodcution type:
bags
cases

Any idea how I can get the report to run like this?

 

[vbaInet]

Look into a subreport.

 

[tony7ana]

Look into a subreport.

Thanks vbaInet. I created my subreport and it providing me the data in the format that I want. However, the report header has a blue back color that I cant remove. I tried changing the background color to transparent and even white and its still shows up blue. I tried copying the background color from my main report and using it in the subreport and still it will not change. Any ideas? thanks again...

 

[vbaInet]

The main report header or the subreport's header? Let me see a screenshot.