MS Access Query

  • Thread starter Thread starter Bawa
  • Start date Start date
B

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
 
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 said:
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?
Click to expand...
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?
 
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
 
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.
 
Back
Top