Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Syntax for Multiple Inner Joins

Status
Not open for further replies.

CaiSupport

Technical User
Nov 1, 2005
18
US
Original Code
UPDATE CustomerService AS CS
INNER JOIN AR_95_UDF_ARInvoiceLines AS Inv
ON (CS.[Next Date]=Inv.Nextservicedate) AND ((CS.ID)=Inv.Scheduleid)
SET CS.[Previous Date] = CS.[Next Date], CS.[Next Date] = UpdateServiceDates(CS.[Next Date],CS.Interval,CS.Frequency);

Changed To add an additional File and now I get a syntax error
UPDATE CustomerService AS CS
INNER JOIN AR_95_UDF_ARInvoiceLines AS Inv
ON (CS.[Next Date]=Inv.Nextservicedate) AND ((CS.ID)=Inv.Scheduleid)
INNER JOIN ARN_InvHistoryHeader as Hst
On (Inv.InvoiceNumber = Hst.InvoiceNumber)
SET CS.[Previous Date] = CS.[Next Date], CS.[Next Date] = UpdateServiceDates(CS.[Next Date],CS.Interval,CS.Frequency);

This is the query I did to find the linkage I wanted.
INNER JOIN CustomerService ON (AR_95_UDF_ARInvoiceLines.Scheduleid = CustomerService.ID) AND (AR_95_UDF_ARInvoiceLines.Nextservicedate = CustomerService.[Next Date])) INNER JOIN ARN_InvHistoryHeader ON AR_95_UDF_ARInvoiceLines.InvoiceNumber = ARN_InvHistoryHeader.InvoiceNumber
 
You may try this:
UPDATE CustomerService AS CS
[!]([/!]INNER JOIN AR_95_UDF_ARInvoiceLines AS Inv
ON (CS.[Next Date]=Inv.Nextservicedate) AND ((CS.ID)=Inv.Scheduleid)[!])[/!]
INNER JOIN ARN_InvHistoryHeader as Hst
On (Inv.InvoiceNumber = Hst.InvoiceNumber)
SET CS.[Previous Date] = CS.[Next Date], CS.[Next Date] = UpdateServiceDates(CS.[Next Date],CS.Interval,CS.Frequency);


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Thank you for your quick response.

I still get Syntax error in Update statement.

 
Another try.
UPDATE CustomerService AS CS
INNER JOIN AR_95_UDF_ARInvoiceLines AS Inv
ON CS.[Next Date]=Inv.Nextservicedate AND CS.ID=Inv.Scheduleid
SET CS.[Previous Date] = CS.[Next Date], CS.[Next Date] = UpdateServiceDates(CS.[Next Date],CS.Interval,CS.Frequency)
WHERE EXISTS (SELECT * FROM ARN_InvHistoryHeader WHERE InvoiceNumber=Inv.InvoiceNumber)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
That seems to work, no error messages, but I have one last request. The UpdateServiceDates is using CS.[Next Date], I wanted to change it to calculate based on ARN_InvHistoryHeader.InvoiceDate. When I change it as follows, it prompts me for a date. I do not know access well. If I do put in a date, the calculation uses the prompted date.
UPDATE CustomerService AS CS INNER JOIN AR_95_UDF_ARInvoiceLines AS Inv ON (CS.ID=Inv.Scheduleid) AND (CS.[Next Date]=Inv.Nextservicedate) SET CS.[Previous Date] = CS.[Next Date], CS.[Next Date] = UpdateServiceDates([ARN_InvHistoryHeader].[InvoiceDate],CS.Interval,CS.Frequency)
WHERE EXISTS (SELECT * FROM ARN_InvHistoryHeader WHERE InvoiceNumber=Inv.InvoiceNumber);

Thank you so much for your time.
 
Perhaps this ?
UPDATE CustomerService AS CS
INNER JOIN AR_95_UDF_ARInvoiceLines AS Inv ON CS.ID=Inv.Scheduleid AND CS.[Next Date]=Inv.Nextservicedate
SET CS.[Previous Date]=CS.[Next Date]
,CS.[Next Date]=UpdateServiceDates((SELECT InvoiceDate FROM ARN_InvHistoryHeader WHERE InvoiceNumber=Inv.InvoiceNumber),CS.Interval,CS.Frequency)
WHERE EXISTS (SELECT * FROM ARN_InvHistoryHeader WHERE InvoiceNumber=Inv.InvoiceNumber);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
I never thought this would be so hard and thank you for all your help. Now it says it is going to run and am I sure I want to run it and the it says "Operation must use
an Updateable query".

Thank you again.
 
And this ?
UPDATE CustomerService AS CS
INNER JOIN AR_95_UDF_ARInvoiceLines AS Inv ON CS.ID=Inv.Scheduleid AND CS.[Next Date]=Inv.Nextservicedate
SET CS.[Previous Date]=CS.[Next Date]
,CS.[Next Date]=UpdateServiceDates(DLookUp("InvoiceDate","ARN_InvHistoryHeader","InvoiceNumber='" & Inv.InvoiceNumber & "'"),CS.Interval,CS.Frequency)
WHERE EXISTS (SELECT * FROM ARN_InvHistoryHeader WHERE InvoiceNumber=Inv.InvoiceNumber);

If InvoiceNumber is defined as numeric then get rid of the single quotes.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Now when I update it says it did not update the 2 fields due to a type conversion failure. I tried it with and without the single quotes. Both dates are defined as Date/Time.

Thank you again for your help.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top