Combining fields form different tables into the same column on a listbox

[JohnL7]

Combining fields from different tables into the same column on a listbox

Hi Everyone,

I hope I'm posting in the right place, I've been trying to solve my problem using queries so I thought it might be appropriate here.
I have a database for a health care service which contains among others the following four tables...

Table 1 contains client details, primary key [ClientID] plus other client details.
Table 2 contains Episode of care details, primary key [EpisodeofcareID], [episodeofcareDate] etc...
Table 3 contains all test results for assessment 1, primary key [CoreID], [CoreDate] plus numerous scores for individual tests.
Table 4 contains all test results for assessment 2, primary key [HonosID], [HonosDate] plus numerous scores for individual tests.

Table 1 is linked to Table 2, and Table 2 is linked to both Table 3 & 4.

I have set up a search procedure whereby the user enters a clients name which then opens a list box of all clients with that name. When the client selects from the listbox I want a second listbox to open which has three visible columns. One giving the date of each episode of care for the client, the second giving the date of each assessment completed and the third giving the name of the assessment carried out.

I'm managing to get the date of the Episode of Care to display on the first visible column but I'm getting stuck on the next two. This would seem to involve somehow combining data from different tables into individual columns on the listbox. For example I need the second visible column on this listbox to list all the dates of assessments for the chosen client from tables 3 & 4. (e.g. [CoreDate] and [HonosDate]). Column three would then give the title of the assessment next to the date in column two. None of the tables have a field listing titles as this is determined by which table the data is entered into. I've been trying to solve this by queries without much success.

Could anyone give some ideas as to how I could solve these problems?

Many Thanks

John

 

[neileg]

The problem is that there is nothing that associates the records in the three tables other than the relationship with the main tables. So the concept of a list box is all wrong. The easiset way to get what I think you want is to create three continuous subforms, one for each table, and line these up side by side.

 

[JohnL7]

Hi Neil,

Thanks for the reply. I was keen to use a listbox as I wanted the user to be able to select from the list of assessments completed and then open the relevent form which displays all the details of that assessment.

I tried using SQL in VB code and could get the dates of one assessment on one column in the listbox and the ID field for that assessment in another column. Then the dates of the second assessment in column three and it's ID in column four. However that would mean having two columns in the listbox for each assessment, currently there are only two assessments however that number is likely to grow and having too many columns would become unmanageable.

Is there no way that the date field for each assessment could be combined into one column, perhaps through code?

Fingers are tightly crossed here.

Best Wishes

John

 

[neileg]

You can use the double click event of a textbox in a continuous form to trigger code in the same way as a list box.

If you want the data from the three tables in one list (I thought you wanted three columns side by side) then you can union the three queries together. Look up union queries in Access help.

 

[JohnL7]

Thanks for getting back Neil, sorry about the late reply, I've been away from the project for a bit. Your suggestion worked a treat, I played around and ended up placing a union query inside a select query which seems to do the trick. Many thanks for pointing me in the right direction.

As always another issue has cropped up. The listbox I now have has seven columns of which Three are visible. One visible column (Say Column(5)) will give the title of whatever test was completed for that record (e.g. [Test1] or [Test2] or [Test3]). Is it possible to set something up so that if a record is selected with [Test1] in column(5) then Form A is opened showing that records data, if [Test2] is selected Form B is opened and if [Test3] is selected Form C is opened. I guess this might be attempted in VBA code and i've tried some IF...THEN coding without any success.

I'd really value another of you excellent pointers!

John

 

[neileg]

Are you sure you need three different forms? Can't you use the same form and display different data?

 

[JohnL7]

Hi Neil,

I guess it's just a matter of preference. The forms have already been designed and are unique to the test in question. For user ease and familiarity I was keen for the same form to be displayed from the listbox that they used when originally entering in data.

John

 

[neileg]

OK, If ...Then is the way to go using column 5 as the parameter. You'll need to make the record source for the forms a query that uses the value of the relevant column in your combo box as a criterion.

 

[JohnL7]

Thanks Neil,

Sounds good. I'll try it out later and let you know how I got on. Your rapidly becoming my hero!!!!

John

 

[JohnL7]

Hi Neil,

Sorry for the long delay, yes If... then coding did the trick, many thanks for your help.

John