Solved

Filter by one "Many" value in a query

Posted on 2004-10-07
8
275 Views
Last Modified: 2008-02-26
Ok, this is been a serious PITA for me - I've got an Access 2000 database for a car dealership, and I'm having trouble taking values that I calculate in a form and instead calculate them in a query.

For example, we have a payment table to track customers' monthly payments.  It is setup with a primary key for each payment, and the invoice number from the buys table is associated with it.  

e.g.
 
paymentID     InvoiceNumber   PaymentAmount   PaymentDate  Receipt Number
1                          123                   400.00                 9/21/04          239487
2                          143                   142.78                 8/30/04          059201
3                          123                   400.00                 10/04/04        239498
4                          102                   350.00                 7/23/04          098520

I have a query take these values plus all the sales info included by the invoice number such as vehicle and customer info (date sold, monthly payment, total financed, etc), then a form that filters this by invoice number with a combo box.  

Based on what is loaded into the form from the query, the payments are summed up and past due, months past due, # of payments made, # of payments left, interest-based calculation for early payoff etc etc are done.

Ok!  so I want to do a sort of "For/Next" loop like "For each distinct Invoice Number, sum up Payment value and store that sum".  How do I set a filter for another query to do that?

e.g.

Invoice Number       LastPaymentMade       PaymentSum   PastDueAmount
102                         7/23/04                     350.00            700.00
123                         10/04/04                   800.00            400.00
143                          8/30/04                    142.78            300.78
 
0
Comment
Question by:shakparl
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 

Author Comment

by:shakparl
ID: 12253671
The payments themselves are displayed in a subform with the invoice number as the parent field, and the sum of the payments is calculated on the fly in the form.
0
 
LVL 3

Expert Comment

by:Informative
ID: 12253698
In a query the GROUP BY clause will cause it to sum the values in the other column by default
0
 
LVL 3

Accepted Solution

by:
Informative earned 500 total points
ID: 12253748
So like if you have this

Invoice Number       LastPaymentMade       PaymentSum   PastDueAmount
102                         7/23/04                     250.00            700.00
102                         7/15/04                     125.00            700.00
123                         10/04/04                   800.00            400.00
143                          8/30/04                    142.78            300.78

your query will look something like this
SELECT Table1.invnumber, max(Table1.date), sum(Table1.amount)
FROM Table1
group by Table1.invnumber;

with results something like this
number      Expr1001      Expr1002
102      7/1/04      375
123      8/5/04      800
143           8/30/04    142.78
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:shakparl
ID: 12254908
Ok, that sums the payments up beautifully, but it's difficult to work with that query.  I can include a number of other values from other query like customer name and date sold, but when I try to add an expression that is calculated from another query, it complains about "Invalid Use of Null", or something about not being included in an aggregate function.

Should I just redo the expressions in this new paymentsum query?

e.g. I need the total financed, monthly payment and such to get all this together, but those are like this:

Monthly Payment: CCur((CLng(100*((([Total Financed]+[IntAccrDFP1])*[Periodic Rate])/(1-((1+[Periodic Rate])^((-1)*Buys.[Number of Payments]))))))/100)

 in the original "Buyers Order Query".  Some of them could be 0/Null, is that what the problem is?
0
 

Author Comment

by:shakparl
ID: 12280795
I'm trying to rebuild the expression from the Buyers Order Query and keep getting this:

3122 You tried to execute a query that does not include the specified expression <name> as part of an aggregate function.

Including expressions from other queries never caused me trouble before.  What is with this error when using GROUP BY?
0
 
LVL 3

Expert Comment

by:Informative
ID: 12280882
You said:  (when I try to add an expression that is calculated from another query ... something about not being included in an aggregate function.)

This is equivalent to Access Design view when you add the "group by" row all columns must have either "SUM" or "GROUP BY" or other "aggregate instruction" which determines if they will create a new row on each uniqie value for that column or add all of the values together for that cell based on other columns unique groupings.

Take this oversimplified query for an example:

SELECT Table1.name, Sum(Table1.cost) AS SumOcost, Table1.city FROM Table1 GROUP BY Table1.name, Table1.city;

Notice how three columns are pulled from table1 yet only two are grouped by?  We could not simply include a column without applying some form of ranged calculation on the remaining column because several rows of those values will need to be USED in some way in this case we SUM them for the total for that grouping.

We could Average them or take a Count of them just as well but if you had five rows in a group it needs an aggregate handler to deliver a single value for those five entries under that group/subgroup.

I hope that clears it up some.
0
 
LVL 3

Expert Comment

by:Informative
ID: 12280968
Notice in the above example query we combined invoice 102 into a single row it had to know what to do with the other columns.  The Max function would have returned 250.  Average function would return 187.5, sum gave us 375, Count would return 2.. etc.  the function which takes the many entries and returns the single entry is the aggregate.  

In the example I used Max to return just the most recent date.
0
 

Author Comment

by:shakparl
ID: 12282940
Thanks for the response.  When I properly set all values in the query to Group By, I get the "invalid use of null error", which (from what I've read) is just Access choking on a null value, and not related to this thread.  Yuk, I have to rebuild that query with Nz([field],0) since a lot of them are null.

Anyway, thanks again for the useful info!  You've been really helpful!
0

Featured Post

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question