Appending data to a table....sort of

[Kev365428]

Firstly, apologies if this has been covered in another thread, but I failed to find anything that resembled my question.

I have a linked ODBC table in my database that contains data like this -

Task	                                        Serial Crew	Amount
00_INSTALL ENGINE INTO PEDESTAL STAND	        707308	AS3	0.2
00_INSTALL ENGINE INTO PEDESTAL STAND	        707308	OMB	0.2
01_BORESCOPE INSPECTION	                        706496	AS1	14.24
01_BORESCOPE INSPECTION	                        706496	AS2	23.24
01_BORESCOPE INSPECTION	                        706496	AS3	28.78
01_BORESCOPE INSPECTION	                        890510	AS1	1.54
01_BORESCOPE INSPECTION	                        890510	AS2	13.1
01_BORESCOPE INSPECTION	                        890510	AS3	17.84
01_INSPECT THE EXHAUST NOZZLE	                890510	AS3	2
01_INSTALL OIL TANK                             707308	AS1	5.67
01_REMOVE WIRE BUNDLE - W1062	                888804	OMA	0.45
02_BORESCOPE INSPECTION	                        811504	OMA	20
02_INSPECT WIRE BUNDLE W1062	                890510	AS1	2
02_REMOVE EXHAUST SLEEVE	                890510	AS3	2.7
02_REMOVE STARTER VALVE AND DUCT	        888804	OMA	1
02_REMOVE STARTER VALVE AND DUCT	        890449	OMA	1

What I would like to do is remove the duplicate values for the Task field, but show how much time each Crew has spent on each Task per Serial. The end result would look like this -

Task	                                               Serial	AS1	AS2	AS3	OMA	OMB
01_BORESCOPE INSPECTION	                               706496	14.24	23.24	28.78		
00_INSTALL ENGINE INTO PEDESTAL STAND	               707308	0.2				0.2
01_INSTALL OIL TANK	                               707308	5.67				
02_BORESCOPE INSPECTION	                               811504				20	
01_REMOVE WIRE BUNDLE - W1062	                       888804				0.45	
02_REMOVE STARTER VALVE AND DUCT	               888804				1	
02_REMOVE STARTER VALVE AND DUCT	               890449				1	
01_BORESCOPE INSPECTION	                               890510	1.54	13.1	17.84		
01_INSPECT THE EXHAUST NOZZLE	                       890510			2		
02_INSPECT WIRE BUNDLE W1062	                       890510	2				
02_REMOVE EXHAUST SLEEVE	                       890510			2.7

What is the best way to achieve this result? I've played around with Append queries, union queries etc, but nothing seems to give me the result I'm after.

Any thoughts/assistance greatly appreciated.

Regards,

Kev.

 

[pbaldy]

Play around with the crosstab query wizard.

 

[Kev365428]

Well there ya go.
Never even thought of a crosstab query.

A different set of eyes always works.

Cheers mate.

Kev.

 

[pbaldy]

Happy to help Kev!