在Excel VBA中连接表时遇到麻烦

I'm trying to take a crystal report sql and convert it so I can use it in excel. I'm having troubles with the syntax of the joining statements in Excel. I can do a couple of tables individually, but cant combine them.

我正在尝试使用水晶报告sql并将其转换为我可以在excel中使用它。我在Excel中加入语句的语法遇到了麻烦。我可以单独做几张桌子,但不能把它们组合起来。

From crystal reports: What I'm trying to copy:

从水晶报道:我想要复制的内容:

SELECT "Material_Req"."Job", "Material_Req"."Pick_Buy_Indicator", "Material_Req"."Material", "Material_Req"."Description", "Material_Req"."Vendor_Reference", "PO_Detail"."PO", "Material_Req"."Est_Qty", "Source"."Act_Qty", "PO_Header"."Vendor", "PO_Detail"."Due_Date"
 FROM   (("PRODUCTION"."dbo"."Material_Req" "Material_Req" LEFT OUTER JOIN "PRODUCTION"."dbo"."Source" "Source" ON "Material_Req"."Material_Req"="Source"."Material_Req") LEFT OUTER JOIN "PRODUCTION"."dbo"."PO_Detail" "PO_Detail" ON "Source"."PO_Detail"="PO_Detail"."PO_Detail") LEFT OUTER JOIN "PRODUCTION"."dbo"."PO_Header" "PO_Header" ON "PO_Detail"."PO"="PO_Header"."PO"
 WHERE  "Material_Req"."Pick_Buy_Indicator"='b' AND "Material_Req"."Est_Qty">"Source"."Act_Qty"
 ORDER BY "Material_Req"."Job"

Previous one that worked:

以前的工作:

sqlMatlReq = "select job.job,  material_req.vendor_reference, material_req.Material,'' As Description, material_req.Vendor," _
        & "(material_req.est_qty) As Qty, (material_req.act_qty) As Qty,   " _
        & "material_req.due_date, " _
        & " material_req.status " _
        & "from (material_req inner join job on material_req.job=job.job) " _
        & "left join material on material_req.material=material.material " _
        & "where material.material is not null " _
        & "and job.part_number is not null " _
        & "and Job.Status in('Active') " _
        & "and material_req.act_qty in('0') " _
        & "Union all " _
        & "select job.job, material_req.vendor_reference, material_req.Material, material_req.description, material_req.Vendor," _
        & "(material_req.est_qty), (material_req.act_qty) As Qty," _
        & " material_req.due_date, material_req.status " _
        & "from (material_req inner join job on material_req.job=job.job) " _
        & "left join material on material_req.material=material.material " _
        & "where material.material is null " _
        & "and job.part_number is not null " _
        & "and Job.Status in('Active') " _
        & "and material_req.act_qty in('0') " _
        & "order by job.job;"

As far as I got: having troubles combining the three

据我所知:结合三者有麻烦

'sqlMatlReq = "select job.job, material_req.material, material_req.Vendor_Reference, material_req.description, material_req.Est_Qty  " _
      '& "from (material_req inner join job on material_req.job=job.job) " _

'sqlMatlReq = "select source.Act_Qty, PO_Detail.PO, PO_Detail.Due_Date " _
      '& "from source left outer join PO_Detail on source.Act_qty=PO_Detail.PO_Detail "

'sqlMatlReq = "select PO_Header.vendor " _
      '& "from PO_Header"

Thanks in advance.

提前致谢。

1 个解决方案

#1

SQL Server Stored Procedures for Use With Crystal Reports

 CREATE PROCEDURE sp_SampleProcedure
    AS

 BEGIN 
 SET NOCOUNT ON;

 SELECT table_catalog, table_schema, table_name, table_type
   FROM tbldm.information_schema.tables 
  WHERE table_schema = 'dbo'
    AND table_type = 'BASE TABLE';

 END;

Where any query you would like to see supplying data to the Crystal Reports client should be represented by the last SELECT statement issued in the SQL Server STORED procedure. This example points to the SQL data dictionary of Table Names and Types.

如果要查看向Crystal Reports客户端提供数据的任何查询,应使用SQL Server STORED过程中发出的最后一个SELECT语句表示。此示例指向表名称和类型的SQL数据字典。

1. Define a DSN connection between the Crystal Reports client and the SQL Database Server. There may already be one of other reports were designed before this.

   在Crystal Reports客户端和SQL数据库服务器之间定义DSN连接。在此之前可能已经设计了其他一个报告。

2. Stored procedures should show up in its schema catalog alongside the other objects typically found by the Crystal report writer when selecting db objects to use.

   在选择要使用的数据库对象时,存储过程应显示在其架构目录中,以及Crystal报表编写器通常找到的其他对象。

3. Note, procedures can be parametrized and their input variables used throughout the procedure itself.

   注意,程序可以参数化,并且在整个过程本身中使用它们的输入变量。