Combining fields from different rows

[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). 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 :)

[Monster753]

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

[NeoPa]

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.

[Monster753]

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.

[twinnyfo]

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:

SELECT 
    [Your Table Name].Number, 
    [Your Table Name].Letter, 
    [Your Table Name].Color, 
    IIf([AColor] Is Null, "NA", [AColor]) & "/" & IIf([BColor] Is Null,"NA",[BColor]) AS Combination
FROM ([Your Table Name] 
LEFT JOIN 
    (SELECT 
        Number, 
        Left([Letter], 1) AS Ltr, 
        Color AS AColor
    FROM 
        [Your Table Name] 
    WHERE 
        Left([Letter], 1) = "A" 
    ORDER BY 
        Number, 
        Left([Letter], 1))  AS T1 
ON 
    [Your Table Name].Number = T1.Number) 
LEFT JOIN 
    (SELECT 
        Number, 
        Left([Letter], 1) AS Ltr, 
        Color AS BColor 
    FROM 
        [Your Table Name] 
    WHERE 
        Left([Letter], 1) = "B" 
    ORDER BY 
        Number, 
        Left([Letter], 1))  AS T2 
ON 
    [Your Table Name].Number = T2.Number;

Hope this hepps!

[Rabbit]

There's no way to ensure first occurrence without an appropriate time or time surrogate field, like an autoincrement I'd.

[Monster753]

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 :)

[Monster753]

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.

[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:

SELECT 
    [Your Table Name].Number, 
    [Your Table Name].Letter, 
    [Your Table Name].Color, 
    IIf([AColor] Is Null, "NA", [AColor]) & "/" & IIf([BColor] Is Null, "NA", [BColor]) AS Combination
FROM ([Your Table Name] 
LEFT JOIN 
    (SELECT 
        Min([Number]) AS Num, 
        Left([Letter], 1) AS Ltr, 
        Color AS AColor
    FROM 
        [Your Table Name] 
    WHERE 
        Left([Letter], 1) = "A" 
    GROUP BY 
        Number, 
        Left([Letter], 1), 
        Color  
    ORDER BY 
        Number, 
        Left([Letter], 1),
        Color) AS T1 
ON 
    [Your Table Name].Number = T1.Num) 
LEFT JOIN 
    (SELECT 
        Min([Number]) AS Num, 
        Left([Letter], 1) AS Ltr, 
        Color AS BColor 
    FROM 
        [Your Table Name] 
    WHERE 
        Left([Letter], 1) = "B" 
    GROUP BY 
        Number, 
        Left([Letter], 1), 
        Color  
    ORDER BY 
        Number, 
        Left([Letter], 1),
        Color)  AS T2 
ON 
    [Your Table Name].Number = T2.Num
ORDER BY 
    [Your Table Name].Number;

[Monster753]

Thanks, lot twinnyfo, you were completely right I tested it in the test data and it worked flawlessly, I really appreciate your help

[twinnyfo]

Glad I could hepp! Hope you have a great weekend!

[NeoPa]

That looks like it's saved me a job. Well done guys.
Very pleased to see you have a solution now Monster753 :-)