Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««3637383940»»»

The "Numbers" or "Tally" Table: What it is and how it replaces a loop. Expand / Collapse
Author
Message
Posted Monday, January 9, 2012 11:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 16, 2014 7:05 AM
Points: 118, Visits: 226
It isn't staff scheduling/availablity oriented. Its more similar to the example of the various dates that I gave where overlap is entirely possible.
Post #1232699
Posted Friday, April 5, 2013 12:00 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 6:27 AM
Points: 265, Visits: 332
Tally Tables are the bomb, and confirm my own intuitions regarding the power of set-based operations. Many thanks, Jeff!

Now I have another poser for you or anyone else - is there / would there be a way to somehow employ TT's to do the reverse? Meaning roll-up child records into a column of concatenated/delimited values for each parent record?

Disclaimer: I have an Access2010 project I'm trying to sort out, so I don't have recourse to SQL Server CTE or FOR XML Path options...
Post #1439398
Posted Friday, April 5, 2013 12:08 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 16, 2014 7:05 AM
Points: 118, Visits: 226
jjturner (4/5/2013)
Tally Tables are the bomb, and confirm my own intuitions regarding the power of set-based operations. Many thanks, Jeff!

Now I have another poser for you or anyone else - is there / would there be a way to somehow employ TT's to do the reverse? Meaning roll-up child records into a column of concatenated/delimited values for each parent record?

Disclaimer: I have an Access2010 project I'm trying to sort out, so I don't have recourse to SQL Server CTE or FOR XML Path options...


I don't know if you can do that with a TallyTable or if a TT is neccessary. This is a function that should accomplish it. It simply adds the next selected value to the previous. And no, I didn't figure this out myself.

USE [Mercury]
GO

/****** Object: UserDefinedFunction [dbo].[getShowContactsForDepartment_AsString] Script Date: 04/05/2013 14:03:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[getShowContactsForDepartment_AsString]
(
@ShowNumber varchar(8), @ProductionDepartmentId integer
)
RETURNS varchar(500)
AS
BEGIN

DECLARE @names VARCHAR(500)

SET @names = ''

SELECT
@names = @names + ', ' + NameInitials + Case WHEN ScheduleCommentShort Is Null THEN '' ELSE ' ' + ScheduleCommentShort End
FROM
vw_ShowContacts
WHERE
ShowNumber = @ShowNumber AND ProductionDepartmentId = @ProductionDepartmentId
ORDER BY
Id

--Contingent if @names is a zero-length string
IF Len(@names) > 0
BEGIN
SET @names = Right(@names,LEN(@names)-1)
End

RETURN @names

END


GO




Post #1439401
Posted Friday, April 5, 2013 12:17 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 6:27 AM
Points: 265, Visits: 332
Thanks David - that was quick! I'll have a go at translating this into VBA and see what happens.

Although it does look like there's no way around firing a bunch of SELECT statements with a cursor (unless I'm misreading it)...

Cheers,
John
Post #1439409
Posted Friday, April 5, 2013 12:21 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 6:27 AM
Points: 265, Visits: 332
Thanks David - that was quick! I'll have a go at translating this into VBA and see what happens.

Although it does look like there's no way around firing a bunch of SELECT statements with a cursor (unless I'm misreading it)...

Cheers,
John
Post #1439412
Posted Friday, April 5, 2013 12:22 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 6:27 AM
Points: 265, Visits: 332
<obligatory newbie double-post above>
Post #1439413
Posted Friday, April 5, 2013 12:31 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 16, 2014 7:05 AM
Points: 118, Visits: 226
jjturner (4/5/2013)
Thanks David - that was quick! I'll have a go at translating this into VBA and see what happens.

Although it does look like there's no way around firing a bunch of SELECT statements with a cursor (unless I'm misreading it)...

Cheers,
John


Its not a cursor. It was a solution that was presented as an alternative to a cursor. If you're using VBA, I'd guess that you'll have to create the sp on the SQL Server side and then execute the sp from VBA. Using the SELECT @name = @name + [NameLong]... is something that won't be available on the Access side.

However, that being said you could create a recordset and then loop through each record to create the string, however the performance difference between that approach and the one I posted will be quite noticable.
Post #1439418
Posted Friday, April 5, 2013 12:58 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 6:27 AM
Points: 265, Visits: 332
Ok, thanks for the confirm - now I'll have my work cut out for me trying to build a solution with ADO slinging around recordsets between Access and SQL Server!

Post #1439430
Posted Friday, April 5, 2013 1:42 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 16, 2014 7:05 AM
Points: 118, Visits: 226
jjturner (4/5/2013)
Ok, thanks for the confirm - now I'll have my work cut out for me trying to build a solution with ADO slinging around recordsets between Access and SQL Server!



This code will update a pass through query that relies on a stored procedure that requires parameters. The idea is that you would create the sp in SQL Server, then create the pass through query in Access. Your code would then call this sub to update the pass through query to change the parameters before the pass through query is executed. This might be neccessary if you have form based on the pass through query in which case it would be called on the Form_Load event. Or it might be executed before you open a Select statement on the pass through query via .OpenRecord set.

strQryDefName - Name of the Access object that represents the passthrough query
strStatement - Actual statement to the update the passthrough query to, this must include any parameters such as...


Sample usage...
This code builds the parameters passed to the UpdatePassthroughQuery sub by looking up the RecordSource from a table (eg: view_UsageByDate) and then builds the parameters by looping through the controls on a form to determine if a value has been entered. It is a rather nifty approach to supplying criteria driven reports where the criteria is not known and needs to be flexible </tooHorn>.

If Me.pg1_lstReports.Column(3) = True Then
'Lookup the SQL Server view/recordsource to us
strRecordSourceName = Nz(DLookup("txtRecordSourceName", "qry_sp_getReportDefinitions", "[lngId] = " & [Forms]![frmReports]![pg1_lstReports]), "")
strRecordSourceStatement = Nz(DLookup("txtRecordSourceStatement", "qry_sp_getReportDefinitions", "[lngId] = " & [Forms]![frmReports]![pg1_lstReports]), "")
'How to test if either are invalid
'Loop through the criteria for the report, check if it exists in strRecordSourceStatement and replace
'Not certain how I feel about this over all
CriteriaArray = Split(Me.pg1_lstReports.Column(2), ";")
For i = 0 To UBound(CriteriaArray)
ControlSetting = Split(CriteriaArray(i), "=")
If InStr(1, strRecordSourceStatement, ControlSetting(0)) > 0 Then
strRecordSourceStatement = Replace(strRecordSourceStatement, ControlSetting(0), Me.Controls(ControlSetting(0)))
End If
Next i
Call updatePassThroughQuery(strRecordSourceName, strRecordSourceStatement)
End If



Public Sub updatePassThroughQuery(strQryDefName As String, strStatement As String)

'Updates a pass through query to allow parameters to be passed in
'For ease of development the pass through is created if it doesn't exist
'http://support.microsoft.com/kb/131534

Dim MyDb As Database
Dim MyQ As QueryDef
Dim QryDefExists As Boolean
Dim i As Integer

Set MyDb = CurrentDb()

QryDefExists = False

For i = 0 To MyDb.QueryDefs.Count - 1
Debug.Print MyDb.QueryDefs(i).Name
If MyDb.QueryDefs(i).Name = strQryDefName Then QryDefExists = True
Next i

If QryDefExists Then
Set MyQ = MyDb.QueryDefs(strQryDefName)
MyQ.SQL = strStatement
MyQ.Close
MyDb.Close
Set MyQ = Nothing
Else
Set MyQ = MyDb.CreateQueryDef(strQryDefName)
MyQ.Connect = "ODBC;DSN=TrailerManagementSystem;Description=Connection Trailer Management System SQL Server;Trusted_Connection=Yes"
MyQ.ReturnsRecords = True
MyQ.SQL = strStatement
MyQ.Close
MyDb.Close
Set MyQ = Nothing
End If

Set MyQ = Nothing
Set MyDb = Nothing

End Sub


Post #1439452
Posted Friday, April 5, 2013 2:21 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 6:27 AM
Points: 265, Visits: 332
hmm... seeing that my data is in Access and the recordsets are pretty light, I may opt to keep everything local with the VBA cursor on a custom function.
Otherwise I'd have to somehow pass the recordset from Access into the whole sp call. But thanks again for laying the groundwork on the ADO parameter piece!

Cheers,
John
Post #1439474
« Prev Topic | Next Topic »

Add to briefcase «««3637383940»»»

Permissions Expand / Collapse