Query Required in Identify FIrst and Last


I own a big strange Zoo!!

I have 3 type of animal, Cats , Dogs and Mice.

I assign each new animal a unique number.

I tend to aquire my animals in batches.  
i.e I might acquire 10 Cats.  Then 6 months later I might acquire 20 mice. etc

See tblAnimals.

In my fantasy world tblAnimals has grown to almost 500,000 records (1 per animal)

However, my IT manager says I must reduce the number of records substantially!

So, I need a QUERY that converts tblAnimals into a more efficient view...like below.
I.e. One record per batch.   (tblAnimals currently has one record per animal which is wasteful!)

Animal   Start   End

Cat       1          6
Dog       7         8
Mouse     9    10
Cat       14      16
Dog       17     20
Cat       21     23
Patrick O'DeaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Randy PooleCommented:
What is start and end?
Rey Obrero (Capricorn1)Commented:
you need a date of acquisition for every batch

see query1
Patrick O'DeaAuthor Commented:
Thanks folks,

Rey, you solution is close but not quiet what I want.

Allow me clarify.  There are NO dates involved in my problem or solution.

The use of "First" and "Last" does not quite work,

I need the result split into batches (A batch is defined as a "group of similar animals consecutively"

Cat would be from unique record ID 1 to 6
Dogs 7 to 8
Mouse 9 to 10
Cat 14 to 16
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Randy PooleCommented:
So you want a result based upon the entry order(uniqID) which are not separated by another animal type?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rey Obrero (Capricorn1)Commented:
<The use of "First" and "Last" does not quite work,>
it will work as you have seen from Query1 if you have a date field or another field where you could group the batch.

with your current records, you will need vba codes to get what you want.
Randy PooleCommented:
Is creating your own VBA functions to work with the query allowed?
Patrick O'DeaAuthor Commented:
"So you want a result based upon the entry order(uniqID) which are not separated by another animal type?"

YES to the above question!

VBA is fine. Code welcome!

Thanks again!
Rey Obrero (Capricorn1)Commented:
i'll suggest, if you can, to revise your table and add a field to distinguish or identify animals by batch
Try this query sql:

SELECT Query1.Animal, First(Query1.Start) AS Start, Query1.End
FROM (SELECT t.Animal, t.UniqID AS Start, nz( (Select first(uniqid)-1 from tblAnimals As a Where a.animal<> t.Animal and a.uniqid>t.uniqid),   (Select last(uniqid) from tblAnimals )) AS [End]
FROM tblAnimals AS t
)  AS Query1
GROUP BY Query1.Animal, Query1.End
ORDER BY CInt([Query1].[END]);

Open in new window

Patrick O'DeaAuthor Commented:
Thanks for help.

(I ended up with a new approach but assistance appreciated!)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.