4 Replies Latest reply: Apr 10, 2018 5:32 PM by shimon Gowda RSS

    JOINing Access and Excel database

    shimon Gowda

      SET ThousandSep=',';

      SET DecimalSep='.';

      SET MoneyThousandSep=',';

      SET MoneyDecimalSep='.';

      SET MoneyFormat='$#,##0.00;-$#,##0.00';

      SET TimeFormat='h:mm:ss TT';

      SET DateFormat='M/D/YYYY';

      SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

      SET FirstWeekDay=6;

      SET BrokenWeeks=1;

      SET ReferenceDay=0;

      SET FirstMonthOfYear=1;

      SET CollationLocale='en-US';

      SET CreateSearchIndexOnReload=1;

      SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

      SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

      SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

      SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

       

       

       

       

       

       

      LIB CONNECT TO 'MS Access ODBC 2.0';

       

       

      //EXIT Script;

      //vTodayMinusXMonths= date(addmonths(today(),-36),'YYYYMMDD')

       

       

      //Load SubField(ProductName, ' ',2) As Department

      //From "C:\WINDOWS\QWT.mdb"."Products";

       

       

      //Customers:

      //Load SubField(ProductName, ' ',2) As Department

      //From "C:\Users\shimo\OneDrive\Documents\QWT.mdb"."Products";

       

       

      REM LOAD CustomerID,

          CompanyName,

          ContactName,

          Address,

          City,

          StateProvince,

          PostalCode,

          Country,

          Phone,

          Fax;

         

         

      SQL SELECT CustomerID,

          CompanyName,

          ContactName,

          Address,

          City,

          StateProvince,

          PostalCode,

          Country,

          Phone,

          Fax

      FROM "C:\WINDOWS\QWT.mdb"."Customers";

      Loosen Table Customers;

       

       

       

       

      REM LOAD OrderID,

          ProductID,

          UnitPrice,

          Quantity,

          Discount;

      SQL SELECT OrderID,

          ProductID,

          UnitPrice,

          Quantity,

          Discount

      FROM "C:\WINDOWS\QWT.mdb"."Order Details";

      //Loosen Table Order_Details;

       

       

      //Let vCurrentMonth= Max(OrderDate);

      //Let vdate = Addmonths(today(), -36,'MMM-YYYY');

      //Date(MonthStart(OrderDate),'MMM-YY') as Month;

      //Let formatmonth = monthname(OrderDate, -1);

      REM LOAD OrderID,

          CustomerID,

          EmployeeID,

          Freight,

          OrderDate,

          ShipperID;

      SQL SELECT OrderID,

          CustomerID,

          EmployeeID,

          Freight,

          OrderDate,

          ShipperID

      FROM "C:\WINDOWS\QWT.mdb"."Orders";

      //WHERE OrderDate>=$(vdate);

      Loosen Table Orders;

       

       

      REM LOAD ProductID,

          ProductName,

          SupplierID,

          CategoryID,

          QuantityPerUnit,

          UnitCost,

          UnitPrice,

          UnitsInStock,

          UnitsOnOrder;

      SQL SELECT ProductID,

          ProductName,

          SupplierID,

          CategoryID,

          QuantityPerUnit,

          UnitCost,

          UnitPrice,

          UnitsInStock,

          UnitsOnOrder

      FROM "C:\WINDOWS\QWT.mdb"."Products";

      Loosen Table Products;

       

       

       

       

      LOAD

          EmpID,

          "Last Name",

          "First Name",

          Title,

          "Hire Date",

          Office,

          "Extension",

          "Reports To",

          "Year Salary",

          "First Name"&' '&"Last Name" as full_name

      FROM [lib://C/EmpOff.xls]

      (biff, embedded labels, table is Employee$);

       

       

       

       

      MapEmpIDtofull_name:

       

       

       

       

      Mapping LOAD EmpID,full_name,

      "First Name"&' '&"Last Name" as full_name

      FROM [lib://C/EmpOff.xls]

      (biff, embedded labels, table is Employee$);

       

       

      LIB CONNECT TO 'MS Access ODBC 2.0';

      Orders:

           LOAD *,

                ApplyMap('MapEmpIDtofull_name', EmployeeID, null()) as Sales_Rep

              

                FROM "C:\WINDOWS\QWT.mdb"."Orders";

               

      Screenshot (33).png