1 big query or a few small ones?

[Co9ug9ar]

I have a report that does quite a few Dsums (with atleast 2 comparisons per
Dsum) that is based off a single general query. Doing all the sums and
comparisons takes a long time because there are about 3,000 records that
satisfy the query.

Would it be faster to create a bunch of smaller queries with tighter
criteria or does it not matter because either way all the comparisons are
done to the queries anyway?

Any suggestions, even possible different ways to run the reports, are
greatly appreciated.
-Keith

 

[Jeff Boyce]

Keith

Your "quite a few" and mine may not be similar.

I don't understand "with at least 2 comparisons per DSum".

What's the SQL statement you're using for the query?

--
More info, please ...

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Co9ug9ar]

I have one of these statements about 72 times (Twice for each product, 3
products, 12 months) within the report. The 'Summary of Deliverables Query'
has about 3,000 records.

"=IIf(IsNull(DSum("[Number of Reports]","Summary of Deliverables
Query","Product = 'GPP' and [Valuation Month]='January' and [Date Delivered]
<= [Calendar Date Due]")),0,DSum("[Number of Reports]","Summary of
Deliverables Query","Product = 'GPP' and [Valuation Month]='January' and
[Date Delivered] <= [Calendar Date Due]"))"

 

[Jeff Boyce]

It would also help to get a sense of the underlying data structure you are
working from.

Right off the top, it seems like you may not need to use the IIF() function.
If I'm interpreting it correctly, you want to have either the [Number of
Reports] for GPP, in January, and Delivered-before-Due, or, if there is
none, a 0. Why not just use Nz({your DSum()},0)?

It isn't clear if you are doing this in the report or in your query.

Are you doing this for every possible combination of product and month?
What will you do when you add a new product? What about next year's
"January"?

I'm suspecting your underlying data structure would benefit from some
additional normalization. Is there a chance that the Access tables are
exact copies of data coming from a spreadsheet?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

I have one of these statements about 72 times (Twice for each product, 3
products, 12 months) within the report. The 'Summary of Deliverables Query'
has about 3,000 records.

"=IIf(IsNull(DSum("[Number of Reports]","Summary of Deliverables
Query","Product = 'GPP' and [Valuation Month]='January' and [Date Delivered]
<= [Calendar Date Due]")),0,DSum("[Number of Reports]","Summary of
Deliverables Query","Product = 'GPP' and [Valuation Month]='January' and
[Date Delivered] <= [Calendar Date Due]"))"

Keith

Your "quite a few" and mine may not be similar.

I don't understand "with at least 2 comparisons per DSum".

What's the SQL statement you're using for the query?