Link Subdatasheet tbl to 2 columns of another tbl

[Margie]

In Access 2007, I've created a table (Companies) with two Child Fields (Production & Distribution (both columns in the main tblClients)). The Subdatasheet of tblCompanies however only shows the info from the tblClients if the values in the Production and Distribution fields are exactly the same.
Is it possible to have both shown in the Subdatasheet? Meaning if two different companies were involved, it will show the Client info at company A but also at company B (thus potentially having the same record twice in the Subdatasheet).

Thanks in advance Margie

[NeoPa]

Are you talking about a subform? If so, I think you need to be a little more specific about how the subform is linked to the main form, and how the tables are linked in the database too.

My guess (with so little clear info) is that you would need to have separate sub-items (subform whatever) to handle the two distinct links rather than trying to link them in what seems an illogical way.

[ADezii]

In Access 2007, I've created a table (Companies) with two Child Fields (Production & Distribution (both columns in the main tblClients)). The Subdatasheet of tblCompanies however only shows the info from the tblClients if the values in the Production and Distribution fields are exactly the same.
Is it possible to have both shown in the Subdatasheet? Meaning if two different companies were involved, it will show the Client info at company A but also at company B (thus potentially having the same record twice in the Subdatasheet).

Thanks in advance Margie

Your question is a little hazy, but if I interpret it correctly, I can tell you that you can only have 1 Subdatasheet View for a given Table at a time, but all is not lost. You can also have a different Subdatasheet View of the same Table data by:

1. Create a Query based on all Fields contained in the Table.
2. Open the Query in Datasheet View.
3. Insert.
4. Subdatasheet.
5. Select the alternate Child Table.
6. Enter a value for Link Child Fields.
7. Enter a value for Link Master Fields.

To Remove a Subdatasheet:

1. Open Query in Datasheet View.
2. Format.
3. Subdatasheet.
4. Remove.

[Margie]

To prevent a huge post, it appears I have left too much info out. I will try your suggestion ADezii, but first this post to see if we (and NeoPa) are on the same track.

I have a main table: Clients
Around the main table I build a form: Clientview
The info you enter in the form is taken from (but can also put into if info doesn't exist) several other tables. In the form there are for instance 2 fields for companies (Production & Distribution). This specific info comes from the table Companies. Now Access automatically created a subdatasheet in the table Companies which shows all the info from the main table Clients.
The problem is that the subdatasheet only shows the info if the 2 fields (Production & Distribution) are exactly the same.
- Example 1:
the value in the Production field: Microsoft
the value in the Distribution field: Microsoft
-> in the table companies you look at the subdatasheet of the record Microsoft and you can see all the client info from the main table Clients

- Example 2:
the value in the Production field: Microsoft
the value in the Distribution field: TheScripts
-> in the table companies if you look at the subdatasheet of either the record Microsoft or the record TheScripts, it doesn't show any Client info at all.

If I look at Insert Subdatasheet of the table Companies, it shows 2 things.
- The table Companies has:
Link Child Fields: Companies
Link Master Fields: Companies
- The table Clients has:
Link Child Fields: Production;Dist ribution
Link Master Fields: Companies;Compa nies

What I would like is for the client info to be shown at the record Microsoft but also at the record TheScripts. As for the 'best' solution, if it means building a new reorganized database, I have no problem with that. It's part of the learning process. I hope I made it all more understandable with this post. :)

[ADezii]

Just checking, will return at a later time.

[NeoPa]

Margie,

I'm not very familiar with displaying linked tables in sub-datasheets, but it seems (logically) very similar to producing a query with linked tables where the tables are equivalent to the tables listed in a FROM clause and the links are as defined in the ON part of that same (FROM) clause. The JOIN type would be a LEFT JOIN with the two fields you have listed (Production & Distribution) both having to match Companies. It seems this is exactly what you're getting.

You can show what you need with a more sophisticated query (OR rather than AND and two separate instances of the child table linked) but I'm fairly certain that Access won't do this for you at a sub-datasheet level.

This would be easier to deal with (explain and work on) if you provided MetaData for the relevant tables (Master & Child in this case). Here is an example of how to post table MetaData. Please use this format in your post :

Table Name=[[u]tblStudent[/u]]
[i]Field; Type; IndexInfo[/i]
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

Of course, if you are now satisfied with the answers so far, then there's no further need for it.

[Margie]

First off, sorry for the delay. Running a home, going to work and going to school tend to get overwhelming some times and one has to prioritize. And a movie database is ranked low :) . So again sorry for the wait.
If I understand it correctly, MetaData is basicly the design of a table. This info can be seen in Design View correct? If so, this is the info of the main table:

Table Name=[[u]Clients[/u]]
[i]Field; Type; IndexInfo[/i]
ID; AutoNumber
Ok; Yes/No
Status; Text
Property; Text
Client Title; Text
Titles Alt; Text
Series Current; Text
Series Total; Text
Series Name; Text
Production; Text
Distribution; Text
Poster; Attachment
Budget; Text
Genre 1; Text
Genre 2; Text
Genre 3; Text
Year; Text
Grade; Text
Rating; Text
Duration; Text
Country 1; Text
Country 2; Text
Country 3; Text
Direct 1; Text
Direct 2; Text
Direct 3; Text
Cast 1; Text
Cast 2; Text
Cast 3; Text
Cast 4; Text
Person 1; Text
Person 2; Text
Person 3; Text
Person 4; Text
Link MM; Hyperlink
Link IMDB; Hyperlink
Plot; Text
Date View; Date/Time
Date Vote; Date/Time
Remarks; Text
Review; Text

Now, if I understand ADezii correctly, one way to get the desired info is creating a query with the alternate child field link. One query would be for Production and the other for Distribution. This works but the problem in my case is that I have many fields (just look in the code) with the same problem.
For example, I have a table Cast (Field Name: Cast & Data Type: Text) linked to Cast 1, Cast 2, Cast 3 and Cast 4. And 1 query for each field seems a little too much.

Hope this info helps and I'm off to continue with my efforts in getting the data I want into 1 query instead of several queries.

Thanks again :)

[NeoPa]

First off, sorry for the delay. Running a home, going to work and going to school tend to get overwhelming some times and one has to prioritize. And a movie database is ranked low :) . So again sorry for the wait.

This is not a problem. The beauty of a forum based help system is that immediate responses are not really required.

Members post when they can, and the experts do the same. An apology more than covers the delay.

[NeoPa]

Margie, I've had a little play around in my test database and it seems that subdatasheets are limited to one per table.

I set up a table with two unique indices and when I tried to expand the subdatasheet, it asked me to select what I wanted. Now there may be scope here for something clever, but it certainly wouldn't allow me to set up or use more than one of them.

This is not an area of expertise for me so I will leave it there. If ADezii has anything different to say then you may want to wait for his response. However, you may have to resign yourself to missing out on this feature for this particular table.

[NeoPa]

Just as a snippet of information though, in case you were not aware, you can link tables in a query where a number of fields reference the same table by including the table in the FROM clause multiple times (with aliases) as follows :

SELECT tC.Cast1,
       tC1.CastName AS Cast1Name,
       tC.Cast2,
       tC2.CastName AS Cast2Name,
       tC.Cast3,
       tC3.CastName AS Cast3Name,
       tC.Cast4,
       tC4.CastName AS Cast4Name,
       ...
FROM ((([Clients] AS tC LEFT JOIN [Cast] AS tC1
  ON tC.Cast1=tC1.Cast) LEFT JOIN [Cast] AS tC2
  ON tC.Cast1=tC2.Cast) LEFT JOIN [Cast] AS tC3
  ON tC.Cast1=tC3.Cast) LEFT JOIN [Cast] AS tC4
  ON tC.Cast1=tC4.Cast

I use t as a prefix for my aliases to indicate table. Hence, tC4 == {table Cast 4}.
Clients doesn't need to be aliased, I just would to keep the table references shorter when referring to fields.

[ADezii]

First off, sorry for the delay. Running a home, going to work and going to school tend to get overwhelming some times and one has to prioritize. And a movie database is ranked low :) . So again sorry for the wait.
If I understand it correctly, MetaData is basicly the design of a table. This info can be seen in Design View correct? If so, this is the info of the main table:

Table Name=[[u]Clients[/u]]
[i]Field; Type; IndexInfo[/i]
ID; AutoNumber
Ok; Yes/No
Status; Text
Property; Text
Client Title; Text
Titles Alt; Text
Series Current; Text
Series Total; Text
Series Name; Text
Production; Text
Distribution; Text
Poster; Attachment
Budget; Text
Genre 1; Text
Genre 2; Text
Genre 3; Text
Year; Text
Grade; Text
Rating; Text
Duration; Text
Country 1; Text
Country 2; Text
Country 3; Text
Direct 1; Text
Direct 2; Text
Direct 3; Text
Cast 1; Text
Cast 2; Text
Cast 3; Text
Cast 4; Text
Person 1; Text
Person 2; Text
Person 3; Text
Person 4; Text
Link MM; Hyperlink
Link IMDB; Hyperlink
Plot; Text
Date View; Date/Time
Date Vote; Date/Time
Remarks; Text
Review; Text

Now, if I understand ADezii correctly, one way to get the desired info is creating a query with the alternate child field link. One query would be for Production and the other for Distribution. This works but the problem in my case is that I have many fields (just look in the code) with the same problem.
For example, I have a table Cast (Field Name: Cast & Data Type: Text) linked to Cast 1, Cast 2, Cast 3 and Cast 4. And 1 query for each field seems a little too much.

Hope this info helps and I'm off to continue with my efforts in getting the data I want into 1 query instead of several queries.

Thanks again :)

Here's an Off-the-Wall solution. How about Appending all combinations of Parent/Child combinations to a Master Table (tblMaster) consisting of all combinations of Fields for all Tables. In this case Cast is the Parent Table. Let's assume the following Relationships:

1. Cast.[ID](1) ==> [Cast 1].[ID](MANY)
2. Cast.[ID](1) ==> [Cast 2].[ID](MANY)
3. Cast.[ID](1) ==> [Cast 3].[ID](MANY)

1. Fields in Table Cast (Parent)
   1. [Field1]
   2. [Field2]
2. Fields in Table Cast 1 (Child)
   1. [Field3]
   2. [Field4]
3. Fields in Table Cast 2 (Child)
   1. [Field5]
   2. [Field6]
4. Fields in Table Cast 3 (Child)
   1. [Field7]
   2. [Field8]

Now, run the following Append Queries to populate tblMaster, which has its Fields pre-defined:
[CODE=sql]INSERT INTO tblMaster ( MasterID, Field1, Field2 )
SELECT Cast.ID, Cast.Field1, Cast.Field2
FROM [Cast];[/CODE]
[CODE=sql]INSERT INTO tblMaster ( MasterID, Field3, Field4 )
SELECT [Cast 1].ID, [Cast 1].Field3, [Cast 1].Field4
FROM [Cast 1];[/CODE]
[CODE=sql]INSERT INTO tblMaster ( MasterID, Field5, Field6 )
SELECT [Cast 2].ID, [Cast 2].Field5, [Cast 2].Field6
FROM [Cast 2];[/CODE]
[CODE=sql]INSERT INTO tblMaster ( MasterID, Field7, Field8 )
SELECT [Cast 3].ID, [Cast 3].Field7, [Cast 3].Field8
FROM [Cast 3];[/CODE]
I know, I know it's radical and poor NeoPa is saying that ADezii is out of control again, but there really aren't many viable solutions.