If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Using DCount in Group Footer filtering records based on value of the group
I've included a DCount in my report and I'm trying to determine if I can
reference the "value" of a report group as part of the criteria in a DCount function. I'd like to count the number of records in a table where the value of a field equals the value of the group. My first question is whether DCount can even reference the group value. If it can, please help me out. If it can't, I'll have to find out if there is another way to address the reporting needs. My database keeps track of Grant applications. One component of the database is to track the quarterly status reports submitted for each application (who sumbitted reports, what application report is for, when received, completed correctly, etc...) I have a report that shows this information and now want to show the % of applications where reports were submitted. An application may have no status report submitted (0) or any number of reports submitted. I need to reference the Applications table to determine how many applicants SHOULD have sent a report. I will then use this to later calculate the % of reports submitted. The challenge is, my QuarterlyStatus report is grouped by SubProgram and then by Quarter. I need the percent of reports submitted by each SubProgram and each Quarter. tblApplications (Table I'm referencing to count the number of records) ApplicationID ProgramID SubProgramID .... tblStatusReports (Table where information about reports submitted is stored) ApplicationID DateSubmitted ReportingQuarter .... My attempt at getting this to work sort of looks like this: =DCount("[ApplicationID]","tblApplication","subProgramID = Group1.Value") where Group1.value corresponds to the SubProgram of that report group. Any guidance will be appreciated. Thanks in advance, Beth |
#2
|
|||
|
|||
You can try:
=DCount("[ApplicationID]","tblApplication","subProgramID = " & [subProgramID]) This assumes subProgramID is numeric. If it isn't: =DCount("[ApplicationID]","tblApplication","subProgramID = """ & [subProgramID] & """") -- Duane Hookom MS Access MVP -- "Beth" wrote in message news:Xtj0d.79035$yh.55871@fed1read05... I've included a DCount in my report and I'm trying to determine if I can reference the "value" of a report group as part of the criteria in a DCount function. I'd like to count the number of records in a table where the value of a field equals the value of the group. My first question is whether DCount can even reference the group value. If it can, please help me out. If it can't, I'll have to find out if there is another way to address the reporting needs. My database keeps track of Grant applications. One component of the database is to track the quarterly status reports submitted for each application (who sumbitted reports, what application report is for, when received, completed correctly, etc...) I have a report that shows this information and now want to show the % of applications where reports were submitted. An application may have no status report submitted (0) or any number of reports submitted. I need to reference the Applications table to determine how many applicants SHOULD have sent a report. I will then use this to later calculate the % of reports submitted. The challenge is, my QuarterlyStatus report is grouped by SubProgram and then by Quarter. I need the percent of reports submitted by each SubProgram and each Quarter. tblApplications (Table I'm referencing to count the number of records) ApplicationID ProgramID SubProgramID ... tblStatusReports (Table where information about reports submitted is stored) ApplicationID DateSubmitted ReportingQuarter ... My attempt at getting this to work sort of looks like this: =DCount("[ApplicationID]","tblApplication","subProgramID = Group1.Value") where Group1.value corresponds to the SubProgram of that report group. Any guidance will be appreciated. Thanks in advance, Beth |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
suspend footer printing based on grouping. | rusty | Setting Up & Running Reports | 1 | July 16th, 2004 10:20 PM |
making option groups visible/invisible based on another option group selection | Emma | Using Forms | 3 | June 24th, 2004 05:29 AM |
Counting specific records in a group | HeatherC | Setting Up & Running Reports | 3 | June 5th, 2004 06:40 PM |
Filtering last 24 hours Records | Craig Fellows | Running & Setting Up Queries | 3 | June 4th, 2004 03:05 PM |
Complicated display in group footer | Jennifer | Setting Up & Running Reports | 6 | June 1st, 2004 09:06 PM |