Query Required in Identify FIrst and Last

Posted on 2014-07-17
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 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
    LVL 21

    Expert Comment

    by:Randy Poole
    What is start and end?
    LVL 119

    Expert Comment

    by:Rey Obrero
    you need a date of acquisition for every batch

    see query1

    Author Comment

    by:Patrick O'Dea
    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
    LVL 21

    Accepted Solution

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

    Assisted Solution

    by:Rey Obrero
    <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
    Is creating your own VBA functions to work with the query allowed?

    Author Comment

    by:Patrick O'Dea
    "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 119

    Expert Comment

    by:Rey Obrero
    i'll suggest, if you can, to revise your table and add a field to distinguish or identify animals by batch
    LVL 30

    Assisted Solution

    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
    Thanks for help.

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now