Need expression to look in two fields

[clint]

I am creating a report that reflects Mortgage Lenders and Loan Volumes by
State. In the table, I have 1 column for 1st Mtg Lender and another column
for 2nd Mtg Lender. I have a separate entry in the form for each. But on
the Report I want it to list the lender alphabetically from either column.
In the Expression Bldr, when I only use the 1st Mtg Lender, it works for that
column but doesn't see the other. But when I try to use "and", "or",
"and/or", "+", etc...I just get error message or the report shows a zero or
-1.

 

[clint]

I'll try to clarify. On any particular Mortgage transaction, there will be
either one mortgage or two. Consequently, there will either be one lender or
two...but sometimes its the same lender...sometimes its not. So in the Table
and in the Form I have a place for 1st Lender and a place for 2nd Lender. So
the states need me to file a report annually with the total loan volume by
lender...regardless whether it is the 1st or 2nd lender. So I have
everything working on the money. Subtotals, totals, grand totals. All is
well but when I try to have for example the total of Wells Fargo originations
it will only pull either the 1st lender or the 2nd lender but I can't get the
expression builder to accept my 'formula'. I've tried to put both fields in
there every way imaginable but I'm not getting it to work

 

[clint]

State Date Funded 1st Mortgage Loan Amount 2nd Mortgage Loan Amount 1st
Mortgage Lender 2nd Mortgage Lender
Kansas 18-Jan-06 $145,600.00 $36,400.00 American Mortgage Network American
Mortgage Network
Kansas 17-Jan-06 $47,200.00 $11,800.00 Aegis Aegis
Kansas 11-Jan-06 $147,000.00 $0.00 M & I None
Kansas 05-Jan-06 $487,500.00 $13,000.00 First Magnus First Magnus
I tried to make it look right on here but it doesn't Is there another place
to send you a copy of the spreadsheet?

 

[Rick Brandt]

State Date Funded 1st Mortgage Loan Amount 2nd Mortgage Loan Amount 1st
Mortgage Lender 2nd Mortgage Lender
Kansas 18-Jan-06 $145,600.00 $36,400.00 American Mortgage Network American
Mortgage Network
Kansas 17-Jan-06 $47,200.00 $11,800.00 Aegis Aegis
Kansas 11-Jan-06 $147,000.00 $0.00 M & I None
Kansas 05-Jan-06 $487,500.00 $13,000.00 First Magnus First Magnus
I tried to make it look right on here but it doesn't Is there another place
to send you a copy of the spreadsheet?

I was able to paste that into Notepad and get a decent looking grid from it.

Your dilemma is caused by a poor design. You really should have a table
structure where all of the amounts and lenders are in the same field with an
additional field indicating whether the record is considered the first or second
mortgage. Then summing by lender is a piece of cake.

With your current design you need to do some tinkering. You could build a UNION
query that puts the lender names and amounts into two combined columns and then
do a Totals query against the UNION query.

SELECT [1st Mortgage Lender] as Lender, [1st Mortgage Loan Amount] as LoanAmount
FROM TableName
UNION ALL SELECT [2nd Mortgage Lender] as Lender, [2nd Mortgage Loan Amount]
FROM TableName

That query will give you two columns with lender names in the first column and
amounts in the second. Use that as the input to a totals query to get the sum
per lender.

 

[clint]

Let me get some stuff together and reply to the email address you listed. I
think you're correct on the query but I'll need more hand holding. I'm also
sure you're right that its a flawed design. I did it myself. The only
problem I see with structuring it the way you propose is that It wouldn't
differentiate between a tandem 1st and 2nd mortgage---and a stand alone 2nd
mortgage. Other than that, I'd change to your preferred design now.

State Date Funded 1st Mortgage Loan Amount 2nd Mortgage Loan Amount 1st
Mortgage Lender 2nd Mortgage Lender
Kansas 18-Jan-06 $145,600.00 $36,400.00 American Mortgage Network American
Mortgage Network
Kansas 17-Jan-06 $47,200.00 $11,800.00 Aegis Aegis
Kansas 11-Jan-06 $147,000.00 $0.00 M & I None
Kansas 05-Jan-06 $487,500.00 $13,000.00 First Magnus First Magnus
I tried to make it look right on here but it doesn't Is there another place
to send you a copy of the spreadsheet?

I was able to paste that into Notepad and get a decent looking grid from it.

Your dilemma is caused by a poor design. You really should have a table
structure where all of the amounts and lenders are in the same field with an
additional field indicating whether the record is considered the first or second
mortgage. Then summing by lender is a piece of cake.

With your current design you need to do some tinkering. You could build a UNION
query that puts the lender names and amounts into two combined columns and then
do a Totals query against the UNION query.

SELECT [1st Mortgage Lender] as Lender, [1st Mortgage Loan Amount] as LoanAmount
FROM TableName
UNION ALL SELECT [2nd Mortgage Lender] as Lender, [2nd Mortgage Loan Amount]
FROM TableName

That query will give you two columns with lender names in the first column and
amounts in the second. Use that as the input to a totals query to get the sum
per lender.