Query from list box displaying incorrect values.

[adammitchell23]

I'm trying to sort a population of animals from the table [ALL WEIGHTS] by those that are alive (from the table MainDeaths, field [STATUS: DEAD OR ALIVE], which is a list box with the options either "DEAD" or "ALIVE"). I then want to display them by last date they were weighed ([ALL WEIGHTS].[DATE OF WEIGHT]). I am doing this so I can set up a warning that tells me if a (live) animal has not been weighed in the last 14 days. I have tried this using Unique Values and Unique Records, and I've also tried making a separate "Live Animals" query and using this instead of coming straight from the MainDeaths table. I either get one of four results; 1. I get about 62000 results (there are only about 300 animals in the population). 2. I get each animal displayed twice, displayed as both "dead" and "alive". 3. I get the result that every animal in the population is "alive", which is not true. 4. The date of last weight is displayed as 26/11/2013 for every animal (this is the last time the last animal was weighed). This is the SQL I'm using - this particular code gives me the correct number of animals and the correct dates for when each animal was last weighed, but incorrectly tells me that every animal is "alive".

SELECT DISTINCT Last([ALL WEIGHTS].[DATE OF WEIGHT]) AS LastDate, [ALL WEIGHTS].[ANIMAL ID], MainDeaths.[STATUS: DEAD OR ALIVE]
FROM [ALL WEIGHTS], MainDeaths
GROUP BY [ALL WEIGHTS].[ANIMAL ID], MainDeaths.[STATUS: DEAD OR ALIVE]
HAVING (((MainDeaths.[STATUS: DEAD OR ALIVE])="ALIVE"));

.

Thanks all!

Adam

[pbaldy]

Certainly one problem is not having a join between the two tables. Are they related to each other?

[adammitchell23]

Hi Paul,
Yes they are; by a field called "Animal ID" in my primary table (called MainID) - I'd upload my database, but I'm at a research station in the Kalahari and the internet is both crap and expensive!

[June7]

If you can't upload a bare bones version of db, some info on the data structure would be helpful.

Since you are tracking individuals, presume you have a table with a unique record for each individual? You have a separate table for death info? Then you have a table for weight data and each individual can have many records?

You want this query to show the last weigh date but don't need the weight?

SELECT [DATE OF WEIGHT]) AS LastDate, [ANIMAL ID] FROM [ALL WEIGHTS] GROUP BY [ANIMAL ID];

Now join that query to the MainDeath table OR the query and MainDeath to the Individuals table by linking on the [ANIMAL ID] fields.


BTW, for future design reference, advise no spaces or special characters/punctuation (underscore is exception) in names. Also, avoid all caps. Better would be AnimalID or Animal_ID.