query needed please

[Guest]

Hello,

I need to create a query that would do the following:

Return Field displays the primary loan Number if one exists. If no
loan # is designated as primary, but another loan# exists, than the first
loan # will be listed. Otherwise, the first contract# will be listed.

to try and explain a different way. The managers like to have only one
number on the report, it should either be the primary loan if there is a
primary loan, if no primary loan, than it should be the first active loan, if
there is no primary loan and no active loan it should be the first Contract#.

I am sorry if I have not explain something well or have left something out,
please ask and I will explain in more detail. I have provided structure and
samples below.

--phill

[structure and details ]
--------------------------------------------
Table1
Index ID (key ) (unique)
property name
address
misc other

Table2
Index ID (partial key 1)
role_code (partial key 2)
Loan# (duplicates allowed )
contract# (duplicates allowed)

table3
index ID (partial key 1)
contract# (partial key 2)
misc ...

Relationships
Table1 has one-to-many relationship to Table2
Table1 has one-to-many relationship to Table3

Information:
table2 role_code
0 = loan paid off / Not Active
1 = primary loan / active
2 = secondary loan /active
3 = third loan /active


background
First, I have no control over the structure or relationships in this
database, I have only been given access to create several reports.

The primary tables store property information. Table2 stores loan
information where each property is allowed to have several different loans
and stores each of those loan's by a loan number and a role_code, which
denotes if the loan is a primary loan or subsequent loan.
Table3 represents contracts that are sometimes given, similar to loans but
money does not come from a bank but comes from another source.


example:
table1:
index name address1 address2 city state
8000 property1
8001 property2
8002 property3
8003 property4

table2:
index role_code loan# details
8000 0 45500
8000 1 32200 refi
8001 1 32300 new roof
8001 2 32400 expansion
8001 3 32500
8003 1 45200
[ note NO 8002 in this table , no loan]

Table3
Index contract#
8002 34000
8003 34200
8001 34300
[ note: 8002 is in here, it has a contract# ]

 

[David S via AccessMonster.com]

Firstly, is contract# in Table2? Form your data, it looks like this should be
[details] instead...

I think it would also be safe to assume that they're not interested in
retired loans anymore, just the active ones.

OK, to get the loan#, you basically want to work out what the minimum role is
for each property (excluding the inactive ones) and then get the
corresponding loan#

LoanRole:
SELECT Table2.[Index ID], Min(Table2.role_code) AS MinOfrole_code
FROM Table2
WHERE (((Table2.role_code)<>0))
GROUP BY Table2.[Index ID];

Then, LoadReference:
SELECT Table2.[Index ID], Table2.[loan#]
FROM LoanRole INNER JOIN Table2 ON (LoanRole.MinOfrole_code = Table2.
role_code) AND (LoanRole.[Index ID] = Table2.[Index ID]);

Then, you need to figure out which properties have no loans, and get the
first contract from them in FirstContract:
SELECT Table3.[Index ID], First(Table3.[contract#]) AS [FirstOfcontract#]
FROM Table3 LEFT JOIN LoanReference ON Table3.[Index ID] = LoanReference.
[Index ID]
GROUP BY LoanReference.[Index ID], Table3.[Index ID]
HAVING (((LoanReference.[Index ID]) Is Null));

Then, to put the output together, you need to use a UNION query
PropertyReference:
SELECT [Index ID], [loan#] as ReferenceID
FROM LoanReference
UNION
SELECT * FROM FirstContract

With your sample data below, this query gives the output:
Index ID ReferenceID
8000 32200
8001 32300
8002 34000
8003 45200

Is that what you were looking for?

 

[Guest]

Hello David,

Giving it a quick lookthrough it seems like it will work, I'm freeing up
some time tomorrow to work on this project and experiment with your SQL.

One thing I need is either the primary loan# if no primary loan and no other
loan ,then I need the contract#. Your assumptions seem to be valid though
for my
needs.

-phill

 

[David S via AccessMonster.com]

One thing I need is either the primary loan# if no primary loan and no other

loan ,then I need the contract#. Your assumptions seem to be valid though
for my needs.

I guess that's why I was asking about them The above SQL should do the job,
although it assumes that "and no other loan" means "no other active loan". If
you would like to include inactive loans when there is no Primary Loan, it
gets a bit more complicated, since you need to do some more work to figure
out which properties don't have a Primary Loan

LoanRole2:
SELECT Table2.[Index ID], Min(Table2.role_code) AS MinOfrole_code
FROM Table2
GROUP BY Table2.[Index ID];

NonPrimaryLoan:
SELECT Table2.[Index ID], Table2.[loan#]
FROM PrimaryLoan RIGHT JOIN (LoanRole2 INNER JOIN Table2 ON (LoanRole2.[Index
ID] = Table2.[Index ID]) AND (LoanRole2.MinOfrole_code = Table2.role_code))
ON PrimaryLoan.[Index ID] = Table2.[Index ID]
WHERE (((PrimaryLoan.[Index ID]) Is Null));

LoanReference2
SELECT * FROM PrimaryLoan
UNION SELECT * FROM NonPrimaryLoans;

Other queries remain the same (except for swapping in LoanReference2 for
LoanReference, of course)

 

[Guest]

Hi David,

Based on what I had told you, your queries produced the exact results, but I
wonder if you could help some more. I was told that in the case that there
is no active loan (I've created new sample tables), then we do need to show
the inactive loan#.

The reason I'm told is that the managers know the properties and their
loan#'s because the loan numbers represent the location and other
information, based on the first 2-digits, but the index's are just autonumber
fields which the staff do not use.

I really appreciate your help, If there is anything I can help you with
please let me know.


table1:
index name address1 address2 city state
8000 property1
8001 property2
8002 property3
8003 property4

table2:
index role_code loan# details
8000 0 45500
8001 1 32300 new roof
8001 2 32400 expansion
8001 3 32500
8003 1 45200
[ note # 8002 in this table , no loan]
[ Note # 8000 only has an old loan]

Table3
Index contract#
8002 34000
8003 34200
8001 34300
[ note: 8002 is in here, it has a contract# ]

Firstly, is contract# in Table2? Form your data, it looks like this should be
[details] instead...

I think it would also be safe to assume that they're not interested in
retired loans anymore, just the active ones.

OK, to get the loan#, you basically want to work out what the minimum role is
for each property (excluding the inactive ones) and then get the
corresponding loan#

LoanRole:
SELECT Table2.[Index ID], Min(Table2.role_code) AS MinOfrole_code
FROM Table2
WHERE (((Table2.role_code)<>0))
GROUP BY Table2.[Index ID];

Then, LoadReference:
SELECT Table2.[Index ID], Table2.[loan#]
FROM LoanRole INNER JOIN Table2 ON (LoanRole.MinOfrole_code = Table2.
role_code) AND (LoanRole.[Index ID] = Table2.[Index ID]);

Then, you need to figure out which properties have no loans, and get the
first contract from them in FirstContract:
SELECT Table3.[Index ID], First(Table3.[contract#]) AS [FirstOfcontract#]
FROM Table3 LEFT JOIN LoanReference ON Table3.[Index ID] = LoanReference.
[Index ID]
GROUP BY LoanReference.[Index ID], Table3.[Index ID]
HAVING (((LoanReference.[Index ID]) Is Null));

Then, to put the output together, you need to use a UNION query
PropertyReference:
SELECT [Index ID], [loan#] as ReferenceID
FROM LoanReference
UNION
SELECT * FROM FirstContract

With your sample data below, this query gives the output:
Index ID ReferenceID
8000 32200
8001 32300
8002 34000
8003 45200

Is that what you were looking for?

 

[David S via AccessMonster.com]

Based on what I had told you, your queries produced the exact results, but I

wonder if you could help some more. I was told that in the case that there
is no active loan (I've created new sample tables), then we do need to show
the inactive loan#.

Sure - but have you tried the revised queries I put together dated 11-09-2005
00:29? They should show the Inactive loans as well...