Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Most recent dates

Posted on 2012-09-15
6
Medium Priority
?
775 Views
Last Modified: 2012-09-16
To whom it may concern,

The attached file represents the output that I get after running a query in MS Access. From this output, I need to pick the records with the most recent and second most recent dates FOR EACH individual.

Does anyone have any ideas?

-Maroulator
Example.xlsx
0
Comment
Question by:maroulator
  • 3
  • 2
6 Comments
 
LVL 1

Expert Comment

by:vadoodetm
ID: 38402622
Do you want to do this in excel?  Or you want to modify your Access query to give desired output?
0
 

Author Comment

by:maroulator
ID: 38402641
I want to modify my Access query. I was thinking of creating a make-table query to produce the output that I have in the attached spreadsheet and then run another query (this is where you come in) to pick the most recent and second most recent dates from the table created out of the make-table query.

I am open to any and all ideas; if you can produce a query that gives me the output in the attached spreadsheet AND picks the most recent and second most recent dates, then so much the better.

Let me know if you have any more questions.

-Maroulator
0
 
LVL 40

Expert Comment

by:als315
ID: 38402662
If your query is named qry, you can use this query:
SELECT qry.Name, qry.Salary, qry.Title, qry.Date
FROM qry
WHERE (qry.Date In (SELECT Top 2 [Date] From qry as q Where q.Name = qry.Name Order by q.Date))
ORDER BY qry.Name, qry.Date DESC;

Open in new window

0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 40

Expert Comment

by:als315
ID: 38402666
Try not to use reserved words (Date, Name) as field name. In this case you should use square brackets around this field
0
 

Author Comment

by:maroulator
ID: 38402686
And how can I take the percentage difference between the two records within the same query?
0
 
LVL 40

Accepted Solution

by:
als315 earned 2000 total points
ID: 38402691
Precentage is absolutely other question and approach is also different. Due to EE rules you should ask it separately, but you can try this query:
SELECT qry.Name, qry.Salary, qry.Date, qry_1.Salary, qry_1.Date, 100*([qry].[Salary]-[qry_1].[Salary])/[qry_1].[Salary] AS Perc
FROM ((SELECT Query1.Name, Max(Query1.Date) AS MaxOfDate, Min(Query1.Date) AS MinOfDate
FROM (SELECT qry.Name, qry.Salary, qry.Title, qry.Date
FROM qry
WHERE (qry.Date In (SELECT Top 2 [Date] From qry as q Where q.Name = qry.Name Order by q.Date))
ORDER BY qry.Name, qry.Date DESC) as Query1
GROUP BY Query1.Name)  AS qry0 INNER JOIN qry ON (qry0.Name = qry.Name) AND (qry0.MaxOfDate = qry.Date)) INNER JOIN qry AS qry_1 ON (qry0.Name = qry_1.Name) AND (qry0.MinOfDate = qry_1.Date);

Open in new window


May be better to step back and look at original tables. May be solution will be not so complicated.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

576 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question