SQL-Report Query Contains an Error, but where??

[alive84]

Hi folks,

I need once again your help. I have a long SQL-Report Query, which has an error build in, however, I do not see it at all. I have typed it with the SQL-Builder, not with the Wizard (don't like that Click and Go).

Does anybody see the mistake, error message: Syntax Error in FROM clause (great Error message) :-)

SELECT Contacts.FirstName, Contacts.LastName, CostCenter.CostCenter, CostCenter.OrgUnit, CostCenter.Region, 
Workstream.Workstream, Workstream.[Cap/Non-Cap], Work.StartDate, Work.EndDate, Work.[Int/Ext], Vendor.Vendor, 
Contacts.ContactID FROM CostCenter INNER JOIN (Contacts INNER JOIN (Workstream INNER JOIN [Work] (Vendor INNER JOIN [Work] 
ON Vendor.VendorID=Work.Vendor) ON Workstream.WorkstreamID=Work.WorkstreamID)  ON Contacts.ContactID=Work.ContactID) 
ON CostCenter.CostCenterID=Work.CostCenter WHERE (((Work.StartDate)>=[forms]![ReportDateForm]![txtDateFrom]) 
AND ((Work.EndDate)<=[forms]![ReportDateForm]![txtDateTo]));
 

Thanks a lot for the help.

Alive

[FishVal]

Hi folks,

I need once again your help. I have a long SQL-Report Query, which has an error build in, however, I do not see it at all. I have typed it with the SQL-Builder, not with the Wizard (don't like that Click and Go).

Does anybody see the mistake, error message: Syntax Error in FROM clause (great Error message) :-)

SELECT Contacts.FirstName, Contacts.LastName, CostCenter.CostCenter, CostCenter.OrgUnit, CostCenter.Region, 
Workstream.Workstream, Workstream.[Cap/Non-Cap], Work.StartDate, Work.EndDate, Work.[Int/Ext], Vendor.Vendor, 
Contacts.ContactID FROM CostCenter INNER JOIN (Contacts INNER JOIN (Workstream INNER JOIN [Work] (Vendor INNER JOIN [Work] 
ON Vendor.VendorID=Work.Vendor) ON Workstream.WorkstreamID=Work.WorkstreamID)  ON Contacts.ContactID=Work.ContactID) 
ON CostCenter.CostCenterID=Work.CostCenter WHERE (((Work.StartDate)>=[forms]![ReportDateForm]![txtDateFrom]) 
AND ((Work.EndDate)<=[forms]![ReportDateForm]![txtDateTo]));
 

Thanks a lot for the help.

Alive

Hi, Alive.

I'm not sure. Just a guess. I think this join will not work

(Workstream INNER JOIN [Work] (Vendor INNER JOIN [Work]
ON Vendor.VendorID=Work.Vendor) ON Workstream.WorkstreamID=Work.WorkstreamID)

Anyway, when you build complicated table join do it stepwise running the query each time new table is added to the join to see whether it works and returns records you are expecting for.

[alive84]

Hi, Alive.

I'm not sure. Just a guess. I think this join will not work

(Workstream INNER JOIN [Work] (Vendor INNER JOIN [Work]
ON Vendor.VendorID=Work.Vendor) ON Workstream.WorkstreamID=Work.WorkstreamID)

Anyway, when you build complicated table join do it stepwise running the query each time new table is added to the join to see whether it works and returns records you are expecting for.

Hi Fish,

Thanks, good hint, I will try to solve it step-by-step.

May I ask you another question:? I have a tbl_Contact and tbl_Work which are 1--N related. Now what I would like to implement is a button that adds to the Contact a 2nd Work-Information. Is that possible with SQL? Do you know the command? So that I could do some research.

Thanks a lot

[FishVal]

Hi Fish,

Thanks, good hint, I will try to solve it step-by-step.

May I ask you another question:? I have a tbl_Contact and tbl_Work which are 1--N related. Now what I would like to implement is a button that adds to the Contact a 2nd Work-Information. Is that possible with SQL? Do you know the command? So that I could do some research.

Thanks a lot

You are welcome.

There are at least two approaches:

• Normaly this is implemented with Subform
• If you have some very special reason not to use subform you can simulate subform automation by setting DefaultValues of the controls linked to 1-side table to there current values. Actually this is the same that subform does.

In any case I would suggest you to use subform as more simple, natural and reliable solution.


BTW it looks like continuation of you previous thread,
VB and Form!Field populating (automatically)
have you got it working ?

[alive84]

You are welcome.

There are at least two approaches:

• Normaly this is implemented with Subform
• If you have some very special reason not to use subform you can simulate subform automation by setting DefaultValues of the controls linked to 1-side table to there current values. Actually this is the same that subform does.

In any case I would suggest you to use subform as more simple, natural and reliable solution.


BTW it looks like continuation of you previous thread,
VB and Form!Field populating (automatically)
have you got it working ?

No, its already a new issue. Thanks to your help I could solve the first one. :-) I have closed it now.

Back to the ContactID has two WorkID Problem: Do you think I should open a new discussion?
I understand why I should implement a subform, the problem I have is how to create that "create 2ndWork ID button" (the code behind it). Somehow I have to tell this button to leave the information typed in Contacts and simply add a 2nd WorkID.

Thanks

[FishVal]

No, its already a new issue. Thanks to your help I could solve the first one. :-) I have closed it now.

Back to the ContactID has two WorkID Problem: Do you think I should open a new discussion?
I understand why I should implement a subform, the problem I have is how to create that "create 2ndWork ID button" (the code behind it). Somehow I have to tell this button to leave the information typed in Contacts and simply add a 2nd WorkID.

Thanks

Ok. Let's move to another thread. Plz provide comprehensive information on table structure and relationships.