Query on multiple rows

[TPH]

I'm trying to run a query off of a table that has multiple rows for a single loan number. The example I'm providing is a very small portion of the table and amount of data I'm working with, but the concept is the same. I'm trying to show the approved AMT only once in my query results for when I run a total on the query. If the approved AMT shows up more than once the totals are incorrect for what I'm looking for. Below is an example of what I'm looking for. The 1st table shows the data as how it appears in the main database that I have no control or editing rights over and the 2nd table is how I want the results to look in Access once my query is run. Please let me know if this is possible. Thanks and sorry the infomration looks like it does. I don't know how to copy over my tables from Excel and keep the formatting.

Table Data:
Loan Number - Approved AMT - Loan Used - AMT Spent


XYZ - 100,000 - IL - 15,000
XYZ - 100,000 - MO - 5,000
XYZ - 100,000 - AZ - 24,000
XYZ - 100,000 - TX - 2,500
XYZ - 100,000 - FL - 17,500

Results wanted:
Loan Number Approved AMT Loan Used AMT Spent
XYZ - 100,000 - IL - 15,000
XYZ - 0 - MO - 5,000
XYZ - 0 - AZ - 24,000
XYZ - 0 - TX - 2,500
XYZ - 0 - FL - 17,500

[rpeare]

the only way to do this in a query that I know of is to use a domain function, if this is an extremely large dataset (thousands and thousands of records) domain functions in queries are very, very inefficient.

What you'd want to have in your APPROVED field is a formula instead of a field AND you would also have to have a reliable sort on your data, for instance LoanNumber then State

Something like

ApprovedConverted: iif(dcount("*", "TableData", "[LoanNumber] = '" & [LoanNumber] & "' AND [LoanUsed] < '" & [LoanUsed] & "'") >0, 0, [ApprovedAmt])

I didn't test it but basically you use the dcount to count the number of records with the same loan number and a state that is less than (alphabetically) the current record.

[alansidman]

I think that you need to use a report based upon a query to get your expected results. I have included one attached.

Alan

[rpeare]

No attachment mr alan!

But he said he had to export it to excel. The only other thing I might try is exporting your query line by line to a .csv (comma separated value) file and forcing the field to 0 whenever it matches the previous record.

It really depends on how much data you're exporting at one time and it avoids the whole dcount function which I hate!

[TPH]

rpeare - I copied the formula you wrote and it doesn't appear to be working. I'm going to try again to see if I'm doing something wrong.

Thanks

[TPH]

rpeare - I recreated the tables I used for the example and it is working. I must've been doing something wrong. Thanks for your assistance.

[alansidman]

Oops! Attached now. But I did miss the part about exporting to Excel. In 2007, you can export a report to excel.

Alan

[TPH]

Alan - Thanks for the attachment. However, when I opened the report within the database the 100,000 approved AMT showed up on every line and I'm trying to have it show up only once for the loan number. Not sure if I'm doing anything wrong, but don't think so since all I'm doing is opening up the report. Thanks.

[alansidman]

Did you open the Report. That is based off the query. The query shows every line but not the report. In Access 2007, you can export reports to Excel.

Alan

[TPH]

Yes I opened the Report (1st thing I did when I opened the db) and that's where I'm seeing the 100,000 on every line. I tried to do a screen print but it won't paste here.

[alansidman]

My bad! I didn't set the second grouping. Take a look at it now, revised.