Including the sum of records from one query as a field in another (1 Viewer)

nasa09

Registered User.
Local time
Today, 01:11
Joined
Jun 12, 2015
Messages
41
I've been asked to provide account details on a number of accounts in our system, so I'm building a query that pulls account details from one table and financial details from another. The query needs to include a field that sums all of the payments that a client made over a particular time period.

Considering that each of those payments exists in the financial table as a separate record, what is the best and/or easiest way for me to incorporate this into a single field on my query? Can I use DSum in this case? (I've never used DSum and just recently used DCount for the first time.) I'm assuming I'll need to build a sub query.

I appreciate any help or advice you can give.
 

James Deckert

Continuing to Learn
Local time
Today, 03:11
Joined
Oct 6, 2005
Messages
189
Can you use the totals button in the design tab? This will sum a field and let you group by client. Specify the time period in the criteria, but uncheck the Show checkbox for the date so it won't try to group on the date.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:11
Joined
Aug 30, 2003
Messages
36,118
You can use DSum() or a subquery, though it may be better to get this value on the form/report the query is being used for. Putting a DSum() or subquery in a query can affect performance dramatically. Basically you're running an extra query for every record the main query returns for a value you may only need once.
 

nasa09

Registered User.
Local time
Today, 01:11
Joined
Jun 12, 2015
Messages
41
Thanks for the response, guys. Paul, I'm beginning to think you're my guardian angel.

So the problem is that I need to hand over a regular Excel spreadsheet that contains all of the information for each account in a single row. Regardless of how I get there, that's what I have to turn in.

In the end, I ended up running a separate query for the transactions and using VLOOKUP.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:11
Joined
Aug 30, 2003
Messages
36,118
Glad you got it sorted out. Now stop sending all the smoke up here. :p
 

Users who are viewing this thread

Top Bottom