I want to convert a sequential list to a crosstab type of output.
The input is derived from an Excel table in the format:
DocNr ... DocDate ...... TypeCod ... HholdID ... AsSocRef
193.09 ... 12/Dec/2009 ... AS ........ 2537 ........ 25
195.09 ... 12/Dec/2009 ... AC ........ 2537 ........ 25
197.09 ... 14/Dec/2009 ... FM ........ 2537 ........ 25
286.14 ... 15/Apr/2014 .... AS ........ 2537 ........ 25
289.14 ... 16/Apr/2014 .... FM ........ 2537 ........ 25
The DocNr is unique, its date is also specific to that document although many documents can have the same date.
There are 6 possible document types: AS, AC, FM, PI, RE, CE. DocType = TypeCod.
Each document relates to only one household but households may have repeats of any type or none of any type, but they will have at least one document.
The objective is to be able to produce a report for any one household, listing all its documents in a tabulated form:
HholdID ... DocType ... DocNr .... DocDate ....... DocType ... DocNr ....... DocDate ....... DocType ... DocNr ..... DocDate ... etc ... etc
2537 ....... AC .......... 195.09 ... 12/Dec/2009 ... AS ......... 193.09 ... 12/Dec/2009 ..... FM ......... 197.09 .... 14/Dec/2009
2537 .................................................. ........ AS ......... 286.14 ... 15/Apr/2014 ..... FM .......... 289.14 ... 16/Apr/2014
.
I have 6 queries which separately identify the documents for each household by each type but not all households have every type of document.
These queries take the form as follows, with a separate but similar structure for each DocType
Code:
SELECT [AsSocRef] & Format([data],"yy") AS ID1, AsSocDocList.HholdID, AsSocDocList.AsSocRef, AsSocDocList.TypCod, AsSocDocList.DocNr AS ASnr, AsSocDocList.Data AS ASdata
FROM AsSocDocList LEFT JOIN AsSocDocType ON AsSocDocList.TypCod = AsSocDocType.DocTypeCod
GROUP BY [AsSocRef] & Format([data],"yy"), AsSocDocList.HholdID, AsSocDocList.AsSocRef, AsSocDocList.TypCod, AsSocDocList.DocNr, AsSocDocList.Data
HAVING (((AsSocDocList.TypCod)="as"))
ORDER BY AsSocDocList.AsSocRef;
My problem is how to merge them. It looks as though I need to somehow create 6 simultaneous left-joins to compile the records for each household.
Is ALTER TABLE a possible solution? If so, what is its syntax?
Thanks for reading this far!!