Display last two recorded value for a client

[Durga]

Hi Guys,

I would like some help in extracting the last two recorded values for a client in a given table. There are 2000+ clients with various Blood pressure readings, hence a client may have anything from 1 to 40+ readings on different dates, and I would like to list the readings from the last two attendance dates only. I can extract the last readings but how do I extract the last two readings for the 2000+ clients? Table is [BP_Readings] and the fields to list from this table will be (Client_ID), (Reading_1), (date1), (Reading_2) and (date2).

Any help on this will be much appreciated. Thank you.



DD
x

[orange]

You might consider the following table set up (normalization).

ClientID
ReadingDate
ReadingValue




records with date1, date2 raise a red flag suggesting a non normalized structure.

[Durga]

Sure thanks. Slight change as I need to get all the readings from a cut-off date (January 2015) and not just the last two per client. Any help please?

[orange]

As I said, get your table designed for database.
The set up I showed will give you all the flexibility you need.

See this for database design info
Good luck.

[Durga]

Thank you. The database is in Access 2003. It has all been set up. I need to extract all ReadingValue for a client from January 2015 (ReadingDate) for the 2000 clients and display it in Excel with one row per Client, is this possible?

Currently I have 6000+ rows of data with multiple records for each client listing the ReadingDate and ReadingValue sitting on individual rows.

[orange]

Please give us 2 sets of sample data (you can mock it up but it should show record layout)

1: A few typical records to show the table/record layout
2: A sample of what the output should look like

[Durga]

Please give us 2 sets of sample data (you can mock it up but it should show record layout)

1: A few typical records to show the table/record layout
2: A sample of what the output should look like

It actually doesn't make sense for me now as the fields (Value and date) are not differentiated in the name:

Layout:

ClientID	Name	ReadingDate	ReadingValue
1	Smith	01/01/2016	120/75
1	Smith	02/03/2016	130/80
3	Ramsay	04/04/2016	170/92
3	Ramsay	08/09/2016	180/100
3	Ramsay	01/12/2016	170/95
8	Connor	05/05/2016	140/75
8	Connor	02/03/2017	125/91

Desired Output:

ClientID	Name	ReadingDate	ReadingValue	ReadingDate	ReadingValue	ReadingDate	ReadingValue
1	Smith	01/01/2016	120/75	02/03/2016	130/80
3	Ramsay	04/04/2016	170/90	08/09/2016	180/100	01/12/2016	170/95
8	Connor	05/05/2016	140/75	02/03/2017	125/91

Am I looking for something impossible with my knowledge of Access?

[orange]

Look at this function by Allen Browne
Good luck.

[ssanfu]

Thank you. The database is in Access 2003. It has all been set up. I need to extract all ReadingValue for a client from January 2015 (ReadingDate) for the 2000 clients and display it in Excel with one row per Client, is this possible?

Currently I have 6000+ rows of data with multiple records for each client listing the ReadingDate and ReadingValue sitting on individual rows.

Two other options would be to:
1) use VBA to write all of the data to a CSV (text) file, then import into Excel.
2) use automation (VBA) and write the data directly into the Excel workbook.