asked on

Microsoft Access query help

Can this query be designed to "not" display an animal if it is in the same kennel twice?

SELECT Animals.AnimalName, [LastName] & ", " & [FirstName] & " " & [MiddleName] AS PersonName, Visits.DateIn, Visits.DateOut, Kennels.Kennel, Species.Species, Visits.Type, Clients.ClientID, Kennels.PlacementDate
FROM Species INNER JOIN ((((Clients INNER JOIN Animals ON Clients.ClientID = Animals.ClientID) INNER JOIN Kennels ON Animals.AnimalID = Kennels.AnimalID) INNER JOIN Visits ON Clients.ClientID = Visits.ClientID) INNER JOIN (CONtblPeople INNER JOIN Customers ON CONtblPeople.ContactID = Customers.ContactID) ON Clients.ClientID = Customers.ClientID) ON Species.SpeciesID = Animals.SpeciesID
WHERE (((Visits.DateIn)<=Now()) AND ((Kennels.Kennel) Is Not Null) AND ((Visits.Type) Not In ("d","g")) AND ((Kennels.PlacementDate)<=Now()) AND ((Visits.IsCheckedOut)=False)) OR (((Visits.DateIn)<=Now()) AND ((Kennels.Kennel) Is Not Null) AND ((Visits.Type) Not In ("d","g")) AND ((Kennels.PlacementDate)<=Now()) AND ((Visits.IsCheckedOut)=False));

Kent Dyer

I think you need to change your WHERE clause from:

AND ( ( kennels.kennel ) IS NOT NULL )

to be:
AND (COUNT ( kennels.kennel ) < 2 )

On either side of your OR statement..

HTH,

Kent

J.R. Sitman

ASKER

Here is what I changed. See bold portion. See attachment for error message.

SELECT Animals.AnimalName, [LastName] & ", " & [FirstName] & " " & [MiddleName] AS PersonName, Visits.DateIn, Visits.DateOut, Kennels.Kennel, Species.Species, Visits.Type, Clients.ClientID, Kennels.PlacementDate
FROM Species INNER JOIN ((((Clients INNER JOIN Animals ON Clients.ClientID = Animals.ClientID) INNER JOIN Kennels ON Animals.AnimalID = Kennels.AnimalID) INNER JOIN Visits ON Clients.ClientID = Visits.ClientID) INNER JOIN (CONtblPeople INNER JOIN Customers ON CONtblPeople.ContactID = Customers.ContactID) ON Clients.ClientID = Customers.ClientID) ON Species.SpeciesID = Animals.SpeciesID
WHERE (((Visits.DateIn)<=Now()) AND ((Kennels.Kennel) Is Not Null) AND ((Visits.Type) Not In ("d","g")) AND ((Kennels.PlacementDate)<=Now()) AND ((Visits.IsCheckedOut)=False)) OR (((Visits.DateIn)<=Now()) AND (Count(Kennels.Kennel)<2) AND ((Visits.Type) Not In ("d","g")) AND ((Kennels.PlacementDate)<=Now()) AND ((Visits.IsCheckedOut)=False));
query-error.png

jerryb30

Can you post a sample database, maybe a few records?
I think if you do a group by (in query design view), add your criteria, and create a new column
Count(Kennels.Kennel) with your criterium of <2, it might be what you want.
Or, a dcount should work.

chaau

Add this to the end of the query:

AND NOT EXISTS (SELECT K.Kennel FROM Kennels K where K.AnimalID = Animals.AnimalID AND K.Kennel = Kennels.Kennel GROUP BY K.Kennel HAVING COUNT(*) > 1)

Open in new window

J.R. Sitman

ASKER

@chaau, can you modify my query with your addition and post it.

I can't post a sample because it is too large.

chaau

(Just added to the end of your original query)

SELECT Animals.AnimalName, [LastName] & ", " & [FirstName] & " " & [MiddleName] AS PersonName, Visits.DateIn, Visits.DateOut, Kennels.Kennel, Species.Species, Visits.Type, Clients.ClientID, Kennels.PlacementDate
FROM Species INNER JOIN ((((Clients INNER JOIN Animals ON Clients.ClientID = Animals.ClientID) INNER JOIN Kennels ON Animals.AnimalID = Kennels.AnimalID) INNER JOIN Visits ON Clients.ClientID = Visits.ClientID) INNER JOIN (CONtblPeople INNER JOIN Customers ON CONtblPeople.ContactID = Customers.ContactID) ON Clients.ClientID = Customers.ClientID) ON Species.SpeciesID = Animals.SpeciesID
WHERE (((Visits.DateIn)<=Now()) AND ((Kennels.Kennel) Is Not Null) AND ((Visits.Type) Not In ("d","g")) AND ((Kennels.PlacementDate)<=Now()) AND ((Visits.IsCheckedOut)=False)) OR (((Visits.DateIn)<=Now()) AND ((Kennels.Kennel) Is Not Null) AND ((Visits.Type) Not In ("d","g")) AND ((Kennels.PlacementDate)<=Now()) AND ((Visits.IsCheckedOut)=False))
AND NOT EXISTS (SELECT K.Kennel FROM Kennels K where K.AnimalID = Animals.AnimalID AND K.Kennel = Kennels.Kennel GROUP BY K.Kennel HAVING COUNT(*) > 1);

Open in new window

J.R. Sitman

ASKER

sorry, same results. Still showing duplicates. See attached
query-dups.png

chaau

So, it is not that the dogs are recorded in the same kennel then. Its is just a duplicate query. You need to find out where it is duplicating. I recommend you start with replacing all your columns with * (i.e. SELECT * FROM etc.). This way you will see that for duplicating rows at least one column will be different. Tell me what table this column belongs to. I will be able to fix your query

J.R. Sitman

ASKER

Here is an explanation of the process. When the dog arrives it is placed in a kennel until it is checked out and goes home. What is happening is if the staff have to make an additional entry because the dog is going to stay longer that was planned they have to make another entry showing the new dates the dog will be there. So the first dates are still there and the dog is now shown as being in the kennel twice, which can't be corrected until it is checked out. So the report we use lists all the dog in the facility correctly, but the query shows we have more dogs than we really do, because it is in the kennel twice. The way the database is designed, once an entry is saved it can't be deleted. So they can't take the dog out of the kennel from either entry.

Hope this helps. The kennel table is called "kennel"

J.R. Sitman

ASKER

What happened to my help?

jerryb30

If you would post a database with empty tables, I can look at it.

chaau

You have not posted the select * result I have asked.

ASKER CERTIFIED SOLUTION

jerryb30

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

J.R. Sitman

ASKER

Not sure if I can ask this here, but I use an Access programmer for occasional help and design. His health is not so good. I need to find someone who is willing to help us. We are the Society for the Prevention of Cruelty to Animals Los Angeles. We are non-profit so I don't have a lot of funds to pay. Our current guy does many little things (under an hour for free) If he does charge it's $60 per hour.

Are you interested in helping us?

jerryb30

Any expert is free to post an address for job opportunities in their profile.

See this question: https://www.experts-exchange.com/questions/22492767/Job-Posts.html

You may contact me via e-mail for volunteer work. (See my profile-AboutMe tab.)

J.R. Sitman

ASKER

just emailed you

J.R. Sitman

ASKER

The query worked perfectly. Can you modify it so it gives me a count of only "dogs"

jerryb30

Dogs being a species variant, as opposed to cats, gerbils, lizards,etc? And just the count, not detailed info?

To clarify something: You wanted to ignore animals with more than one record, when in fact you only wanted a single record for each unique animal which had checked in but not checked out. So efforts to work a count are meaningless in that light.

Sine this is closed out (Thank You), post additional questions for additional requirements.

J.R. Sitman

ASKER

Not sure I understand " So efforts to work a count are meaningless in that light"? Yes I just want a count of dogs and not the details.

jerryb30

I meant in terms of trying to do a count, and excluding anything over 1.
If you save the query, then use that as a source for "Select count(*) from yourQueryName where species = 'Dog'".
Your query asked for a lot of detail.

J.R. Sitman

ASKER

got it.

Thanks

J.R. Sitman

ASKER

Thanks very much