Creating new columns

B

bagnallc

Finding it rather difficult to get round a few problems im having.
Trouble i have is i dont have Access at work so really not getting much
time on it to learn and i have to complete this before a certain date
(soon!!)

Say you had these columns as part of a database (im listing 10 records
but in theory there would be about 500,000)

Date Animal Placing

10/05/02 Bob the Dog 6
10/07/03 Jim the Cat 4
10/07/04 Willo the Wisp 1
10/05/04 Bob the Dog 3
10/06/04 Jim the Cat 5
10/06/04 Willo the Wisp 9
10/08/05 Bob the Dog 2
10/09/05 Jim the Cat 1
10/10/05 Willo the Wisp 5
10/11/05 Jim the Cat 4

What is required is a couple of new columns to be created called
Previous Placing and Date of Last Show, so the above information would
become readable as:


Date Animal Placing Date of Last Show Previous Placing

10/05/02 Bob the Dog 6 0 0
10/07/03 Jim the Cat 4 0 0
10/07/04 Willo the Wisp 1 0 0
10/05/04 Bob the Dog 3 10/05/02 6
10/06/04 Jim the Cat 5 10/07/03 4
10/06/04 Willo the Wisp 9 10/07/04 1
10/08/05 Bob the Dog 2 10/05/04 3
10/09/05 Jim the Cat 1 10/06/04 5
10/10/05 Willo the Wisp 5 10/06/04 9
10/11/05 Jim the Cat 4 10/09/05 1

Your assistance is much appreciated.
 
J

John Vinson

What is required is a couple of new columns to be created called
Previous Placing and Date of Last Show, so the above information would
become readable as:
Click to expand...

Well, you would certainly NOT do this in a Table.

What you can do is a rather specialized Query called a "Self Join".
You would add your table to the query grid *twice* - once for the
current show, once for the previous show. Access will alias the second
instance by appending a number 1 to the tablename. Join the two on the
animal name (suggestion: you *REALLY REALLY* should have a separate
table of animals with a unique AnimalID and the name, or you'll run
into trouble with duplicate names).

Select the fields you want to see, and as a criterion on Table_1's
Date field (suggestion: rename it to ShowDate; Date is a reserved
word) put

=(SELECT Max([Date]) FROM table AS table_2 WHERE table_2.Animal =
table.Animal AND table_2.[Date] <
.[Date])

to limit the second instance to just the single most recent record
prior to the date in the first table. With this query, you won't see
data for the very first showing - post back if you need that.


John W. Vinson[MVP]
 
B

bagnallc

Many thanks john, that does make sense.

In relation to creating a unique ID for each animal, what is the best
way to do this, bearing in mind that i only have one table currently
and the column with the animal in has 500000 records which include
around 60000 animals appearing up to as many as 50 times (or in some
cases only once).
 
Top