Query problem...

[Atomsk]

OK - What I have now is a bunch of queries, a bunch of subreports to count the records returned by those queries, and finally, a few main reports to enter those subreports into.

Here's the problem: A given query has a [GAP ID] field and a [BSR] field. Records can have the same "GAP ID" value, but different "BSR" values. How can I make it so that, either from the query or the subreport, records with the same "GAP ID" values are either returned or counted as a single record? For example, a query that currently returns:

SEQID IDGAP ID BSR
------------------------------
1 RGNK-4 1
2 RGNK-5 1
3 RGNK-5 2
4 RGNK-5 3
5 RGNK-5 4
6 RGNK-6 1
------------------------------

would be made to return:

SEQID IDGAP ID BSR
------------------------------
1 RGNK-4 1
2 RGNK-5 1
6 RGNK-6 1
------------------------------

or a subreport that currently counts "6" would count "3". Currently, I'm just counting the number of SEQ id values from the queries returned:

=Nz(IIf(IsNull([SeqID]),"0",Count([SeqID])))

Any help would be greatly appreciated. Thanks.

 

[cass60660]

Try this one:

SELECT Count(*) AS Expr1, Table1.id, Min(Table1.bsr) AS MinOfbsr
FROM Table1
GROUP BY Table1.id;

Success

 

[Atomsk]

...where does that go exactly?

 

[BoxOfMoonlight]

Have you tried adding the Total line to your queries and using Group By under [GAP ID]?
([GAP ID] would need to be your left most column for this to work)

 

[Atomsk]

I think that the SQL GROUP BY statment would work, but I'm having a little problems. When I add the GROUP BY statement I get an error. Here's the original:

SELECT GAPS.SeqNumber, ... ,GAPS.DaysRemaining FROM GAPS
WHERE ((GAPS.Status)="Completed"


Here's the modified ( /w syntax error: missing operator ):

SELECT GAPS.SeqNumber, ... ,GAPS.DaysRemaining FROM GAPS
GROUP BY GAPS.GAPID
WHERE ((GAPS.Status)="Completed"

Any ideas? Thanks.

 

[cass60660]

How hard can be?
Table1 its your table and:

SELECT Count(*) AS Cnt, Table1.IDGAPID, Table1.BSR
FROM Table1
GROUP BY Table1.IDGAPID, Table1.BSR
having (((Table1.BSR)=2))

Success

 

[Atomsk]

"Data type mismatch in criteria expression"

SELECT Count(*) AS Cnt, GAPS.IDGAPID, GAPS.BSR
FROM GAPS
GROUP BY GAPS.IDGAPID, GAPS.BSR
having (((GAPS.BSR)=2))

 

[Atomsk]

Never mind. Thanks for your help.

 

[cass60660]

That means you have a different type of data in that column. May be text or date. I dont know.
Try:

SELECT Count(*) AS Cnt, GAPS.IDGAPID, GAPS.BSR
FROM GAPS
GROUP BY GAPS.IDGAPID, GAPS.BSR
having (((GAPS.BSR)='2'))