• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 606
  • Last Modified:

SQL Server 2008 R2 Hierarchy Query

I am very new at SQL and I need to make a Hierarchy Query between two files because I have a grid that will band each group together.  The grid can colapse or expand.  But the problem is I need a Hierachy query for this to work and I do not know how to make one.
And I am not sure of what questions to ask to get help.

Can any one help me with this eventhough this is vagque.

Band (0) = Mary Jane (Table TRANSCRIBERS)
                      Band(1) = File 1  (Table AUDIOS)
                      Band(1) = File 2  (Table AUDIOS)
                      Band(1) = File 3  (Table AUDIOS)
Band(0)  = Tom Smith (Table TRANSCRIBERS)
                      Band(1) = File 1  (Table AUDIOS)
                      Band(1) = File 2  (Table AUDIOS)
                      Band(1) = File 3  (Table AUDIOS)
so on and so on


The below Tables are relateted via TRANSCRIBERS.TRIPID and AUDIOS.TRNSID

Dim what As String
what = "PickUp"


MystrSQL = "SELECT TRANSCRIBERS.[Name], AUDIOS.[TapeID], AUDIOS.[Case], AUDIOS.[Regarding], AUDIOS.[Unit], AUDIOS.[Detective], AUDIOS.[Priority], AUDIOS.[Rush], AUDIOS.[Language], AUDIOS.[Audio Length], AUDIOS.[Due on], AUDIOS.[TRNSID]" & _
'" FROM AUDIOS INNER JOIN TRANSCRIBERS ON AUDIOS.[TRNSID] = TRANSCRIBERS.[TRIPID]" & _
'" Where(((AUDIOS.[Audio Status]) = '" & what & "'))" & _
'" ORDER BY AUDIOS.[TRNSID] DESC;"
0
MKadric
Asked:
MKadric
  • 24
  • 6
  • 5
  • +3
1 Solution
 
sameer_goyalCommented:
Hey there,

Can you provide some sample data as it would be in the tables that you have?

I am sure with that, I should be able to help you out. Also, I am interested to see the structure of your tables
0
 
MKadricAuthor Commented:
I would show you data but I do not know how get a variable to work in SQl.  
Tables are attached.

SELECT TRANSCRIBERS.[Name], AUDIOS.[TapeID], AUDIOS.[Case], AUDIOS.[Regarding], AUDIOS.[Unit], AUDIOS.[Detective], AUDIOS.[Priority], AUDIOS.[Rush], AUDIOS.[Language], AUDIOS.[Audio Length], AUDIOS.[Due on], AUDIOS.[TRNSID]
FROM AUDIOS INNER JOIN TRANSCRIBERS ON AUDIOS.[TRNSID] = TRANSCRIBERS.[TRIPID]
WHERE (((AUDIOS.[Audio Status])= "Pickup" ))
ORDER BY AUDIOS.[TRNSID] DESC



Msg 207, Level 16, State 1, Line 3
Invalid column name 'Pickup'.
0
 
sameer_goyalCommented:
instead of "Pickup", use 'Pickup'
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
MKadricAuthor Commented:
Wow Thank you

Attached you will find the data
0
 
sameer_goyalCommented:
hey,

sorry but i cant find any attachment. can you pls post the data again?
0
 
MKadricAuthor Commented:
Here you go
0
 
sameer_goyalCommented:
hi there,

I am not able to find the attachment yet. After browsing the file to attach, are you clicking on the attach button provided?
0
 
MKadricAuthor Commented:
USE [xxx]
GO

/****** Object:  Table [dbo].[AUDIOS]    Script Date: 09/18/2012 07:20:02 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[AUDIOS](
      [TapeID] [int] IDENTITY(1,1) NOT NULL,
      [Case] [nvarchar](15) NULL,
      [Detective] [nvarchar](40) NULL,
      [Badge] [nvarchar](10) NULL,
      [Unit] [nvarchar](2) NULL,
      [Interviewed] [nvarchar](40) NULL,
      [Regarding] [nvarchar](100) NULL,
      [Arrest] [bit] NOT NULL,
      [Recording File Name] [nvarchar](255) NULL,
      [Submitted On] [datetime] NULL,
      [Needed by] [datetime] NULL,
      [Recorded On] [datetime] NULL,
      [Language] [nvarchar](10) NULL,
      [Audio Length] [nvarchar](20) NULL,
      [Recording Secs] [nvarchar](10) NULL,
      [Recording Bytes] [nvarchar](15) NULL,
      [TRNSID] [int] NULL,
      [Transcriber] [nvarchar](30) NULL,
      [Due on] [datetime] NULL,
      [Out on] [datetime] NULL,
      [Received on] [datetime] NULL,
      [Total Words] [smallint] NULL,
      [Total Pages] [smallint] NULL,
      [Last Paid On] [datetime] NULL,
      [Last Update] [datetime] NULL,
      [By] [nvarchar](30) NULL,
      [Comments] [nvarchar](255) NULL,
      [Priority] [smallint] NULL,
      [Audio Status] [nvarchar](10) NULL,
      [Rush] [bit] NULL,
      [Changed] [bit] NULL,
      [MSWORD FileName] [nvarchar](255) NULL,
      [MSWORD Words] [smallint] NULL,
      [MSWORD Pages] [smallint] NULL,
      [Grace Period Days] [smallint] NULL,
      [Notified] [bit] NULL,
      [Email Sent On] [datetime] NULL,
      [Marked Paid On] [datetime] NULL,
      [Transcription Status] [nvarchar](10) NULL,
PRIMARY KEY CLUSTERED
(
      [TapeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
      [Case] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
0
 
MKadricAuthor Commented:
USE [xxx]
GO

/****** Object:  Table [dbo].[TRANSCRIBERS]    Script Date: 09/18/2012 07:21:01 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TRANSCRIBERS](
      [TRIPID] [int] IDENTITY(1,1) NOT NULL,
      [Name] [nvarchar](40) NULL,
      [Work Phone] [nvarchar](14) NULL,
      [Home Phone] [nvarchar](14) NULL,
      [Cell Phone] [nvarchar](14) NULL,
      [Email] [nvarchar](40) NULL,
      [Capabilities] [nvarchar](255) NULL,
      [Contract] [nvarchar](255) NULL,
      [Availiable Time] [nvarchar](255) NULL,
      [City] [nvarchar](50) NULL,
      [Address] [nvarchar](120) NULL,
      [State] [nvarchar](2) NULL,
      [Zip] [nvarchar](10) NULL,
      [Comments] [nvarchar](255) NULL,
      [Email Sent On] [datetime] NULL,
      [Marked Paid On] [datetime] NULL,
      [Statement Through] [datetime] NULL,
      [Active] [bit] NULL,
PRIMARY KEY CLUSTERED
(
      [TRIPID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
      [Name] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
0
 
MKadricAuthor Commented:
Data

Data
0
 
sameer_goyalCommented:
Great. Thanks for posting the schema. Now that I have the schema, tell me or give me a sample of the output that you need

I am not asking for a result from the sql query but just a skeleton of what kind of output are you looking at from the above 2 tables?
0
 
MKadricAuthor Commented:
Hmm
  Not sure how to answer... Just need a Hierarchy Query between two files file so that my control will see that there are two sources  it will produce a second band automatically for me.  Like Person A has 15 files to view.  It’s a One to many relationship but needs to be Hierarchy Query. Right now I have a single dimensional flat file I guess.  The documentation on the control indicated that I need a Union or shape or maybe a group by.  The control will figure out if it’s two dimensional.  I am sorry I just do not know the question to ask.
0
 
Jim P.Commented:
Try something like this:

SELECT	t.[Name], a.[TapeID], a.[Case], a.[Regarding], a.[Unit],
		a.[Detective], a.[Priority], a.[Rush], a.[Language], 
		a.[Audio Length], a.[Due on], a.[TRNSID],
		ROW_NUMBER() OVER ( [ PARTITION BY a.[TRNSID] ORDER BY a.[Rush], a.[TapeID] ) AS Seq_Num
FROM	TRANSCRIBERS t
LEFT JOIN AUDIOS a
	ON	a.[TRNSID] = t.[TRIPID]
WHERE a.[Audio Status])= 'Pickup' ))
ORDER BY a.[TRNSID] DESC

Open in new window


It will give a row number for each transcriber based on the TRNSID.
0
 
hnasrCommented:
"Not sure how to answer... Just need a Hierarchy Query between two files file ...  Like Person A has 15 files to view."

Not sure either, but try:

Table A(a)
a
1
2

Table B(a, b)
a      b
1      1
1      2
2      1

select A.a, ('has ' + cast(count(B.b) as varchar(25)) + ' b''s') AS bCount from A
INNER JOIN B On A.a=B.a
GROUP BY A.a;

a      bCount
1      has 2 b's
2      has 1 b's
0
 
MKadricAuthor Commented:
jarmod101 Thank you for your help but Please see the error attached when i run the below code.

This is my modified sql code.

MystrSQL = "SELECT  t.[Name], a.[TapeID], a.[Case], a.[Regarding], a.[Unit]," & _
       " a.[Detective], a.[Priority], a.[Rush], a.[Language]," & _
       " a.[Audio Length], a.[Due on], a.[TRNSID]," & _
       " ROW_NUMBER() OVER ([PARTITION BY a.[TRNSID] ORDER BY a.[Rush], a.[TapeID]) AS Seq_Num " & _
" FROM    TRANSCRIBERS t" & _
" LEFT JOIN AUDIOS a" & _
" ON  a.[TRNSID] = t.[TRIPID]" & _
" WHERE a.[Audio Status])= 'Pickup' ))" & _
" ORDER BY a.[TRNSID] DESC;"

VB6 Error
0
 
MKadricAuthor Commented:
hnasr Thank you for your help but Please see the error attached when i run the below code.

This is my modified sql code.


MystrSQL = "select A.a, ('has ' + cast(count(B.b) as varchar(25)) + ' b''s') AS bCount from A" & _
" INNER JOIN B On A.a=B.a" & _
" GROUP BY A.a;"

VB 6 Error
0
 
hnasrCommented:
My code works with SSMS.
Try it in SSMS and see if it works with you.
Next step adjustment for VB6 is required.
0
 
Jim P.Commented:
I had an extra bracket in it that wasn't needed.

SELECT	t.[Name], a.[TapeID], a.[Case], a.[Regarding], a.[Unit],
		a.[Detective], a.[Priority], a.[Rush], a.[Language], 
		a.[Audio Length], a.[Due on], a.[TRNSID],
		ROW_NUMBER() OVER (PARTITION BY a.[TRNSID] ORDER BY a.[Rush], a.[TapeID] ) AS Seq_Num
FROM	TRANSCRIBERS t
LEFT JOIN AUDIOS a
	ON	a.[TRNSID] = t.[TRIPID]
WHERE a.[Audio Status])= 'Pickup' ))
ORDER BY a.[TRNSID] DESC

Open in new window


I do agree with hnasr. You should bring up the SQL Management Studio and get the query(s) working first. Then go for the VB coding as the next step.
0
 
MKadricAuthor Commented:
SELECT      t.[Name], a.[TapeID], a.[Case], a.[Regarding], a.[Unit],
            a.[Detective], a.[Priority], a.[Rush], a.[Language],
            a.[Audio Length], a.[Due on], a.[TRNSID],
            ROW_NUMBER() OVER (PARTITION BY a.[TRNSID] ORDER BY a.[Rush], a.[TapeID] ) AS Seq_Num
FROM      TRANSCRIBERS t
LEFT JOIN AUDIOS a
      ON      a.[TRNSID] = t.[TRIPID]
WHERE a.[Audio Status])= 'Pickup' ))
ORDER BY a.[TRNSID] DESC

Error
Msg 4145, Level 15, State 1, Line 8
An expression of non-boolean type specified in a context where a condition is expected, near ')'.

and if I remove ")" then I get error

Msg 4145, Level 15, State 1, Line 8
An expression of non-boolean type specified in a context where a condition is expected, near ')'.

and then if I remove ")" I get errorMsg 102, Level 15, State 1, Line 8
Incorrect syntax near ')'.

Then if I remove ")" the sql runs and I get two records

However if I run the same SQL in  VB I get ZERO RECORDS

MystrSQL = "SELECT  t.[Name], a.[TapeID], a.[Case], a.[Regarding], a.[Unit]," & _
       " a.[Detective], a.[Priority], a.[Rush], a.[Language]," & _
       " a.[Audio Length], a.[Due on], a.[TRNSID]," & _
       " ROW_NUMBER() OVER (PARTITION BY a.[TRNSID] ORDER BY a.[Rush], a.[TapeID] ) AS Seq_Num" & _
" FROM    TRANSCRIBERS t" & _
" LEFT JOIN AUDIOS a" & _
" ON  a.[TRNSID] = t.[TRIPID]" & _
" WHERE a.[Audio Status]= 'Pickup' " & _
" ORDER BY a.[TRNSID] DESC;"
0
 
MKadricAuthor Commented:
hnasr

select A.a, ('has ' + cast(count(B.b) as varchar(25)) + ' b''s') AS bCount from A
INNER JOIN B On A.a=B.a
GROUP BY A.a;

I get error
Msg 208, Level 16, State 1, Line 1
Invalid object name 'A'.
0
 
hnasrCommented:
Does this work?

SELECT A.a, B.b
FROM A
INNER JOIN B On A.a=B.a;
0
 
MKadricAuthor Commented:
hnasr I do not understand where you get A.a or B.b... my tables are AUDIOS and AUDIOS

when I put your code in I get error Msg 208, Level 16, State 1, Line 1
Invalid object name 'A'.... because I do not have a table A or a field a or a table b or a field b... I must be realy confused.
0
 
hnasrCommented:
This is the reason you get the error, you do not have the tables A and B. I gave a simple example of two tables A(a nvarchar(25)), B(a nvarchar(25), b nvarchar(25)).

But you can use your tables and the respective primary key and the foreign key.

ON table1.primaryKey = table2.ForeignKey
0
 
Jim P.Commented:
hnasr,

It appears that the asker is new to SQL. I think it would be better if you took the time to give her examples that are based off her real tables and names.
0
 
Jim P.Commented:
Now that we have the query working, the next step is to see if the VB is connecting and getting data back at all.

Do you get rows back if you do a simple

MystrSQL = "select * FROM    TRANSCRIBERS;"

Open in new window

0
 
MKadricAuthor Commented:
jimpen Yes, I get all my records on all my queries....If I do just a simple select I get all 5 records.
0
 
Jim P.Commented:
So in the SQL Studio you get 2 but from VB you get none. What about if there's no where clause?

MystrSQL = "SELECT  t.[Name], a.[TapeID], a.[Case], a.[Regarding], a.[Unit]," & _
       " a.[Detective], a.[Priority], a.[Rush], a.[Language]," & _
       " a.[Audio Length], a.[Due on], a.[TRNSID]," & _
       " ROW_NUMBER() OVER (PARTITION BY a.[TRNSID] ORDER BY a.[Rush], a.[TapeID] ) AS Seq_Num" & _
" FROM    TRANSCRIBERS t" & _
" LEFT JOIN AUDIOS a" & _
" ON  a.[TRNSID] = t.[TRIPID]" & _
" ORDER BY a.[TRNSID] DESC;" 

Open in new window

0
 
MKadricAuthor Commented:
No records, I even made I single line of code to make sure.

Mater of fact in all of your cases i get a -1 not a zero records.  I am sorry I should have said that instead of zero records.
0
 
MKadricAuthor Commented:
I have spent most of the day investigating.. And keep coming up with the Shape SQL but I do not know how to write it.


This is what I think it should be but it won't run in MSSMS or My Program

In MSSMS
-------------------------------------------
SHAPE {Select TRANSCRIBERS.TRIPID,TRANSCRIBERS.Name from TRANSCRIBERS} as TRANSCRIBERS APPEND ({Select * from AUDIOS} Relate TRIPID to TRNSID) as AUDIOS

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Select'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '}'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '}'.


In Program
---------------------------------------------
Dim cn As New ADODB.Connection
Set cn = dbConnection(MystrDBType, MystrDBName, MystrUserID, MystrPassword)
Dim RsTemp As New ADODB.Recordset

MystrSQL = "SHAPE {Select TRANSCRIBERS.TRIPID,TRANSCRIBERS.Name from TRANSCRIBERS} as TRANSCRIBERS APPEND ({Select * from AUDIOS} Relate TRIPID to TRNSID) as AUDIOS;"

Set RsTemp = CreateRecordSet(cn, RsTemp, EXEC_SQL, MystrSQL)
0
 
hnasrCommented:
Sorry, I don't have VB6 installed.
If you can recreate it using visual basic 2010 Express, I might be able to help. The issue now is Basic problem, as you mentioned that with SSMS query works fine.
0
 
MKadricAuthor Commented:
Can someone help me with the Shape command in SQL Server 2008 R2
0
 
Mark WillsTopic AdvisorCommented:
Yes, the SHAPE command is an ADO query, not really a SQL Server 2008 T-SQL query.

Have a look at : http://support.microsoft.com/kb/189657

Now, if you have a couple of moments, type into a spreadsheet a couple of examples of input data and expected results.

Not sure if you need a *real* heirarchy or more of a straight forward master / detail type query.

And, really best idea is to go into SSMS and open a query window and try your query there until you are happy with it and then put that into your code...

so, lets start with your current query (but all columns) that you posted previously :

SELECT AUDIOS.*, TRANSCRIBERS.*
FROM AUDIOS 
INNER JOIN TRANSCRIBERS ON AUDIOS.[TRNSID] = TRANSCRIBERS.[TRIPID]
WHERE AUDIOS.[Audio Status]= 'Pickup' 
ORDER BY AUDIOS.[TRNSID] DESC

Open in new window


Then, once happy with that then start being selective about the columns you really need :

SELECT TRANSCRIBERS.[Name], AUDIOS.[TapeID], AUDIOS.[Case], AUDIOS.[Regarding],
             AUDIOS.[Unit], AUDIOS.[Detective], AUDIOS.[Priority], AUDIOS.[Rush], 
             AUDIOS.[Language], AUDIOS.[Audio Length], AUDIOS.[Due on], AUDIOS.[TRNSID]

FROM AUDIOS 
INNER JOIN TRANSCRIBERS ON AUDIOS.[TRNSID] = TRANSCRIBERS.[TRIPID]

WHERE AUDIOS.[Audio Status]= 'Pickup'
ORDER BY AUDIOS.[TRNSID] DESC

Open in new window


Now, if you are getting results, then we need to understand the differences to your expected results... That is where the spreadsheet comes into it.
0
 
QlemoC++ DeveloperCommented:
Note that with ADO, a record count of -1 means "unknown" or "not appliable".
Whether you get the actual record count or not from the SQL depends on the cursor you use. A forward, read-only, client based cursor will not be able to tell you how many rows are found, you'll need to parse thru the recordset until you hit an end of data (RsTemp.EOF is true).
I agree to what Mark told - you should first try to assemble your results in a query tool, like SSMS, and then migrate that to a VB solution. Else you have to fight with different issues, and it is really getting confusing.
0
 
MKadricAuthor Commented:
Thank you mark_wills, The flat queries allways gives me the records I want.
I need a Hierarchy Query which will allow the data to be banded.  

Exmple how it would appear in a VB6 Data Report, If I use a flat query in the Data Report
I get the report but it is flat.  

EXAMPLE FLAT
***************************
Transcriber "A" Detail record (1)
Transcriber "A" Detail record (2)
Transcriber "B" Detail record (1)
Transcriber "B" Detail record (2)
Transcriber "B" Detail record (3)
Transcriber "C" Detail record (1)

Example of Banded (Hierarchy) Data Report
******************************
Transcriber "A"
-----------------------------------------------------------------------------------------------------                    
                    Detail record (1)
                    Detail record (2)

 Transcriber "B"
-----------------------------------------------------------------------------------------------------
                   Detail record (1)
                   Detail record (2)
                   Detail record (3)

Transcriber "C"
-----------------------------------------------------------------------------------------------------
                  Detail record (1)
0
 
MKadricAuthor Commented:
mark_willsPosted
first query  I got 2 records
Second query I got 2 records

Both queries produce a flat query

Please read above
0
 
Mark WillsTopic AdvisorCommented:
Hi,

If you really want to use the SHAPE query, then the KB Article is a pretty good place to start.

Now that you have your underlying query sorted out, you basically use the different tables in the shape ie, you basically SHAPE transcribers and APPEND audio. Which is a bit different from how that JOIN was originally suggested... But not to worry if it is an inner join...

But normally for reporting purposes (even in vb6) I would be inclined to use the flat query and use a reporting tool with your transcriber.username as a group band.

Dim cnConnx As New ADODB.Connection
Dim rsAudio As New ADODB.Recordset
 
rsAudio.Open "SHAPE {Select TRANSCRIBERS.[Name],TRANSCRIBERS.[TRIPID] from TRANSCRIBERS} as TRANSCRIBERS " _
           & "APPEND ({Select * from AUDIOS} " _ 
           & "RELATE TRIPID to TRNSID) as AUDIOS", cnConnx

Open in new window

   

Now, we do assume that the cnConnx is establised first, just included because of the OPEN (dont have to do it that way).

BUT, it is vitally important that you use MSDataShape in your connection string otherwise it simply will not understand what the heck that shape command is all about... Maybe that was your only issue in the code you posted previously. The SHAPE command looks pretty good.

For more details, have a look at : http://msdn.microsoft.com/en-us/library/windows/desktop/ms681565(v=vs.85).aspx and there are examples in there too. Might even be worth starting from the "top" at : http://msdn.microsoft.com/en-us/library/windows/desktop/ms676186(v=vs.85).aspx
0
 
MKadricAuthor Commented:
Sucess I got 2 records,

But I do not know the field names to use.  I've tried the fields in the Audio table but I get an errors saying field does not exsists.

Below are the properties for the Data Report TextBox.
Properties Data Field
Properties Data member

Im I soposed to put something in these properties?


SQLPassword = INIRead("conn", "Password", wAppPathBIN$ & "\conn.ini")
SQLUserID = INIRead("conn", "UserID", wAppPathBIN$ & "\conn.ini")
SQLDataSource = INIRead("conn", "DataSource", wAppPathBIN$ & "\conn.ini")

         
Dim cnConnx As New ADODB.Connection
Dim rsAudio As New ADODB.Recordset
 
 cnConnx.Provider = "MSDataShape"
 cnConnx.Open "Data Provider=SQLNCLI10.1;Password=" & SQLPassword & ";User ID=" & SQLUserID & ";Initial Catalog=" & MystrDBName & ";Data Source=" & SQLDataSource
 
 
rsAudio.Open "SHAPE {Select TRANSCRIBERS.[Name],TRANSCRIBERS.[TRIPID] from TRANSCRIBERS} as TRANSCRIBERS " _
           & "APPEND ({Select * from AUDIOS} " _
           & "RELATE TRIPID to TRNSID) as AUDIOS", cnConnx
           

Debug.Print rsAudio.RecordCount
Set drPickup.DataSource = rsAudio
drPickup.DataMember = rsAudio.DataMember

drPickup.Show
0
 
MKadricAuthor Commented:
I still need help with the above so I can get a report, but I am so excited I put the same code into my grid and wow I finally got a banded grid....THANKS SO MUCH

Now I need to
Put a where clause and and order by in the shape SQL

WHERE AUDIOS.[Audio Status]= 'Pickup'
WHERE AUDIOS.[Last Update] = A DATE ENTERED
ORDERED BY TRANSCRIBERS.[TRIPID]

Can you help me with?
0
 
hnasrCommented:
Thanks for your question, it allowed me to do some useful homework. To get the SHAPE command in access 2010, add reference to Microsoft ActiveX Data Objects 6.1 Library

Add the Where And the Order By with each respective select statement in the SHAPE command.

Example:
SHAPE{select * from A where aid='1' AND adesc ='new student' order by adesc} As rsA APPEND ({select * from B WHERE B.aid ='111' Order By bdesc} AS rsB  RELATE aid TO aid)

Example of usage in access:

'Table A(aid, adesc, ....)
'Table B(aid, bid, bdesc, ...)

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String

     Set cn = New ADODB.Connection
     Set rs = New ADODB.Recordset
     sql = "SHAPE{select * from A where aid='1' order by adesc}" _
            & " APPEND ({select * from B} AS rsB " _
            & " RELATE aid TO aid)"
           
     cn.Provider = "MSDataShape"
   
     Set rs = cn.Execute(sql)

The Microsoft article above " How To Use the ADO SHAPE Command" was useful.
0
 
MKadricAuthor Commented:
Thanks for the information above but I am not using Access I am using SQL Server 2008 R2  and as of last night I got the below code to work using your starting point...I can't express how greatfull I am.  I have been working on this problem for two weeks.

I have one more question... in the below code I need to add

[Out On] IsNull  in the Where clause

I keep getting a syntax error.
It does not like IsNull.  This is a date field and I only want the records that
does not have a date and looking at the records on the SQL server the field is
NULL so I want the records only when this field is null.


Thanks again
------------------------THiS CODE WORKS-----------------------------------------------------------

SQLPassword = INIRead("conn", "Password", wAppPathBIN$ & "\conn.ini")
SQLUserID = INIRead("conn", "UserID", wAppPathBIN$ & "\conn.ini")
SQLDataSource = INIRead("conn", "DataSource", wAppPathBIN$ & "\conn.ini")

         
Dim cnConnx As New ADODB.Connection
Dim rsAudio As New ADODB.Recordset
 
 cnConnx.Provider = "MSDataShape"
 cnConnx.Open "Data Provider=SQLNCLI10.1;Password=" & SQLPassword & ";User ID=" & SQLUserID & ";Initial Catalog=" & MystrDBName & ";Data Source=" & SQLDataSource
 
 
 rsAudio.Open "SHAPE {Select TRANSCRIBERS.[Name],TRANSCRIBERS.[TRIPID] as ID from TRANSCRIBERS} as TRANSCRIBERS " _
           & "APPEND ({Select * from AUDIOS " _
           & "WHERE [Last Update] < '10/04/2012' AND [Audio Status] = '" & what & "'} " _
           & "RELATE ID to TRNSID) as AUDIOS", cnConnx
0
 
Mark WillsTopic AdvisorCommented:
In the select * from audios you should be able to add in the criteria 'and [out on] is null'

the isnull is a function to check (replace) null column e.g. isnull([out on],'2099-12-31') will substitute 31st Dec 2099 when [out on] is null.


(well, the above assumes that [out on] is part of the audios select :) )
0
 
MKadricAuthor Commented:
mark_wills I hate to bother you again but I had to change by DB structure.

I now have three tables involved. I split the Table Audio into two tables. TRANSCRIPTION transcription information and Audio file information.
A decision was made that a TRANSCRIPTION can have as many Audio files as needed.
The TRANSCRIPTION has the TRANSCRIBERS ID
The AUDIOS has the TRANSCRIPTION ID (and there could be more than one record)

The Hierarchy  the tables would be

TRANSCRIBERS (TRIPID)
TRANSCRIPTION (TransID)  Transcribers Record ID(TRNSID)
AUDIOS(AudioID)        Transcirption Record ID(TransID)

I need to add the last Table AUDIOS and on the field [Audio Status] in AUDIOS a where clause like this...     [Audio Status] = '" & What & "'

CURRENT CODE:
rsAudio.Open "SHAPE {Select TRANSCRIBERS.[Name],TRANSCRIBERS.[TRIPID] as ID from TRANSCRIBERS} as TRANSCRIBERS " _
           & "APPEND ({Select * from TRANSCRIPTIONS " _
           & "WHERE [Out On] Is Null AND [Last Update] < '" & UseThisDate & "'} " _
           & "RELATE ID to TRNSID) as TRANSCRIPTIONS", cnConnx

Thank you
0
 
Mark WillsTopic AdvisorCommented:
Well, the first APPEND is not a simple select, but another embedded SHAPE command

e.g. (and only an example only)

SHAPE  {SELECT * from TRANSCRIBERS} as TRANSCRIBERS
APPEND ((SHAPE  {select * from TRANSCRIPTIONS} 
         APPEND ({select * from AUDIOS} AS AUDIOS
         RELATE audioid TO transid)) AS TRANSCRIPTIONS
RELATE ID to TRNSID)

Open in new window


But this is really getting into another question methinks...

Have a play with it and see if you can embed that new "shape"
0
 
MKadricAuthor Commented:
mark_wills Thanks so much.. The code below iis what I finally ened up using

Where would the where clauses go?

The field  [Last Update]  is in table TRANSCRIPTIONS
WHERE [Out On] Is Null AND [Last Update] < '" & UseThisDate & "'} " 

The field    [Audio Status] is in the table AUDIO
 [Audio Status] = '" & What & "'


rsAudio.Open "SHAPE  {SELECT * from TRANSCRIBERS} as TRANSCRIBERS " _
& "APPEND ((SHAPE  {select * from TRANSCRIPTIONS} " _
& "APPEND ({select * from AUDIOS} AS AUDIOS " _
& "RELATE TransID TO TransID)) AS TRANSCRIPTIONS " _
& "RELATE TRIPID to TRNSID)", cnConnx
0
 
MKadricAuthor Commented:
I came up with the answer above....
Thanks again for all your help.

I have one more question

The fields below are used for the relationships, but I do not want them on my report.  Is it possible to NOT show on report?

TRANSCRIBERS.[TRIPID] as ID
TRANSCRIPTIONS.[TRNSID]

rsAudio.Open "SHAPE  {SELECT TRANSCRIBERS.[Name],TRANSCRIBERS.[TRIPID] as ID from TRANSCRIBERS} as TRANSCRIBERS " _
& "APPEND ((SHAPE  {select TRANSCRIPTIONS.[TransID] as TranscriptionID, TRANSCRIPTIONS.[TRNSID] as TranscriberID, TRANSCRIPTIONS.[Case] , TRANSCRIPTIONS.[Regarding], TRANSCRIPTIONS.[Unit], TRANSCRIPTIONS.[Detective], TRANSCRIPTIONS.[Priority], TRANSCRIPTIONS.[Rush], TRANSCRIPTIONS.[Due on] from TRANSCRIPTIONS WHERE [Out On] Is Null AND [Last Update] < '" & UseThisDate & "'} " _
& "APPEND ({select * from AUDIOS WHERE [Audio Status] = '" & What & "'} AS AUDIOS " _
& "RELATE TranscriptionID TO TransID)) AS TRANSCRIPTIONS " _
& "RELATE ID to TranscriberID)", cnConnx
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 24
  • 6
  • 5
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now