missing record

[Bladerunner]

Why would a simple query not pick up a record in a table. I have tried everything and get nothing. The record Number is 34 and the AnimalID = 102. Using a simple query that is based upon the animal type, the query will pull up most all animals but like 102 and a few others, it will not pull up. The group and location fields are blank for these animals. The query is pulling up other animals but cannot pull up a select few. just don't understand this. Can pull up the main table and they are all there.

any suggestions out there.

Thanks in advance

Blade

 

[pbaldy]

What is the SQL of the query? Either a criteria or join is likely the culprit. From the sound of it, a join.

 

[Bladerunner]

After I Posted the initial question, I started removing tables from the query and checking the outcome each time. What I found out was that the group and location affected the outsome of the query. Now both of these fields for #102 are empty. Is there a way I can prevent the query from missing these records based upon an empty field.

Thanks again

Blade

 

[Bladerunner]

One other question please.

How would you add a simple animal count (number of animalID that the query has pulled up) on the query?

Thanks again

Blade

 

[pbaldy]

You didn't post the SQL so I can just suggest editing the join between the tables.

 

[Bladerunner]

SELECT [tblAnimal Setup].AnimalID, tblGender.Gender, [tblAnimal Type].AnimalType, [tblAnimal Setup].DateSold, [tblAnimal Setup].DateDied, tblGroup.Groups, tblLocation.Location
FROM tblLocation INNER JOIN (tblGroup INNER JOIN ([tblAnimal Type] INNER JOIN (tblGender INNER JOIN [tblAnimal Setup] ON tblGender.GenderID = [tblAnimal Setup].Gender) ON [tblAnimal Type].AnimalTypeID = [tblAnimal Setup].AnimalType) ON tblGroup.GroupID = [tblAnimal Setup].Group) ON tblLocation.LocationID = [tblAnimal Setup].Location
WHERE (((tblGender.Gender)<>"Male-Lamb") AND (([tblAnimal Type].AnimalType)="sheep") AND (([tblAnimal Setup].DateSold) Is Null) AND (([tblAnimal Setup].DateDied) Is Null));

 

[namliam]

Why do people expect magic and just splash sql onto a forum, please post READABLE code and do so in code tags, see my signature for a link on how to use it.

SELECT [tblAnimal Setup].AnimalID, tblGender.Gender, [tblAnimal Type].AnimalType, [tblAnimal Setup].DateSold, [tblAnimal Setup].DateDied, tblGroup.Groups, tblLocation.Location
FROM tblLocation 
INNER JOIN (tblGroup 
INNER JOIN ([tblAnimal Type] 
INNER JOIN (tblGender 
INNER JOIN [tblAnimal Setup] ON tblGender.GenderID = [tblAnimal Setup].Gender) 
                             ON [tblAnimal Type].AnimalTypeID = [tblAnimal Setup].AnimalType) 
                             ON tblGroup.GroupID = [tblAnimal Setup].Group) 
                             ON tblLocation.LocationID = [tblAnimal Setup].Location
WHERE (((tblGender.Gender)<>"Male-Lamb") 
  AND  (([tblAnimal Type].AnimalType)="sheep") 
  AND  (([tblAnimal Setup].DateSold) Is Null) 
  AND  (([tblAnimal Setup].DateDied) Is Null));

What you do is double click on the line between your tables in the query design.
Here you choose the option that is right for you, try experementing with the 3 options given to you to see the different out comes.

This is called an Outer Join, depening on the option you choose Left outer join or Right outer join.

Good luck !

 

[Bladerunner]

Thanks for the advice... My apologies for the posting of the sql in unedited form.

Blade