MS Access Query

[Bawa]

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

 

[Gina Whipp]

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

 

[Bob Barrows]

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?

 

[John Spencer]

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

 

[B Lynn B]

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.