I have a query from an Access Database. It queries this stupid program called Navision ODBC Database and then returns the results to a spreadsheet. We recently upgraded this program a full two versions and now these queries need to be updated. The problems I am facing are that I do not know how the tables have changed and I can't understand this SQL. Could someone please explain some of this to me.
I understand what the brackets are for and what the Iif statement is doing but beyond that I am pretty lost.
I think that this was generated by Access. :/
Code: Select all
SELECT
[Rental Header].[Gen_ Bus_ Posting Group] AS [Branch(GBPG)],
[Rental Header].No_ AS [Order No],
[Rental Header].[Shortcut Dimension 2 Code] AS [Project Code],
[Project Code] & '-' & [Order No] AS [Job-Cust-OrdNo],
[Rental Header].[Sell-to Customer No_],
[Rental Header].[Sell-to Customer Name],
[Rental Header].[Sell-to Contact],
[Rental Header].[Phone No_],
[Rental Header].[Sell-to Phone No_],
[Sell-to Customer No_] & '-' & [Sell-to Customer Name] AS [CustNo-Name],
[Rental Header].[Expected Return Date],
[Rental Line].[No_] AS [Product No],
[Rental Line].Description,
[Rental Line]!No_ & '-' & [Rental Line]!Description AS [ProdNo-Description],
[Rental Line].[Line No_],
[Rental Line].[From Date],
[Rental Line].[Thru Date],
[Rental Line].[Declared Value],
[Rental Line].[Unit Price],
[Rental Line].[Shipment Date],
[Rental Line].[Return Date],
[Shipment Ledger Entry].[No_] AS [FA No],
[Fixed Asset].[Description] AS [FA Description],
[Fixed Asset].[Serial No_],
[Shipment Ledger Entry]!No_ & '-' & [Fixed Asset]!Description & '-' & [Fixed Asset]![Serial No_] AS [FANo-Description-SN],
[Fixed Asset].[On Rent], [Fixed Asset].[Bill-to Customer No_] AS FACustNo, Customer.Name,
[Shipment Ledger Entry].Quantity AS [Ship Qty],
[Shipment Ledger Entry].[Posting Date],
[Shipment Ledger Entry].[Transaction Type],
[Shipment Ledger Entry].Open,
[Rental Line].[Deal Code],
IIf (IsNull([Rental Price]!Price),0,[Rental Price]!Price) AS [Book Rate],
[Unit Price]-[Book Rate] AS [Price Difference],
[Customer].[Salesperson Code],
[Fixed Asset].[FA Class Code],
[Rental Header].[Insurance Expiration Date],
[Rental Header].[Signed Rental Order],
[Rental Header].[Tax Area Code]
FROM ([Rental Price]
RIGHT JOIN
(
(
[Shipment Ledger Entry]
INNER JOIN
(
[Rental Line] INNER JOIN [Rental Header] ON [Rental Line].[Document No_] = [Rental Header].No_
)
ON
(
[Shipment Ledger Entry].[Rental Line No_] = [Rental Line].[Line No_])
AND ([Shipment Ledger Entry].[Document No_] = [Rental Line].[Document No_])
AND ([Shipment Ledger Entry].[Document No_] = [Rental Line].[Document No_])
)
INNER JOIN [Fixed Asset] ON [Shipment Ledger Entry].No_ = [Fixed Asset].No_
) ON [Rental Price].No_ = [Rental Line].No_
)
LEFT JOIN Customer ON [Fixed Asset].[Bill-to Customer No_] = Customer.No_
WHERE
(
(
(
[Rental Header].No_)="RO-05848"
)
)
);
Code: Select all
[Rental Line]!No_ & '-' & [Rental Line]!Description AS [ProdNo-Description],
There are also some brackets that seem out of place but I can not say for sure because I only use basic sql commands.
I understand if nobody wants to help, I sure as hell do not want to be doing this but thank you very much to anyone who can and does help!