Show empty "kennels" in report

[HLarkin]

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

 

[Ofer]

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

 

[HLarkin]

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.

 

[John Vinson]

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.

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]

 

[Ofer]

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