Pick out dates and organise in specified columns

[chris-uk-lad]

Hi all,
I currently have a table with values at certain periods. Does anyone know how i can use a query to create a new table that identifies the dates.

E.g the first table is like:

MEMREF, DATE, VALUE, STATUS
AAAAA1, 01/01/01, 10.00, A
AAAAA1, 01/01/01, 14.00, B
AAAAA1, 01/01/01, 7.00, C
AAAAA1, 02/02/02, 11.00, A
AAAAA1, 02/04/02, 8.00, A

And i want the new table to ressemble:

MEMREF, DATE1, DATE2, DATE3
AAAAA1, 01/01/01, 02/02/02, 02/04/02

There will always be a maximum of 5 dates per reference, and will be several references. I just cannot fathom how to specify the first / second date into a new column as its not acting on a variable. I tried adding a sequence number and '+1' but no luck.

All help appreciated, thanks.

 

[The_Doc_Man]

You are attempting to take a normalized table and build a denormalized view of it. I've seen this for reports many times.

One way might be to place an ordinal number in the table to show how many times the same name has occurred. The first entry would be 1, the second entry 2, etc. etc.

You could then write a query that does

SELECT [personalname], DLookup("[TheDate]","the table name", "[Personalname] = """ &
[PersonalName] & """ AND [Ordinal] = 1"), DLookup("[TheDate]","the table name", "[Personalname] = """ &
[PersonalName] & """ AND [Ordinal] = 2"), etc. etc.

It would be up to you to define the ordinal, which could be the result of a simple update query on your ordinal field, where you would store 1 + DCount( "[key field]", "table name", "[Datefieldname] < """ & CStr( [Datefieldname] ) & """" )

But note that if the date field is not a string, the above gets more complex. I'll leave you that little piece to play around a bit.

 

[chris-uk-lad]

This count (and the following query) worked fine for 1 member but once i entered several more members, it then counted for all members in the table so instead of the count being '1', '2', '3' it became '1', '4', '8'.

Is there a way to keep these in sequence upto 5? Ive tinkered with autonumbers but yet to no avail.

Thanks