Query Multiple years

[sammer021486]

Here is the code that I used to query 2006 and find all the values that were true in 2006, but not 2009.

PARAMETERS [Enter First Year to search] Value, [Enter Second Year to search] Value;
SELECT Owner.OwnerNo, Owner.Salutation, Owner.[Owner1 LastName], Owner.[Owner1 FirstName], Owner.StreetAddress, Owner.[PO Box], Owner.MailingAddress, Pet.PetName, Tag.Year, Owner.MovedDeceased, Pet.Deceased, Pet.Type
FROM (Owner INNER JOIN Pet ON Owner.OwnerNo = Pet.OwnerNo) INNER JOIN Tag ON Pet.PetNo = Tag.PetNo
WHERE (((Tag.Year)=[Enter First Year to search]) AND ((Pet.Type)="dog") AND ((Exists (SELECT petno from tag as Dupe where (Dupe.petno = pet.petno) and (dupe.[year] = [Enter Second Year to Search])))<>True));

Now I need to query all the years 2006-2009 and show all the pets that were registered in those years or not registered. The year registered is all contained in the Tag table, so I have to rename the tag table to search it multiple times.

An example would be

Petno 2006 2007 2008 2009
1 x x x x
2 x x
3 x x



Petno 1 was registered all years, petno 2 was not registered in 2008 & 2009, Petno 3 was not registered in 2006 & 2007

[dcrake]

It seems like your table sare not normalised. If you have a different field for each year then it certainly is not. What you need is a new table with a link to the main table that is a 1:m. A new record will be created for each year a pet is registered.

David

[sammer021486]

It seems like your table sare not normalised. If you have a different field for each year then it certainly is not. What you need is a new table with a link to the main table that is a 1:m. A new record will be created for each year a pet is registered.

David

Sorry that was a bad example. There is just one year column in the dog table and when the dog is issued a new tag number every year a new record is created and a tag key number is then associated with the tag number and the tag key number becomes the primary key in the tag table, the pet number is the primary key in the pet table, and the owner number is the primary key in the owner table.

So my tables are set up correctly I think. 1 owner : M pets, 1 pet : M tags.

The relationship is set up so that the owner table has the owner number, then the pet table has the owner number and the pet number, then the tag table has the pet number and the tag key number.

Edit: the tag key is different from the tag issued number, because every year the tag issued starts back at one

[sammer021486]

So I think that I got it figured out properly. I am still new to access and SQL so some times I have the SQL formula incorrect and it wont run for me.

SELECT DISTINCT Owner.OwnerNo, Owner.Salutation, Owner.[Owner1 LastName], Owner.[Owner1 FirstName], Owner.StreetAddress, Owner.MailingAddress, Owner.[PO Box], Pet.PetNo, Tag.Year, Tag.TagNo, Pet.Type
FROM (Owner INNER JOIN Pet ON Owner.OwnerNo = Pet.OwnerNo) INNER JOIN Tag ON Pet.PetNo = Tag.PetNo
WHERE (((Pet.Type)="dog") AND ((Owner.MovedDeceased)=No) AND ((Pet.Deceased)=No))
order by owner.ownerno, pet.petno, tag.year;

The above SQL statement should return all the dogs, first by the owner's number, then by the pet's number and finally show all the years the pet was registered. Leaving out any duplicates if there are any.