DCount within GroupFooter 1

[schumarg]

Hi,

I'm trying to use DCount to count the records within a report Group with a value of $0.00 and show in a group footer textbox.

The report is based on a query.

I created:-

=DCount(" [RIHAUFK]![AUFNR ]","RIHAUFK"," [RIHAUFK]![GKSTI ]=0&quot

for the entire report and it's works well and the count is shown in a Report Header textbox and does return the correct count of zero dollar records for the entire report.

But what I would also like to show, is the count of the records within each group of records in a group footer textbox.

Any help would be greatly appreciated.

Bob

 

[JoaoTL]

Hi, try to use a query like this. You can SELECT A PrimaryKEY or a Field in All Records.


SELECT RIHAUFK.PrimaryKEY, Count(RIHAUFK.GKSTI) AS CountOfGKSTI
FROM RIHAUFK
GROUP BY RIHAUFK.PrimaryKEY
HAVING (((Count(RIHAUFK.GKSTI))=0));

Hope it helps
Best Regards

---
JoaoTL
NOSPAM_mail@jtl.co.pt

http://www.jtl.co.pt

http://www.jtl.co.pt/Access/eng/FAQE.htm

 

[schumarg]

JoaoTL

Thanks for the reply. But....

Tried the following:

SELECT RIHAUFK.AUFNR, Count(RIHAUFK.GKSTI) AS CountOfGKSTI FROM RIHAUFK GROUP BY RIHAUFK.AUFNR HAVING (((Count(RIHAUFK.GKSTI))=0));

I hope it's only syntax (which have given me alot of gray hair) because it is asking for a parameter. Tried inputting a parameter and it returns exactly what I type in.

Don't want to be a bother, but really appreciate your help.

Bob

 

[JoaoTL]

Ok

1. CHANGE RIHAUFK for your TABLE NAME
2. CHANGE AUFNR for your Primary Key
3. CHANGE GKSTI for your Field to Count

SELECT RIHAUFK.AUFNR, Count(RIHAUFK.GKSTI) AS CountOfGKSTI FROM RIHAUFK GROUP BY RIHAUFK.AUFNR HAVING (((Count(RIHAUFK.GKSTI))=0));

Forther trouble replay
Best Regards

---
JoaoTL
NOSPAM_mail@jtl.co.pt

http://www.jtl.co.pt

http://www.jtl.co.pt/Access/eng/FAQE.htm

 

[schumarg]

JoaoTL

Thanks for you assistance, but it seems I forgot a small bit of info. My appologies, the report was made some time ago and my memory is not as it should be.

The report is Grouped on a Joined field that I did not mention and this is probably the problem. The table and field names are correct, the report is based on an SAP query that is exported to an ACCESS database, ergo the strange field names.

I'm not sure if what I want is possible, therefore I'll not trouble anyone anymore than this last attempt. Your help is greatly appreciated and I don't want to be a bother.

The report itself is based on a query of fields from table RIHAUFK and is grouped by a field, MECHANIC.MECHANICNAM that has a relationship to the table RIHAUFK.GEWRK via Mechanic.MechanicID. What I would like to show is a total of zero dollar record values within each Group. Hope this not all confusing.

Table Field Data
RIHAUFK GEWRK Work Center ID
AUFNR Work Order Number
GKSTI Value of Order

MECHANIC MECHANICID Work Center ID
MECHANICNAM Mechanic's Name

If possible, again, your help is appreciated.

Bob