MySQL Forums
Forum List  »  Newbie

Re: Microsoft Access
Posted by: Zayd Safar
Date: October 30, 2018 11:04AM

Thanks Peter - they're actually Microsoft SQL Server 17 statements...the first one is:

The first Query is as follows: -
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([x]<([Now]-3),[Total Value£],0) AS [<-3 Days], IIf([x]=([Now]-3),[Total Value£],0) AS [-3 Days], IIf([x]=([Now]-2),[Total Value£],0) AS [-2 Days], IIf([x]=([Now]-1),[Total Value£],0) AS [-1 Days], IIf([x]=[Now],[Total Value£],0) AS Today, IIf([x]=([Now]+1),[Total Value£],0) AS [+1 Days], IIf([x]=([Now]+2),[Total Value£],0) AS [+2 Days], IIf([x]=([Now]+3),[Total Value£],0) AS [+3 Days], IIf([x]>([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;

The second query is as follows: -
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;

Within Access when the second query is run it will automatically run the first query.

In SQL I get the following Error message: -

ERROR [07002] [Microsoft[]ODBC Microsoft Access Driver] Too few parameters. Expected 46.

Thanks

Options: ReplyQuote


Subject
Written By
Posted
October 30, 2018 08:05AM
October 30, 2018 09:09AM
Re: Microsoft Access
October 30, 2018 11:04AM
October 30, 2018 11:27AM
October 30, 2018 11:46AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.