Sticky Table Fields

[M_Herb]

Hello smart people,



I am attempting to create a medical equipment list for a hospital. My end goal is to have a report that lists each room in the hospital with the equipment in each room.

I have created a database with three tables. The room table has room names and room numbers. The equipment table has equipment name and equipment number. I then created a third table called “equipment by room” and related the other two tables to this table. The problem I am having is with the third table. I can’t get the equipment number and equipment name to “stick” together. When I select equipment number “101” I want the equipment name to say “bed”. Currently, when I select “101” the equipment name field allows me to select any equipment name from the list. I want the equipment name field to automatically say “bed” if the equipment number is “101”.

How do I do that?

Additionally, I am using queries for each room to show the equipment in that room.

I then plan on creating a report to list all the queries.

Is this the best way to achieve my goal?

Thank you very much for any help you can provide.

Matt

[jzwp11]

One of the rules of relational databases is to not repeat data in a table if it is already stored in another table. So storing the description in the Equipment by Room table is not a good idea. You would only store the primary key value as a foreign key in the equipment by room table. I assume that the 101 you referred to is the primary key value for the piece of equipment.

With that said, the best approach would be to use a main form/subform design with the main form being based on the room table and the subform being based on the equipment by room table. In the subform, you would use a combo box to populate the equipment. In that combo box you would bring in both the number (101) and the description. You can show the description in another (unbound) control in the subform by setting its control source to the following:

=comboboxname.column(x) where x corresponds to the column corresponding to the description. Access starts counting the columns at zero.

For example, if the row source for the combo box looked like this:

SELECT equipmentnumber, equipmentdescription

...your expression would be this

=comboboxname.column(1)

Additionally, I am using queries for each room to show the equipment in that room.

I then plan on creating a report to list all the queries.

You would only need 1 query. You can use a parameter to specify whichever room you want. The same would be true for the report.

[M_Herb]

jzwp11 (smart person)

thanks for that, it helped.

Two more questions, then i think i will have it.

1. In my main form, when i change the Room_Number field the correct corresponding equipment appears in the sub-form, but the Room_Name in the main form does not change. How do i get the Room_Name to change as i change the Room_Number?

2. Latly, can you briefly explain how to create a report that captures the new correctly displayed data in the form? I can't seem to create a report that lists the room and the equipment in that room. I am new to Access, so please dumb it down for me.

Thanks again.

[jzwp11]

1. In my main form, when i change the Room_Number field the correct corresponding equipment appears in the sub-form, but the Room_Name in the main form does not change. How do i get the Room_Name to change as i change the Room_Number?

You should not be changing the room number but rather going to the next record to see the data for the next room (use the record navigation buttons). In so changing the room number you are actually changing the association of room number and room name which will mess up your data. Once you have all of the room records entered, you may want to lock the controls in the main form so they cannot be changed. You would still be able to enter the equipment in the subform

2. Latly, can you briefly explain how to create a report that captures the new correctly displayed data in the form? I can't seem to create a report that lists the room and the equipment in that room. I am new to Access, so please dumb it down for me.

First, have you established the relationships between the 3 tables in the Relationship Window? If not, you will want to do that. Once you do that you will need to create a query that includes the 3 tables (the relationships you established in the Relationship Window will carry over to the query design grid). Select the fields you want to see in your report and then save the query.

Now create a new report using that query. You will need to set up a grouping level by the room number. The detail section should show the equipment for the room shown in the group header.

[M_Herb]

that did it!

thank you again.

until next time....

[jzwp11]

You're welcome.