SQL Query to transform/group data by Date

Hi, 

I have a large Access table with data organised as follows: 

Field1: Code
Field2: Date
Field3: Value1
Field4: Value2

There are seveal different codes and therefore duplicate dates. I'd like to
run a query to bring back each code grouped by date and so put the codes
along the top as feilds. For example the query below brings back the
following data for two codes.

SELECT field2, field1, field3
FROM Data
WHERE field1 In ('LLOY','RSA');

21/01/05, LLOY, 12454
22/01/05, LLOY, 31541
21/01/05, RSA, 21241
22/01/05, RSA, 12414
 
Instead I want the data to look like this: 

Date         LLOY     RSA 
21/01/05   12454    21241
22/01/05   31541    12414

Would this be easy to do? 

Thanks, 

Lucas

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200708/1

0
Lucas_london
8/6/2007 4:57:03 PM
access.queries 6343 articles. 1 followers. Follow

6 Replies
892 Views

Similar Articles

[PageSpeed] 14

A crosstab query will do it.
-- 
KARL DEWEY
Build a little - Test a little


"Lucas_london via AccessMonster.com" wrote:

> Hi, 
> 
> I have a large Access table with data organised as follows: 
> 
> Field1: Code
> Field2: Date
> Field3: Value1
> Field4: Value2
> 
> There are seveal different codes and therefore duplicate dates. I'd like to
> run a query to bring back each code grouped by date and so put the codes
> along the top as feilds. For example the query below brings back the
> following data for two codes.
> 
> SELECT field2, field1, field3
> FROM Data
> WHERE field1 In ('LLOY','RSA');
> 
> 21/01/05, LLOY, 12454
> 22/01/05, LLOY, 31541
> 21/01/05, RSA, 21241
> 22/01/05, RSA, 12414
>  
> Instead I want the data to look like this: 
> 
> Date         LLOY     RSA 
> 21/01/05   12454    21241
> 22/01/05   31541    12414
> 
> Would this be easy to do? 
> 
> Thanks, 
> 
> Lucas
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200708/1
> 
> 
0
Utf
8/6/2007 5:44:00 PM
Thanks, 

I did the crosstab query and it works fine. A couple of questions.

Do you know if I can use the same syntax below to create the same result in
other applications e.g SQL Server, oracle etc? I've never come accross the
Transform function or the pivot table function. Is this unique to access?

TRANSFORM Avg(Field3) AS AvgOfField3
SELECT Field2
FROM Data
GROUP BY Field2
PIVOT Field1;

The reason why I ask is, I have a much bigger dataset that I need to do this
for, but Access has a limit in terms of  the number of columns it can display
but I have 600. Or to get around this, does anyone know if it is possible to
write the output from a crosstab query in access directly to a text file?
Then I could just import the file into another database to work on.

Thanks

Lucas

Lucas_london wrote:
>Hi, 
>
>I have a large Access table with data organised as follows: 
>
>Field1: Code
>Field2: Date
>Field3: Value1
>Field4: Value2
>
>There are seveal different codes and therefore duplicate dates. I'd like to
>run a query to bring back each code grouped by date and so put the codes
>along the top as feilds. For example the query below brings back the
>following data for two codes.
>
>SELECT field2, field1, field3
>FROM Data
>WHERE field1 In ('LLOY','RSA');
>
>21/01/05, LLOY, 12454
>22/01/05, LLOY, 31541
>21/01/05, RSA, 21241
>22/01/05, RSA, 12414
> 
>Instead I want the data to look like this: 
>
>Date         LLOY     RSA 
>21/01/05   12454    21241
>22/01/05   31541    12414
>
>Would this be easy to do? 
>
>Thanks, 
>
>Lucas

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200708/1

0
Lucas_london
8/7/2007 10:05:30 AM
As far as I know the Cross tab query is unique to Access.  MS SQL may have 
something like it in the later versions.

Yes is possible to export the results of query that returns records. 
However, you are limited to 255 columns, so you would have to find a way to 
split the number of columns that is returned into multiple groups of 255 or 
less.  In addition, you would have to have some method of tying the records 
from query one to the records in query two (a primary key that is passed in 
each group of records or a set of unique values).

If I can find it I will see if I can dig out some old code I used to do 
something similar for getting data out of Access into a format that SAS 
could use.


-- 
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Lucas_london via AccessMonster.com" <u35944@uwe> wrote in message 
news:76554b27e6012@uwe...
> Thanks,
>
> I did the crosstab query and it works fine. A couple of questions.
>
> Do you know if I can use the same syntax below to create the same result 
> in
> other applications e.g SQL Server, oracle etc? I've never come accross the
> Transform function or the pivot table function. Is this unique to access?
>
> TRANSFORM Avg(Field3) AS AvgOfField3
> SELECT Field2
> FROM Data
> GROUP BY Field2
> PIVOT Field1;
>
> The reason why I ask is, I have a much bigger dataset that I need to do 
> this
> for, but Access has a limit in terms of  the number of columns it can 
> display
> but I have 600. Or to get around this, does anyone know if it is possible 
> to
> write the output from a crosstab query in access directly to a text file?
> Then I could just import the file into another database to work on.
>
> Thanks
>
> Lucas
>
> Lucas_london wrote:
>>Hi,
>>
>>I have a large Access table with data organised as follows:
>>
>>Field1: Code
>>Field2: Date
>>Field3: Value1
>>Field4: Value2
>>
>>There are seveal different codes and therefore duplicate dates. I'd like 
>>to
>>run a query to bring back each code grouped by date and so put the codes
>>along the top as feilds. For example the query below brings back the
>>following data for two codes.
>>
>>SELECT field2, field1, field3
>>FROM Data
>>WHERE field1 In ('LLOY','RSA');
>>
>>21/01/05, LLOY, 12454
>>22/01/05, LLOY, 31541
>>21/01/05, RSA, 21241
>>22/01/05, RSA, 12414
>>
>>Instead I want the data to look like this:
>>
>>Date         LLOY     RSA
>>21/01/05   12454    21241
>>22/01/05   31541    12414
>>
>>Would this be easy to do?
>>
>>Thanks,
>>
>>Lucas
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200708/1
> 


0
John
8/7/2007 8:11:18 PM
Here is a VBA routine I used to export data with more than 255 columns. 
Note that I had two recordsets based on queries.  The queries had the same 
number of records and the records were ordered in a unique order by same set 
of fields (or the primary key).  The first query had the unique order fields 
as the first fields in the query.  The second query did not return those 
fields (not in the select list).

Hope you can expand this to cover your situation.

Public Function funExportToTabDelimited()
Dim db As database
Dim rs1 As Recordset, rs2 As Recordset
Dim strHolder As String, strTemp As String
Dim intCounter As Integer
Dim strFldDelimiter As String

   strFldDelimiter = vbTab 'or Comma "," or Other value

   Set db = CurrentDb
   Set rs1 = db.OpenRecordset("tblExportValues", dbOpenDynaset)
   Set rs2 = db.OpenRecordset("tblExportValues_Section2", dbOpenDynaset)

   strHolder = db.Name
   strHolder = Left(strHolder, Len(strHolder) - Len(Dir(strHolder)))

   Open strHolder & "DataFlatFile" & Format(Date, "_mm-dd-yy") & ".txt" For 
Output As #1

   With rs1
      strHolder = ""
      'Get Field Names
      For intCounter = 0 To .Fields.Count - 1
         If intCounter <> 1 Then
            strHolder = strHolder & Left$(.Fields(intCounter).Name, 8) & 
strFldDelimiter
         End If
      Next intCounter
      For intCounter = 0 To rs2.Fields.Count - 1
         If intCounter > 1 Then
            strHolder = strHolder & Left$(rs2.Fields(intCounter).Name, 8) & 
strFldDelimiter
         End If
      Next intCounter

      'Write Field Names to file
      strHolder = Left(strHolder, Len(strHolder) - Len(strFldDelimiter))
      Print #1, strHolder

      'Get field contents for each record and write to file
      Do While Not .EOF

         'get matching line of data in secondary file
         strHolder = "SurveyDocId ='" & .Fields("SurveyDocID") & "'"
         rs2.FindFirst strHolder
         strHolder = vbNullString

         For intCounter = 0 To .Fields.Count - 1
            If intCounter <> 1 Then
               strTemp = .Fields(intCounter) & ""
               strTemp = ReplaceString(strTemp, vbTab, " ")    'Strip Tabs
               strTemp = ReplaceString(strTemp, vbCrLf, " ")   'Strip C/R

               '----Surround with Quotes if appropriate
               If strFldDelimiter <> "|" Then
                  If InStr(1, strTemp, ",") > 0 Then
                     strTemp = Chr(34) & strTemp & Chr(34)
                  End If
               End If

               strHolder = strHolder & strTemp & strFldDelimiter
            End If
         Next intCounter

         For intCounter = 0 To rs2.Fields.Count - 1
            If intCounter > 1 Then
               strTemp = rs2.Fields(intCounter) & ""
               strTemp = ReplaceString(strTemp, vbTab, " ")
               strTemp = ReplaceString(strTemp, vbCrLf, " ")

               If InStr(1, strTemp, ",") > 0 Then
                  strTemp = Chr(34) & strTemp & Chr(34)
               End If
               strHolder = strHolder & strTemp & strFldDelimiter
            End If
         Next intCounter

         strHolder = Left$(strHolder, Len(strHolder) - Len(strFldDelimiter))
         Print #1, strHolder

         .MoveNext
      Loop

   End With 'rs1

 Beep


funExportToTabDelimited_Exit:
   Close #1
   rs1.Close
   rs2.Close
   db.Close
   Set rs1 = Nothing
   Set rs2 = Nothing
   Set db = Nothing
Exit Function

funExportToTabDelimited_Error:


End Function




-- 
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"John Spencer" <spencer@chpdm.edu> wrote in message 
news:O69sH8S2HHA.5884@TK2MSFTNGP02.phx.gbl...
> As far as I know the Cross tab query is unique to Access.  MS SQL may have 
> something like it in the later versions.
>
> Yes is possible to export the results of query that returns records. 
> However, you are limited to 255 columns, so you would have to find a way 
> to split the number of columns that is returned into multiple groups of 
> 255 or less.  In addition, you would have to have some method of tying the 
> records from query one to the records in query two (a primary key that is 
> passed in each group of records or a set of unique values).
>
> If I can find it I will see if I can dig out some old code I used to do 
> something similar for getting data out of Access into a format that SAS 
> could use.
>
>
> -- 
> John Spencer
> Access MVP 2002-2005, 2007
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> .
>
> "Lucas_london via AccessMonster.com" <u35944@uwe> wrote in message 
> news:76554b27e6012@uwe...
>> Thanks,
>>
>> I did the crosstab query and it works fine. A couple of questions.
>>
>> Do you know if I can use the same syntax below to create the same result 
>> in
>> other applications e.g SQL Server, oracle etc? I've never come accross 
>> the
>> Transform function or the pivot table function. Is this unique to access?
>>
>> TRANSFORM Avg(Field3) AS AvgOfField3
>> SELECT Field2
>> FROM Data
>> GROUP BY Field2
>> PIVOT Field1;
>>
>> The reason why I ask is, I have a much bigger dataset that I need to do 
>> this
>> for, but Access has a limit in terms of  the number of columns it can 
>> display
>> but I have 600. Or to get around this, does anyone know if it is possible 
>> to
>> write the output from a crosstab query in access directly to a text file?
>> Then I could just import the file into another database to work on.
>>
>> Thanks
>>
>> Lucas
>>
>> Lucas_london wrote:
>>>Hi,
>>>
>>>I have a large Access table with data organised as follows:
>>>
>>>Field1: Code
>>>Field2: Date
>>>Field3: Value1
>>>Field4: Value2
>>>
>>>There are seveal different codes and therefore duplicate dates. I'd like 
>>>to
>>>run a query to bring back each code grouped by date and so put the codes
>>>along the top as feilds. For example the query below brings back the
>>>following data for two codes.
>>>
>>>SELECT field2, field1, field3
>>>FROM Data
>>>WHERE field1 In ('LLOY','RSA');
>>>
>>>21/01/05, LLOY, 12454
>>>22/01/05, LLOY, 31541
>>>21/01/05, RSA, 21241
>>>22/01/05, RSA, 12414
>>>
>>>Instead I want the data to look like this:
>>>
>>>Date         LLOY     RSA
>>>21/01/05   12454    21241
>>>22/01/05   31541    12414
>>>
>>>Would this be easy to do?
>>>
>>>Thanks,
>>>
>>>Lucas
>>
>> -- 
>> Message posted via AccessMonster.com
>> http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200708/1
>>
>
> 


0
John
8/8/2007 11:02:57 AM
Hi John, 

Thanks for the code. Have to admit I'm not really a VBA whizz so will take me
a while to understand the code. In the meantime, I'll try to get hold of the
latest version of Access (Access 12) has I think it accomadates thousands of
columns!

Thanks, 

Lucas

John Spencer wrote:
>Here is a VBA routine I used to export data with more than 255 columns. 
>Note that I had two recordsets based on queries.  The queries had the same 
>number of records and the records were ordered in a unique order by same set 
>of fields (or the primary key).  The first query had the unique order fields 
>as the first fields in the query.  The second query did not return those 
>fields (not in the select list).
>
>Hope you can expand this to cover your situation.
>
>Public Function funExportToTabDelimited()
>Dim db As database
>Dim rs1 As Recordset, rs2 As Recordset
>Dim strHolder As String, strTemp As String
>Dim intCounter As Integer
>Dim strFldDelimiter As String
>
>   strFldDelimiter = vbTab 'or Comma "," or Other value
>
>   Set db = CurrentDb
>   Set rs1 = db.OpenRecordset("tblExportValues", dbOpenDynaset)
>   Set rs2 = db.OpenRecordset("tblExportValues_Section2", dbOpenDynaset)
>
>   strHolder = db.Name
>   strHolder = Left(strHolder, Len(strHolder) - Len(Dir(strHolder)))
>
>   Open strHolder & "DataFlatFile" & Format(Date, "_mm-dd-yy") & ".txt" For 
>Output As #1
>
>   With rs1
>      strHolder = ""
>      'Get Field Names
>      For intCounter = 0 To .Fields.Count - 1
>         If intCounter <> 1 Then
>            strHolder = strHolder & Left$(.Fields(intCounter).Name, 8) & 
>strFldDelimiter
>         End If
>      Next intCounter
>      For intCounter = 0 To rs2.Fields.Count - 1
>         If intCounter > 1 Then
>            strHolder = strHolder & Left$(rs2.Fields(intCounter).Name, 8) & 
>strFldDelimiter
>         End If
>      Next intCounter
>
>      'Write Field Names to file
>      strHolder = Left(strHolder, Len(strHolder) - Len(strFldDelimiter))
>      Print #1, strHolder
>
>      'Get field contents for each record and write to file
>      Do While Not .EOF
>
>         'get matching line of data in secondary file
>         strHolder = "SurveyDocId ='" & .Fields("SurveyDocID") & "'"
>         rs2.FindFirst strHolder
>         strHolder = vbNullString
>
>         For intCounter = 0 To .Fields.Count - 1
>            If intCounter <> 1 Then
>               strTemp = .Fields(intCounter) & ""
>               strTemp = ReplaceString(strTemp, vbTab, " ")    'Strip Tabs
>               strTemp = ReplaceString(strTemp, vbCrLf, " ")   'Strip C/R
>
>               '----Surround with Quotes if appropriate
>               If strFldDelimiter <> "|" Then
>                  If InStr(1, strTemp, ",") > 0 Then
>                     strTemp = Chr(34) & strTemp & Chr(34)
>                  End If
>               End If
>
>               strHolder = strHolder & strTemp & strFldDelimiter
>            End If
>         Next intCounter
>
>         For intCounter = 0 To rs2.Fields.Count - 1
>            If intCounter > 1 Then
>               strTemp = rs2.Fields(intCounter) & ""
>               strTemp = ReplaceString(strTemp, vbTab, " ")
>               strTemp = ReplaceString(strTemp, vbCrLf, " ")
>
>               If InStr(1, strTemp, ",") > 0 Then
>                  strTemp = Chr(34) & strTemp & Chr(34)
>               End If
>               strHolder = strHolder & strTemp & strFldDelimiter
>            End If
>         Next intCounter
>
>         strHolder = Left$(strHolder, Len(strHolder) - Len(strFldDelimiter))
>         Print #1, strHolder
>
>         .MoveNext
>      Loop
>
>   End With 'rs1
>
> Beep
>
>funExportToTabDelimited_Exit:
>   Close #1
>   rs1.Close
>   rs2.Close
>   db.Close
>   Set rs1 = Nothing
>   Set rs2 = Nothing
>   Set db = Nothing
>Exit Function
>
>funExportToTabDelimited_Error:
>
>End Function
>
>> As far as I know the Cross tab query is unique to Access.  MS SQL may have 
>> something like it in the later versions.
>[quoted text clipped - 74 lines]
>>>>
>>>>Lucas

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200708/1

0
Lucas_london
8/12/2007 3:15:58 PM
No, Access is limited to 255 columns in a table or in a query.  There is 
no change in the limits.

Excel 2007 will support over a million rows and I don't remember how 
many thousand columns.

'====================================================
  John Spencer
  Access MVP 2002-2005, 2007
  Center for Health Program Development and Management
  University of Maryland Baltimore County
'====================================================


Lucas_london via AccessMonster.com wrote:
> Hi John, 
> 
> Thanks for the code. Have to admit I'm not really a VBA whizz so will take me
> a while to understand the code. In the meantime, I'll try to get hold of the
> latest version of Access (Access 12) has I think it accomadates thousands of
> columns!
> 
> Thanks, 
> 
> Lucas
> 
> John Spencer wrote:
>> Here is a VBA routine I used to export data with more than 255 columns. 
>> Note that I had two recordsets based on queries.  The queries had the same 
>> number of records and the records were ordered in a unique order by same set 
>> of fields (or the primary key).  The first query had the unique order fields 
>> as the first fields in the query.  The second query did not return those 
>> fields (not in the select list).
>>
>> Hope you can expand this to cover your situation.
>>
>> Public Function funExportToTabDelimited()
>> Dim db As database
>> Dim rs1 As Recordset, rs2 As Recordset
>> Dim strHolder As String, strTemp As String
>> Dim intCounter As Integer
>> Dim strFldDelimiter As String
>>
>>   strFldDelimiter = vbTab 'or Comma "," or Other value
>>
>>   Set db = CurrentDb
>>   Set rs1 = db.OpenRecordset("tblExportValues", dbOpenDynaset)
>>   Set rs2 = db.OpenRecordset("tblExportValues_Section2", dbOpenDynaset)
>>
>>   strHolder = db.Name
>>   strHolder = Left(strHolder, Len(strHolder) - Len(Dir(strHolder)))
>>
>>   Open strHolder & "DataFlatFile" & Format(Date, "_mm-dd-yy") & ".txt" For 
>> Output As #1
>>
>>   With rs1
>>      strHolder = ""
>>      'Get Field Names
>>      For intCounter = 0 To .Fields.Count - 1
>>         If intCounter <> 1 Then
>>            strHolder = strHolder & Left$(.Fields(intCounter).Name, 8) & 
>> strFldDelimiter
>>         End If
>>      Next intCounter
>>      For intCounter = 0 To rs2.Fields.Count - 1
>>         If intCounter > 1 Then
>>            strHolder = strHolder & Left$(rs2.Fields(intCounter).Name, 8) & 
>> strFldDelimiter
>>         End If
>>      Next intCounter
>>
>>      'Write Field Names to file
>>      strHolder = Left(strHolder, Len(strHolder) - Len(strFldDelimiter))
>>      Print #1, strHolder
>>
>>      'Get field contents for each record and write to file
>>      Do While Not .EOF
>>
>>         'get matching line of data in secondary file
>>         strHolder = "SurveyDocId ='" & .Fields("SurveyDocID") & "'"
>>         rs2.FindFirst strHolder
>>         strHolder = vbNullString
>>
>>         For intCounter = 0 To .Fields.Count - 1
>>            If intCounter <> 1 Then
>>               strTemp = .Fields(intCounter) & ""
>>               strTemp = ReplaceString(strTemp, vbTab, " ")    'Strip Tabs
>>               strTemp = ReplaceString(strTemp, vbCrLf, " ")   'Strip C/R
>>
>>               '----Surround with Quotes if appropriate
>>               If strFldDelimiter <> "|" Then
>>                  If InStr(1, strTemp, ",") > 0 Then
>>                     strTemp = Chr(34) & strTemp & Chr(34)
>>                  End If
>>               End If
>>
>>               strHolder = strHolder & strTemp & strFldDelimiter
>>            End If
>>         Next intCounter
>>
>>         For intCounter = 0 To rs2.Fields.Count - 1
>>            If intCounter > 1 Then
>>               strTemp = rs2.Fields(intCounter) & ""
>>               strTemp = ReplaceString(strTemp, vbTab, " ")
>>               strTemp = ReplaceString(strTemp, vbCrLf, " ")
>>
>>               If InStr(1, strTemp, ",") > 0 Then
>>                  strTemp = Chr(34) & strTemp & Chr(34)
>>               End If
>>               strHolder = strHolder & strTemp & strFldDelimiter
>>            End If
>>         Next intCounter
>>
>>         strHolder = Left$(strHolder, Len(strHolder) - Len(strFldDelimiter))
>>         Print #1, strHolder
>>
>>         .MoveNext
>>      Loop
>>
>>   End With 'rs1
>>
>> Beep
>>
>> funExportToTabDelimited_Exit:
>>   Close #1
>>   rs1.Close
>>   rs2.Close
>>   db.Close
>>   Set rs1 = Nothing
>>   Set rs2 = Nothing
>>   Set db = Nothing
>> Exit Function
>>
>> funExportToTabDelimited_Error:
>>
>> End Function
>>
>>> As far as I know the Cross tab query is unique to Access.  MS SQL may have 
>>> something like it in the later versions.
>> [quoted text clipped - 74 lines]
>>>>> Lucas
> 
0
John
8/12/2007 6:02:22 PM
Reply:

Similar Artilces:

Date format | Month/Year only changes to Day/Month/CurrentYear
Hi Y'all,I have a table field which I've formatted to MM/YY. On a form theinput box for that field is formatted the same. However if I enter adate such as 03/08 (March 2008) it becomes 08/07. Access sees 03/08 as3rd August and adds the current year. The data stored is 03/08/07.How can I stop this happening? I assume I can just use a formattednumber field but surely there's a way to keep the date format?Thanks in advance!Paul Paul wrote:> Hi Y'all,>> I have a table field which I've formatted to MM/YY. On a form the> input box for that field is formatted the same....

Is it possible to automatically move data entered in columns into rows?
Is it possible to automatically move data entered in columns into rows? It seems like a simple thing to do but I can't find a function anywhere. Cheers, Richard Thorneycroft Husky Products If you enter something in ONE cell it is automatically in a row and a column. So, perhaps a bit more explanation is in order. -- Don Guillett SalesAid Software donaldb@281.com "Richard Thorneycroft" <dancecommander81@hotmail.com> wrote in message news:9e2f3f75.0411160750.7003c6ab@posting.google.com... > Is it possible to automatically move data entered in columns into rows? >...

Query help please 10-12-07
I have two tables. Accounts and AcctDetails. Accounts has two columns, Account and Desc. AcctDetails has three columns, Account, Date, and Balance. there are multiple detail lines per account, just will different dates and balances. For each account in Accounts, I need the most recent date row and that balance from the details table. I am getting hung up and can't get this. Thanks, steve -- VS 2005, VB .NET, SQL Server Message posted via http://www.accessmonster.com Thanks! I understand your solution using the sub select, but, I don't know how to use sub selects when using the ...

Running Apend,Delete and Make queries with VB
I have a macro that I run 4 difference query updates with. qrydeleteopencounts (Delete Query) qryMakeTableCYCDATECOPY (Make Table Query) qryAppendProductionMSList (Append Query) qryAppendFGList (Append Query) Is there a easier why to do this so I do not have the click "Yes" a dozen times while running this macro? Thanks, Aaron "Aaron" <Aaron@discussions.microsoft.com> wrote in message news:8B98EC01-C5EB-4CD8-943E-B5CFD626876D@microsoft.com... >I have a macro that I run 4 difference query updates with. > > qrydeleteopencounts (Delet...

Scheduled CRM SQL Jobs
Are the following Jobs important to be run on the SQL server for a CRM3.0 installation: Start_Incremental on Company_Name_MSCRM.ftcat_documentindex.[7.5] and CompanyName_MSCRM.Update Contract States Does it matter if these do not run. i.e. could I delete these jobs? They are scheduled but fail every time they try to run. For the 1st job a get the following error: The Job was invoked by Schedule 5 (Indexed Document Title Schedule). The last step to run was step 1 (Full-Text Indexing). For the 2nd job I get this error: The job failed. The Job was invoked by Schedule 7 (Update Contract Stat...

Unable to close excel as a group from the task bar
Hi all, I have noticed (ok actually a user logged it) that when you have multiple worksheets open in excel (and they are grouped on the windows taskbar) you cant close minimise the group, in fact all the options in the right click menu are greyed out. I've had a look on the web and kind fine a few instances of people asking this question but no one seems to have an answer. ( i know i can untick windows in taskbar and just have one instance of excel but im curious why close group doesnt work) thanks in advance >>but im curious why close group doesnt work There are lots of que...

Reg Sql Server 2005 Full and Differential Backup
Hi I am create a full backup every day at 1am and from morning 7am onwards differential back until night 11pm (as a single file for 24 hours) next day new file. if i open and restore the backup it show like this Example ----------- FUll backup 120020 Diff at 7 am 123580 Diff at 8 am 124430 Diff at 9 am 124430 Diff at 10 am 128430 Diff at 11 am 129490 Diff at 12 pm 131430 Diff at 1 pm 131430 Diff at 2 pm 123331 (the is a great change in size) Like this upto 11pm. but in middle of the diff . backup the is a size different . and i can able to restore upto 1pm. but can...

Filtering data based on historical values
Hello everyone, I am trying to sort a list of temperatures to analyze a chillers operation. The chiller turns on when the outside air temperature is 80, and turns off when the outside air temperature is 70. I am trying to find the time when the chiller is on (temperature was above 80 and didn't drop below 70) and the temperature is between 70 and 75. All I need is excel to display a 1 if it is on (and between 70-75) or 0 if off, because every 1 is a unit of time. All the sorts I have tried are unable to look back and see if it was turned on, because it may have been above 80 t...

Variable input to populate data fields
I need some help with the following problem: 1. I have built a s/s that analyzes multiple variables (e.g. cost of sales, SG&A spend, etc.) in a "current state" and "next gen state". The variables are analyzed for a range of company revenue sizes (e.g. >$25M, $25-$100M, $101-$250M, etc.). 2. Based upon the above data, I would like to populate a s/s with that data, dependent upon the input from a third party. For example, if the third party inputs $55M revenues, I would like to populate the sheet with all of the data related to "current state" and &q...

Cannot Access Reports. CRM 3.0 and SQL 2005 on Separated Servers
I have CRM 3.0 and MS SQL 2005 64 bits on separated servers. When i access the Reports in My Activities, appears: Error. Please contact the system administrator. -- Allan Machado Microsoft Certified Professional Hi Alan, Are you accessing your CRM using an IP address or name, if it's IP address, it will not work. It only works with name because IP don't work with Kerbero. If it's still not working, follow the white paper from Microsoft to check your SPN, etc... Hope this will help. Darren Liu Crowe Chizek and Company http://www.crowecrm.com On Apr 14, 1:06 pm, Allan Macha...

Countif and dates???
I have a 20,000 entry spreadsheet that contains dates in this format 07/04/2010, As you can imagine there are quite a few of these entries for each month. I want to count the number of them for a particular month. I can't seem to use wildcards for some reason and cant find the answer why on the net. There formula that I would expect to work goes like this: - (ps I am looking for all the March(03) entries in the column A) =COUNTIF(A:A, "*/03/8") This always returns a 0 and I can't see why? In an ideal would I would like this to count the above AND also ...

change text and icon of a XP grouped taskbar button
Hi I need to change the text and icon of the taskbar button that XP collects simular buttons under, (after choosing "Group simular taskbar buttons" in the taskbar properties) Can anyone give me directions on where to start looking? thanks and regards Anders Sandberg ...

Outlook 2007 Task Form Insert Ribbon (Attach Group)
Does anybody know what happened to the Insert Tab on Task Forms in Outlook 2007. Even some Microsoft training materials show an Insert Tab on the form for new Tasks. However, I can't find any way to make it appear, though it does show up on new Appointment forms. Has somethiing changed perhaps? Thanks for any insight anyone has. Bill I see it here as the 2nd tab on an open task. There is no Attach group, = though. The group with attachment commands is called Include. = =20 --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turt...

Create a new 2005 data file with 2004 settings.
Hello: I am using Money 2004. I would like to create a new data file for 2005 but retain my settings from the 2004 data file I currently have. Is there a way to do this? Basically I need to use the 2004 data file settings as a template for 2005. I want to retain my accounts, and various lists... Thanks Harold In microsoft.public.money, harold wrote: > >I am using Money 2004. I would like to create a new data file for 2005 but >retain my settings from the 2004 data file I currently have. Is there a way >to do this? Basically I need to use the 2004 data file settings as a ...

Billpay Date problems
BillPay seems to take an incredibly long time to pay bills. For instance, I tried to pay a bill last Wednesday and the earliest date payment could be made was 7 days later! Is this 7 days to clear or just 7 days to send? Also, BillPay "sent" a payment out of one of my accounts Friday, but as of Monday PM, it has not shown up. What's going on? There's almost no real useful information on the Billpay site and the very vague dates are making me think that paying my bank $5/month for their payment service is much sounder than this BillPay uncertainty... OK. Thanks. I'...

List View Grouping in MFC 9.0
Dear Friends, I want to split List control into groups at run time similiar to Windows VISTA MY Computer view. i.e. 1) I can expand and collapse the items at run time ? 2) Header of groups should be in user defined color etc. Any help is highly appreciated. I am developing the MFC application in Visual Studio 2008 + feature pack 1. You may want to look at some of these controls. The Outlook bar control might work for you, or perhaps a tree control and list control in a splitter window: http://www.codejock.com/downloads/samples/controls.asp#controls_vistataskdialog Tom <Smadhuka...

Copying data
Hi How do I set up a drop down list (Sales April, May, June ect) to copy data from 1 column to another in a specified location? Thanks Andre You can use and Advanced Filter to copy data from one location to another. There are instructions in Excel's Help, and here: http://www.contextures.com/xladvfilter01.html Also, there are sample files here that use Data Validation to create dropdown lists, and Advanced Filter to extract data: http://www.contextures.com/excelfiles.html Under Filters, look for 'Product List by Category ' Andre wrote: > How do I set u...

Linking Multiple Queries and a SharePoint List
Hi, I have an Access 2007 Database (MyDB) which connects to an enterprise database (EnDB) using an ODBC connection. MyDB extracts a list of servers from the EnDB that have an error status, and appends (no duplicates) any records found into MyDB. All works well. I then have another query that cross-checks MyDB against the EnDB for any servers listed in MyDB where the error no longer exists in the EnDB, and if any are found it flags a feild in my local databse. I use two queries to acheive this - 1) Find unmatched Query, 2) Update Query (using the data produced in the Fi...

Recover data
When I opened a workbook I had linked data in, I found the linked data gone! vanished! My coworker says she did not move, rename, or remove any of the source files. Is there a method in 2007 that would save a backup somewhere or do I have to reenter/relink this data. GRRRRR -- Janet A. The linked data is gone but are the linking formulas still there? If not, someone has deleted the links then saved. Unless you have a backup copy, you are outtaluck. If formulas still there do you get any errors in the cells? Gord Dibben MS Excel MVP On Fri, 6 Nov 2009 12:30:03 -0800, Janet A. Thom...

CRM 3.0 and SQL 2005 SP2
Hi, Is CRM 3.0 fully compatible with SQL 2005 SP2? I read that some functions will fail. Thanks, John We are running CRM with SQL Server 2005 and testing it extensively. So far, we have found no SQL Server related failures. We are comparing the results against our previous experience of running CRM against SQL Server 2000. The only thing is that when you install SQL Server 2005, you MUST enable the SQL Server Agent to be installed during setup. The option to do this is DESELECTED by default in the SQL Server 2005 setup program (whereas other SQL Server setups have the Server Agent selec...

SQL Server Express 2008 installation Problem
I have an existing SQL Server Express 2005 running on a Vista 64bit. I need to keep this as i'm supporting production 2005 installations. I'm attempting to install SQL Server Express 2008 on the same box. I'm getting 'SQL Server Express 2008 with Service Pack 1 is uncompatible with Microsoft SQL Server Express 2005' Can anyone advise me on this? -- Joe Reiss Jackson Creek Software, Inc. www.emc2library.com Live Long and Prosper \\// On 2009-12-10 19:31, Photon wrote: > > I have an existing SQL Server Express 2005 running on a Vista 64bit...

ESM error
When I am navigating through the System Manager for Exchange 2003 on Server 2003, I get to the First Storage Group, and as soon as I try to view anything within that container, an error is displayed - "An unknown error has occurred" ID no: 80040d1b Exchange System Manager I just took this site over, and cannot view any mailboxes, etc within that group. This is nothing special as far as configuration goes. Full text indexing has not been run as far as I know. There is limited information on this error, and all relates to gatherer folders not matching the registry settings, but ...

Due date confusion
I've set up Money Plus in Bill Settings to Remind me 7 days in advance of a due bill and to count only business days. However, when I go to my bills summary page, a bill that is due next Saturday (today is Sunday) has a message in Money saying: "This electronic payment must be sent within 3 days". If I've instructed Money to remind me 7 days in advance, counting only business days, shouldn't this message say something like: "This transaction is 2 days overdue" ? I'm confused. :-/ -Mike ...

Calendar by Month
When I view my calendar by month it is defaulting to Dec 28 when today is January 25 - if I view by week or day it is fine. It is just by month that it won't start at the current date. "CPDIANE" <CPDIANE@discussions.microsoft.com> wrote in message news:D1B1F9C7-7742-4D50-B1C4-CD9F3FA49730@microsoft.com... > When I view my calendar by month it is defaulting to Dec 28 when today is > January 25 - if I view by week or day it is fine. It is just by month that > it > won't start at the current date. Outlook will show you the current month with t...

Multiple data on 2 carts
I have a spreadsheet with a column of data that is either O or N. The 2nd column is Either F or A The 3rd is the date. The fourth is a temperature. I am supposed to produce seperate line charts. One for the O and one for the N I set up the auto sort feature which works great, but I have to produce 2 seperate charts, one showing the trend in "N" and one for the "O". I had it done originally with a line graph showing the N and O. THe data is so close that it blends together. With the auto sort I can only show one at a time. Is there a way to show the N on one cha...