Is it possible to combine 2 crosstab Queries?? 1

[motherof2]

I have a report that I am trying to build using 2 crosstab queries. Here is a little insight on the situation: Within the report, I need to include hours and dollars as well as gross and net pay for particular employees. I am unable to find one table that will give me all of the results that I need. So, I have built 2 crosstab queries (one that gives me hours and dollars and the other gives me gross and net pay). The 2 crosstabs (ran seperately) give me the data that I need. But, how can I use these 2 crosstabs in one report??

I have tried combining them into a 3rd query but, the data becomes distored and duplicated. Is there maybe a totally different approach that I am just missing or unaware of?? Or is it possible to combine 2 crosstab queries without distoring my data???

Thank you in advance for any advice!!

 

[JoanneM]

Morning


Have you thought of two small sub reports sitting on one report or do you have to send this to another application?

Carefull sizing and layout (ie do one report - copy it and then source it with the other query and source the txt boxes accordingly this will duplicate layout - nb if you accidentally move one of the boxes it's quicker to start again.)

Hope this is what you are thinking of

regards

Jo

 

[motherof2]

Good Morning!!

The 2 sub-report idea worked!! Thank you!!

I have another issue with this same report since I used the sub reports. Within the main report, I have used criteria to filter between check dates for a specific employee. When I put the criteria in the main report, it does filter to the sub report. However, it does not filter to my totals. I need to be able to total my 'gross pay' and 'net pays' for a specific check date range specified, which is on the sub report.

Here is what I have tried and the results:

I have tried to use the sum function on the main report but it does not recognize the field(s) that are in the sub report (the gross and net pays).

I have tried to make the query in the sub report a sum query, but it only recognizes the last entry.
It does not total all the entries for the check date range.

I have also tried to put the sum function on the sub report but once again it does not total for the check date range. It only recognizes the last entry when I run it from the main report. When I run just the sub report, it does total everything, but it does not on the main report???

Is this even possible to do???

Thank you for any input!!

 

[JoanneM]

Syntax for subforms has always been my bette noir but since the introduction of Bang meaning "!" and Dot meaning "." things got easier

As we are always dealing with DB's the order is Dot bang.

In case you think I have lost the plot - this is an aide memoire for future use

Syntax as follows

dim SRptTotal1 as integer

SRpttotal1 = forms!formname!subformname.Form!SRpttotalsName

now you have the values in a variable on your main form

try summing them now


see - dot Report Bang boxname = [color].Report![/color]

next you need to look at the Running sum property in help

This may clarify a little

hope it helps

regards

jo