room chart problems

[_Bigred]

Hello All,

I have a report that has unbound boxes for each room, and each unbound
box is named for a room i.e N047A,N047B,N048A,N048B etc..

Is there a way that I can use the label name such as "N047A" as the
criteria for a dlookup or other function .. that would populate the
unbound box with the persons name [tblFullName] and date they were
placed in that room [AssignDate].

This particular report will have 34 total rooms, and I need it to
autopopulate each persons information in the appropriate box on the
report.

TIA,
_Bigred

 

[Philip Herlihy]

Hello All,

I have a report that has unbound boxes for each room, and each unbound
box is named for a room i.e N047A,N047B,N048A,N048B etc..

Is there a way that I can use the label name such as "N047A" as the
criteria for a dlookup or other function .. that would populate the
unbound box with the persons name [tblFullName] and date they were
placed in that room [AssignDate].

This particular report will have 34 total rooms, and I need it to
autopopulate each persons information in the appropriate box on the
report.

TIA,
_Bigred

I guess you could do this, but it's weird. You can access the label
names via the form's collection of controls (look up "controls
collection" in Help), but this collection will contain other controls, I
presume, as well.

What you're doing is storing data in the form's control names. You'd
want to use a table for that! Table design that immediately suggests
itself to me is a table for Rooms, a table for Persons, and a
"associative" table, which would contain the ID of a Room, the ID of a
person, and fields for start-date and end-date, if needed. Then you'd
write a query to join the tables, and base your form or report on that.

Phil, London

 

[Duane Hookom]

I expect you want to have the boxes for each room appear at a particular
place in your report with some field values displaying in the room box. If
so, I would use a solution similar to the Calendar Reports Sample found at
http://www.access.hookom.net/Samples.htm.

The sample has reports that display the text box at a location determined by
date/time and/or doctor. You could use similar code to place the text box
based on X and Y coordinates from a room table.

If that gets too complicated, I would probably use code to loop through a
recordset of the day's room schedule and set the caption of labels or value
of text boxes.

 

[_Bigred]

Hello All,

I have a report that has unbound boxes for each room, and each unbound
box is named for a room  i.e   N047A,N047B,N048A,N048B etc..

Is there a way that I can use the label name such as "N047A" as the
criteria for a dlookup or other function .. that would populate the
unbound box with the persons name [tblFullName] and date they were
placed in that room [AssignDate].

This particular report will have 34 total rooms, and I need it to
autopopulate each persons information in the appropriate box on the
report.

TIA,
_Bigred

If I setup a table for the rooms and then a table for the people, and
then setup a join query how do i then put the occupants of each room
in their "very specifically layout" rooms on my report (chart)? There
is only 1 occupant to each room # i.e. N047A, N047B etc...

I just figured since I already had the chart made with unbound boxes
and named them for each room, I could some how easily do this.

 

[Duane Hookom]

Do you have a table structure you could share? How about some sample data?
Does [AssignDate] actually contain a room number?

Do you know how to use code?

--
Duane Hookom
Microsoft Access MVP

Hello All,

I have a report that has unbound boxes for each room, and each unbound
box is named for a room i.e N047A,N047B,N048A,N048B etc..

Is there a way that I can use the label name such as "N047A" as the
criteria for a dlookup or other function .. that would populate the
unbound box with the persons name [tblFullName] and date they were
placed in that room [AssignDate].

This particular report will have 34 total rooms, and I need it to
autopopulate each persons information in the appropriate box on the
report.

TIA,
_Bigred

If I setup a table for the rooms and then a table for the people, and
then setup a join query how do i then put the occupants of each room
in their "very specifically layout" rooms on my report (chart)? There
is only 1 occupant to each room # i.e. N047A, N047B etc...

I just figured since I already had the chart made with unbound boxes
and named them for each room, I could some how easily do this.

 

[Philip Herlihy]

Hello All,

I have a report that has unbound boxes for each room, and each unbound
box is named for a room i.e N047A,N047B,N048A,N048B etc..

Is there a way that I can use the label name such as "N047A" as the
criteria for a dlookup or other function .. that would populate the
unbound box with the persons name [tblFullName] and date they were
placed in that room [AssignDate].

This particular report will have 34 total rooms, and I need it to
autopopulate each persons information in the appropriate box on the
report.

TIA,
_Bigred

If I setup a table for the rooms and then a table for the people, and
then setup a join query how do i then put the occupants of each room
in their "very specifically layout" rooms on my report (chart)? There
is only 1 occupant to each room # i.e. N047A, N047B etc...

I just figured since I already had the chart made with unbound boxes
and named them for each room, I could some how easily do this.

Am I right in now thinking that the boxes on the report are, in effect,
a map of the rooms? That makes a difference - normally you'd want a
solution to be as general as possible so that it can adapt to changes,
but this might be an exception.

If this is right, then for this very specific (and inflexible)
application you could do something like this:

Blow normalisation for once (shudder). Create a table with two fields
for every room; one for the name of the occupant, and one for the name
they were assigned to that room.

On your form, lay out your "rooms" but use two text boxes; one for the
name, and one for the date. You may want labels (you get one free with
every text box unless you delete it) with captions "Occupant" and
"Assigned" (or similar) for these boxes, plus a free-standing label
giving the name of the room (you could put a "Rectangle" around each
group purely for cosmetics).

Make the text-boxes bound controls, by setting the ControlSource
property to the name of the corresponding field in your table.

Yes, I still think this is weird. The whole point of a database is that
it manages changes in your data, and this is very static. Since what
you want appears to be a graphic map, why not use a graphic tool to
create one? Any graphic tool which can draw rectangles, lines and label
them with text will do, including PowerPoint, Fireworks, DrawPlus (free
edition available at freeserifsoftware.com) or even Paint. The effort
of drawing this in such a package will be less than the effort of
mangling Access to do this, and the effort of updating changes will be
about the same.

In fact, if you drew up a form in Access, and used only rectangles and
labels (no tables!) you could achieve everything you seem to want
perfectly well!

Phil

PS: Good grief...

 

[Duane Hookom]

Phil's response got me thinking about using a crosstab to get the data to
appear un-normalized. You could use the rooms as the Column Headings. You
would need to enter every room into the Column Headings property of the
crosstab.

--
Duane Hookom
Microsoft Access MVP

Hello All,

I have a report that has unbound boxes for each room, and each unbound
box is named for a room i.e N047A,N047B,N048A,N048B etc..

Is there a way that I can use the label name such as "N047A" as the
criteria for a dlookup or other function .. that would populate the
unbound box with the persons name [tblFullName] and date they were
placed in that room [AssignDate].

This particular report will have 34 total rooms, and I need it to
autopopulate each persons information in the appropriate box on the
report.

TIA,
_Bigred

If I setup a table for the rooms and then a table for the people, and
then setup a join query how do i then put the occupants of each room
in their "very specifically layout" rooms on my report (chart)? There
is only 1 occupant to each room # i.e. N047A, N047B etc...

I just figured since I already had the chart made with unbound boxes
and named them for each room, I could some how easily do this.

Am I right in now thinking that the boxes on the report are, in effect,
a map of the rooms? That makes a difference - normally you'd want a
solution to be as general as possible so that it can adapt to changes,
but this might be an exception.

If this is right, then for this very specific (and inflexible)
application you could do something like this:

Blow normalisation for once (shudder). Create a table with two fields
for every room; one for the name of the occupant, and one for the name
they were assigned to that room.

On your form, lay out your "rooms" but use two text boxes; one for the
name, and one for the date. You may want labels (you get one free with
every text box unless you delete it) with captions "Occupant" and
"Assigned" (or similar) for these boxes, plus a free-standing label
giving the name of the room (you could put a "Rectangle" around each
group purely for cosmetics).

Make the text-boxes bound controls, by setting the ControlSource
property to the name of the corresponding field in your table.

Yes, I still think this is weird. The whole point of a database is that
it manages changes in your data, and this is very static. Since what
you want appears to be a graphic map, why not use a graphic tool to
create one? Any graphic tool which can draw rectangles, lines and label
them with text will do, including PowerPoint, Fireworks, DrawPlus (free
edition available at freeserifsoftware.com) or even Paint. The effort
of drawing this in such a package will be less than the effort of
mangling Access to do this, and the effort of updating changes will be
about the same.

In fact, if you drew up a form in Access, and used only rectangles and
labels (no tables!) you could achieve everything you seem to want
perfectly well!

Phil

PS: Good grief...

 

[Philip Herlihy]

Phil's response got me thinking about using a crosstab to get the data to
appear un-normalized. You could use the rooms as the Column Headings. You
would need to enter every room into the Column Headings property of the
crosstab.

Duane Hookom Microsoft Access MVP

Hello All,

I have a report that has unbound boxes for each room, and each unbound
box is named for a room i.e N047A,N047B,N048A,N048B etc..

Is there a way that I can use the label name such as "N047A" as the
criteria for a dlookup or other function .. that would populate the
unbound box with the persons name [tblFullName] and date they were
placed in that room [AssignDate].

This particular report will have 34 total rooms, and I need it to
autopopulate each persons information in the appropriate box on the
report.

TIA,
_Bigred

If I setup a table for the rooms and then a table for the people, and
then setup a join query how do i then put the occupants of each room
in their "very specifically layout" rooms on my report (chart)? There
is only 1 occupant to each room # i.e. N047A, N047B etc...

I just figured since I already had the chart made with unbound boxes
and named them for each room, I could some how easily do this.

Am I right in now thinking that the boxes on the report are, in effect,
a map of the rooms? That makes a difference - normally you'd want a
solution to be as general as possible so that it can adapt to changes,
but this might be an exception.

If this is right, then for this very specific (and inflexible)
application you could do something like this:

Blow normalisation for once (shudder). Create a table with two fields
for every room; one for the name of the occupant, and one for the name
they were assigned to that room.

On your form, lay out your "rooms" but use two text boxes; one for the
name, and one for the date. You may want labels (you get one free with
every text box unless you delete it) with captions "Occupant" and
"Assigned" (or similar) for these boxes, plus a free-standing label
giving the name of the room (you could put a "Rectangle" around each
group purely for cosmetics).

Make the text-boxes bound controls, by setting the ControlSource
property to the name of the corresponding field in your table.

Yes, I still think this is weird. The whole point of a database is that
it manages changes in your data, and this is very static. Since what
you want appears to be a graphic map, why not use a graphic tool to
create one? Any graphic tool which can draw rectangles, lines and label
them with text will do, including PowerPoint, Fireworks, DrawPlus (free
edition available at freeserifsoftware.com) or even Paint. The effort
of drawing this in such a package will be less than the effort of
mangling Access to do this, and the effort of updating changes will be
about the same.

In fact, if you drew up a form in Access, and used only rectangles and
labels (no tables!) you could achieve everything you seem to want
perfectly well!

Phil

PS: Good grief...

Now that really is thinking out of the box (ouch!).

Phil

 

[_Bigred]

Do you have a table structure you could share? How about some sample data?
Does  [AssignDate] actually contain a room number?

Do you know how to use code?

--
Duane Hookom
Microsoft Access MVP

Hello All,
I have a report that has unbound boxes for each room, and each unbound
box is named for a room  i.e   N047A,N047B,N048A,N048B etc..
Is there a way that I can use the label name such as "N047A" as the
criteria for a dlookup or other function .. that would populate the
unbound box with the persons name [tblFullName] and date they were
placed in that room [AssignDate].
This particular report will have 34 total rooms, and I need it to
autopopulate each persons information in the appropriate box on the
report.
TIA,
_Bigred

If I setup a table for the rooms and then a table for the people, and
then setup a join query how do i then put the occupants of each room
in their "very specifically layout" rooms on my report (chart)?  There
is only 1 occupant to each room #  i.e.  N047A, N047B  etc...

I just figured since I already had the chart made with unbound boxes
and named them for each room, I could some how easily do this.- Hide quoted text -

- Show quoted text -

Hello Duane,

First off thanks for the time & attention.

I have currently have tblCenterInfo
LastName FirstName Member# Room# (Room# are
selected from a lookup field)
Smith John 012345 N047A
Doe Suzy 882357 N047B
Johnson Charlie 456780 S218A

Then I have a tblRooms that is used for the Room# lookup, and also
drives some other reports that are seperate from the Room Charts.

In all I have 164 beds, they are spread over 44 single rooms and the
remainder of rooms are double rooms.

The room numbers i.e N047A etc... and the physical location will
never change, the occupants will change frequently due to various
reasons.

I tblCenterInfo also contains a bunch of other fields that are used
for job assignments rosters, evaluations listing, sanitations
schedules etc...

The Room Charts would be arbitrarily divided up across a North, North
Center, South Center, and South Sides ... (so 4 seperate room charts
in total).

hope this helps.

TIA,
_Bigred

 

[_Bigred]

Hello All,
I have a report that has unbound boxes for each room, and each unbound
box is named for a room  i.e   N047A,N047B,N048A,N048B etc..
Is there a way that I can use the label name such as "N047A" as the
criteria for a dlookup or other function .. that would populate the
unbound box with the persons name [tblFullName] and date they were
placed in that room [AssignDate].
This particular report will have 34 total rooms, and I need it to
autopopulate each persons information in the appropriate box on the
report.
TIA,
_Bigred

If I setup a table for the rooms and then a table for the people, and
then setup a join query how do i then put the occupants of each room
in their "very specifically layout" rooms on my report (chart)?  There
is only 1 occupant to each room #  i.e.  N047A, N047B  etc...

I just figured since I already had the chart made with unbound boxes
and named them for each room, I could some how easily do this.

Am I right in now thinking that the boxes on the report are, in effect,
a map of the rooms?  That makes a difference - normally you'd want a
solution to be as general as possible so that it can adapt to changes,
but this might be an exception.

If this is right, then for this very specific (and inflexible)
application you could do something like this:

Blow normalisation for once (shudder).  Create a table with two fields
for every room; one for the name of the occupant, and one for the name
they were assigned to that room.

On your form, lay out your "rooms" but use two text boxes; one for the
name, and one for the date.  You may want labels (you get one free with
every text box unless you delete it) with captions "Occupant" and
"Assigned" (or similar) for these boxes, plus a free-standing label
giving the name of the room (you could put a "Rectangle" around each
group purely for cosmetics).

Make the text-boxes bound controls, by setting the ControlSource
property to the name of the corresponding field in your table.

Yes, I still think this is weird.  The whole point of a database is that
it manages changes in your data, and this is very static.  Since what
you want appears to be a graphic map, why not use a graphic tool to
create one?  Any graphic tool which can draw rectangles, lines and label
them with text will do, including PowerPoint, Fireworks, DrawPlus (free
edition available at freeserifsoftware.com) or even Paint.  The effort
of drawing this in such a package will be less than the effort of
mangling Access to do this, and the effort of updating changes will be
about the same.

In fact, if you drew up a form in Access, and used only rectangles and
labels (no tables!) you could achieve everything you seem to want
perfectly well!

Phil

PS: Good grief...- Hide quoted text -

- Show quoted text -

Yes the boxes on the report are the layout of 164 total beds spread
across double occupancy rooms and 44 single bed rooms. The physical
layout will never change, I the occupants will change on a frequent
basis - on average 12+ room changes every 7-14 days. The reason I want
to create a report in access is so when the tblCenterInfo data is
modified the Room Charts are updated right along with Room Directories
and other reports that are pulled from tblCenterInfo - since I'm not
the only user of the database, if I can keep in ALL in access would be
extremely beneficial .

Thanks for your time,
_Bigred

 

[Philip Herlihy]

Hello All,
I have a report that has unbound boxes for each room, and each unbound
box is named for a room i.e N047A,N047B,N048A,N048B etc..
Is there a way that I can use the label name such as "N047A" as the
criteria for a dlookup or other function .. that would populate the
unbound box with the persons name [tblFullName] and date they were
placed in that room [AssignDate].
This particular report will have 34 total rooms, and I need it to
autopopulate each persons information in the appropriate box on the
report.
TIA,
_Bigred
If I setup a table for the rooms and then a table for the people, and
then setup a join query how do i then put the occupants of each room
in their "very specifically layout" rooms on my report (chart)? There
is only 1 occupant to each room # i.e. N047A, N047B etc...
I just figured since I already had the chart made with unbound boxes
and named them for each room, I could some how easily do this.

Am I right in now thinking that the boxes on the report are, in effect,
a map of the rooms? That makes a difference - normally you'd want a
solution to be as general as possible so that it can adapt to changes,
but this might be an exception.

If this is right, then for this very specific (and inflexible)
application you could do something like this:

Blow normalisation for once (shudder). Create a table with two fields
for every room; one for the name of the occupant, and one for the name
they were assigned to that room.

On your form, lay out your "rooms" but use two text boxes; one for the
name, and one for the date. You may want labels (you get one free with
every text box unless you delete it) with captions "Occupant" and
"Assigned" (or similar) for these boxes, plus a free-standing label
giving the name of the room (you could put a "Rectangle" around each
group purely for cosmetics).

Make the text-boxes bound controls, by setting the ControlSource
property to the name of the corresponding field in your table.

Yes, I still think this is weird. The whole point of a database is that
it manages changes in your data, and this is very static. Since what
you want appears to be a graphic map, why not use a graphic tool to
create one? Any graphic tool which can draw rectangles, lines and label
them with text will do, including PowerPoint, Fireworks, DrawPlus (free
edition available at freeserifsoftware.com) or even Paint. The effort
of drawing this in such a package will be less than the effort of
mangling Access to do this, and the effort of updating changes will be
about the same.

In fact, if you drew up a form in Access, and used only rectangles and
labels (no tables!) you could achieve everything you seem to want
perfectly well!

Phil

PS: Good grcief...- Hide quoted text -

- Show quoted text -

Yes the boxes on the report are the layout of 164 total beds spread
across double occupancy rooms and 44 single bed rooms. The physical
layout will never change, I the occupants will change on a frequent
basis - on average 12+ room changes every 7-14 days. The reason I want
to create a report in access is so when the tblCenterInfo data is
modified the Room Charts are updated right along with Room Directories
and other reports that are pulled from tblCenterInfo - since I'm not
the only user of the database, if I can keep in ALL in access would be
extremely beneficial .

Thanks for your time,
_Bigred

I'd guess there's a neat solution to this using Visio, the Microsoft
drawing package which is database-aware.

Failing that, my strongest advice would be not to let the desire for a
"map" to distort the way you store your data, which would be essentially
as I described earlier. The problem with your proposed report is that
you want to lay out the boxes as though they were all fields in one
record, whereas the "natural" way of storing these rooms, and room
assignments, is as discrete records. A multi-record data structure will
make all your other processing needs easier.

So, I think what's left is to keep the boxes unbound, and use code in
the Open() event handler to pluck out the data field by field. Big
overhead (I'd guess) in having 164 separate calls, but that's a better
price to pay than the consequences of mangling your table structure for
the sake of a single report. You could use:
Me!textbox_N047A.Value = DLookup([LastName], MyQuery, "[Room] = N047A")
Of course you can generalise this by putting the room number in a
variable. MyQuery must provide the necessary join across your tables
and also pick the latest assignment date for a given room. (You may
need to store a null string or "empty" against the date the latest
person checks out, unless you're storing date of arrival and also
departure). I guess MyQuery would need to group by Room and select the
Max([Date]), off the top of my head (see caveat).

A more efficient alternative would be to save the MyQuery output as a
recordset and move through that structure one record at a time while you
perform your assignments.

Caveat: Some of the people responding here are real experts. I'm not;
I've been exposed to Access for years but have only fairly recently done
anything serious with it, and I'm loving it. Apart from a desire to
reciprocate some of the help I've received in this and other newsgroups,
struggling with other people's problems is a great way to continue
learning. Some of my posts are subsequently corrected by the real experts!


Phil

 

[Duane Hookom]

I would probably create an unbound report with text boxes or labels
positioned exactly where you want in a big detail section. Then in the On
Format event of the detail section, use code to open a recordset of the
current days room assignments. If your text boxes are named similar to your
room names, you can simply loop through the recordset once to set the value
or caption property of the text boxes or labels.

--
Duane Hookom
Microsoft Access MVP

Hello All,

I have a report that has unbound boxes for each room, and each unbound
box is named for a room i.e N047A,N047B,N048A,N048B etc..

Is there a way that I can use the label name such as "N047A" as the
criteria for a dlookup or other function .. that would populate the
unbound box with the persons name [tblFullName] and date they were
placed in that room [AssignDate].

This particular report will have 34 total rooms, and I need it to
autopopulate each persons information in the appropriate box on the
report.

If I setup a table for the rooms and then a table for the people, and
then setup a join query how do i then put the occupants of each room
in their "very specifically layout" rooms on my report (chart)? There
is only 1 occupant to each room # i.e. N047A, N047B etc...

I just figured since I already had the chart made with unbound boxes
and named them for each room, I could some how easily do this.

Am I right in now thinking that the boxes on the report are, in effect,
a map of the rooms? That makes a difference - normally you'd want a
solution to be as general as possible so that it can adapt to changes,
but this might be an exception.

If this is right, then for this very specific (and inflexible)
application you could do something like this:

Blow normalisation for once (shudder). Create a table with two fields
for every room; one for the name of the occupant, and one for the name
they were assigned to that room.

On your form, lay out your "rooms" but use two text boxes; one for the
name, and one for the date. You may want labels (you get one free with
every text box unless you delete it) with captions "Occupant" and
"Assigned" (or similar) for these boxes, plus a free-standing label
giving the name of the room (you could put a "Rectangle" around each
group purely for cosmetics).

Make the text-boxes bound controls, by setting the ControlSource
property to the name of the corresponding field in your table.

Yes, I still think this is weird. The whole point of a database is that
it manages changes in your data, and this is very static. Since what
you want appears to be a graphic map, why not use a graphic tool to
create one? Any graphic tool which can draw rectangles, lines and label
them with text will do, including PowerPoint, Fireworks, DrawPlus (free
edition available at freeserifsoftware.com) or even Paint. The effort
of drawing this in such a package will be less than the effort of
mangling Access to do this, and the effort of updating changes will be
about the same.

In fact, if you drew up a form in Access, and used only rectangles and
labels (no tables!) you could achieve everything you seem to want
perfectly well!

Phil

PS: Good grief...- Hide quoted text -

- Show quoted text -

Yes the boxes on the report are the layout of 164 total beds spread
across double occupancy rooms and 44 single bed rooms. The physical
layout will never change, I the occupants will change on a frequent
basis - on average 12+ room changes every 7-14 days. The reason I want
to create a report in access is so when the tblCenterInfo data is
modified the Room Charts are updated right along with Room Directories
and other reports that are pulled from tblCenterInfo - since I'm not
the only user of the database, if I can keep in ALL in access would be
extremely beneficial .

Thanks for your time,
_Bigred