How to do this

[slide]

I have 3 tables.

Table 'Items' holds an entry to every item and then a table for each type of item that holds more specific data. I want to create a form that lets you browse ALL the items which means that it needs to be able to get and display the data from the 2 other tables which have different data, can anyone help me figure out how to do this? The two ways I would like to do it are as follows:

1. A subform on the main form and depending on what row is being shown, change the subform to the appropriate table type.

2. Show/Hide another form becides the 'Items Form' when a specific row is being shown. So If i goto item "01002AB" it will popup the form to display its data.

 

[georgedwilkinson]

Or use a union query for your subform, selecting the appropriate table in the query.

 

[slide]

How will this work? If I set the subform query to get a 'Pump' item it will automatically show the Pump table fields?

 

[namliam]

Or if all data is related to items... make a query to join all 3 tables into one query and use that as your rowsource in the form...

 

[slide]

But since the tables have different data, they can't be union'ed! (right??) The only way i figured out how to union tables that are different is by using, "null as columnname" for each column that wasnt in the other table, but then with that access wont allow you to edit it

 

[georgedwilkinson]

But since the tables have different data, they can't be union'ed! (right??)

We don't know, you didn't give us that information.

My first fear when I saw your original post was that we would get down to this: are you sure your design is normalized/object oriented? I didn't bother asking it the first time I responded, assuming you could be implementing a polymorphic class/table set, which has to be done manually in Access. That could still be the case.

Can you post information about all 3 tables?

 

[slide]

needs to be able to get and display the data from the 2 other tables which have different data

This is an example of my db:

Items
- ID, Description

Motors
- ID, Speed, Torque

Fans
- ID, Diameter

Where ID in Motors and in Fans correspond to a 1-1 relationship with the ID field in Items.

 

[georgedwilkinson]

So, is there only either a Motor or a Fan related to an Item?

If so, this looks like a perfect example of polymorphism. In that case, I would use a flat query with outer joins, put an option group to indicate whether the item is a Motor or a Fan, and put a VBA event on the option group to display the fields for either the Motor or for the Fan (could also use a combo box or other designator), hiding the fields that don't apply. You'll find that, as long as there is only exactly one Motor/Fan per Item, the form will enter data into both of the pertinent tables, as I recall from my last experimentation with People/Organizations.

There is a chance you will programmatically have to change the query, however.

I'm really interested in the outcome of this issue. If/when you solve it, post back the results.

 

[namliam]

Select ID, description, speed, torque, Diameter
from Items
left join motors on items.id = motors.id
left join Diameter on items.id = fans.id

Is that (more or less) what you are looking for?