Probelm with a query on a report

[Benjamin Bolduc]

Hi everyone,

I'm having a problem with a new report I'm working on. It's a rather complicated report that has lots of codeing and objects. Anyway, the final product of this report is the average efficiency of the two product lines we had for the previous week.

But... I need also need, on the same report, the same efficiency for the week before that.

Basically, it takes the whole compicated report to come up with one number, but I need that number for last week AND the week before.

I don't know how to tackle this.
I tried copying the report, changing the query and placing it as a sub-report on the main report. But that didn't work.

Does anyone know what I'm talking about? or better yet.. have any ideas on how to fix this?

Thanks for the help!
Ben

 

[RichMorrison]

Ben,

This sounds like a "period over period" report; in your case week over week.

This technique is reasonably simple.

1) make a query or queries to calculate you efficiency numbers BY PRODUCT LINE for the previous week.

2) make a second query/queries for efficiency BY PRODUCT LINE for week minus 2.

3) make a third query that joins the first 2 queries on product line.

Then you have last week and two weeks ago in one record suitable for reports. This assume you always have last week's and 2 week's ago data for each product line. If not the join in query 3 fails.

You have to figure out how to select the data for last week versus 2 weeks ago.

HTH,
RichM

 

[Benjamin Bolduc]

That sounds like a good Idea, but I don't it's possible to get the efficiency from a query. That number is generated by dozens of calculated fields from 5 different sub-forms. I;ve tried duplicating the results in a query but it's just too complicated for one. Could there be any other way of using the set-up I already have in the report for second Date?

Thanks for the reply!
Ben

 

[Rich]

Never tried this so it's just theory, why not open the first report hidden and grab the values required from it or save then to a Public variable then use them on the second report

 

[RichMorrison]

Ben,

Not sure I understand how or why you use "5 different sub forms" for a REPORT ?

Anyway, how about making the report a sub report ? Then clone all the components and modify as needed for a second subreport of two weeks ago.

Put both subreports inside one container report.

RichM

 

[Benjamin Bolduc]

Of course I meant Sub-reports, but i'll give that a try and let you know if it worked.

Thanks!

 

[Benjamin Bolduc]

Ok, I tried To create 2 subreports of the same report only with one showing the last week, and the other showing two weeks ago. The problem I have now is connecting the two reports. I need to add a text box on the subreport that I want to be visible to show just the efficiency of the that line. The way it is now, it's just taking the first value and appling it to all the pages.

Is there anyway of keeping the two report in sync?

Thanks
Ben

 

[RichMorrison]

You wrote
<<
Is there anyway of keeping the two report in sync?
>>

Ben,

I don't know all your details, but the general solution I have used is this:
1) Make a main report
2) Make 2 subreports
3) Go back to the main report and add 2 subreport controls. Set the "source" to the subreports.

To make this work, you need to ensure that the 2 subreports have the same column layout and the same number of rows. You need to do this to make the subreports align well in the main reports.

If you were reporting something like "This Year / Last Year Sales by Product" and you had the Product Name on the left side of the report, then you need to be sure you have a row for the current and the old Year for each Product Name value.

HTH,
RichM