DCount in footer

[hithere]

Is it possible to have DCount field count specific records in a report footer? I am working with a report that shows how many orders have been fulfilled. I have a DCount filed that shows how many orders have been made so I can calculate a fulfillment percentage in the reprot header.



I have a report footer that groups by fulfillment date (month). I would like to place the DCount in the footer as well to show the stats by month. I copied the DCount textbox from the report header but it is giving me the exact same info in the footer as the header.

[June7]

What do you mean you have a DCount field - this is a calculated field in a query?

Report footers don't do grouping, need Group Section for that. Doesn't matter if the textbox is in report header or report footer, the data is the same.

Want to provide db for analysis? Follow instructions at bottom of my post.

[hithere]

Sorry for the late response. I had to focus my attention on another project.

The report I have displays how many orders have been shipped by the selected Company and dates in the Sales by Company Dialog form.

What I would also like to show is how many orders there are in total to produce a percentage. I have two highlighted fields in my report that I am having trouble with, which are DCounts. What I would like the first Dcount to do is to display from the Order Info query how many orders there were for the selected Company in the Sales by Company Dialog form.

In the second text box I would like the info from the first DCount text box broken down by month...i.e. how many orders there were for the selected company in the Sales by Company Dialog form that occured within that month. I am having difficulty getting either of these two fields to work correctly. I have attached my db for analysis.

[June7]

Try:

Orders Placed: =Count(*)

Orders Shipped: =Sum(IIf(Not IsNull([ShippedDate]),1,0))

Might want to add two graphic lines at bottom of OrderDate footer so it will better define the groups.

You posted db with actual addresses & phone numbers? Might want to remove the file from post.

[hithere]

Hi June 7,

I tried your suggestion and Orders Placed and Orders Shipped turn out to be the exact same number all the time when there are several orders that have not been shipped. In which case there will be more orders for a product than there are shipped.

[June7]

That does make sense. Do you expect something else?

[hithere]

In the report the orders placed and orders shipped are always equal. While this may be true in some instances, the number of orders should generally be more than than the orders shipped. I'm not sure why they always come up equal.

[June7]

That is because the Sales By Company report (titled: Ship Report from ... - ...) RecordSource is a query retrieving only OrderDetails records with a ShipID. The Count and Sum expressions are based on records in the report. If you want something else then modify the RecordSource or the calculations. For Orders placed, would need a DCount that has the same date range and company criteria as the report's RecordSource.

[hithere]

Ahhh....duh...that makes sense. Having the record souce include all orders (whether shipped or not will properly display the results). However, I need the report to be limited to only those orders that have been shipped but somehow pull a field that calculates how many orders have been made for that item within the specified time frame....and then breaking it down by month in the group footer. I think what I am trying to accomplish may not be possible since the dcount order field won't be assoicated with the control source of the report.

[June7]

The OrderInfo query won't work for order counts because it has order detail records.

Really should include the CustomerID field in Ship Report query and do filter on that field. Make CustomerID the BoundColumn of combobox. Could also include OrderID in the query.

Calculate two fields in Ship Report query:
Start: [Forms]![Sales By Company Dialog]![StartDate]
End: [Forms]![Sales By Company Dialog]![EndDate] (Odd, this calc doesn't show up in query, but the value is available to report)
Then instead of referring to the form controls in textboxes on report, can refer to the constructed fields.

Report Header orders placed calc: =DCount("[OrderID]", "Orders", "[CustomerID] ='" & [CustomerID] & "' AND [OrderDate] BETWEEN #" & [Start] & "# AND # " & [End] & "#")

Right now the only group in the report is grouping on OrderDate, not month. If you want monthly instead then Group By on Expression: Month([OrderDate]). Like the Start and End field calcs, this month calc could also be done in query and that constructed field used as the Grouping criteria and in the Group DCount calc shown below.

Place a textbox (can be hidden) named tbxMonth in the Group section: =Month([OrderDate]).
Group Footer orders placed calc: =DCount("[OrderID]", "Orders", "[CustomerID] ='" & [CustomerID] & "' AND [OrderDate] BETWEEN #" & [Start] & "# AND # " & [End] & "# AND Month([OrderDate])=" & [tbxMonth])

BTW, I NEVER build lookups in tables. When I view tables I want to see the real values. http://access.mvps.org/access/lookupfields.htm