looking up the actual string with value.

[ganeshbabu346]

Trying to achieve in SQL Query way..
Let me take you through other way....
I am trying to achieve through MS Access SQL Query.

tblPatient

patient_Name	Events1	Event2	Event3
A	1	2	3
B	3

tblEvents

Patient_Name	Events	Event_Name
A	1	Vomiting
A	2	Ashtma
A	3	Fever
A	4	Cough
B	1	Viral
B	3	Nasuhe

I want the result as shown below by joining the tables..
output

Patient_Name	Event 1	Event_Name	Event2	Event_Name	Event3	Event_Name
A	1	Vomiting	2	Ashtma	3	Fever
B	3	Nasuhe

the below query I used for the above output, but its giving some error type Mismatch expression.

SELECT eot.patient_Name, 
       eot.Events1, 
       tb1.Event_Name, 
       eot.Event2, 
       tb2.Event_Name, 
       eot.Event3, 
       tb3.Event_Name
FROM (TblPatients AS eot LEFT JOIN tblEvents AS tb1 ON eot.patient_Name = tb1.patient_Name AND eot.Events1 = tb1.Events) 
      LEFT JOIN tblEvents AS tb2 ON eot.patient_Name = tb2.patient_Name AND eot.Events2 = tb2.Events 
      LEFT JOIN tblEvents AS tb3 ON eot.patient_Name = tb3.patient_Name AND eot.Events3 = tb3.Events;

how can i go forward with this error?

[orange]

You may want to review this material and determine if it will apply to your situation.

[ganeshbabu346]

@orange, i have edited the orginal post and let me know the SQL syntax is right, its giving me some error as "Missing Expression"...

[orange]

Your data structure is incorrect for database. It may be fine for spreadsheet.

A table should be about 1 subject.
Every table should have a primary key -- that is a field or combination of fields whose value is unique within the table and serves to identify each and every record in the table.
When you see fields with names such as Event1, Event2, Event3,... it is a red flag saying "Probable table structure error." It is usually a sign of a hidden Entity (another subject) in the database.

In your set up with:tblPatient and tblEvents

tblPatient is NOT about Patient, but tblEvents is NOT about Events, they are both about Patients and Events.

The structure you need, strictly from a database view is,

tblPatient
patientID PK
patientName
otherPatientInfo

tblEvent and
eventId PK
eventname

jnctblPatientEvent a junction table
patientID
eventID
and possibly eventDate or eventDateTime if more than 1 event per patient per day

The junction table resolves Many to Many relationships issues. In overview it says This patient had this Event (at this Date and Time)


tblPatient ---> jnctblPatientEvent <---tblEvent

It is critical to get your structure designed to support your requirement, and to follow rules of Normalization.

What exactly does this represent

Patient_Name
Events
Event_Name

A
1
Vomiting

A
2
Ashtma

A
3
Fever

A
4
Cough

B
1
Viral

B
3
Nasuhe

My understanding was EventId and EventName, but now I see 1---Vomiting and 1---Viral; 3---Fever and 3---Nasuhe?????
You should give meaningful names to fields. Does the Event represent the numberOfOccurrences or similar?

Good luck

[orange]

I created a small data base based on your sample data and my interpretation.
Tables were design I suggested.

tblPatientX

patientID	patientName
1	A
2	B

Some made up data for table
jnctblPatientEvent

PatientID	EventId	EventDate
1	1	29/12/2014
1	2	31/12/2014
1	3	12/11/2014
1	4	07/10/2014
2	3	12/11/2014
2	5	08/01/2015
2	6	20/10/2014

tblEventX

EventID	EventName
1	Vomiting
2	Asthma
3	Fever
4	Cough
5	Viral
6	Nausea

This query (starting point)

 SELECT tblPatientX.patientName, concatRelated('EventID','jnctblPatientEvent','PatientID = ' & [PatientID] ) as Events
      FROM tblPatientX

to get this result (I haven't attempted to get the names of events)

patientName
Events

A
1 , 2 , 3 , 4

B
5 , 6 , 3

UPDATE: Another look and was able to put the EventId and Names together in concatenated format.

I used a query to put the eventId and EventName together in query 'EventsWithNames'

SELECT jnctblPatientEvent.PatientID, [jnctblPatientEvent].[EventId] & " " & [tblEventX].[EventName] AS EventIdAndName
FROM tblEventX INNER JOIN jnctblPatientEvent ON tblEventX.EventID = jnctblPatientEvent.EventId;

which gives this format

PatientID
EventIdAndName

1
1 Vomiting

1
2 Asthma

1
3 Fever

2
3 Fever

1
4 Cough

2
5 Viral

2
6 Nausea

and then modified the final concatenation using this query

SELECT tblPatientX.patientName, concatRelated(' EventIDandName','EventsWithNames','PatientID = ' & [PatientID] ) as Events
      FROM tblPatientX

which produces the format you requested

patientName
Events

A
1 Vomiting , 2 Asthma , 3 Fever , 4 Cough

B
3 Fever , 5 Viral , 6 Nausea

Hope this is useful.
Good luck

[ganeshbabu346]

Thanks for the input.

I created the table as you shown.. but I am getting error as 'Undefined function 'ConcatRelated' in expression.

[ganeshbabu346]

any update? pleasee

[June7]

ConcatRelated is a custom function in VBA. One example of code is in http://allenbrowne.com/func-concat.html

[ganeshbabu346]

can you suggest me in Queries instead of VBA?

[orange]

I gave a link to allen Browne's concat related material in post #2, and June gave it in post #8.
You have all the info you need to give it an honest try. The query, database structure and results are shown in detail in post #5.

Please tell us exactly what the issue is. And please show us what you have done.

[June7]

Assuming table structures as show in post 5, try:

Query1
SELECT jnctblPatientEvent.PatientID, tblPatients.PatientName, jnctblPatientEvent.EventID, jnctblPatientEvent.EventDate, DCount("*","jnctblPatientEvent","PatientID=" & [jnctblPatientEvent].[PatientID] & " AND EventDate<#" & [EventDate] & "#")+1 AS GrpSeq, tblEvents.EventName
FROM (jnctblPatientEvent LEFT JOIN tblEvents ON jnctblPatientEvent.EventID = tblEvents.EventID) LEFT JOIN tblPatients ON jnctblPatientEvent.PatientID = tblPatients.PatientID
ORDER BY jnctblPatientEvent.PatientID, jnctblPatientEvent.EventDate;

Query2
TRANSFORM First(Query1.EventName) AS FirstOfEventName
SELECT Query1.PatientName
FROM Query1
GROUP BY Query1.PatientName
PIVOT Query1.GrpSeq;

or

Query2
TRANSFORM First([EventID] & " " & [EventName]) AS Expr1
SELECT Query1.PatientID, Query1.PatientName
FROM Query1
GROUP BY Query1.PatientID, Query1.PatientName
PIVOT Query1.GrpSeq;