Re: room chart problems
- From: Philip Herlihy <bounceback@xxxxxxx>
- Date: Fri, 20 Feb 2009 12:52:06 +0000
_Bigred wrote:
On Feb 19, 8:07 am, Philip Herlihy <bounceb...@xxxxxxx> wrote:_Bigred wrote:On Feb 18, 9:37 am, _Bigred <nelli...@xxxxxxxxxx> wrote:Am I right in now thinking that the boxes on the report are, in effect,Hello All,If I setup a table for the rooms and then a table for the people, and
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
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.
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
.
- References:
- room chart problems
- From: _Bigred
- Re: room chart problems
- From: _Bigred
- Re: room chart problems
- From: Philip Herlihy
- Re: room chart problems
- From: _Bigred
- room chart problems
- Prev by Date: Re: Access 2007 Strange Report Design Behavior
- Next by Date: Re: Print preview doesn't match what actually prints
- Previous by thread: Re: room chart problems
- Next by thread: Re: room chart problems
- Index(es):
Relevant Pages
|
Loading
