Relating information in a query to other tables & queries

[jcocomo]

Hi there. I am quite new to MS-Access and databases in general, so please bear with me if this is a very basic question.

I am trying to create a database in Access 2000 that will track patients as they move from room to room in a hospital setting. I have a table for patient demographics, called “Patients”, one for room numbers and information, called “Rooms”, and one for information on movements from room to room, called “Movement.” “Patients” consists of a medical record number field as the primary key, and a number of text fields containing demographic information. “Rooms” consists of a text field with room numbers as the primary key and a text field for room type. “Movement” consists of a field for medical record number, a date and time field, a starting room field and an ending room field. The medical record number field in “Movement” is related to that in “Patients”, and the starting and ending room fields are both related to the room number field in “Rooms.”

I need to create a query or a report that will show only the most recent movement record for each patient, and then filter out those records whose ending room is not of a certain type, essentially a census of a given unit.

What I have tried is creating a query as such:

Select EndRoom, Max(DateTime) as LastTime
From Movement
Group by EndRoom

Things work reasonably well up until this point. This query spits out a list of rooms and with the last time a patient was moved into them.

However, when I go the design view and attempt to add other fields, strange things happen. If I add fields from “Patients” I start to get multiple records per room. If I add the “RoomType” field from “Rooms” and try to add a criteria so that I only get the room types I want, I get records that appear to be largely random.

Can anybody suggest anything? I would be extremely grateful for any help.

Jason Merrill

[puppydogbuddy]

Hi there. I am quite new to MS-Access and databases in general, so please bear with me if this is a very basic question.

I am trying to create a database in Access 2000 that will track patients as they move from room to room in a hospital setting. I have a table for patient demographics, called “Patients”, one for room numbers and information, called “Rooms”, and one for information on movements from room to room, called “Movement.” “Patients” consists of a medical record number field as the primary key, and a number of text fields containing demographic information. “Rooms” consists of a text field with room numbers as the primary key and a text field for room type. “Movement” consists of a field for medical record number, a date and time field, a starting room field and an ending room field. The medical record number field in “Movement” is related to that in “Patients”, and the starting and ending room fields are both related to the room number field in “Rooms.”

I need to create a query or a report that will show only the most recent movement record for each patient, and then filter out those records whose ending room is not of a certain type, essentially a census of a given unit.

What I have tried is creating a query as such:

Select EndRoom, Max(DateTime) as LastTime
From Movement
Group by EndRoom

Things work reasonably well up until this point. This query spits out a list of rooms and with the last time a patient was moved into them.

However, when I go the design view and attempt to add other fields, strange things happen. If I add fields from “Patients” I start to get multiple records per room. If I add the “RoomType” field from “Rooms” and try to add a criteria so that I only get the room types I want, I get records that appear to be largely random.

Can anybody suggest anything? I would be extremely grateful for any help.

Jason Merrill

Place your query in design view. Right click anywhere in the table area of the query grid (the open area only; do not click on a table). This will bring up a shortcut menu from wihich you will select "Properties". In the query property sheet, set "unique values" to yes.....run your query....it should be working the way you want it to. Setting the unique value property to yes is the same as using the "Distinct" keyword.