Help with query calculated colums and query count (1 Viewer)

lsk88

New member
Local time
Today, 08:05
Joined
Oct 11, 2019
Messages
5
I'm trying to create two queries that needs to:
1. display animal and number of animals on loan

I do not know what I'm doing wrong as all regions are showing only 1 animal on loan:


SELECT Animals.Animal, Count(Loans.[Animal ID]) AS [Number oF Animal On Loan]
FROM Loans INNER JOIN Animals ON Loans.ID = Animals.ID
GROUP BY Animals.Animal
ORDER BY Count(Loans.[Animal ID]) DESC;
:banghead:



2. display region of the world and the number of animals on loan from that region:Here is my code and need to know if this correct


SELECT Animals.Region, Count(Loans.[Animal ID]) AS [Number Of Animals On Loan]
FROM Loans INNER JOIN Animals ON Loans.ID = Animals.ID
GROUP BY Animals.Region
ORDER BY Count(Loans.[Animal ID]) DESC;
:banghead:
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:05
Joined
May 7, 2009
Messages
19,175
Code:
1. SELECT Animals.Animal, Count(Loans.[Animal ID]) AS [Number oF Animal On Loan]
FROM Animals LEFT JOIN Loans ON Loans.[Animal ID] = Animals.[Animal ID]
GROUP BY Animals.Animal
ORDER BY Count(Loans.[Animal ID]) DESC;

2. SELECT Animals.Region, Count(Loans.[Animal ID]) AS [Number Of Animals On Loan]
FROM Animals LEFT JOIN Loans ON Loans.[Animal ID] = Animals.[Animal ID]
GROUP BY Animals.Region
ORDER BY Count(Loans.[Animal ID]) DESC;
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:05
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF! I am with Arnel on this. You should be linking primary key to foreign key, but you seem to be linking both primary keys instead.
 

lsk88

New member
Local time
Today, 08:05
Joined
Oct 11, 2019
Messages
5
Thanks all for the response and help.

I was able to correct the issue and the query run as expected.



FROM Animals LEFT JOIN Loans ON Loans.[Animal ID] = Animals.[Animal ID]


Correct:
FROM Animals LEFT JOIN Loans ON Animals.[ID] = Loans.[Animal ID]
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:05
Joined
May 7, 2009
Messages
19,175
then Linked on common field?

SELECT Animals.Animal, Count(Loans.[Animal ID]) AS [Number oF Animal On Loan]
FROM Animals LEFT JOIN Loans ON Loans.[Zoo ID] = Animals.[Owned by Zoo ID]
GROUP BY Animals.Animal
ORDER BY Count(Loans.[Animal ID]) DESC;
 

lsk88

New member
Local time
Today, 08:05
Joined
Oct 11, 2019
Messages
5
then Linked on common field?

SELECT Animals.Animal, Count(Loans.[Animal ID]) AS [Number oF Animal On Loan]
FROM Animals LEFT JOIN Loans ON Loans.[Zoo ID] = Animals.[Owned by Zoo ID]
GROUP BY Animals.Animal
ORDER BY Count(Loans.[Animal ID]) DESC;




I was able to correct the query with your guidance.
Thanks a lot.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:05
Joined
May 7, 2009
Messages
19,175
it will be easy on the other query.
 

lsk88

New member
Local time
Today, 08:05
Joined
Oct 11, 2019
Messages
5
Need help on another query.


I'm trying to calculate the number of days on loan;

SELECT Zoos.[Zoo Name], Animals.Animal, <MATH>(<Today's Date> - Loans.[Date Borrowed]) AS [Days On Loan]
FROM Animals INNER JOIN (Loans INNER JOIN Zoos ON Loans.[Zoo ID] = Zoos.[Zoo ID]) ON Animals.ID = Loans.[Animal ID];
GROUP BY Animals.Animal
ORDER BY Count(Loans.[Animal ID]) DESC;




I'm lost:
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:05
Joined
May 7, 2009
Messages
19,175
SELECT Zoos.[Zoo Name], Animals.Animal, DateDiff("d", Loans.[Date Borrowed], Date()) AS [Days On Loan]
FROM Animals INNER JOIN (Loans INNER JOIN Zoos ON Loans.[Zoo ID] = Zoos.[Zoo ID]) ON Animals.ID = Loans.[Animal ID];
GROUP BY Animals.Animal
ORDER BY Count(Loans.[Animal ID]) DESC;
 

lsk88

New member
Local time
Today, 08:05
Joined
Oct 11, 2019
Messages
5
Thanks for the clarification


I'm trying to create another query with the following data:
Create a Volunteer Awards Query:
Length as a Volunteer: (Date()-[Start Date])/365
Award: IIf([Length as a Volunteer]>7,"Gold Pen","Silver Pen")



This is what I have so far:


SELECT Volunteers.[Last Name], Volunteers.[First Name], (Date()-[Start Date])/365 AS [Length as a Volunteer], IIf([Length as a Volunteer]>7,"Gold Pen","Silver Pen") AS Award
FROM Volunteers
ORDER BY (Date()-[Start Date])/365 DESC , IIf([Length as a Volunteer]>7,"Gold Pen","Silver Pen") DESC;


When I run the query, I'm getting a promote for length as a volunteer. What I'm doing wrong.



Also when I run a report, it does not display the exact data as in the query for the length as volunteer


Thanks.
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 13:05
Joined
Jan 14, 2017
Messages
18,186
You can't use a calculated field as part of another field in the same query.
You need to use the entire expression for length as volunteer again in your IIf statement.

Code:
SELECT Count((Date()-[Start Date])/365) AS [Length as a Volunteer], 
IIf(Count((Date()-[Start Date])/365)>7, "Gold Pen","Silver Pen") AS Award
FROM Volunteers
GROUP BY Volunteers.[Start Date]
ORDER BY Count((Date()-[Start Date])/365) DESC;
 
Last edited:

Users who are viewing this thread

Top Bottom