• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 174
  • Last Modified:

Access Query

I have a table with data on account number and holder name fields. I need to extract two types of information:
List of most recent option by account from holders of the account
111 AAA 2 15/10/2013
111 BBB 1 15/10/2013

On an account level, the different options selected independently from the holder.
111 2 1

Please find file attached.

Your help would be greatly appreciated.

1 Solution
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You'd have to give us more to go on. For example, do you ALWAYS want the first 3 characters extracted? And then you also want the value in the 9th position extracted?

Or do you want everything preceding that last Date value extracted up to the SECOND space character (from the END of that string)?

In other words - you have to more clearly define the rules you have in place for extracting the data before we can really help.

It's easy to grab the first 3 characters of a String. In a query, you'd do this:

SELECT YourField, Left(YourField, 3) AS [First_Three] FROM YourTable

You can use the Mid function to grab items as well:

SELECT YourField, Mid((YourField, 1, 3) AS [First_Three] FROM YourTable

To grab the 9th character:

SELECT YourField, Mid((YourField, 9, 1) AS [Ninth_Char] FROM YourTable

Here's more info on those functions:

Try this query:
SELECT Table1.ID, Table1.AccountNumber, Table1.HolderName, Table1.Option, Table1.DateOption
FROM Table1
WHERE (((Table1.DateOption)=DateValue(DMax("DateOption","Table1","AccountNumber=" & Chr(34) & [Table1].[AccountNumber] & Chr(34) & " And HolderName = " & Chr(34) & [Table1].[HolderName] & Chr(34)))))
ORDER BY Table1.AccountNumber, Table1.HolderName;

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now