Show empty "kennels" in report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am building a database for my kennel/training operation. I have built a
form, tables, etc that show what I need.

However, when I build a report I want to be able to show ALL the kennels
even if there's not a dog in them. (kennel space report) I have the columns
Kennel #, Dog Name, Reason here, date out.

How would I show all the Kennels names even if there's no dog of info
entered for that kennel.

Thanks
 
If the report based on two tables, you need to change the link between the
two tables from inner join to left/right join, that will display all the
records from kannel table, together with the raleted dog.
If you need help with the SQL, please post it
 
I have 4 tables at the moment. Dogs, Kennel Name, Reasons for coming, Stays.
The Stays table connects all the tables together (in the relationship, stays
is in the center) All tables 1 to many & join type (connections) 1.

I have 3 query tables Dog, Kennel, Reasons.

My report includes Dog (from dog Q), Kennel (from kennel Q), Reason (from
reason Q) & Date out (from Stay table).

My SQL for the Kennel Query is

SELECT Kennel.KennelID, Kennel.KennelText
FROM Kennel
ORDER BY Kennel.KennelText;

Any help would be appreciated greatly.
 
I have 3 query tables Dog, Kennel, Reasons.

My report includes Dog (from dog Q), Kennel (from kennel Q), Reason (from
reason Q) & Date out (from Stay table).

My SQL for the Kennel Query is

SELECT Kennel.KennelID, Kennel.KennelText
FROM Kennel
ORDER BY Kennel.KennelText;

Any help would be appreciated greatly.
Click to expand...

If you want to find those Kennels which are NOT currently occupied,
try:

SELECT Kennel.KennelID, Kennel.KennelText
FROM Kennel
WHERE KennelID NOT IN
(SELECT KennelID FROM Stay
WHERE [Date In] <= Date()
AND ([Date Out] >= Date() OR [Date Out] IS NULL))

John W. Vinson[MVP]
 
I don't know which field been used to join the two tables Kennel and Dogs, so
I joined them by the ID field, this query will return all the Kennel even if
they don't have a related record in the dog table

SELECT Kennel.KennelID, Kennel.KennelText, Dogs.DogName
FROM Kennel LEFT JOIN Dogs ON Kennel.KennelID = Dogs.KennelText
 
Back
Top