Multiple Relationships between two tables

[Gman]

I have two tables, roominfo and exhausttypetable.

In the exhauasttypetable I store the name of the exhaust equipmenttype, MAXCFM, MINCFM, and some other info.

In the roominfo table, I have 10 fields named, exhausttype1, exhausttype2, etc.

I have a form that enters info for the roominfo table, and I use a combo box to select the exhausttype, the row source is connected to the exhausttypetable. I have 10 of the same, this will allow me to selct up to ten different exhaust types.

If exhaustype1 in the roominof was set to cabinet, then I need to retreive the MAXCFM and MINCFM for that exhausttype.

If I was to only have one exhausttype, I could use a relationship, but I cannot use this, I set up relationship between, the each exhausttype in roominof with the exhausttype in exhausttypetable, buyt this did not work.

What would be the best method to do this. Can you use relationships

Thanks

 

[pbaldy]

The problem is the repeating fields (search on normalization). The exhaust types should be in another table, related to roominfo by the ID field from that table. In other words, if a room had 3 exhaust types, it would have 3 records in that table. That tables exhaust type field would be related to your exhaust type table.

 

[Gman]

The roominfo table stores all roominfo, so everyroom would only have one record in this table.

But, you could have ten different pieces of equipment.

I also have a query that combines roominfo and some other tables through relationships. In this query is were I need to get the other fields so that i can perform calculations.

I guess that I could use Dlookup to retrieve the info, but have been unsuccesful in doing this.

 

[pbaldy]

Yes, I understand. What I'm saying is that you need another table to hold the exhaust data, rather than having 10 fields in the room info table. You will find most tasks easier to accomplish with that extra table, as it is the normalized structure Access is designed to work with.

 

[Gman]

Thanks,

i'll try that approach

 

[Gman]

How would you set up the table you informed me of.

I have a form that is linked to a query, and that query is related the roominfo table.

You indicated that I should use another table to enter all ten exhaust types for each record in the room table. I understand how I would link this new table to the exhausttypetable. But I am unable to figure, when I have the form open, I currently have ten combo boxes that were linked to the roominfo table, but how would you enter ten records in the new table.

Not sure how to link this table in and be able to select the different types of exhaust in the form that is linked to roominfo.

Any direction would help.

 

[pbaldy]

One common way would be a form and subform, where the form is bound to your room info table, and the subform is bound to this new table. Master/child links between the two would ensure that the records displayed in the subform related to the room on the form. As I said, if a particular room had 3 exhaust types, it would have 3 records displayed in the subform.

 

[Gman]

I have been reading up on master/child links. Understand them slightly, but not entirely.

I have created the new New ExhaustTable. I also removed the ten exhausts types with the roominfo table. I realize or think that the new table will only have one feild named Exhausttype, then a second field will somehow been linked to the roominfo table. So if I enter a new record in the form, this record should get an autonumber, then this should be copied by the link

I put a autonumber as a feild in this table, I think that this should be the field that is linked to the autonumber from the roominof table.

I created a subform for this new table, them provided a subform in my main form.

But am still not clear on this whole approach.

I realize that this new table, should have two fields, one will be the exhausttype and the other should be a reference to a primary key in the roominfo, so if i open up this table, it could have up to ten records for the same roomname, but the exhausttype would be what i select in the form.

I just need to keep reading, is there any examples you could provide.

Thanks for your help so far

 

[pbaldy]

You can have an autonumber key field in this table, but it will NOT be the field linked to the room info table. An autonumber can't have duplicates, and the field linked to the room info table will have to. Can you post a sample of your db?

 

[Gman]

I attached the file, it was to large to upload, so I cut it down to the basics stuff that i need help with,

Bare with me, as I could not provide some of modules.

Take a look, any help would be apprecated

I am not sure of how you set up multiple combo boxes in the subform, which is linked to the RoomExhaustInfo.

I thought that when you are in a form, that you can only see one record, but still learning.

 

[pbaldy]

Look at the subform now, and the room named "testtest".

 

[Gman]

Thats awesome, thanks for your help,
I understand how it works now I erased the roomname in the subform, so that I cannot ever chnage it, still maybe little uncertain as exactly how, but I can decifer the code behind it.

So every time I select an exhaust exhaust type, it automatically adds the roomname from the record I am in, and then adds another row.
I should be able to expand on this,

Again, thanks for you rhelp.

 

[pbaldy]

The two main changes I made, if memory serves, is to set the master/child links and change the subform from single view to continuous, so you'll see multiple records. Oh, and I changed the exhaust type textbox in the subform to a combo.