Query Required in Identify FIrst and Last

Posted on 2014-07-17
Medium Priority
Last Modified: 2014-07-29

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
Question by:Patrick O'Dea
  • 3
  • 3
  • 3
  • +1
LVL 21

Expert Comment

by:Randy Poole
ID: 40201864
What is start and end?
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40201875
you need a date of acquisition for every batch

see query1

Author Comment

by:Patrick O'Dea
ID: 40201948
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 21

Accepted Solution

Randy Poole earned 668 total points
ID: 40201984
So you want a result based upon the entry order(uniqID) which are not separated by another animal type?
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 668 total points
ID: 40201988
<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.
LVL 21

Expert Comment

by:Randy Poole
ID: 40202005
Is creating your own VBA functions to work with the query allowed?

Author Comment

by:Patrick O'Dea
ID: 40202226
"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!
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40202299
i'll suggest, if you can, to revise your table and add a field to distinguish or identify animals by batch
LVL 31

Assisted Solution

hnasr earned 664 total points
ID: 40202975
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


Author Closing Comment

by:Patrick O'Dea
ID: 40228241
Thanks for help.

(I ended up with a new approach but assistance appreciated!)

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question