Merge Two reports

[Garyj]

How do I merge two reports into one report? Both report uses its own query to get its results. I would like to open one report and get the results for both. Is this possible? Please explain.


Thanks

 

[GumbyD]

If you create a new report and then add both of the reports as subreports with no link to the main report it should display both reports.

GumbyD

 

[Garyj]

Could you explain further

Thanks

 

[GumbyD]

Gary -

In a new report create add two subreports to the Detail section of the report. Set up the record sources as the first report and the second report you have already created. There is no relationship (I assume) between the reports so you will not need to set up child / parent relations between any of the reports. When you run the main report it should run both of the other reports as subreports. One thing to note if the reports you are running have their own subreports and lots of page after or before section type formatting you may have to change that stuff around to get it to print out nicely. Make sure you do not set the Keep Together propetry to true or you may get an endless formatting problem (lots of blank pages).

GumbyD

 

[Garyj]

OK, I just noticed that my results will be the same as another report I created. I explained what I needed all wrong. Let me see if I can break down better.

I have a report that list employee names, id, time used, and earning codes, ect. In the report it groups all names, ids and earning codes separately for each employee. Of these earning codes three codes are getting their time from each other. Example: If I have 100 hours in earning code 921 any time using that code 961s or 961u comes from the same pot.

What I am trying to do in a report is to list these three codes in the same group header instead of separately. Is this possible? If so, can you explain what must I do to achieve the results I need.

Thanks

 

[GumbyD]

Gary -

I think your best bet is to create an iif statement in your query and then group by the results of the iif statement. The iif statement will look something like:

Iif([YourCodeFieldName] in ("961s","961u"),"921",iif([YourCodeFieldName] ...

I hope you get the idea. Then you can group on this field so all the 961s and 961u records will now be under the value 921. Is that what you were looking for?

GumbyD

 

[Garyj]

Thank you I'll give that a try.

 

[Garyj]

Thank you for th advice but I am not sure how to complete the sintax of this iff statement. I would appreciate any assistance you can offer. Here is what I have so.

iif([EARNINGS CODE] In ("961s","961u"),"921", iif( [EARNINGS CODE]

Thank you

 

[GumbyD]

Gary -

I am not sure if or how you want to group the other values. If the only ones that get grouped up are 961s and 961u, then the whole iif statement will look as follows:

iif([EARNINGS CODE] In ("961s","961u"),"921", [EARNINGS CODE])

if you have more Earnings code groupings than you will need more embedded iif statements:


iif([EARNINGS CODE] In ("961s","961u"),"921", iif([EARNINGS CODE] In ("421b","421c"),"460", iif([EARNINGS CODE] In ("111r","267b","355x","455j"),"200", [EARNINGS CODE])))

In this case the expression will return
921 if the Earnings code is 961j or 961u
460 if the Earnings code is 421b or 421c
200 if the Earnings code is 111r, 267b, 355x or 455j
it will return the value of the Earnings code field if it is not one of the values above.

You can also have it return a default value if it not one of the selected values like this:

iif([EARNINGS CODE] In ("961s","961u"),"921", iif([EARNINGS CODE] In ("421b","421c"),"460", iif([EARNINGS CODE] In ("111r","267b","355x","455j"),"200", "555")))

This expression is the same as the 2nd one except that it returns 555 if the earnings code is not on of the one in any of the in statements.

Hope that helps!

GumbyD

 

[Garyj]

Thanks, I have been working with your guide and using different iif solutions. So far I haven't gotten the desired results. In the report I am creating I do have other earning codes that are grouped but there is only one for each code, totaling 21. Four of which needs to be grouped in the report since the amount is coming from the same bucket.

Ideally I would love to have on section group in the report like this.

Planned PTO 921 Planned PTO

1/2/2004 500.00 Received
1/2/2004 -7.50 Used
1/26/2004 -3.75 Used

Planned PTO Sick 921 Planned PTO Sick

3/25/2004 -7.50 Used
3/26/2004 -7.50 Used

Unplanned PTO 961 Unplanned PTO

3/01/2004 -7.50 Used

Unplanned PTO Sick 961 Unplanned PTO Sick

2/1/2004 -7.50 Used
2/2/2004 -7.50 Used

Hour Left: 451.25

I am still working different angles to this solution including yours. I hope the above example gives you an idea of what I'm looking to achieve.

As for the other earning code each code will be grouped into its own section but this one I need together in the report.

 

[GumbyD]

Gary -

I can assist you with the if statement if you can give me a bit of information:

On your sample report you have it grouped what earnings codes represent each group?

Planned PTO 921 Planned PTO

Planned PTO Sick 921 Planned PTO Sick

Unplanned PTO 961 Unplanned PTO

Unplanned PTO Sick 961 Unplanned PTO Sick

Are these all the groups?

GumbyD

 

[Garyj]

I have created several if statements but when I run the query it picks up all the earnings codes including the four I need.

Thank you for all your help thus far.

 

[Garyj]

The groups are:

921 Planned PTO
921 Planned PTO Sick
961 Unplanned PTO Sick
961 Unplanned PTO


Here is a sample of what I am trying to do. I have uploaded a sample of my db Access 97. In the query area I have included all the queries that I am using. When you run the report use 01/01/04 to 03/30/04.

 

[Garyj]

Can anyone help me with this problem?

Thank you

 

[GumbyD]

Gary -

After looking at and working with your issue some more - there is one method I think would work, but it will be slow and ugly. Since you know it will always be 1 month of information and you have a start date. You could build subreports pulling data with criteria for each week - the criteria would have to be filled from the value on the main report. Then on the on open of the subreport you could use on no data to hide the subreport if it did not contain data and make visible a line of zeros or some text stating there were no errors during the week.

I have been trying to think of other options but so far no luck. I hope that gets you started. If you have any questions let me know.

GumbyD

 

[Garyj]

GumbyD,

Thanks for your help.

 

[GumbyD]

Gary -

From my last post you must think I am nuts! I got this thread mixed up with another one that I have been looking at. Sorry about that.

How do you distingush '921 Planned PTO' from '921 Planned PTO Sick'? Same question with the unplanned paid time off?

Is there a code for 921 that is different from 921 sick? Does that even really matter in your design? Would it be possible for you to post your database or a sample version of it? I work with HR systems everyday and absences are my speciality - so if I can get a look at your structure I may be able to give you a clear answer.

Thanks,

GumbyD