Monday, August 1, 2016

Access_Crosstab_Query_to_TSQL


Converting an Access Crosstab query into SQL Server (T-SQL) is more difficult than you might think.  Take the following crosstab:

----------------------------------------------------------------------------------------
------  Access Crosstab Query   -----------------
----------------------------------------------------------------------------------------

/*
TRANSFORM Sum(Line_Days) AS SumOfLine_Days
SELECT Factory, Line, Sum(Line_Days) AS [Total Of Line_Days]
FROM Factory_Line_temp
GROUP BY Factory, Line
PIVOT DateID;

*/
 


In order do this in SQL Server, you need to build the SQL statement dynamically, adding the crosstab headers by reading them from the query, then building them in code. 



----------------------------------------------------------------------------------------
------ T-SQL Equivalent -----------------
----------------------------------------------------------------------------------------
DECLARE  @ColumnOrder  AS  TABLE(ColumnName 
                                 varchar(8)
                                 NOT NULL
                                 PRIMARY KEY)


DECLARE  @strSQL  AS NVARCHAR(4000)
INSERT INTO @ColumnOrder SELECT DISTINCT DateID FROM Factory_Line_temp


DECLARE  @XTabColumnNames  AS NVARCHAR(MAX)
DECLARE         @XTabColumn      AS varchar(20)
SET @XTabColumn = (SELECT MIN(ColumnName) FROM  @ColumnOrder)


SET @XTabColumnNames = N''


-- Create the xTab columns


WHILE (@XTabColumn IS NOT NULL)
  BEGIN
    SET @XTabColumnNames = @XTabColumnNames + N',' +
      QUOTENAME(CAST(@XTabColumn AS NVARCHAR(10)))
   SET @XTabColumn = (SELECT MIN(ColumnName)
                          FROM   @ColumnOrder
                          WHERE  ColumnName > @XTabColumn)
  END


SET @XTabColumnNames = SUBSTRING(@XTabColumnNames,2,LEN(@XTabColumnNames))


PRINT @XTabColumnNames


SET @strSQL = N'SELECT * FROM (
              SELECT Factory, Line, DateID, Line_Days
              FROM Factory_Line_temp) as header
              pivot (sum(Line_Days)
                    FOR DateID IN(' + @XTabColumnNames + N'))  AS Pvt '


PRINT @strSQL 


-- Execute strSQL sql

EXEC sp_executesql @strSQL




Monday, July 25, 2016

Access UPDATE query to SQL Server

How do I convert an Access UPDATE query to SQL Server?


Access SQL




  • UPDATE Books INNER JOIN UpdateList
       ON Books.ISBN = UpdateList.ISBN
       SET Books.Price = [Updatelist].[price]


SQL Server
  • UPDATE Books
        SET Books.Price = [Updatelist].[price]
       
        FROM Books INNER JOIN UpdateList
           ON Books.ISBN = UpdateList.ISBN




    Tuesday, July 19, 2016

    Access InStr() function to SQL Server equivalent


    What is the SQL Server equivalent to Access InStr() function?

    TargetField: String expression being searched
    SearchValue: String expression being sought
    StartPosition: Starting position for each search (optional)
    • InStr(TargetField, SearchValue)
    • InStr(StartPosition, TargetField, SearchValue)
    SQL Server
    • CHARINDEX(SearchValue, TargetField)
    • CHARINDEX(SearchValue, TargetField, StartPosition )

    Wednesday, July 13, 2016

    Access IIf() function to SQL Server equivalent

    What is the SQL Server equivalent to Access IIf() function?


    Access SQL
    • IIf([ResultValue]>=1, [Result Value], Null)


    SQL Server
    •  CASE WHEN [Result Value]>=1
    •    THEN [Result Value]
    •    ELSE NULL 
    •  END 

    Thursday, July 7, 2016

    Access NZ() function to SQL Server equivalent


    What is the SQL Server equivalent to Access NZ() function?


    Access SQL
    • NZ([Event Timestamp])


    SQL Server Equivalent
    • COALESCE([Event Timestamp],0)
    • ISNULL([Event Timestamp],0)
      • do not confuse this with the Access isnull() function.

    Tuesday, July 5, 2016

    Access CDate() function to SQL Server equivalent


    What is the SQL Server equivalent to Access CDate() function?


    Access SQL
    • CDate([Event Timestamp])


    SQL Server Equivalent
    • cast([Event Timestamp] as datetime)
    • convert(datetime, [Event Timestamp])

    Friday, July 1, 2016

    Access DateValue() function to SQL Server equivalent

    What is the SQL Server equivalent to Access DateValue() function?


    Access SQL
    • DateValue([Event Timestamp])


    SQL Server Equivalent
    • cast([Event Timestamp] as date)
    • convert(date, [Event Timestamp])

    Thursday, June 23, 2016

    How do I create a Running Sum Query?

    SQL is a very powerful query language that is built into Microsoft Access. But one thing SQL does not do very well is refer to a previous row to calculate values. As a result, some things that are simple to do in a spreadsheet are difficult in a query. One of those is creating a running sum.

    A running sum adds the value of a field in a record to the value of the same field in the previous record.

    Running Sums are fairly easy in an Access Report, but more difficult in a query.  Difficult, but not impossible. This article will show how to create a running sum query.

    Running Sum in a Report

    Create a running sum in a report with an unbound textbox in the Detail section. This time, however, in the control source, put the name of the field you want the running sum on. And just like the number query, set the Running Sum property to Over All.

    A running sum adds the value of a field in a record to the value of the same field in the previous record. image
    Figure 1: Shows the property dialog box for a text box on a report that will create a running sum.

    Running Sum in Query

    But suppose I don't want to do it in a report. Suppose I want to do it directly in a query. There are two different ways to accomplish this. The first uses the Domain Aggregate Function DCount and the second uses a Correlated Subquery.

    Both of these methods require a unique column in the table to create the sequence on. This could be the Primary Key field or any field that has a Unique Index. In the following example, the Customers table has two such columns, CustID (Customer ID), which is the primary key, and CustName (Customer Name), which has a unique index.

    I’ve already discussed the Domain Function method in a previous article here: Domain Function Example: Running Sum with DSum, so in this article, I’ll address the Sub Query method.

    Two Types: OverAll and OverGroup

    There are two common types of running sums. The first is a running sum over the entire resultset. see Figure 2 for RunningSumOverAll 

    image
    Figure 2 illustrates the running sum of the Price field in an Order Details table.

    The second is a running sum over a group. See Figure 3 RunningSumOverGroup

    image
    Figure 3 shows a running sum over a group.

    In this case, the group is the OrderID field, and you'll see that the query starts the summing over as the group number value changes.

    Correlated Sub Query Method

    When used in either the FROM clause or Field List, a subquery must be correlated to the main query. What does that mean? Well, as the name implies, correlated means co-related, that is the records in the subquery must be related to a single record in the main query. In this way, the subquery can be executed for each record in the main query.

    Let me take this in small steps.  Suppose I have an Order Details table that looks like this:

    image

    The Main Query

    The table is sorted on OrderDetailID, so the first thing is to create a placeholder in the query:

    image

    SELECT tblOrderDetails.OrderDetailID,
         tblOrderDetails.OrderID,
         tblOrderDetails.ProductID,
         tblOrderDetails.Price,
         Null AS RunningSum
    FROM tblOrderDetails;

    It is very important that the main query should be sorted on the field the sequence will be created on.  In this case OrderDetailID.

    The Sub Query

    Now I just need to replace the NULL with the running sum value.  But how? In Excel, I’d reference the cell to the left (Price) and add it to the cell above (RunningSum).  So cell B5 would have a formula of =A5+B4.

    image
    Figure 5: Method that WILL NOT work in Access.

    But tempting as this is, Access can’t reference individual cells.  I need a different method.

    Since addition is cumulative, I can sum A1 through A4, in other words, B4 = SUM(A1:A4)

    image
    Figure 6:This method CAN be translated to Access

    This method I can use. 

    Running Sum Over All

    I can find the value of any individual record (say OrderDetailID=4) with a query like this:

    image
    Figure 7

    or in SQL

    SELECT Sum([Price])
    FROM tblOrderDetails
    WHERE tblOrderDetails.OrderDetailID <= 4

    The result of which will be:

    image
    Figure 8:

    Adding that to my main query, I could try something like this:

    SELECT tblOrderDetails.OrderDetailID,
         tblOrderDetails.OrderID,
         tblOrderDetails.ProductID,
         tblOrderDetails.Price,
         (SELECT Sum(Price) FROM tblOrderDetails
            WHERE OrderDetailID <= 4)
    AS RunningSum
    FROM tblOrderDetails;

    but this will create a couple of problems. 

    The first is a syntax issue.  I can’t directly reference the same table in two separate FROM clauses in a single SQL statement.  I need to Alias the table name in the subquery

    Select Sum(Price) FROM tblOrderDetails as OD WHERE OD.OrderDetailID <=4

    so I’d get:

    SELECT tblOrderDetails.OrderDetailID,
         tblOrderDetails.OrderID,
         tblOrderDetails.ProductID,
         tblOrderDetails.Price,
         (SELECT Sum(Price) FROM tblOrderDetails as OD
            WHERE OD.OrderDetailID <= 4)
    AS RunningSum
    FROM tblOrderDetails;

    The second problem is that this will return a $12.00 for every record.

    image
    Figure 9:
     
    So I need to link the subquery with a particular record in the main query.
     
    Pulling Them Together
     
    The last piece is to reference the OrderDetailID in the main query rather than the hard-coded 4.
     
    SELECT tblOrderDetails.OrderDetailID,
         tblOrderDetails.OrderID,
         tblOrderDetails.ProductID,
         tblOrderDetails.Price,
        (SELECT Sum(Price) FROM tblOrderDetails as OD
            WHERE OD.OrderDetailID <= tblOrderDetails.OrderDetailID)
              
    AS RunningSum
    FROM tblOrderDetails;

    Which gives me this:

    image
    Figure 10: Results of the completed Running Sum Over All query.

    Here's how it works.
     
    For each record in the main query, Access runs the subquery. The subquery returns the number of records where the OrderDetailID in the subquery is less than or equal to the OrderDetailID in that record of the main query.

    So in the first record, the OrderDetailID is 1. So the subquery opens the OrderDetails table again and sees that there is only 1 record whose OrderDetailID is less than or equal to 1. So it sums the value of that one field, which simply returns that value ($5.00).
     
    Then it processes the second record. The OrderDetailID of that record is 2, and the subquery sees that there are only 2 records which have an OrderDetailID whose value is less than or equal to 2. So it sums those records ie. $5.00 + (-$2.00) = $3.00.
     
    And so on.

    Running Sum Over Group

    Instead of creating a running sum over the entire resultset as we did above, you might want to create a running sum for a grouping of records. In the case of the Order Details table above, it might make more sense to give a running sum for each OrderID group.

    Creating a running sum over a group of records rather than the whole resultset is a little more complicated, but not much. Again, I can use subquery. But I need to add an additional criterion to the Where condition to include the value that we want to group on.

    And again, the only addition to the subquery method is to sum only those values that have the same OrderID. Thus the subquery looks like this:

    SELECT Sum(Price) FROM tblOrderDetails as OD
            WHERE OD.OrderDetailID <= tblOrderDetails.OrderDetailID AND
                 OD.OrderID=tblOrderDetails.OrderID

    And the whole query, like this:

    SELECT tblOrderDetails.OrderDetailID,
         tblOrderDetails.OrderID,
         tblOrderDetails.ProductID,
         tblOrderDetails.Price,
        (SELECT Sum(Price) FROM tblOrderDetails as OD
            WHERE OD.OrderDetailID <= tblOrderDetails.OrderDetailID
               AND OD.OrderID=tblOrderDetails.OrderID) AS RunningSum
    FROM tblOrderDetails;

    The result looks like Figure 11.

    image
    Figure 11: Result of the Running Sum query over a group. Notice how the running sum resets when the OrderID changes from 1234 to 1235.

    You can find a sample called

    RunningSumInQuery

    which illustrates both Over All and Over Group as well as the corresponding DSum Method (domain aggregate function).

    Wednesday, June 22, 2016

    * Index to Access 101 *

    Access Basics

    Compacting Databases

    Domain Functions Demystified

    Date Stuff

     Database Design Basics

    What’s Wrong With Repeated Columns?

    Normalizing Repeating Columns

     

    Queries

    Select Queries Series:

    Subqueries


    Union Query

    Data Definition Language (DDL) Queries:

    Top Queries Revealed:

    Count Distinct In Access Series:

    Miscellaneous

    Reports

    VBA/Programming

    Automation

    Web Databases

    • The Application

    Thursday, June 16, 2016

    How do I create a Numbered Query in Access?

    SQL is a very powerful query language that is built into Microsoft Access. But one thing SQL does not do very well is refer to a previous row to calculate values. As a result, some things that are simple to do in a spreadsheet are difficult in a query. On of those is creating a numbered sequence for your query. However, difficult does not mean impossible. This article will show you how to create a numbered query.

    Numbered Query

    A numbered query is a query where each record is numbered sequentially. There may be a variety of reasons to do this. You might have a test question database where you want each question to be automatically numbered. Or you might want to export a customer list with sequential numbering to an external source like Excel. While this is simple to do in an Access report, it requires some advanced techniques to do in a query.

    In an Access report, all you need to do is add an unbound text box in the Detail section, put =1 in the control source, and set the Running Sum property to Over All. See Figure 1.

    Figure1

    Figure 1: Shows the property dialog box for a text box on a report that will create a numbered sequence.

    But suppose you don't want to do it in a report. Suppose you want to do it directly in a query. There are two different ways to accomplish this. The first uses the Domain Aggregate Function DCount and the second uses a Correlated Subquery.

    Both of these methods require a unique column in the table to create the sequence on. This could be the Primary Key field or any field that has a Unique Index. In the following example, the Customers table has two such columns, CustID (Customer ID), which is the primary key, and CustName (Customer Name), which has a unique index.

    I’ve already discussed the Domain Function method in a previous article here: Domain Function Example: Numbered Query With DCount, so in this article, I’ll address the Sub Query method.

    Correlated Sub Query Method

    Subqueries can be used in multiple places in an SQL statement. Most of the time they’re used in the FROM clause or the WHERE clause.  But they can also be used in the Field List, which is where I’ll use it here. 

    When used in either the FROM clause or Field List, the query must be correlated to the main query. What does that mean? Well, as the name implies, correlated means co-related, that is the records in the subquery must be related to a single record in the main query. In this way, the subquery can be executed for each record in the main query.

    Let me take this in small steps.  Suppose I have a Customer table that looks like this:

    image

    The Main Query

    The table is sorted on CustID, but that field is not sequential since there are gaps in the numbering.  I can see from the Record Navigator at the bottom of the query that CustID of 7 is actually the 5th record

    So first, I’ll create a placeholder in my query:

    image

    SELECT Null AS Sequence,
        Customers.CustID,
        Customers.CustName,
        Customers.CustPhone
    FROM Customers
    ORDER BY Customers.CustID;

    It is very important that the main query should be sorted on the field the sequence will be created on.  In this case CustID.

    The Sub Query

    Now I just need a way to replace the Null with the sequential value.  I can find the value of any individual record (say CustID=7) with a query like this:

    image

    or in SQL

    SELECT Count(1) AS Rec
    FROM Customers
    WHERE Customers.CustID<=7

    The result of which will be:

    image

    Adding that to my main query, I could try something like this:

    SELECT
        (Select Count(1) FROM Customers
            WHERE Customers.CustID <=7
    ) AS Sequence,
        Customers.CustName,
        Customers.CustPhone,
        Customers.CustID
    FROM Customers
    ORDER BY Customers.CustID;

    but this will create a couple of problems. 

    The first is a syntax issue.  I can’t directly reference the same table in two separate FROM clauses in a single SQL statement.  I need to Alias the table name in the subquery

    Select Count(1) FROM Customers as A WHERE A.CustID <=7

    so I’d get:

    SELECT
        (Select Count(1) FROM Customers as A
            WHERE A.CustID <=7
    ) AS Sequence,
        Customers.CustName,
        Customers.CustPhone,
        Customers.CustID
    FROM Customers
    ORDER BY Customers.CustID;

    The second problem is that this will return a 5 for every record.

    image
     
    Pulling Them Together
     
    The last piece is to reference the CustID in the main query rather than the hard-coded 7.

    SELECT
        (Select Count(1) FROM Customers as A
            WHERE A.CustID <=Customers.CustID
    ) AS Sequence,
        Customers.CustName,
        Customers.CustPhone,
        Customers.CustID
    FROM Customers
    ORDER BY Customers.CustID;

    Which gives me this:

    image

     
    Here's how it works.
     
    For each record in the main query, Access runs the subquery. The subquery returns the number of records where the CustID in the subquery is less than or equal to the CustID in that record of the main query.

    So in the first record, the CustID is 1. So the subquery opens the Customers table again and sees that there is only 1 record whose CustID is less than or equal to 1. So it returns 1.
     
    Then it processes the second record. The CustID of that record is 3, and the subquery sees that there are only 2 records which have an CustID whose value is less than or equal to 3. So it returns 2.
     
    And so on.
    Other Fields
    You don't need to use a number field as your Order By field. You can sort on text fields and number the query as well.
     
    If you wanted to sort on the Customer Name field (CustName), you would change the subquery to the following:

    SELECT
        (Select Count(1) FROM Customers as A
            WHERE A.CustName <=Customers.CustName) AS Sequence,
        Customers.CustName,
        Customers.CustPhone,
        Customers.CustID
    FROM Customers
    ORDER BY Customers.CustName;

     The output would look like this:

    image

    Domain Aggregate Function Method
    As I said, this can also be done with a DCount Function, which I discussed in a previous article: Domain Function Example: Numbered Query With DCount

    However, you can find both methods on my website in this sample: NumberedQuery.
    .

    Friday, June 10, 2016

    How do I use Conditional Formatting in Access

    Traditionally, conditional formatting in Access was accomplished through the use of VBA code.  If I wanted to change the format of a field based on it’s value, I’d do something like this to change the background color of the Balance field based on its value.

    Select Case Me!txtBal
       Case Is < 2500
          Me!txtBal.BackColor = vbWhite
       Case Is < 5000
          Me!txtBal.BackColor = vbGreen
       Case Is < 7500
          Me!txtBal.BackColor = vbYellow
       Case Is >= 7500
          Me!txtBal.BackColor = vbRed
    End Select

    or maybe this to format fields based on the value in another field (Check14).

    If Me!Check14 = True Then
      Me!txtDesc.BackColor = vbRed
      Me!Acct.ForeColor = vbRed
    Else
      Me!txtDesc.BackColor = vbBlue
      Me!Acct.ForeColor = vbBlue
    End If

    Which gives me the following on one record

    image

    But this on the next record

    image

    This works well in Single Form view, but not so well in Continuous Form View or Datasheet View.

    image

    image

    Datasheet View has no formatting at all.

    image

    The problem is that formatting with VBA affects all records the same. This is fine in Single Form view, because you can only see one.

    Conditional Formatting

    So to solve this problem, Microsoft introduced Conditional Formatting to Access Forms and Reports. I’m going to concentrate on forms here, but the same applies to reports.

    Conditional Formatting is built into the form controls themselves.  So to set it, you need to open the form in Design View to get at the control properties.

    You need to select the control (in this case a textbox) , the FORMAT tab on the Ribbon, and then Conditional Formatting.

    image

    You’ll get a dialog box like this.

    image

    Click the New Rule button and you will get yet another dialog box

    image

    In this article, I’m goin to concentrate on comparing to values within the current record, so I’ll leave the top rule type selected.

    The next step is to choose the Field Value Is drop down box.  It will have 3 choices:

    image

    1. Field Value Is: Compares the selected control to some hard coded value(s)
    2. Expression Is: Compares the selected control to the value of another control.
    3. Field Has Focus: Determines whether the control is currently selected.

    Field Value Is

    Comparing the selected control to some hard coded value(s)

    So first, I’m going to change the formatting a field (Balance) based on the value it contains. If you recall from above, the Balance field had the following conditions.

      txtBal < 2500 –> White
      txtBal < 5000 –> Green
      txtBal < 7500 –> Yellow
      txtBal >= 7500 –> Red

    I just need to replicate this logic in the Formatting Rule dialog box. First, I’ll make the following selections:image

    Next, I’ll set the background color image to white

    image

    Then click OK.

    image

    Next, create another New Rule for the next condition

    image

    Giving you this result.

    image

    Complete the rest of the conditions. It should look like this.

    image

    Which results in this Continuous View

    image

    I can also modify multiple properties for each rule

    image

    image

    Expression Is:

    Comparing the selected control to the value of another control

    Another common  problem is to change formatting of a field base on the value in another field.  Above, in VBA, I formatted the Acct and Desc fields with logic as follows:

    If the check box is checked (True) Then
      the background color of Desc becomes Red
      the text color of Acct becomes Blue

    If the check box is not checked (False) Then
      the background color of Desc becomes Blue
      the text color of Acct becomes Red
    End If

    I can use the Expression Is option to accomplish this.

    Select the field you want to format (Desc) and open the Conditional Formatting editor. Create a New Rule.

    image

    Select Expression Is

    image

    The ellipsis at the end will open the Expression Builder

    Make the following selections:

    image

    image

    image

    The result is [Check14] = True.  I don’t have to use the Expression Builder.  I could just type it in the expression field.  But the Expression Builder is a great tool to investigate the range of expressions that can be used for formatting.

    Then finish the formatting

    image

    Create another rule for the False Condition [Check14] = False, and format it appropriately.

    image

    Repeat the process for field Acct.

    image

    And your formatting is done.

    image

    Field Has Focus

    Determines whether the control is currently selected.

    Lastly, I can format based on whether or not a form control has the focus.  This is great for highlighting the currently selected field.

    image 

    Clicking anywhere in the table will highlight the selected field.  Now, I have to set the conditional formatting for each field, which would be tedious except that Access allows me to set the formatting for multiple fields at the same time.

    In Design View, select all the textboxes in the Detail section.

    image

    Then go to the Conditional Formatting editor. Notice how the selection for formatting says Multiple.

    image

    Create a New Rule and selecting Has Focus  and formatting it as shown

    image

    Click OK here and in the Rules Manager and I’m done.

    Highlight Selected Record

    To highlight the selected record, I do essentially the same thing with a few modifications.

    Add an unbound textbox control (txtBackColor3) to the form and put it behind the other control.  Make sure this unbound control is the same shape as the Detail section. (I’ve shown it below the other text boxes here to illustrate.)

    image

    Then set the Back Style property of the other textboxes to Transparent.

    image

    It also requires one bit of VBA code.  In the OnCurrent event of the form, add this:

    Private Sub Form_Current()
        If Not Me.NewRecord Then
            Me!txtBackColor3.ControlSource = "=[txtGLID] = " & Me!GLID
        Else
            Me!txtBackColor3.ControlSource = "=False"
       End If
    End Sub

    What does this do?

    The textbox that displays the primary key of the record (GLID) is txtGLID. If the value of txtGLID matches the GLID of the underlying recordset, set the value of txtBackColor3 to TRUE. If it’s not, set the value to FALSE.

    Now, I can make a Conditional Format that tests the value of txtBackColor3 and sets the Fill to Green if TRUE.

    image

    Now just make sure the txtBackColor3 control is moved behind the other controls.

    image

    And it’s done.

    image

    If the conditional formatting of the other textboxes are set to green fill when it has the focus (as in Field Has Focus example above), the currently selected field will also be green.  If not, the selected field in the selected row will be white.

    image

    Sample Database

    You can find a sample database illustrating all of these techniques here:

    http://www.rogersaccesslibrary.com/forum/topic375.html