Creating a Ward Map

  • Thread starter Thread starter Nurse Matthew
  • Start date Start date
N

Nurse Matthew

I need help with an issure for a ward in a medium stay hospital setting. I
have 21 available beds, and patients change rooms daily.

I have a database that stores the names, room numbers, etc. All of our
on-ward reports are generated from here.

I have been given a request to be able to print a ward map, showing the name
in the correct place on the map (of the ward), and of course updating and
reprinting several times daily.

How can I make a report that shows this type of data?

I am a "power user," not a programmer.
MS Access 2003, but I have 2007 at home and could use that if necessary.

Thanks in advance for your help.
Matthew
 
Do you have field names and data types you could share? I would expect you
could create a Crosstab query with the beds as column headings and patients
as the values. You could then place these anywhere you want in the detail
section of a report.
 
Okay, the field names are [Full Name] and [Rooms] (I believe in the KISS
method)
There is a query that links the 2 together.

[Rooms] lists all 22 beds distinctively, so even ward rooms show the actual
room/bed number

I don't know how to have multiple fields in the detail section. All of our
current reports are lists. (Sorry if I'm a dimwit)
:oP
Matthew
 
Assuming you have a query that returns the current occupant of each room like:

[FullName] [Room]
Joe A-1
Bill A-2
Tom A-4
Mary B-2
Sally B-6

You could create a crosstab query with a
- Row Heading of [FullName] Count
- Column Heading of Room Group By
- Value of FullName First

Then set the Column Headings property to all possible Room values
Column Headings: "A-1","A-2","A-3","A-4","A-5",...
"B-1","B-2","B-3","B-4","B-5",...

Running this report should display a single record with the current occupant
under each Room/column. You can then build a report and simply bind the
columns to text boxes in the detail section. If you use a full page detail
section, you have room to move the text boxes where ever they need to be.
--
Duane Hookom
Microsoft Access MVP


Nurse Matthew said:
Okay, the field names are [Full Name] and [Rooms] (I believe in the KISS
method)
There is a query that links the 2 together.

[Rooms] lists all 22 beds distinctively, so even ward rooms show the actual
room/bed number

I don't know how to have multiple fields in the detail section. All of our
current reports are lists. (Sorry if I'm a dimwit)
:oP
Matthew

Duane Hookom said:
Do you have field names and data types you could share? I would expect you
could create a Crosstab query with the beds as column headings and patients
as the values. You could then place these anywhere you want in the detail
section of a report.
Click to expand...
Click to expand...
 
Hi Duane,

PMFJI,

I've been following this thread, and I just tried your suggested solution,
and find that it doesn't work. Setting up a crosstab query as you propose
gives the following message when you try to run it: "You must enter Group By
in the Total row for at least one of the Row Heading options you enter in
the crosstab row."

Fortunately, there's a very simple fix. Instead of [FullName] Count as the
Row heading, enter "FullName" (with the quotes) as the Field for the Row
Heading, with Group By in the Total row; this will give a single record
query as required for use in the report layout.

The SQL for my test query is:
TRANSFORM First(tblWardMap.FullName) AS FirstOfFullName
SELECT "FullName" AS Expr1
FROM tblWardMap
GROUP BY "FullName"
PIVOT tblWardMap.Room In
("A-1","A-2","A-3","A-4","A-5","A-6","B-1","B-2","B-3","B-4","B-5");

HTH,

Rob


Duane said:
Assuming you have a query that returns the current occupant of each
room like:

[FullName] [Room]
Joe A-1
Bill A-2
Tom A-4
Mary B-2
Sally B-6

You could create a crosstab query with a
- Row Heading of [FullName] Count
- Column Heading of Room Group By
- Value of FullName First

Then set the Column Headings property to all possible Room values
Column Headings: "A-1","A-2","A-3","A-4","A-5",...
"B-1","B-2","B-3","B-4","B-5",...

Running this report should display a single record with the current
occupant under each Room/column. You can then build a report and
simply bind the columns to text boxes in the detail section. If you
use a full page detail section, you have room to move the text boxes
where ever they need to be.
Okay, the field names are [Full Name] and [Rooms] (I believe in the
KISS method)
There is a query that links the 2 together.

[Rooms] lists all 22 beds distinctively, so even ward rooms show the
actual room/bed number

I don't know how to have multiple fields in the detail section. All
of our current reports are lists. (Sorry if I'm a dimwit)
oP
Click to expand...
Matthew

Duane Hookom said:
Do you have field names and data types you could share? I would
expect you could create a Crosstab query with the beds as column
headings and patients as the values. You could then place these
anywhere you want in the detail section of a report.

--
Duane Hookom
Microsoft Access MVP


:

I need help with an issure for a ward in a medium stay hospital
setting. I have 21 available beds, and patients change rooms daily.

I have a database that stores the names, room numbers, etc. All of
our on-ward reports are generated from here.

I have been given a request to be able to print a ward map,
showing the name in the correct place on the map (of the ward),
and of course updating and reprinting several times daily.

How can I make a report that shows this type of data?

I am a "power user," not a programmer.
MS Access 2003, but I have 2007 at home and could use that if
necessary.

Thanks in advance for your help.
Matthew
Click to expand...
Click to expand...
Click to expand...
 
Thanks Rob. Your solution is a good one. I was vaguely aware there must be at
least one "Group By" as a Row Heading. I hoped a simple Count would suffice
(apparently not).

Do you think the remainder of my suggested solution will work as long as the
number of columns/rooms doesn't get too large (approach 200)?

--
Duane Hookom
Microsoft Access MVP


Rob Parker said:
Hi Duane,

PMFJI,

I've been following this thread, and I just tried your suggested solution,
and find that it doesn't work. Setting up a crosstab query as you propose
gives the following message when you try to run it: "You must enter Group By
in the Total row for at least one of the Row Heading options you enter in
the crosstab row."

Fortunately, there's a very simple fix. Instead of [FullName] Count as the
Row heading, enter "FullName" (with the quotes) as the Field for the Row
Heading, with Group By in the Total row; this will give a single record
query as required for use in the report layout.

The SQL for my test query is:
TRANSFORM First(tblWardMap.FullName) AS FirstOfFullName
SELECT "FullName" AS Expr1
FROM tblWardMap
GROUP BY "FullName"
PIVOT tblWardMap.Room In
("A-1","A-2","A-3","A-4","A-5","A-6","B-1","B-2","B-3","B-4","B-5");

HTH,

Rob


Duane said:
Assuming you have a query that returns the current occupant of each
room like:

[FullName] [Room]
Joe A-1
Bill A-2
Tom A-4
Mary B-2
Sally B-6

You could create a crosstab query with a
- Row Heading of [FullName] Count
- Column Heading of Room Group By
- Value of FullName First

Then set the Column Headings property to all possible Room values
Column Headings: "A-1","A-2","A-3","A-4","A-5",...
"B-1","B-2","B-3","B-4","B-5",...

Running this report should display a single record with the current
occupant under each Room/column. You can then build a report and
simply bind the columns to text boxes in the detail section. If you
use a full page detail section, you have room to move the text boxes
where ever they need to be.
Okay, the field names are [Full Name] and [Rooms] (I believe in the
KISS method)
There is a query that links the 2 together.

[Rooms] lists all 22 beds distinctively, so even ward rooms show the
actual room/bed number

I don't know how to have multiple fields in the detail section. All
of our current reports are lists. (Sorry if I'm a dimwit)
oP
Matthew

:

Do you have field names and data types you could share? I would
expect you could create a Crosstab query with the beds as column
headings and patients as the values. You could then place these
anywhere you want in the detail section of a report.

--
Duane Hookom
Microsoft Access MVP


:

I need help with an issure for a ward in a medium stay hospital
setting. I have 21 available beds, and patients change rooms daily.

I have a database that stores the names, room numbers, etc. All of
our on-ward reports are generated from here.

I have been given a request to be able to print a ward map,
showing the name in the correct place on the map (of the ward),
and of course updating and reprinting several times daily.

How can I make a report that shows this type of data?

I am a "power user," not a programmer.
MS Access 2003, but I have 2007 at home and could use that if
necessary.

Thanks in advance for your help.
Matthew
Click to expand...
Click to expand...
Click to expand...
 
I think your solution is an excellent one. All the OP needs do is, as you
said, set up the design of the report section to match his ward/bed layout,
and place a text box for each bed (which seems to me to be the information
in the Room field - I assume that for your devised sample data A-1 would
denote bed 1 in room A), bound to the crosstab query, in the appropriate
position. Neat solution to the original problem. And since the OP has only
21 beds, the limit on the number of fields in the crosstab won't be an issue
;-)

Rob

Duane said:
Thanks Rob. Your solution is a good one. I was vaguely aware there
must be at least one "Group By" as a Row Heading. I hoped a simple
Count would suffice (apparently not).

Do you think the remainder of my suggested solution will work as long
as the number of columns/rooms doesn't get too large (approach 200)?

Hi Duane,

PMFJI,

I've been following this thread, and I just tried your suggested
solution, and find that it doesn't work. Setting up a crosstab
query as you propose gives the following message when you try to run
it: "You must enter Group By in the Total row for at least one of
the Row Heading options you enter in the crosstab row."

Fortunately, there's a very simple fix. Instead of [FullName] Count
as the Row heading, enter "FullName" (with the quotes) as the Field
for the Row Heading, with Group By in the Total row; this will give
a single record query as required for use in the report layout.

The SQL for my test query is:
TRANSFORM First(tblWardMap.FullName) AS FirstOfFullName
SELECT "FullName" AS Expr1
FROM tblWardMap
GROUP BY "FullName"
PIVOT tblWardMap.Room In
("A-1","A-2","A-3","A-4","A-5","A-6","B-1","B-2","B-3","B-4","B-5");

HTH,

Rob


Duane said:
Assuming you have a query that returns the current occupant of each
room like:

[FullName] [Room]
Joe A-1
Bill A-2
Tom A-4
Mary B-2
Sally B-6

You could create a crosstab query with a
- Row Heading of [FullName] Count
- Column Heading of Room Group By
- Value of FullName First

Then set the Column Headings property to all possible Room values
Column Headings: "A-1","A-2","A-3","A-4","A-5",...
"B-1","B-2","B-3","B-4","B-5",...

Running this report should display a single record with the current
occupant under each Room/column. You can then build a report and
simply bind the columns to text boxes in the detail section. If you
use a full page detail section, you have room to move the text boxes
where ever they need to be.

Okay, the field names are [Full Name] and [Rooms] (I believe in the
KISS method)
There is a query that links the 2 together.

[Rooms] lists all 22 beds distinctively, so even ward rooms show
the actual room/bed number

I don't know how to have multiple fields in the detail section. All
of our current reports are lists. (Sorry if I'm a dimwit)
oP
Matthew

:

Do you have field names and data types you could share? I would
expect you could create a Crosstab query with the beds as column
headings and patients as the values. You could then place these
anywhere you want in the detail section of a report.

--
Duane Hookom
Microsoft Access MVP


:

I need help with an issure for a ward in a medium stay hospital
setting. I have 21 available beds, and patients change rooms
daily.

I have a database that stores the names, room numbers, etc. All
of our on-ward reports are generated from here.

I have been given a request to be able to print a ward map,
showing the name in the correct place on the map (of the ward),
and of course updating and reprinting several times daily.

How can I make a report that shows this type of data?

I am a "power user," not a programmer.
MS Access 2003, but I have 2007 at home and could use that if
necessary.

Thanks in advance for your help.
Matthew
Click to expand...
Click to expand...
Click to expand...
 
EXCELLENT. This is exactly what I needed. Thank you both!
Matthew

Duane Hookom said:
Thanks Rob. Your solution is a good one. I was vaguely aware there must be at
least one "Group By" as a Row Heading. I hoped a simple Count would suffice
(apparently not).

Do you think the remainder of my suggested solution will work as long as the
number of columns/rooms doesn't get too large (approach 200)?

--
Duane Hookom
Microsoft Access MVP


Rob Parker said:
Hi Duane,

PMFJI,

I've been following this thread, and I just tried your suggested solution,
and find that it doesn't work. Setting up a crosstab query as you propose
gives the following message when you try to run it: "You must enter Group By
in the Total row for at least one of the Row Heading options you enter in
the crosstab row."

Fortunately, there's a very simple fix. Instead of [FullName] Count as the
Row heading, enter "FullName" (with the quotes) as the Field for the Row
Heading, with Group By in the Total row; this will give a single record
query as required for use in the report layout.

The SQL for my test query is:
TRANSFORM First(tblWardMap.FullName) AS FirstOfFullName
SELECT "FullName" AS Expr1
FROM tblWardMap
GROUP BY "FullName"
PIVOT tblWardMap.Room In
("A-1","A-2","A-3","A-4","A-5","A-6","B-1","B-2","B-3","B-4","B-5");

HTH,

Rob


Duane said:
Assuming you have a query that returns the current occupant of each
room like:

[FullName] [Room]
Joe A-1
Bill A-2
Tom A-4
Mary B-2
Sally B-6

You could create a crosstab query with a
- Row Heading of [FullName] Count
- Column Heading of Room Group By
- Value of FullName First

Then set the Column Headings property to all possible Room values
Column Headings: "A-1","A-2","A-3","A-4","A-5",...
"B-1","B-2","B-3","B-4","B-5",...

Running this report should display a single record with the current
occupant under each Room/column. You can then build a report and
simply bind the columns to text boxes in the detail section. If you
use a full page detail section, you have room to move the text boxes
where ever they need to be.

Okay, the field names are [Full Name] and [Rooms] (I believe in the
KISS method)
There is a query that links the 2 together.

[Rooms] lists all 22 beds distinctively, so even ward rooms show the
actual room/bed number

I don't know how to have multiple fields in the detail section. All
of our current reports are lists. (Sorry if I'm a dimwit)
oP
Matthew

:

Do you have field names and data types you could share? I would
expect you could create a Crosstab query with the beds as column
headings and patients as the values. You could then place these
anywhere you want in the detail section of a report.

--
Duane Hookom
Microsoft Access MVP


:

I need help with an issure for a ward in a medium stay hospital
setting. I have 21 available beds, and patients change rooms daily.

I have a database that stores the names, room numbers, etc. All of
our on-ward reports are generated from here.

I have been given a request to be able to print a ward map,
showing the name in the correct place on the map (of the ward),
and of course updating and reprinting several times daily.

How can I make a report that shows this type of data?

I am a "power user," not a programmer.
MS Access 2003, but I have 2007 at home and could use that if
necessary.

Thanks in advance for your help.
Matthew
Click to expand...
Click to expand...
Click to expand...
 
EXCELLENT. This is exactly what I needed. Thank you both!
Matthew

Rob Parker said:
I think your solution is an excellent one. All the OP needs do is, as you
said, set up the design of the report section to match his ward/bed layout,
and place a text box for each bed (which seems to me to be the information
in the Room field - I assume that for your devised sample data A-1 would
denote bed 1 in room A), bound to the crosstab query, in the appropriate
position. Neat solution to the original problem. And since the OP has only
21 beds, the limit on the number of fields in the crosstab won't be an issue
;-)

Rob

Duane said:
Thanks Rob. Your solution is a good one. I was vaguely aware there
must be at least one "Group By" as a Row Heading. I hoped a simple
Count would suffice (apparently not).

Do you think the remainder of my suggested solution will work as long
as the number of columns/rooms doesn't get too large (approach 200)?

Hi Duane,

PMFJI,

I've been following this thread, and I just tried your suggested
solution, and find that it doesn't work. Setting up a crosstab
query as you propose gives the following message when you try to run
it: "You must enter Group By in the Total row for at least one of
the Row Heading options you enter in the crosstab row."

Fortunately, there's a very simple fix. Instead of [FullName] Count
as the Row heading, enter "FullName" (with the quotes) as the Field
for the Row Heading, with Group By in the Total row; this will give
a single record query as required for use in the report layout.

The SQL for my test query is:
TRANSFORM First(tblWardMap.FullName) AS FirstOfFullName
SELECT "FullName" AS Expr1
FROM tblWardMap
GROUP BY "FullName"
PIVOT tblWardMap.Room In
("A-1","A-2","A-3","A-4","A-5","A-6","B-1","B-2","B-3","B-4","B-5");

HTH,

Rob


Duane Hookom wrote:
Assuming you have a query that returns the current occupant of each
room like:

[FullName] [Room]
Joe A-1
Bill A-2
Tom A-4
Mary B-2
Sally B-6

You could create a crosstab query with a
- Row Heading of [FullName] Count
- Column Heading of Room Group By
- Value of FullName First

Then set the Column Headings property to all possible Room values
Column Headings: "A-1","A-2","A-3","A-4","A-5",...
"B-1","B-2","B-3","B-4","B-5",...

Running this report should display a single record with the current
occupant under each Room/column. You can then build a report and
simply bind the columns to text boxes in the detail section. If you
use a full page detail section, you have room to move the text boxes
where ever they need to be.

Okay, the field names are [Full Name] and [Rooms] (I believe in the
KISS method)
There is a query that links the 2 together.

[Rooms] lists all 22 beds distinctively, so even ward rooms show
the actual room/bed number

I don't know how to have multiple fields in the detail section. All
of our current reports are lists. (Sorry if I'm a dimwit)
oP
Matthew

:

Do you have field names and data types you could share? I would
expect you could create a Crosstab query with the beds as column
headings and patients as the values. You could then place these
anywhere you want in the detail section of a report.

--
Duane Hookom
Microsoft Access MVP


:

I need help with an issure for a ward in a medium stay hospital
setting. I have 21 available beds, and patients change rooms
daily.

I have a database that stores the names, room numbers, etc. All
of our on-ward reports are generated from here.

I have been given a request to be able to print a ward map,
showing the name in the correct place on the map (of the ward),
and of course updating and reprinting several times daily.

How can I make a report that shows this type of data?

I am a "power user," not a programmer.
MS Access 2003, but I have 2007 at home and could use that if
necessary.

Thanks in advance for your help.
Matthew
Click to expand...
Click to expand...
Click to expand...
 
Back
Top