Help with an SQL query

[MikeB]

Hi,

I have three tables (more actually, but only these three are relevant
to this query).

tblLOANS
Key: Text(23)
Status: Text(15)
FK_Group: Text(23)
FK_Member: Text(23)
:
:

tblGROUPS
Key: Text(23)
ShortName: Text(20)
:
:

tblMEMBERS
Key: Text(23)
FK_Group: Text(23)
:
:

From these three tables I want to construct a query that will list all

groups meeting the following criteria:
Group must have 5 loans AND
Group must have 10 members

I tried the following query, but the count values for both Members and
Loans are equal, not the values in the two tables that I was hoping to
get.

SELECT Group.ShortName, Count(Loan.Key) AS Loans, Count(Member.Key) AS
Members
FROM Member INNER JOIN ([Group] INNER JOIN Loan ON Group.Key =
Loan.GroupKey) ON Member.Key = Group.MemberKey
WHERE (((Loan.Status)="Completed"))
GROUP BY Group.ShortName
HAVING (((Count(Loan.Key))>5) AND ((Count(Member.Key))>10));

I can see that this is wrong, but I just can't figure out how to
construct a correct query.

Thanks

 

[John Nurick]

Hi Mike,

Groups with 5 loans:

SELECT First(FK_Group)
FROM tblLOANS
GROUP BY FK_Group
HAVING COUNT(Key) >= 5

Groups with 10 members:

SELECT First(FK_Group)
FROM tblMEMBERS
GROUP BY FK_Group
HAVING COUNT(Key) = 10

So with a bit of luck (i.e. I haven't taken the trouble to create tables
and test it) this will do the job:

SELECT Key, ShortName
FROM tblGROUPS
WHERE (Key IN (
SELECT First(FK_Group)
FROM tblLOANS
GROUP BY FK_Group
HAVING COUNT(Key) >= 5
))
AND (Key IN (
SELECT First(FK_Group)
FROM tblMEMBERS
GROUP BY FK_Group
HAVING COUNT(Key) = 10
))
;

BTW, in your structure you have 1:M relationships between
Members and Loans
Groups and Loans
Groups and Members.

Doesn't this mean that a Loan can belong to one Group, and also to a
Member who belongs to a different Group? Does this matter?

Hi,

I have three tables (more actually, but only these three are relevant
to this query).

tblLOANS
Key: Text(23)
Status: Text(15)
FK_Group: Text(23)
FK_Member: Text(23)
:
:

tblGROUPS
Key: Text(23)
ShortName: Text(20)
:
:

tblMEMBERS
Key: Text(23)
FK_Group: Text(23)
:
:

From these three tables I want to construct a query that will list all

groups meeting the following criteria:
Group must have 5 loans AND
Group must have 10 members

I tried the following query, but the count values for both Members and
Loans are equal, not the values in the two tables that I was hoping to
get.

SELECT Group.ShortName, Count(Loan.Key) AS Loans, Count(Member.Key) AS
Members
FROM Member INNER JOIN ([Group] INNER JOIN Loan ON Group.Key =
Loan.GroupKey) ON Member.Key = Group.MemberKey
WHERE (((Loan.Status)="Completed"))
GROUP BY Group.ShortName
HAVING (((Count(Loan.Key))>5) AND ((Count(Member.Key))>10));

I can see that this is wrong, but I just can't figure out how to
construct a correct query.

Thanks

 

[Guest]

Sorry, that should have read:

SELECT ShortName,
(SELECT COUNT(*)
FROM Member
WHERE Member.GroupKey = Group.Key) AS MemberCount,
(SELECT COUNT(*)
FROM Loan
WHERE Loan.GroupKey = Group.Key
AND Status = "Completed") AS CompletedLoanCount
FROM Group
WHERE
(SELECT COUNT(*)
FROM Member
WHERE Member.GroupKey = Group.Key) >=10
AND
(SELECT COUNT(*)
FROM Loan
WHERE Loan.GroupKey = Group.Key) >=5;

I've assumed the foreign key columns in Loan and Member are both called
GroupKey.

The way it works is that the same subqueries are used in both the SELECT and
WHERE clauses of the outer query, in the first returning the counts of
members and loans per group, in the second restricting the outer query on the
basis of the counts. The subqueries are correlated with the outer query on
the keys in each case.

I'd recommend against using generalised names like Key for columns. Instead
use descriptive names , e.g. GroupKey as the primary key of the Group table
and as the names of the foreign keys in the member and Loan tables which
reference this. Using the same names for primary key columns and foreign key
columns which reference them makes the semantics of the SQL clearer both when
writing and reading the query.

Ken Sheridan
Stafford, England

 

[MikeB]

BTW, in your structure you have 1:M relationships between
Members and Loans
Groups and Loans
Groups and Members.

Doesn't this mean that a Loan can belong to one Group, and also to a
Member who belongs to a different Group? Does this matter?

John, thanks for the help with the query. I've solved it in the interim
by creating 3 queries, one to get the groups w 5 loans, one to get the
groups w 10 members and then another query to select groups in both of
the prior queries. I'll try out your suggestion, it is more what I
wanted and seems more elegant.

As for your question, above.

There is one member who "owns" a group. This member must not be in the
group he "owns", but can be in another group.
Each member may join one or zero groups..
Each member may have one or zero loans.

Each group thus can have many members and many loans.

I see the "problem" you refer to, but I guess the programs that create
the data keeps it straight so that a loan and its member always point
to the same group. A member cannot leave a group while they have an
active loan. Even after they leave the group, the old loans remain
attributed to the group for group statistics.

I must tell you that I have an incomplete view of the data. I get a
daily extract from a much more complicated database to manipulate for
statistical reasons. The extended credit data and payment data is not
disclosed in the extract I get.

Now for another question. What is a good SQL primer? I feel that what I
learned in university is not enough to get me by and I think I have a
problem with the Help on my Access XP as it does not seem to have much
about SQL in it.

I've seen some references about downloading help, but the link I have
didn't take me to a download. Probably too old by now.

 

[John Nurick]

Mike,

You've probably spotted the typos in what I posted, mainly a mix-up of

= and = . Obviously it's = for "with 5 loans", and => for groups with 5

or more loans.

SQL Primer: some Access textbooks cover this, but if you want a book
specifically on SQL I don't know one better than Hernandez & Viescas,
"SQL Queries for Mere Mortals" (Addison Wesley).