Limit row in report

[Guest]

I have a union query that produces four rows of data. In my report, I have
created unbound text boxes with =sum([FieldName]) as the source.

My report show four text boxes with the same info, one for each row in the
query.

How do I limit my report to only show one line?

Cheers

 

[Steve Schapel]

Paul,

If I understand you correctly, you need to put the textboxes in the
Footer section of the report.

 

[Guest]

Steve,

I tried your suggestion, however the result I get is the 'query is too
complex'

My query looks like this,
------------------------------------------------------------------------------------
SELECT Abs(Sum([Form2]="Filed")) AS Form2
FROM tblUnit1
WHERE (((tblUnit1.Region)=Forms![frm_Crew_Forms]![Region]) AND
((tblUnit1.Station)=Forms![frm_Crew_Forms]![Station]));
UNION ALL
SELECT Abs(Sum([Form2]="Filed")) AS Form2
FROM tblUnit2
WHERE (((tblUnit2.Region)=Forms![frm_Crew_Forms]![Region]) AND
((tblUnit2.Station)=Forms![frm_Crew_Forms]![Station]));
UNION ALL
SELECT Abs(Sum([Form2]="Filed")) AS Form2
FROM tblUnit3
WHERE (((tblUnit3.Region)=Forms![frm_Crew_Forms]![Region]) AND
((tblUnit3.Station)=Forms![frm_Crew_Forms]![Station]));
--------------------------------------------------------------------------------

I removed most of the fields being collected to make it readable here.

Now the query when run produces:
----------------
Form2
9
4
3
----------------

In my report, I have created unbound text boxes with =sum([Form2]) as the
source.

What I get is three lines with the same thing. I do not want to show the
individual rows of data, just the total.


Thanks for having a look.

Paul,

If I understand you correctly, you need to put the textboxes in the
Footer section of the report.

--
Steve Schapel, Microsoft Access MVP

I have a union query that produces four rows of data. In my report, I have
created unbound text boxes with =sum([FieldName]) as the source.

My report show four text boxes with the same info, one for each row in the
query.

How do I limit my report to only show one line?

Cheers

 

[Steve Schapel]

Paul,

Without going down the track of analysing your query at this stage, I
can't understand how what you wrote here is related to my earlier reply.
Do you mean you have the textboxes such as =sum([Form2]) in the report
footer? Or in the detail section? If they are in the detail section,
move them to the footer. Make sure they are not named Form2 etc. If
they are already in the footer section, I cannot see how they would get
dispayed multiple times.

If you just want to see the totals and not the detail records, set the
Visible property of the detail section to No.

 

[Guest]

Hi Steve,

They are in the details section. I moved them to the footer section and I
get the error message 'query is too complex' and the report will not open.

Should I be using a second query to get the totals from the first query and
then base my report on that?

Cheers

Paul,

Without going down the track of analysing your query at this stage, I
can't understand how what you wrote here is related to my earlier reply.
Do you mean you have the textboxes such as =sum([Form2]) in the report
footer? Or in the detail section? If they are in the detail section,
move them to the footer. Make sure they are not named Form2 etc. If
they are already in the footer section, I cannot see how they would get
dispayed multiple times.

If you just want to see the totals and not the detail records, set the
Visible property of the detail section to No.

--
Steve Schapel, Microsoft Access MVP

Steve,

I tried your suggestion, however the result I get is the 'query is too
complex'

My query looks like this,
------------------------------------------------------------------------------------
SELECT Abs(Sum([Form2]="Filed")) AS Form2
FROM tblUnit1
WHERE (((tblUnit1.Region)=Forms![frm_Crew_Forms]![Region]) AND
((tblUnit1.Station)=Forms![frm_Crew_Forms]![Station]));
UNION ALL
SELECT Abs(Sum([Form2]="Filed")) AS Form2
FROM tblUnit2
WHERE (((tblUnit2.Region)=Forms![frm_Crew_Forms]![Region]) AND
((tblUnit2.Station)=Forms![frm_Crew_Forms]![Station]));
UNION ALL
SELECT Abs(Sum([Form2]="Filed")) AS Form2
FROM tblUnit3
WHERE (((tblUnit3.Region)=Forms![frm_Crew_Forms]![Region]) AND
((tblUnit3.Station)=Forms![frm_Crew_Forms]![Station]));
--------------------------------------------------------------------------------

I removed most of the fields being collected to make it readable here.

Now the query when run produces:
----------------
Form2
9
4
3
----------------

In my report, I have created unbound text boxes with =sum([Form2]) as the
source.

What I get is three lines with the same thing. I do not want to show the
individual rows of data, just the total.


Thanks for having a look.

 

[Steve Schapel]

Paul,

They are in the details section. I moved them to the footer section and I
get the error message 'query is too complex' and the report will not open.

There must be a simple explanation for this, regarding which fields are
represented on your report, in which section, and the names of the
controls. This should work. If you've got the [Form2] field in the
Detail section and it shows data, then putting an unbound textbox, which
is not named Form2, in the report footer, with a Control Source of
=Sum([Form2]) should work.

Should I be using a second query to get the totals from the first query and
then base my report on that?

Yes, that would be a good approach.

 

[Guest]

Well that is what I did and that works. I looked at the SQL the query wizard
created, and although I am no expert, I could see how I could accomplish this
any other way. I tried a couple combining a couple of ideas from my 2
queries, but kept getting errors.

At least this works now. Thanks for your help.

Paul,

They are in the details section. I moved them to the footer section and I
get the error message 'query is too complex' and the report will not open.

There must be a simple explanation for this, regarding which fields are
represented on your report, in which section, and the names of the
controls. This should work. If you've got the [Form2] field in the
Detail section and it shows data, then putting an unbound textbox, which
is not named Form2, in the report footer, with a Control Source of
=Sum([Form2]) should work.

Should I be using a second query to get the totals from the first query and
then base my report on that?

Yes, that would be a good approach.