Form Requires more than one Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to build a form/report which uses a different query for each
control. Each query works seperately, but when I try to Combine them I get a
#Name? error in each control. The queries are all count queries using the
same field in the same table with different criteria. Can anyone help??
 
YoDuck,

I would expect that if you make a query which includes all of your
existing queries, suitably joined on the PK field of the original table,
and then base your form or report on this query, it should produce the
goods. The Name and Control Source of the controls on the form/report
would need to be distinguished from each other, e.g.
[NameOfCountQuery].[CountOfField], or in the combining query, you could
give each column an alias, and use this in the form/report.

A simpler approach may be to drop the idea of a separate query for each
criteria, and do it all in the one query. This would involve making a
calculated field in the quey fro each criteria, something like this...
FredCount: Abs(Sum([Person]="Fred"))
(adapt according to the criteria which actually applies in your database)

A variation on this theme is to hide the detail section of the report,
and in the footer, put an unbound textbox for each item, and set its
Control Source to the equivalemt of...
=Abs(Sum([Person]="Fred"))

Please post back with more details, including examples, if you need more
specific help with this.
 
Thanks for the Quick Reply. The Queries are as follows:

Contol 1 Query 1
SELECT DISTINCTROW ([ClaimNumbersTbl.ActClaimNo]) AS [Count]
FROM ClaimNumbersTbl INNER JOIN TransactionRecordsTbl ON
[ClaimNumbersTbl].[ActClaimNo]=[TransactionRecordsTbl].[ActClaimNo]
WHERE ((([TransactionRecordsTbl].[AcctgPerid])="03-12"));

Contol 1 Query 2
SELECT Count([03-12ClaimsCount].[Count]) AS [Claim Count]
FROM [03-12ClaimsCount];

-------------------------------------
Contol 2 Query 1
SELECT DISTINCTROW [ClaimNumbersTbl].[ActClaimNo]
FROM ClaimNumbersTbl INNER JOIN TransactionRecordsTbl ON
[ClaimNumbersTbl].[ActClaimNo]=[TransactionRecordsTbl].[ActClaimNo]
WHERE ((([TransactionRecordsTbl].[AcctgPerid])="03-12") And
(([TransactionRecordsTbl].[Sta])="7"));

Contol 2 Query 2
SELECT Count([03-12ClaimsSTA-7].[ActClaimNo]) AS [Claims STA 7]
FROM [03-12ClaimsSTA-7];

There are 7 more controls, each with 2 queries like "Control 2" above.

I am in way over my head, and appreciate all the help I can get.


Steve Schapel said:
YoDuck,

I would expect that if you make a query which includes all of your
existing queries, suitably joined on the PK field of the original table,
and then base your form or report on this query, it should produce the
goods. The Name and Control Source of the controls on the form/report
would need to be distinguished from each other, e.g.
[NameOfCountQuery].[CountOfField], or in the combining query, you could
give each column an alias, and use this in the form/report.

A simpler approach may be to drop the idea of a separate query for each
criteria, and do it all in the one query. This would involve making a
calculated field in the quey fro each criteria, something like this...
FredCount: Abs(Sum([Person]="Fred"))
(adapt according to the criteria which actually applies in your database)

A variation on this theme is to hide the detail section of the report,
and in the footer, put an unbound textbox for each item, and set its
Control Source to the equivalemt of...
=Abs(Sum([Person]="Fred"))

Please post back with more details, including examples, if you need more
specific help with this.

--
Steve Schapel, Microsoft Access MVP

I am trying to build a form/report which uses a different query for each
control. Each query works seperately, but when I try to Combine them I get a
#Name? error in each control. The queries are all count queries using the
same field in the same table with different criteria. Can anyone help??
Click to expand...
Click to expand...
 
YoDuck,

Thanks for the further clarification. I think I understand what you are
trying to achieve here. I think I would do this in two queries. The
first is to identify which ClaimNos have Transactions that meet the
various criteria, and the second to count them. Something like this...

SELECT [ActClaimNo], Min([AcctgPerid]="03-12") AS [03-12Claims],
Min([AcctgPerid]="03-12" And [Sta]="7") AS [03-12ClaimsSTA7], etc etc...
FROM TransactionRecordsTbl
GROUP BY [ActClaimNo]
(... let's say you call this query AccountClaims)

Then...
SELECT [ActClaimNo], Abs(Sum([03-12Claims])) AS [03-12ClaimsCount],
Abs(Sum([03-12ClaimsSTA7])) AS [03-12ClaimsSTA7Count], etc etc...
FROM AccountClaims
GROUP BY [ActClaimNo]

You should then be able to base your report directly on this second
query and get the numbers you want.

This works based on the concept that Access evaluates True to -1, so for
example, if there is a ClaimNo for which the [AcctgPerid] is "03-12",
then ([AcctgPerid]="03-12") will return -1, and if there is any -1
values for this ClaimNo, then Min([AcctgPerid]="03-12") will also be -1.

Please let me know if I have misunderstood in any way.
 
Back
Top