One to Many Listing in Forms

[zunebuggy]

I have 3 tables like in the examples shown below, in my MS Access database.

fruit_table
ID |fruit
---------------------
1 |Apple
2 |Orange
3 |Peach
4 |Banana
5 |Pear
6 |Grapes
7 |Lemon
8 |Watermellon


etc...

matching_table
matchID |statecode
----------------------
1 |5
1 |16
1 |21
2 |7
2 |27
3 |18
3 |3
3 |11
3 |5
3 |16
etc...

state_table
statecd |state
-----------------------
5 |MI
16 |WI
21 |OH
7 |FL
27 |CA
18 |IA
3 |MO
11 |IN
etc...

In the form view, I would like the user to scroll through every item in the fruit_table and have every matching item from the state_table listed in a large text field. Matching is done via the matching_table using the ID and statecode field which are both invisible to the user. Below is an example of what I want the user to see.

----------- ----------------------------
|Apple | |MI |
----------- |OH |
|WI |
| |
| |
----------------------------
----------- ----------------------------
|Orange | |CA |
----------- |FL |
| |
| |
| |
----------------------------
----------- ----------------------------
|Peach | |IA |
----------- |IN |
|MO |
|MI |
|WI |
----------------------------

What is the SQL query statement to accomplish this? I realize that I could have just used the state abreviations instead of the statecode and eliminated that table all together, but this is just an example. In reality I have millions of records in tables already set up similar to this (in structure) and want to work with what I have, not change the data.
Thank you,
Joe

[zunebuggy]

The above did not format propery at all. Sorry. But what I want is only two fields on the form. A small text field with the fruit name and then a list field or large multiline text field that lists the states associated with the fruit based on the 3 tables.

Thank you.

[TheShabz]

You dont need that matchingTable at all. Get rid of it. Just add in a field into your fruit_table that contains the stateID. Look up the concept of a "foreign key." After you have that worked out, the Form shouldn't be difficult.


**EDIT**
To clarify:
fruit_table (i'd call it tblFruit to make life easier on you when you write out SQL or VBA):
FruitID(PrimaryKey), FruitName, StateID(ForeignKey)

state_table (tblState, see above):
StateID(PK), StateName

your relationship would then be:
tblFruit M------------1 tblState
StateID(FK) ------- (PK)StateID

[zunebuggy]

After looking at the schema more closely, it is more complicated than I first explained. I only used fruit as an example. It is actually city data I am working with. The example below is closer to what I actually have. Turns out the table you told me to get rid of does not exist and the schema is like this:

Table1
ID (Primary Key)|Postal|City|Province ------ (All data in this table is unique)
1|ABC123|Mycity|Metropolia
2|CDE222|Anytown|Atlantis
3|WEE312|Anywhere|Valhala
...


Table2
field1|field2 ------ (Table2.field1 has a many to one relationship to Table1.ID)
1|2 ------ (Table2.field2 also has a many to one relationship to Table1.ID)
1|13
1|10 ------ (Table2 is used to map the City(s) (linked to) field1 to City (linked to) field2 and group them as shown below)
2|3
2|17
2|13
2|9
3|2
3|10
...

if the user is looking at:
Mycity, Metropolia (from Table2.field1)
it also lists ALL the associated cities below:
------ ------ ------ ------ Anytown|Atlantis|CDE222 ------ (where field 2 = 2 and field1 = 1)
------ ------ ------ ------ Sometown|Anyprov|EEE333 ------ (where field 2 = 13 and field1 = 1)
------ ------ ------ ------ Smalltown|Ruralarea|FDS329 ------ (where field 2 = 10 and field1 = 1)

if the user is looking at:
Anytown, Atlantis (Table2.field1)
if also lists ALL the associated cities below:
------ ------ ------ ------ Anywhere|Valhala|WEE312 ------ (where field 2 = 3 and field1 = 2)
------ ------ ------ ------ Bigville|Anyprov|EEE487 ------ (where field 2 = 17 and field1 = 2)
...

and so on...

Thank you.

[ConneXionLost]

It looks like you want to set up a form / sub-form to display your data. Have a look at this:

Subform

Cheers,

[zunebuggy]

Thank you very much. I got it using the Form Wizard and a slight design view edit.