Converting rows to columns: data type mismatch?

[hroy]

Hi,

I hope I put this in the right sub-forum.

I'm somewhat of a newbie in Access but I've managed to find my way reasonably well by trial & error and looking up a lot of stuff online. I'm currently facing a problem that I find difficult to solve though. I have a table that looks a bit like the one below here. The rows are all unique patient visits that have their respective patient ID and name, the date of the visit, a number of variables (I only added "echo_var" now) and for each patient their visits are ordered chronologically.

ID	patientID	patientname	studydate	echo_var	echo_number
1	1234	Bob	01-01-2014	15	4
2	1234	Bob	01-01-2000	13	1
3	1234	Bob	03-08-2012	14	3
4	1234	Bob	13-02-2005	13	2
5	6625	Louie	12-11-2010	25	3
6	6625	Louie	08-10-1998	20	1
7	6625	Louie	19-04-2006	21	2

What I want to achieve is transform the data so it looks more like the table below, where each patient is on only one row and all the unique timepoints and the data obtained at that time point are displayed in columns (the actual amount of columns would depend on the number of visits).

patientID	echo_number1	studydate1	echo_var1	echo_number2	study_date2	echo_var2
1234	1	01-01-2000	13	2	13-02-2005	13
6625	1	08-10-1998	20	2	19-04-2006	13

[hroy]

I'm having trouble editing my posts (it says my message is too short), apologies, so I have to write the rest here:

I've tried the following queries:

SELECT
[patientID],
"echo_number" & [echo_number] & "_" & "echo_var" AS [ValueID],
[echo_var] AS [ValueValue]
FROM [TABLENAME]
UNION ALL SELECT
[patientID],
"echo_number" & [echo_number] & "_" & "study_date" AS [ValueID],
[study_date] AS [ValueValue]
FROM [TABLENAME];
Followed by:


TRANSFORM Sum(NEWQUERY.ValueValue) AS SumOfValueValue
SELECT NEWQUERY.patientID
FROM NEWQUERY
GROUP BY NEWQUERY.patientID
PIVOT NEWQUERY.ValueID


But using the second query gives me the error message. I figured it had to do with data formatting, but I tried setting the study_date variable to text and number, to no avail. When I substituted this variable with a random other variable contained in the original database, similar to echo_var, it worked perfectly fine. It seems it still has something to do with the dates, however I can't seem to find the solution. If anyone knows how to address this problem please let me know.

[June7]

Can't Sum() the ValueValue field. Use First() or Max().

You are aware only 255 columns are allowed in table or query? Could a patient have enough visits to exceed this limit?

Do you intend to print this data?

Review http://allenbrowne.com/ser-67.html#MultipleValues and http://allenbrowne.com/func-concat.html

Should not save the patient name in that table, just patient ID.

[hroy]

Ah, thanks a lot, I feel really stupid. Using First solved the problem entirely.

I was aware about the column limit yes, it shouldn't be a problem . And you're right I shouldn't need the names - the file was delivered to me this way - I now use the table without names.

Thanks for the help and the links, I appreciate it

[Minty]

Be aware that the First() function is likely to not always give you the results you want.
Have a read here https://access-programmers.co.uk/for...ad.php?t=42291

Many people think the First and Last functions should be removed from Access as they aren't really useful and get misused!