Updating this from previous explanation, I feel this is a better way to ask.
I have two tables, T1 and T2. T1 has the following information:
T1
Order----Name------Other Info
101-------Item A-------1
101-------Item B-------2
102-------Item C-------3
103-------Item B-------4
103-------Item D-------5
103-------Item C-------6
T2 has the following information:
T2
AddName--------LinkedItem
Item E------------Item B
Item E------------Item D
Every time there is Item B or Item D, I'd like to add the AddName to the Order field. I can create a query to find out every Order the LinkedItem appears in (see below for example result for Query1)
Q1
T1.Order---T2.LinkedItem----T2.AddName
101---------Item B-------------Item E
103---------Item B-------------Item E
103---------Item D-------------Item E
But I'd like to create a query where the field "Name" could apply to both T1.Name and T2.Name, and that it would add to what we see in T1, and fields that T1 has and T2 doesn't have would appear as null values. For end result example, see below.
Q2
Order----Name------Other Info
101-------Item A-------1
101-------Item B-------2
101-------Item E-------
102-------Item C-------3
103-------Item B-------4
103-------Item E-------
103-------Item D-------5
103-------Item E-------
103-------Item C-------6
How would I write SQL to accomplish this? Where Query 2's Order and Name fields are pulling from T1, and if T1's Name matched Q1's (from T2) LinkedItem, then it would also pull Q1.Order data for Query 2's Order field and Q1.AddName data for Query 2's Name field. I would also like for the Other Info field to be left blank when this was the case.
I'm just getting all sorts of syntax errors when I try things, and when I do write it correctly (avoiding syntax errors), what I get is the AddName information replacing it's LinkedItem counterpart:
Q2 (No Item B's or Item C's)
Order----Name------Other Info
101-------Item A-------1
101-------Item E-------
102-------Item C-------3
103-------Item E-------
103-------Item E-------
103-------Item C-------6
Or I get a similar result as Q1:
Q2 (No Item A's, B's, C's, or D's)
Order----Name------Other Info
101-------Item E-------
103-------Item E-------
103-------Item E-------
And all other information is not displayed.
Any suggestions would be welcome!