DataBase - Select Distinct subquery in a Report Control?

I have written a report  that aggregates (avg, sum, count) a 10 or 15 measures
($, Days to Book, # rejects, %within SLA, etc.)  I need to do a COUNT
DISTINCT on one control (D&B number) to determine how many distinct companies
I am dealing with in a particular country or region.

I have written a SQL subquery that does what I want it to do, but I cannot
figure out how to imbed that statement into a control expression in a Report.
I prefer to work with the report engine because I need to take a number of
cuts on different groups and levels of detail and just find it easier to do
using Reports.  it is working great except for this one control.  Any thoughts?

Thanks, Jim

  Duane Hookom replied to Jim K

You cannot use a SQL statement or query name in the control source of a text
box. You might be able to use a domain aggregate function such as DLookup()
or DMax() or DSum().

It would help if we knew more about your tables and/or query.

--
Duane Hookom
Microsoft Access MVP

aggregates if you can avoid it? Thanks for any feedback. Access Queries Discussions Database (1) Report (1) DSum (1) DMax (1) Bontrager (1) Min (1) Aggregates (1) Subqueries (1) If you are talking about things like DCount, DMax, and such, they can be really slow if you have a lot (like 50, 000 two. Keith Bontrager - Bicycle Builder. If you mean the "domain aggregate functions" e.g. DLookup(), DSum(), DCount(), etc., then yes they should be avoided whenever possible in queries. The reason is for this reason it is sometimes better to use the Domain Aggregate Functions (things like DMAX, DMIN, DSUM, DLOOKUP). Domain Aggregate Functions are slower, but they avoid some of the problems with Aggregate sum, count or whatever, and when you do, you have a choice of using a report, or three different query methods. (david) keywords: Opinion, about, aggregates description: Im fairly new to

Some info not showing up in my report DataBase Good morning. I have a report that is pulling from a query. When I run the query, it has all the data it should have. But when I open the report, I don't see all of the information that was in the query. Since the the query. . . . . . does anyone have any tips on what I should look for in my report, that it would all not be coming thru? I have no filters set up. Thanks via http: / / www.accessmonster.com Access Reports Discussions Access 2007 (1) VB (1) VATPercent (1) Report (1) UnitPrice (1) NetCost (1) DMax (1) DAvg (1) Never mind! Found the answer myself. . . . . . . . . . .have a good day everyone! - - Message What was the answer - I am having the exact same problem! Are you using the Report Print Preview instead of the Report View button? Or are you running Acc2007? Evi run the I tips thru? Yah! You

Add to text box String DataBase Im trying to add a total off my report to this string for emailing, problem being the text box is on the report and not the form tbGrandTotalM is on the report that is being emailed rptOwnerPaymentMethod How do I code tbAmount below in between the * ** ** ** Private think you will need to use one of two methods: 1.) A Domain Aggregrate function, DSum, which uses a table or query with a specified field, and the appropriate criteria. Here is an example of using the DLookup function; all of the D functions (DLookup, DSum, DMin DMax, etc.) include parameters for specifying a field, a table or query, and an optional criteria keywords: Add, to, text, box, String description: Im trying to add a total off my report to this string for emailing, problem being the text box is on the report and not the form tbGrandTotalM is on the r

Cumulative sum in report DataBase Hi- I have created a query (with much help from folks on this forum) that lists deliveries of parts by date. Now I am struggling to create a report that summarizes the information. I could use more help. There are 7 different Part types V2 value (1-11) V3 value (1-4) Part Type# (1-7) Delivery date The report that I have created (so far) organizes the information with the following nested groups: Group P4, P5, P6, P7 So far that all works fine. The grouping levels in the report show only the Parts by Variable that exist as records (i.e. I have avoided somehow create the cumulative sum in a totals query and then show it in the report?? - - Message posted via AccessMonster.com http: / / www.accessmonster.com / Uwe / Forums.aspx / access-reports / 200804 record perhaps your Autonumber if records are always entered chronologically. You could use in your report's query. DSum the Value field with the criteria that YourVariable = Your current Variable and Part Number = Your DrumSizeID, [Core parts2]. BeadSizeID, [Core parts2].DrumWidthID, [Core parts2].[Ship date (est)], [Core parts2].[2], DSum("2", "Core parts2", "[DrumSizeID] = " & [Core parts2]. DrumSizeID) AS RunSum2, [Core parts2].[5], [Core parts2].[1