If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
MS Access Query
Hi
I need help with this: COnsider this Data: Description // SW Version // Asset Tag // Machine Name // Name Acrobat Standard// 4 // 12345 // abcde // Steve Acrobat Standard// 6 // 12346// abcde // Steve Acrobat Standard// 6 // 12346// abcde // Steve Acrobat Standard// 9 // 23456 // zxcvb// Dick Acrobat Pro // 7 // 65432 // qwert // Dave Acrobat Pro// 9 // 54321 // qwert // Dave Acrobat Std // 8 //67324 // asdfg // Ryan I want the output as only the Unique & Highet Vales Unique Based on Machine Name And Highest SW Version Output: Description // SW Version // Asset Tag // Machine Name // Name Acrobat Standard// 6 // 12346// abcde // Steve Acrobat Standard// 9 // 23456 // zxcvb// Dick Acrobat Pro// 9 // 54321 // qwert // Dave Acrobat Std // 8 //67324 // asdfg // Ryan I have this Data in Excel, what is the best way to get the output? I was thinking I can import the data in MS Access and then do a query to get the desired output, I have Access 2003. Or can this be done in Excel? Please guide me. Thanks |
#2
|
|||
|
|||
MS Access Query
Bawa,
Hmmm, I *know* it can be done in Access and this being an Access newsgroup that will most likely be the standard reply. I cross-posted to the Excel newsgroup so you can get an answer about the possiblity and/or how-to in Excel. -- Gina Whipp 2010 Microsoft MVP (Access) "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "Bawa" wrote in message ... Hi I need help with this: COnsider this Data: Description // SW Version // Asset Tag // Machine Name // Name Acrobat Standard// 4 // 12345 // abcde // Steve Acrobat Standard// 6 // 12346// abcde // Steve Acrobat Standard// 6 // 12346// abcde // Steve Acrobat Standard// 9 // 23456 // zxcvb// Dick Acrobat Pro // 7 // 65432 // qwert // Dave Acrobat Pro// 9 // 54321 // qwert // Dave Acrobat Std // 8 //67324 // asdfg // Ryan I want the output as only the Unique & Highet Vales Unique Based on Machine Name And Highest SW Version Output: Description // SW Version // Asset Tag // Machine Name // Name Acrobat Standard// 6 // 12346// abcde // Steve Acrobat Standard// 9 // 23456 // zxcvb// Dick Acrobat Pro// 9 // 54321 // qwert // Dave Acrobat Std // 8 //67324 // asdfg // Ryan I have this Data in Excel, what is the best way to get the output? I was thinking I can import the data in MS Access and then do a query to get the desired output, I have Access 2003. Or can this be done in Excel? Please guide me. Thanks |
#3
|
|||
|
|||
MS Access Query
Bawa wrote:
Hi I need help with this: COnsider this Data: Description // SW Version // Asset Tag // Machine Name // Name Acrobat Standard// 4 // 12345 // abcde // Steve Acrobat Standard// 6 // 12346// abcde // Steve Acrobat Standard// 6 // 12346// abcde // Steve Acrobat Standard// 9 // 23456 // zxcvb// Dick Acrobat Pro // 7 // 65432 // qwert // Dave Acrobat Pro// 9 // 54321 // qwert // Dave Acrobat Std // 8 //67324 // asdfg // Ryan I want the output as only the Unique & Highet Vales Unique Based on Machine Name And Highest SW Version Output: Description // SW Version // Asset Tag // Machine Name // Name Acrobat Standard// 6 // 12346// abcde // Steve Acrobat Standard// 9 // 23456 // zxcvb// Dick Acrobat Pro// 9 // 54321 // qwert // Dave Acrobat Std // 8 //67324 // asdfg // Ryan I have this Data in Excel, what is the best way to get the output? I was thinking I can import the data in MS Access and then do a query to get the desired output, I have Access 2003. Or can this be done in Excel? You could import the data into Access, which will require re-importing it when the data changes in Excel, or you could create a link in Access to the Excel spreadsheet, which would be my choice. Either way, getting the result you want in Access will require using a Union query, which will require honing your sql skills since union queries cannot be created in the Access query builder's Design View. Before I go into details, I need to know what you would desire for a result in this situation: Description // SW Version // Asset Tag // Machine Name // Name Acrobat Standard// 4 // 12345 // abcde // Steve Acrobat Standard// 6 // 12346// abcde // Steve Acrobat Standard// 6 // 12346// abcde // Steve Acrobat Standard// 9 // 23456 // zxcvb// Dick Acrobat Pro // 7 // 65432 // qwert // Dave Acrobat Pro// 9 // 54321 // qwert // Dave Acrobat Std // 8 //67324 // asdfg // Ryan Acrobat Standard// 8 // 67324 // asdfg // Ryan Would you want both "Acrobat Std" and "Acrobat Standard" to appear in Ryan's results? Or one of them. If the latter, which one? -- Microsoft MVP - ASP/ASP.NET - 2004-2007 Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" |
#4
|
|||
|
|||
MS Access Query
Assumption: SW Version field is a number field.
SELECT [Description] , [SW Version] , [Asset Tag] , [Machine Name] , [Name] FROM [SomeData] WHERE [SomeData].[SW Version] = (SELECT Max(Temp.[SW Version]) FROM [SomeData] as Temp WHERE Temp.[Machine Name] = [SomeData].[Machine Name]) If SW Version is a text field that contains only numbers, you can use the val function to force a valid comparison. SELECT [Description] , [SW Version] , [Asset Tag] , [Machine Name] , [Name] FROM [SomeData] WHERE Val([SomeData].[SW Version]) = (SELECT Max(Val(Temp.[SW Version])) FROM [SomeData] as Temp WHERE Temp.[Machine Name] = [SomeData].[Machine Name]) You can either import the data in Access or create a link to the Excel sheet. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Bawa wrote: Hi I need help with this: COnsider this Data: Description // SW Version // Asset Tag // Machine Name // Name Acrobat Standard// 4 // 12345 // abcde // Steve Acrobat Standard// 6 // 12346// abcde // Steve Acrobat Standard// 6 // 12346// abcde // Steve Acrobat Standard// 9 // 23456 // zxcvb// Dick Acrobat Pro // 7 // 65432 // qwert // Dave Acrobat Pro// 9 // 54321 // qwert // Dave Acrobat Std // 8 //67324 // asdfg // Ryan I want the output as only the Unique & Highet Vales Unique Based on Machine Name And Highest SW Version Output: Description // SW Version // Asset Tag // Machine Name // Name Acrobat Standard// 6 // 12346// abcde // Steve Acrobat Standard// 9 // 23456 // zxcvb// Dick Acrobat Pro// 9 // 54321 // qwert // Dave Acrobat Std // 8 //67324 // asdfg // Ryan I have this Data in Excel, what is the best way to get the output? I was thinking I can import the data in MS Access and then do a query to get the desired output, I have Access 2003. Or can this be done in Excel? Please guide me. Thanks |
#5
|
|||
|
|||
MS Access Query
Exce pivot table tools could handle this, although keeping the columns in the
same order you have them might be best done with a little bit of VBA code. The code steps could mostly be recorded with the macro recording utility. Post back her if you need more. "Gina Whipp" wrote: Bawa, Hmmm, I *know* it can be done in Access and this being an Access newsgroup that will most likely be the standard reply. I cross-posted to the Excel newsgroup so you can get an answer about the possiblity and/or how-to in Excel. -- Gina Whipp 2010 Microsoft MVP (Access) "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "Bawa" wrote in message ... Hi I need help with this: COnsider this Data: Description // SW Version // Asset Tag // Machine Name // Name Acrobat Standard// 4 // 12345 // abcde // Steve Acrobat Standard// 6 // 12346// abcde // Steve Acrobat Standard// 6 // 12346// abcde // Steve Acrobat Standard// 9 // 23456 // zxcvb// Dick Acrobat Pro // 7 // 65432 // qwert // Dave Acrobat Pro// 9 // 54321 // qwert // Dave Acrobat Std // 8 //67324 // asdfg // Ryan I want the output as only the Unique & Highet Vales Unique Based on Machine Name And Highest SW Version Output: Description // SW Version // Asset Tag // Machine Name // Name Acrobat Standard// 6 // 12346// abcde // Steve Acrobat Standard// 9 // 23456 // zxcvb// Dick Acrobat Pro// 9 // 54321 // qwert // Dave Acrobat Std // 8 //67324 // asdfg // Ryan I have this Data in Excel, what is the best way to get the output? I was thinking I can import the data in MS Access and then do a query to get the desired output, I have Access 2003. Or can this be done in Excel? Please guide me. Thanks . |
Thread Tools | |
Display Modes | |
|
|