Hi - I've used MS Access to run a query:
SELECT dbo_order_header.oh_order_number, IIf(Left([oh_cust_order_ref],6)="100000",".no",IIf(Left([oh_cust_order_ref],3)="100",".co.uk",IIf(Left([oh_cust_order_ref],1)="3",".de",IIf(Left([oh_cust_order_ref],1)="4",".fr",IIf(Left([oh_cust_order_ref],1)="5",".es",IIf(Left([oh_cust_order_ref],1)="6",".it",IIf(Left([oh_cust_order_ref],1)="7",".nl",IIf(Left([oh_cust_order_ref],1)="8",".dk",IIf(Left([oh_cust_order_ref],1)="9",".se",IIf(Left([oh_cust_order_ref],2)="11",".pl",IIf(Left([oh_cust_order_ref],2)="12",".pt",IIf(Left([oh_cust_order_ref],2)="13",".ie",IIf(Left([oh_cust_order_ref],2)="14",".fi"))))))))))))) AS Category, dbo_customer_detail.cd_statement_name, dbo_order_header.oh_datetime, dbo_order_header.oh_promise_date, dbo_order_status.os_description, dbo_order_sub_status.oss_sub_status, dbo_order_type.ot_description, dbo_order_analysis.oa_description, dbo_order_line_item.oli_item_price, dbo_order_line_item.oli_qty_unallocated, dbo_order_line_item.oli_qty_allocated, dbo_order_line_item.oli_total_cost, [oli_qty_unallocated]+[oli_qty_allocated] AS [Total Qty], [oli_item_price]*[Total Qty] AS [Total Value£], IIf(<([Now]-3),[Total Value£],0) AS [<-3 Days], IIf(=([Now]-3),[Total Value£],0) AS [-3 Days], IIf(=([Now]-2),[Total Value£],0) AS [-2 Days], IIf(=([Now]-1),[Total Value£],0) AS [-1 Days], IIf(=[Now],[Total Value£],0) AS Today, IIf(=([Now]+1),[Total Value£],0) AS [+1 Days], IIf(=([Now]+2),[Total Value£],0) AS [+2 Days], IIf(=([Now]+3),[Total Value£],0) AS [+3 Days], IIf(>([Now]+3),[Total Value£],0) AS [>+3 Days], IIf([ot_description]="Wholesale",Int([oh_promise_date]),Int([oh_datetime])) AS [Date], Day([oh_datetime]) & "/" & Month([oh_datetime]) & "/" & Year([oh_datetime]) AS Date1, [Date] AS x, Int(Now()) AS Now, IIf([ot_description]="Wholesale",[ud_username] & [ot_description],IIf([ot_description]="Killer Ink Use",[ud_username] & [ot_description],IIf([ot_description]="TO PHOTO",[ud_username] & [ot_description],IIf([ot_description]="Website",[ud_username] & [ot_description] & [Category],[ud_username] & [Category])))) AS Cat, dbo_order_header_total.oht_outstanding_balance, dbo_order_header_detail.ohd_input_datetime, dbo_order_header_detail.ohd_last_amended_datetime
FROM dbo_order_header_detail INNER JOIN (dbo_order_header_total INNER JOIN (((((((dbo_order_header_analysis INNER JOIN (dbo_order_header INNER JOIN dbo_order_status ON dbo_order_header.oh_os_id = dbo_order_status.os_id) ON dbo_order_header_analysis.oha_oh_id = dbo_order_header.oh_id) INNER JOIN dbo_order_sub_status ON dbo_order_header.oh_oss_id = dbo_order_sub_status.oss_id) INNER JOIN dbo_order_type ON dbo_order_header.oh_ot_id = dbo_order_type.ot_id) INNER JOIN dbo_order_line_item ON dbo_order_header.oh_id = dbo_order_line_item.oli_oh_id) INNER JOIN dbo_customer_detail ON dbo_order_header.oh_cd_id = dbo_customer_detail.cd_id) INNER JOIN dbo_order_analysis ON dbo_order_header.oh_oa_id = dbo_order_analysis.oa_id) INNER JOIN dbo_user_detail ON dbo_order_header.oh_ud_id = dbo_user_detail.ud_id) ON dbo_order_header_total.oht_oh_id = dbo_order_header.oh_id) ON dbo_order_header_detail.ohd_oh_id = dbo_order_header.oh_id
WHERE (((dbo_order_status.os_description)="Awaiting Despatch") AND ((dbo_order_sub_status.oss_sub_status) Not Like "In picking") AND ((dbo_order_type.ot_description) Not Like "Conventions" And (dbo_order_type.ot_description) Not Like "Replacement") AND (([oli_qty_unallocated]+[oli_qty_allocated])>0))
ORDER BY dbo_order_header.oh_datetime;
Then run a 2nd query using the output from the first:
SELECT IIf([RetailCat]=1,"UK, IE, ADAM & FRANCIS",IIf([RetailCat]=2,"DE, DAVID & NADINE",IIf([RetailCat]=3,"FR, NICOLAS, DAMIEN & KRISTELLE",IIf([RetailCat]=4,"ES, PT & ANA",IIf([RetailCat]=5,"IT & ANGELA",IIf([RetailCat]=6,"NL, PL, FI & MARTINE",IIf([RetailCat]=7,"DK, NO, SE & JAMIE",""))))))) AS [Retail Category], Sum([Aged Orders Query Awaiting Despatch].[<-3 Days]) AS [SumOf<-3 Days], Sum([Aged Orders Query Awaiting Despatch].[-3 Days]) AS [SumOf-3 Days], Sum([Aged Orders Query Awaiting Despatch].[-2 Days]) AS [SumOf-2 Days], Sum([Aged Orders Query Awaiting Despatch].[-1 Days]) AS [SumOf-1 Days], Sum([Aged Orders Query Awaiting Despatch].Today) AS SumOfToday, Sum([Aged Orders Query Awaiting Despatch].[+1 Days]) AS [SumOf+1 Days], Sum([Aged Orders Query Awaiting Despatch].[+2 Days]) AS [SumOf+2 Days], Sum([Aged Orders Query Awaiting Despatch].[+3 Days]) AS [SumOf+3 Days], Sum([Aged Orders Query Awaiting Despatch].[>+3 Days]) AS [SumOf>+3 Days]
FROM [Aged Orders Query Awaiting Despatch] INNER JOIN [Sales Analysis Category Table] ON [Aged Orders Query Awaiting Despatch].Cat = [Sales Analysis Category Table].Category
WHERE ((([Aged Orders Query Awaiting Despatch].os_description) Not Like "Cancelled" And ([Aged Orders Query Awaiting Despatch].os_description) Not Like "Completed"))
GROUP BY IIf([RetailCat]=1,"UK, IE, ADAM & FRANCIS",IIf([RetailCat]=2,"DE, DAVID & NADINE",IIf([RetailCat]=3,"FR, NICOLAS, DAMIEN & KRISTELLE",IIf([RetailCat]=4,"ES, PT & ANA",IIf([RetailCat]=5,"IT & ANGELA",IIf([RetailCat]=6,"NL, PL, FI & MARTINE",IIf([RetailCat]=7,"DK, NO, SE & JAMIE",""))))))), [Sales Analysis Category Table].RetailCat
HAVING (((IIf([RetailCat]=1,"UK, IE, ADAM & FRANCIS",IIf([RetailCat]=2,"DE, DAVID & NADINE",IIf([RetailCat]=3,"FR, NICOLAS, DAMIEN & KRISTELLE",IIf([RetailCat]=4,"ES, PT & ANA",IIf([RetailCat]=5,"IT & ANGELA",IIf([RetailCat]=6,"NL, PL, FI & MARTINE",IIf([RetailCat]=7,"DK, NO, SE & JAMIE","")))))))) Not Like ""))
ORDER BY [Sales Analysis Category Table].RetailCat;
Is there a way to do this directly in SQL? I get the error message ERROR [07002] [Microsoft[]ODBC Microsoft Access Driver] Too few parameters. Expected 46.
Thanks!