What type of query is this? (1 Viewer)

[mohobrien]

I'm not really looking for code, just a clue as to what to read up on as I have been beating my head against the wall with this one. I can do it in about three steps but I am trying to do it in one.

The first table1 is like this:
id     Field1          Field2     Field3                       
1      TagNo44         Y          Yes                       
2      TagNo5          C          No                       
3      TagNo127        Q          Maybe                       
4      TagNo4          T          Perhaps                      
5      TagNo88         Z          Probably Not                 
6      TagNo17         P          Could be                     

The second table2 is like this:
id     Field1          Field2         Field3                   
1      TagNo44         Outfit6        30                       
2      TagNo44         Outfit14       70                       
3      TagNo127        Outfit14       100                      
4      TagNo4          Outfit3        100
5      TagNo88         Outfit3        50                       
6      TagNo88         Outfit8        50                       
7      TagNo5          Outfit7        100
8      TagNo17         Outfit92       100

Output table to be like this
id     Field1          Field2                                  
4      TagNo4          Outfit3&100                       
2      TagNo5          Outfit7&100                             
6      TagNo17         Outfit92&100                            
1      TagNo44         Outfit14&70&Outfit6&30                  
5      TagNo88         Outfit3&50&Outfit8&50                   
3      TagNo127        Outfit14&100

Table1Field1 is in a one to many relationship to table2.field1
All fields are strings.

 

[EMP]

I think you can do it only with VBA code, not queries.

^

 

[TylerTand]

Quote:
The first table1 is like this:
id Field1 Field2 Field3
1 TagNo44 Y Yes
2 TagNo5 C No
3 TagNo127 Q Maybe
4 TagNo4 T Perhaps
5 TagNo88 Z Probably Not
6 TagNo17 P Could be

The second table2 is like this:
id Field1 Field2 Field3
1 TagNo44 Outfit6 30
2 TagNo44 Outfit14 70
3 TagNo127 Outfit14 100
4 TagNo4 Outfit3 100
5 TagNo88 Outfit3 50
6 TagNo88 Outfit8 50
7 TagNo5 Outfit7 100
8 TagNo17 Outfit92 100

Output table to be like this
id Field1 Field2
4 TagNo4 Outfit3&100
2 TagNo5 Outfit7&100
6 TagNo17 Outfit92&100
1 TagNo44 Outfit14&70&Outfit6&30
5 TagNo88 Outfit3&50&Outfit8&50
3 TagNo127 Outfit14&100


It looks to me like you could write the query:
Select id, Field1, Field2, Field3
FROM Table2
ORDER BY id, Field1, Field2&Field3;

This should merge Fields 2 and three together if this is the desired result.

 

[mohobrien]

Thanks for the responses TylerTand and EMP. I think I have it now.
http://www.access-programmers.co.uk/forums/showthread.php?t=137653