Unify data in a single line

[r621]

Dears,

I need your support about Access, I would like to know, how to unify in a unique line the following data:

WireNo Cabinet Term
WIRE 1 CAB1 1
WIRE 1 CAB1 2
WIRE 1 CAB1 3

I need a unique line with the all information of WireNo in this format:

WireNo Cabinet Term1 Term2 Term3
WIRE 1 CAB1 1 2 3

Could you please let know.
Looking forward for your kind reply.
Regards

 

[theDBguy]

Hi. Welcome to AWF!

You should be able to do that by using a Crosstab Query.

 

[arnelgp]

TRANSFORM Count(yourTable.Cabinet) AS CountOfCabinet
SELECT yourTable.WireNo, yourTable.Cabinet
FROM yourTable
GROUP BY yourTable.WireNo, yourTable.Cabinet
PIVOT "Term" & [Term];

 

[r621]

Hello, I'va tried what you suggested to me but is not working or I'm not able how to do it...

 

[theDBguy]

Hello, I'va tried what you suggested to me but is not working or I'm not able how to do it...

Can you post a sample copy of your db with test data?

 

[r621]

Query1

Tag_Number	Junction Box	Multicable Tag	Terminal_Number__Name_
18-DFIU-001	18-JG-017	18-MG-017	1
18-DFIU-001	18-JG-017	18-JG-017	2
18-DFIU-001	18-JG-017	18-MG-017	3

This is my Real DB , crossing two tables with a query I've obtained this and I need to have the Terminal Number Name in a unique line like following:

18-DFIU-001 ----18-JG-017 ---- M18-MG-017 ----1 ----2----3

Could you please help me

In attacched DB with a sample table, could you please insert the SQL or VBA code in order to obtain what I mentioned above and then upload the modified db

I forgot the number 1---2---3 shall be in separate and new columns

Thanks in advance for your cooperation

 

[arnelgp]

see Query1.

 

[r621]

Thanks Arnelgp , I checked the database but there's only 1 point :

The data in coloumns Terminals 1,Terminals 2,Terminals 3 shall be like 1 2 3 , in the database upload by you are 1 1 1 , I need the following solution:

Table:

Tag_Number	Junction Box	Multicable Tag	Terminal_Number__Name_
18-DFIU-001	18-JG-017	18-MG-017	1
18-DFIU-001	18-JG-017	18-JG-017	2
18-DFIU-001	18-JG-017	18-MG-017	3

Query1 shall return as below:

ID	Junction Box	Multicable Tag	Terminal 1	Terminal 2	Terminal 3
1​	18-JG-017	18-MG-017	1​	2​	3​

Could you please help me.

Best Regards

 

[jdraw]

??? How do you account for different values in Multicable Tag? 18-JG-017 vs 18-MG-017??

 

[r621]

I'm sorry all multicables are 18-MG-017 is a mistake JG.

Tabella3 Tabella3

ID	Tag_Number	Junction Box	Multicable Tag	Terminal_Number__Name_
1​	18-DFIU-001	18-JG-017	18-MG-017	1
2​	18-DFIU-001	18-JG-017	18-MG-017	2
3​	18-DFIU-001	18-JG-017	18-MG-017	3

Query result shall be

ID	Junction Box	Multicable Tag	Terminal 1	Terminal 2	Terminal 3
1	18-JG-017	18-MG-017	1	2	3

 

[arnelgp]

see this.

 

[Pat Hartman]

Use a crosstab query.

 

[r621]

Is exactly what I want thank you very much Arnelgp!
But I've a last question :

Suppose that I've a table like this:

Tabella3 Tabella3

ID	Tag_Number	Junction Box	Multicable Tag	Terminal_Number__Name_
1​	18-DFIU-001	18-JG-017	18-MG-017	1
2​	18-DFIU-001	18-JG-017	18-MG-017	2
3​	18-DFIU-001	18-JG-017	18-MG-017	3
4​	18-DFIU-002	18-JG-019	18-MG-018	4
5​	18-DFIU-002	18-JG-019	18-MG-018	5
6​	18-DFIU-002	18-JG-019	18-MG-018	6
7​	18-DFIU-002	18-JG-019	18-MG-018	7
8​	18-DFIU-002	18-JG-019	18-MG-018	8
9​	18-DFIU-003	18-JG-020	18-MG-019	6

Using your croostab query I will have a lot of colomns:



The max number of terminals for each JB is 5: So I need to have Terminal 1 to Terminal 5 columns and insert here the value of the terminal i.e.:


Tabella1 Tabella1

Junction Box	Multicable Tag	Terminal 1	Terminal 2	Terminal 3	Terminal 4	Terminal 5
18-JG-017	18-MG-017	1	2	3
18-JG-019	18-MG-018	4	5	6	7	8
18-JG-020	18-MG-019	6

Is it possible?

 

[Pat Hartman]

Then you need to generate a sequence number (1-5) for each group and pivot on the sequence number and take the First() value.

 

[r621]

thanks Pat I got the point , Just I don't understand what do you mean take the First () valute, could you please make a sample?

Ok Finally Got It thanks for your support

 

[Pat Hartman]

In the crosstab, select First as the aggregate option.