By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,068 Members | 837 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,068 IT Pros & Developers. It's quick & easy.

Combining fields from different rows

P: 6
I have 1 table containing multiple letters A,B,C... from all these letters I need to find the first occurrence of A and B (Note: there can be multiple A's and B's), In addition I need to only evaluate the values that have the same Number ID (this is not a primary key). After this, I take the color field from the first occurrence of A and concatenate it with the first occurrence of B to the combination field. finally, want to repeat the combination field for all the records that contain the respective ID.

My current issue is that I am not able to access both values since they are in different rows so I can not merge the values. and if there are any suggestions on how to replicate a value for all the records that match a specific ID It would be appreciated.

Thanks for reading and for your help :)
Jun 4 '20 #1

✓ answered by twinnyfo

Monster,

I think the only thing you should need to change is the Table and Field Names.

I have tested with a Table named "Your Table Name" with your listed Field Names and your listed example data.

It produces the identical results that you desire in your second post. And, I had originally intended to use the "first" occurrence based upon the Number field:

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.     [Your Table Name].Number, 
  3.     [Your Table Name].Letter, 
  4.     [Your Table Name].Color, 
  5.     IIf([AColor] Is Null, "NA", [AColor]) & "/" & IIf([BColor] Is Null, "NA", [BColor]) AS Combination
  6. FROM ([Your Table Name] 
  7. LEFT JOIN 
  8.     (SELECT 
  9.         Min([Number]) AS Num, 
  10.         Left([Letter], 1) AS Ltr, 
  11.         Color AS AColor
  12.     FROM 
  13.         [Your Table Name] 
  14.     WHERE 
  15.         Left([Letter], 1) = "A" 
  16.     GROUP BY 
  17.         Number, 
  18.         Left([Letter], 1), 
  19.         Color  
  20.     ORDER BY 
  21.         Number, 
  22.         Left([Letter], 1),
  23.         Color) AS T1 
  24. ON 
  25.     [Your Table Name].Number = T1.Num) 
  26. LEFT JOIN 
  27.     (SELECT 
  28.         Min([Number]) AS Num, 
  29.         Left([Letter], 1) AS Ltr, 
  30.         Color AS BColor 
  31.     FROM 
  32.         [Your Table Name] 
  33.     WHERE 
  34.         Left([Letter], 1) = "B" 
  35.     GROUP BY 
  36.         Number, 
  37.         Left([Letter], 1), 
  38.         Color  
  39.     ORDER BY 
  40.         Number, 
  41.         Left([Letter], 1),
  42.         Color)  AS T2 
  43. ON 
  44.     [Your Table Name].Number = T2.Num
  45. ORDER BY 
  46.     [Your Table Name].Number;

Share this Question
Share on Google+
11 Replies


P: 6
This is an example of The start table and the result table:

Start Table:

Number Letter Color Combination
1 Aa Yellow
1 Ct Blue
2 Bn Red
4 Aa Orange
2 Ct Pink
1 Baa Brown
2 a Purple

End Table:

Number Letter Color Combination
1 Aa Yellow Yellow/Brown
1 Ct Blue Yellow/Brown
2 Bn Red Purple/Red
4 Aa Orange Orange/NA
2 Ct Pink Purple/Red
1 Baa Brown Yellow/Brown
2 a Purple Purple/Red
Jun 4 '20 #2

NeoPa
Expert Mod 15k+
P: 31,761
I'm having serious problems just trying to understand your description of your requirement. I can't work to help with a solution without a clear and unambiguous understanding of the requirement.
  1. Monster753:
    I have 1 table containing multiple letters A,B,C... from all these letters I need to find the first occurrence of A and B (Note: there can be multiple A's and B's), In addition I need to only evaluate the values that have the same Number ID (this is not a primary key).
    Are you saying that you are looking to group the records where both the [Number] & the [Letter] values match?
    NB. Calling fields by names such as Number & Letter is guaranteed to lead you into confusion when attempting to explain things. They have meanings in general which will leave your meaning unclear.
  2. You mention what to do with A & B but don't give any clue how [Letter] values in general should be handled.
  3. Your explanation mentions [Letter] values but your data appears to have text values that (obviously) start with a letter but are not just a letter.
Please don't see this as criticism. You do need to be clearer, but we also understand this is hard for people trying for the first time to put their problems into words.
Jun 4 '20 #3

P: 6
Appreciate the advice Neo, Thanks for the feedback I will try again.

I have one table that contains the columns ID, key, color and finally a result column which is empty initially. The IDs are not unique therefore, you can find multiple records with the same ID. The Key its a group of letters that determine the color and some other characteristics of the records. Finally, the result is a field that will contain The first occurrence of the Key that starts with an "A*" + the first occurrence of the Key that starts with "B*".

Initial Table:

ID Key Color Result
1 Aa Yellow
1 at Blue
2 Bn Red
4 Aa Orange
2 Ct Pink
1 Baa Brown
2 a Purple

So in this table, we have some example values lets say we are calculating ID 1, for this, we filter by ID and retrieve all records with ID = 1.

ID Key Color Result
1 Aa Yellow
1 at Blue
1 Baa Brown

Then, we determine the first occurrence of A in the Key column And make sure ID = 1 as this are the records we are evaluating, we repeat the process for B. if there is no A value we just place n/a and same thing in the case that there is not B value.

So after we determine the first occurrence of A and B in the key column, we merge these values in this format "A/B" and place them into the result column, this result should be applied to all records under that ID in this case 1.

it would look like this:

ID Key Color Result
1 Aa Yellow Yellow/Brown
1 Ct Blue Yellow/Brown
1 Baa Brown Yellow/Brown

So the part that I am having issues is getting data from 2 different rows into my result field and replicating this value into all the records that have this ID in this case 1.

Hope this is way more clear, I tried to show the steps I am following to achieve this.
Jun 5 '20 #4

twinnyfo
Expert Mod 2.5K+
P: 3,482
I have no idea what this might be used for, but I will try to hepp!

You must create two queries, one that finds all the "A" colors, one that finds all the "B" colors; left join them to your table, and concatenate the values. If there is no corresponding value for that Number, display "NA".

Here is a quick example:

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.     [Your Table Name].Number, 
  3.     [Your Table Name].Letter, 
  4.     [Your Table Name].Color, 
  5.     IIf([AColor] Is Null, "NA", [AColor]) & "/" & IIf([BColor] Is Null,"NA",[BColor]) AS Combination
  6. FROM ([Your Table Name] 
  7. LEFT JOIN 
  8.     (SELECT 
  9.         Number, 
  10.         Left([Letter], 1) AS Ltr, 
  11.         Color AS AColor
  12.     FROM 
  13.         [Your Table Name] 
  14.     WHERE 
  15.         Left([Letter], 1) = "A" 
  16.     ORDER BY 
  17.         Number, 
  18.         Left([Letter], 1))  AS T1 
  19. ON 
  20.     [Your Table Name].Number = T1.Number) 
  21. LEFT JOIN 
  22.     (SELECT 
  23.         Number, 
  24.         Left([Letter], 1) AS Ltr, 
  25.         Color AS BColor 
  26.     FROM 
  27.         [Your Table Name] 
  28.     WHERE 
  29.         Left([Letter], 1) = "B" 
  30.     ORDER BY 
  31.         Number, 
  32.         Left([Letter], 1))  AS T2 
  33. ON 
  34.     [Your Table Name].Number = T2.Number;
Hope this hepps!
Jun 5 '20 #5

Rabbit
Expert Mod 10K+
P: 12,427
There's no way to ensure first occurrence without an appropriate time or time surrogate field, like an autoincrement I'd.
Jun 5 '20 #6

P: 6
Hello Rabbit, I do have one dedicated column to sort the records so we keep the first record as the first row, I think it serves the same purpose as the surrogate field which I assumed you intended to use based on increment so the records would be like this 1,2,3,4,5 and even if you filter them you could still know who came first "1,3,5". sadly I do have a primary key in this table but the value is not sequential since it is not generated by access. Also, feel free to correct me if you had another idea in mind :)
Jun 5 '20 #7

P: 6
Hello twinnyfo,

I am currently testing your approach, but I keep getting a "Syntax error in JOIN operation". I double-checked all the table names but I do not see anything out of the ordinary.
Jun 5 '20 #8

twinnyfo
Expert Mod 2.5K+
P: 3,482
Monster,

I think the only thing you should need to change is the Table and Field Names.

I have tested with a Table named "Your Table Name" with your listed Field Names and your listed example data.

It produces the identical results that you desire in your second post. And, I had originally intended to use the "first" occurrence based upon the Number field:

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.     [Your Table Name].Number, 
  3.     [Your Table Name].Letter, 
  4.     [Your Table Name].Color, 
  5.     IIf([AColor] Is Null, "NA", [AColor]) & "/" & IIf([BColor] Is Null, "NA", [BColor]) AS Combination
  6. FROM ([Your Table Name] 
  7. LEFT JOIN 
  8.     (SELECT 
  9.         Min([Number]) AS Num, 
  10.         Left([Letter], 1) AS Ltr, 
  11.         Color AS AColor
  12.     FROM 
  13.         [Your Table Name] 
  14.     WHERE 
  15.         Left([Letter], 1) = "A" 
  16.     GROUP BY 
  17.         Number, 
  18.         Left([Letter], 1), 
  19.         Color  
  20.     ORDER BY 
  21.         Number, 
  22.         Left([Letter], 1),
  23.         Color) AS T1 
  24. ON 
  25.     [Your Table Name].Number = T1.Num) 
  26. LEFT JOIN 
  27.     (SELECT 
  28.         Min([Number]) AS Num, 
  29.         Left([Letter], 1) AS Ltr, 
  30.         Color AS BColor 
  31.     FROM 
  32.         [Your Table Name] 
  33.     WHERE 
  34.         Left([Letter], 1) = "B" 
  35.     GROUP BY 
  36.         Number, 
  37.         Left([Letter], 1), 
  38.         Color  
  39.     ORDER BY 
  40.         Number, 
  41.         Left([Letter], 1),
  42.         Color)  AS T2 
  43. ON 
  44.     [Your Table Name].Number = T2.Num
  45. ORDER BY 
  46.     [Your Table Name].Number;
Jun 5 '20 #9

P: 6
Thanks, lot twinnyfo, you were completely right I tested it in the test data and it worked flawlessly, I really appreciate your help
Jun 5 '20 #10

twinnyfo
Expert Mod 2.5K+
P: 3,482
Glad I could hepp! Hope you have a great weekend!
Jun 5 '20 #11

NeoPa
Expert Mod 15k+
P: 31,761
That looks like it's saved me a job. Well done guys.
Very pleased to see you have a solution now Monster753 :-)
Jun 5 '20 #12

Post your reply

Sign in to post your reply or Sign up for a free account.