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
853 Views

Similar Articles

[PageSpeed] 50

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:

97-2K Linked table recordset SQL
I am not a programmer so this may be an easy question for you. We have an Access DB that has records in table#1 (an internal table) to find a match on then Delete the records in table#2 (a linked table DB3). This worked with Access97 but now with Access2K we get error: "RunTime error 3061: Too few parameters. Expected 1 Any ideas? Some of code (edited) is posted below Thanks Set Rst1 = dbs.OpenRecordset("OBJ2612", dbOpenTable, dbReadOnly While Not Rst1.EOF ' <--- internal table# FVNO = (Rst1!XBI1VNO '*** below is linked table#2 to delete recs if ...

Posting new data to PM & RM before year-end or fiscal period close
Hi GP Users, If I have already posted some new data before closing the old financial year, what are the possible problems that may happen when I close it? Any solutions to that? Thanks Andrew ...

One Line of Data Per Page of Report
When viewing/printing reports reports we are only getting one line of data per page. So instead of having a 5 page report with 60 entires we have a 60 page report with 1 entry per page. Is there an easy way to solve this probelm? We are not very familar with access and need this problem solved. Thanks! On Thu, 17 Jan 2008 11:07:03 -0800, Coutu <Coutu@discussions.microsoft.com> wrote: >When viewing/printing reports reports we are only getting one line of data >per page. So instead of having a 5 page report with 60 entires we have a 60 >page report with 1 entry per pag...

CONVERT TIME & TRIM DATE
I need help converting time to an AM/PM time format our database displays time in 4 digits : 0006, 0737, 1217, 2149. I am finding that 0006 is 12:06 AM and 1217 is 12:17 PM, etc. The date displays: 2007-03-09 00:00:00.000, how do I have it display the date as 03-09-2007? Thanks. Specify the display format of the control or field: mm-dd-yyyy RENEE705 wrote: >I need help converting time to an AM/PM time format >our database displays time in 4 digits : 0006, 0737, 1217, 2149. I am >finding that 0006 is 12:06 AM and 1217 is 12:17 PM, etc. >The date displays: 2007-03-09 00:00:00.0...

SQL Server 2005 CAL question
If I have 1 HQ Server with with 2 HQ Clients, how many CALS do I need at the HQ server. Thanks in advance. Phil get a min of 5 cals Its not only the clients to think about its also any office PC that connects to SQL. "Phil V" <PhilV@discussions.microsoft.com> wrote in message news:3E2F3590-032F-4B4C-9CEC-5A6A6BC7F46D@microsoft.com... > If I have 1 HQ Server with with 2 HQ Clients, how many CALS do I need at > the > HQ server. Thanks in advance. > > Phil ...

help with range lookup and date criteria
Hello, I have a list of doctors (column A) that are each on-cal through a number of days. My argument is: if the date value of B1 an C1 is within the current date, then repeat the value of A1 -- James Spaldin ----------------------------------------------------------------------- James Spalding's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2567 View this thread: http://www.excelforum.com/showthread.php?threadid=39088 hi, James ! > ... a list of doctors (column A) that are each on-call through a number of days. > ... argument is: if the date value ...

Recovering a mailbox from a deleted storage group
The lawyers are on my back ... They want a copy of one of my user's mailbox from a year ago ... The problem is, I have moved all the mailboxes to new stores and new storage groups a couple of months ago ... the user still exists ... can anyone point me to a reference that can help me (especially one that can get me out of this ...) oh yeah , exchange 2003/sp1 thanks Mike p.s. we are going to purchase a journaling solution so that we are not bothered with this in the future ...(and we are making the lawyers pay for it ..) Do you have backups? Try creating a new Storage Group wit...

CFtpFileFind return null creation date
Hi All I am trying to list the URL and timestamp of some files on a server in a list box in a simple dialog using FTP. The code is as follows: void CFTPTestDlg::OnTest() { CListBox* pbox = (CListBox*)GetDlgItem(IDC_LB_FILES); pbox->ResetContent(); // Create session object to initialise libs CInternetSession sess(_T("My Test")); // Declare a FTPConnection CFtpConnection* pftpConn = NULL; try{ pftpConn = sess.GetFtpConnection("ftp.,mytestsite.co.uk", "mylogon","mypassword"); if (!pftpConn->SetCurrentDirectory("/Testdir/T...

Max Query
Hi all, I have 2 tables. One holds account numbers. One holds invoices, linked to account numbers. Ive got a query to show the latest invoice for each account number, via the MAX function. However, once this has been done, due to it being an aggregate function. There is no way of me editing this query once done. Is there any way around this, as i would like to only show the latest invoice, and edit information in that. Regards, A correlated sub-query in the WHERE clause might work for you if you don't have a lot of records. SELECT * FROM Accounts INNER JOIN Invoic...

extract year from Date Value
Good morning, Could someone help me extract the year portion from a date value such as this 11/20/2009? Thanks in advance, Mike With the Date in A1, place =YEAR(A1) in B1 Takeadoe wrote: > Good morning, > > Could someone help me extract the year portion from a date value such > as this 11/20/2009? > > Thanks in advance, > > Mike ...

Help with complex query 05-09-07
Hi all, I've got a query that I'm not sure how to develop. My tables: Quotes - QuoteNo, RaisedBy, Customer QuoteItems - RecordID, QuoteNo, PartNo, Lifecycle, Value There's a one-to-many relationship between Quotes and QuoteItems, i.e. one quote can have many items. I need to run a query to show a list of quotes with totals from the QuoteItems table i.e. QuoteNo, RaisedBy, Customer, List of PartNos, List of Lifecycles, TotalValue I haven't got a clue how to start this, I know it needs to be nested queries, but the listing of parts and lifecycles is particularly stumpin...

overlaping dates in two files
MS Money 2001 deluxe on Windows XP Pro I archived my 2004 accounts and never balanced them. I decided i needed to and now have two files, both .mny files with 2003Archive.mny is from 1/2002 to 12/2004 Active2005.mny is from 1/2004 to present. I'd like to combine them into one file so I can balance the accounts (three main accounts) because my statements are not ending nicely on the end of the month. Please recommend the best method so I can balance the accounts. I think I need to conbine the files or somehow get the months that are now balanced in the 2003Archive.mny file to give...

Help with Date/Time Function
I am working with date/time functions trying to figure out how many hours were spent doing a job. I have a start time and a finish time. But I need to subtract out any time between five pm and six am and weekends from the times. These would be times that no one was actually working on the job. For example I have 4/5/2005 18:23 4/6/2005 17:04 Could someone give me a clue as to how this might be done? Thanks, Steve Monczka smonczka@hotmail.com It's not a trivial thing, here's an example http://www.cpearson.com/excel/DateTimeWS.htm or here http://tinyurl.com/cbphn -- R...

listview control display as a group #2
I have a requirement to display the list items as group. I have been trying to use the CListCtrl::insertGroup() method. The method works fine but the grouping doesnt show it does show the items just as regular items. Here is the code: <html> <head> </head> <body> <font SIZE="2"> <font SIZE="2" COLOR="#0000ff"> <p>void</font><font SIZE="2"> Ctt1View::OnInitialUpdate()</p> <p>{</p> <blockquote> <p>CListView::OnInitialUpdate();</p> </font> <p></...

Past calendar dates losing their bolding
I know that the calendar has the current month, previous month and up to 10 months in the future to have the dates bolded if there are appointments there, but I have several clients who want more dates in the past - at least a year - to be bolded. Any way to do this? Mari (tulipmari.nospam@shaw.ca) remove the .nospam to respond to my email -- Sorry, no. it goes back just 2 months. -- Diane Poremsky [MVP - Outlook] http://www.poremsky.com - http://www.cdolive.com Outlook Tips: http://www.outlook-tips.net/ Expert Zone http://www.microsoft.com/windowsxp/expertzone Search for answers: http:/...

Notification Email on duration date
Hi I have a question that is there any way to send email to owner when their task is reaching due date set for the task. I tried to make it work with workflow but I couldn't. Also I read through all the post here and couldn't find anything relating to my question. Sorry, subject should be "Notification Email on due date", note "duration date" Thks, motoC "motoC" wrote: > Hi > > I have a question that is there any way to send email to owner when their > task is reaching due date set for the task. > > I tried to make it work with...

Date calcutation
Good afternoon, i need some help with the following: I have a maintenace DB, i have the following fields MachineHours (long int), MaintenaceHours (long int), MaintenaceDate (date) and WeeklyHourWork (long int). I have a text box NextMaintenace (date) where i calcute the date of the next maintenace based on the date of the maintenace (MaintenaceDate) difference between MachineHours, MaintenaceHours and WeeklyHourWork. example: if MachineHours=4000, MaintenaceHours=7000, MaintenaceDate=26-10-2007, WeeklyHourWork=40 then NextMaintenace=15-03-2009 This works fine but I need to put a limit to...

Data format in pivot table
I am running a Pivot table on some swim data. Even though the data is formatted the same way "mm:ss.00", the fraction of the second is not showing up or is not part of the numbers in the Pivot table. Pivot table data Back 25 Breast 25 Fly 25 00:31.00 00:27.00 00:28.00 00:31.00 00:33.00 00:31.00 00:36.00 00:31.00 00:27.00 00:28.00 00:23.00 00:25.00 00:24.00 Data the Pivot table is based on 7 CMSA-SE 00:21.87 00:21.49 6 BMAC-SE 00:22.95 00:21.91 7 BMAC-SE 00:23.13 00:22.16 6 BMAC-SE 00:27.97 00:22.63 8 BMAC-SE 00:21.07 00:22.70 7 UN-SE 00:00.00 00:22.94 6 CMSA-SE 00:26.36 00...

SQL options and new upgrade GP9.0 (from GP7.5)
Here is my problem after I upgraded to GP9.0. I need to add a user with GP, unless I refresh its password (in SQL Enterprise Manager) and re-define its properties, I cannot use it to access GP moreover, use SQL resources. I also discoverred that DYNGRP dont have permissions on some of the GP tables. Also when I change the password in GP. It does not update the one used in SQL. Why is that? How can I correct this issue? If you create a user in GP the password is encrytped by GP so that the password that is sent to SQL is not the same. This is why a GP user can not do anything in SQL...

Highlight Data Points that change in Graph
Hi all Is it possible to highlight data points that change slope in Excel 2003 and if so how. Richard ...

Subreport Data Listing Twice for Each Report Record
On a report & subreport pair, the subreport data is listing out twice, and I can't seem to sort out why. Any suggestions appreciated. -- Thanks, tbl tbl wrote: >On a report & subreport pair, the subreport data is listing >out twice, and I can't seem to sort out why. Does the subreport diplay two records? If so, the problem is that your main report's record source query has a Join to the subreport's data source. -- Marsh MVP [MS Access] in article bq9q83pdvsepjusn44j91g84rpveu5a9bk@4ax.com, Marshall Barton at marshbarton@wowway.com wrote on 7/5/07 1:18 PM...

ActiveSync 3.7 doesn't stay up to date with Outlook 2003
I just recently upgraded to Outlook 2003 (from XP) and I've found that my Pocket PC won't stay current with emails while it's connected to my PC in the cradle. I've noticed the problem when I delete emails from my inbox on the PC or I move emails to a folder on the PC. I would expect ActiveSync to fire up and update the Pocket PC but it doesn't seem to recognize that anything has changed. However, if I disconnect the Pocket PC from the cradle and reconnect it seems to recognize the differences and get itself up to date. Has anyone seen this problem? Any ideas o...

DSum in Query
In my query, I have a field: New_Cost:[AllocatedCost]+[Base] I need to get the Total of that column and divide by each record [New_Cost] to come up with a percentage in a new field -- New_Cost_Percentage. Can this be done and if so, how? SELECT itemID, SUM(qty) AS totalForThisItem, SUM(qty) / (SELECT SUM(qty) FROM myTable) AS percentage FROM myTable GROUP BY itemID is an example about how you can do it (in SQL view of the query). change the table name and the fields name (any thing which is not all cap), as appropriate for your case. Hoping it may help, Vandergh...

Refresh data in the quick journal transaction grid
Hi, I have a requirement to allow user to enter and save amounts in the quick journal setup screen. This is also required to update the amounts as soon as the user selects quick journal setup in the quick journal transaction screen. I have successfully updated the amounts in the database as soon as the user select the quick journal. But the transaction grid is not refreshing automatically. It refresh (get the updated amount from the database) only when I click any row in the grid. Can any body tell me how can I automatically refresh the grid as soon as I updates the amounts in the database. ...

WEB Query in Office For Mac 2004
I have one simple question Does the function "WEB QUERY" in excel of OFFICE 2004 FOR MAC exist. I have a trail verison of OFFICE 2004 FOR MAC but this feature does not exist. I like this option and use it alot at work on the WINDOWS version. Having this option would sway me to buying it for sure. THANKS In article <944a5172.0409260409.68a71831@posting.google.com>, simon.simonblack@gmail.com (Simon Black) wrote: > Does the function "WEB QUERY" in excel of OFFICE 2004 FOR MAC exist. I > have a trail verison of OFFICE 2004 FOR MAC but this feature does not ...