Creating calculated fields from different tables

[Brownie1987]

I'm relatively new to using Access. I have built a basic patient database for my hospital's surgical service but am struggling with a few things.
This time, I am struggling to link operations into a specific admission.



What I am trying to achieve is for all of the operation records relating to an admission record (held in separate one-many (admission-operation) linked tables) to be able to show as a single, calculated field in final report.

Is anyone able to explain how to do this, please?

Thanks

Oli

[ranman256]

you would put it in the query. Speed: [Dist]/[Time]

then you can show it on forms or reports.

[Brownie1987]

Thanks,

I am able to write reports to query it but I'm wondering if it is possible to pull all of the records into a single calculated field.

For example in my patient database I might have

Table: Patients

Donald Duck
Mickey Mouse

Table: Operations
Donald Duck - Appendicectomy
Donald Duck - Haemorrhoidectomy
Mickey Mouse - Left leg amputation

I want to know if I can create a calculated field which would give a record in the Patient table which looked more like

Donald Duck - Appendicectomy, Haemorrhoidectomy
Mickey Mouose - Left Leg Amputation

It's vital that the operations are maintained as a separate table, because there's loads more information in other fields and to maintain their auditability.
In actual fact, Patients have admissions and within admissions have operations! but am looking initially to work out how to create the calculated field in Admissions so that the final report can show multiple operations per admission in a single field (rather than creating multiple lines). Does anyone know how to do this?

Thanks

[ranman256]

databases dont really work that way....they show data vertically, with zero effort.

Now a report can show items top-down then next column
or
left-to-right, then next row.
(in report, page setup)

[Gicu]

Hi Brownie1987,

Yes, you can do that fairly easy. There is a custom function on the web called JoinFromArray that would give you exactly what you want. I believe I got it from the Access MVP site, but you can also download it from my site (forestbyte.com) under the VBA code samples page.

Cheers,
Vlad

[Brownie1987]

Thanks vlad!

I've been reading about various modules you can write to concatenate tables!

Will look at this one

Best

O

[June7]

Review http://allenbrowne.com/func-concat.html