Lates date for each ID

[Rzadziu]

Hi,



I have create query where i have data from to tabels and also i have create some calculating fields.

Now i want to filtr out latest ConDate for each toolID and then use calculation to show just records which are bigger then frequency.

Unfortunately my access knowledge is low and I need your help to solve this issue.

SELECT Controls.IDTool, Controls.[Serial number], Controls.ConDate, Tools.User, Tools.Size, Date() AS Today, DateDiff("d",[ConDate],[Today]) AS DifDate, Tools.Frequency
FROM Tools INNER JOIN Controls ON Tools.IDTool = Controls.IDTool;

[JoeM]

Take a look at Aggregate (Totals) Queries in Access's built-in help files. You can "group" on certain records, and then take the Max (latest) value of another field. Note that you only want to include fields that are either "grouped" or "aggregated" in your result set for it to work out correctly.
To compare to Frequency, you would just use that in Criteria.

If you have issues getting it to work out the way you like, please post a small data sample and then post what your expected results should look like.

[Rzadziu]

Unfortunately i cant fix that myself.
I started to create a query that has to show me the tools I need to check this week but i cant filter out rigth data.
Please have a look on attached file.
All ideas are welcome

CDC.zip


Regards
Marcin

[JoeM]

Unfortunately, I am unable to download files from my present location. I may be able to take a look tonight when I am at home.
Or, you could post a small sample of your data, and your expected outcome (as screen prints).

[JoeM]

I tried opening your database, but was getting an "unrecognized format" error. I see that you are using Access 2010. I only have Access 2007. So I am unable to open your database.

[Rzadziu]

Ok please have a look ot print screen maybe this will help.

[JoeM]

Create a separate Aggregate Query just to get your Latest ConDate for each IDTool, i.e.

SELECT 
   Controls.IDTool, 
   Max(Controls.ConDate) AS MaxOfConDate
FROM 
   Controls
GROUP BY 
   Controls.IDTool;

Now, include this new aggregate query in your other query, linking on IDTool and MaxOfConDate (to ConDate in your other table).

[Rzadziu]

You are good !!!!
It's work as I woud like to have thanks a lot.

[Rzadziu]

Hi again :-)
Im still working on this database and now I have a problem with the criteria for a query.

I would like to make criteria based on other field in this same query.
If is possible i would like to show only those records where Difdate is bigger than Frequency

SELECT Tools.IDTool, Tools.Frequency, Controls.ConDate AS [Last Control], Users.EmpName AS Name, Tools.Size, Maxofcontools.DifDate, Tools.Serial
FROM ((Users INNER JOIN Tools ON Users.[EmpID] = Tools.User) INNER JOIN Controls ON Tools.[IDTool] = Controls.[Serial number]) INNER JOIN Maxofcontools ON (Controls.[Serial number] = Maxofcontools.[Serial number]) AND (Controls.ConDate = Maxofcontools.MaxOfConDate);

[JoeM]

Just add criteria to your query:

WHERE Maxofcontools.DifDate > Tools.Frequency

For future reference, I think you want to post brand new questions to new threads. Otherwise, if I am not around, there is a good chance no one will see this as a new question and it might not get answered.

[Rzadziu]

Hej JoeM,
Thanks for your replay.
I didn't want to make two threads for the same datebase :-)

When i run query with criteria from you i got nothing, no records.

Please have a look on links below.

http://screenshooter.net/6633498/gjrjaso
http://screenshooter.net/6633498/vfmkbrx
http://screenshooter.net/6633498/bpdonkp

[JoeM]

Thanks for your replay.
I didn't want to make two threads for the same datebase :-)

You are really going to limit your replies if you do that. People often look for questions with no replies to search for unanswered questions (most of us do not read every thread, and threads that already have replies are often not looked at, assuming that the person is already being helped). The only people who get notified of a new post to an existing thread are those who have already replied or subscribed to those threads. And just because I was willing and able to help with a previous question does not mean I am able to on the next one.

I think you only want to keep posting to the same thread if your new question is directly related and dependent on your previous one (where the question doesn't make any sense without the previous information). If you have a brand new question (even if on the same database or same query), you'll increase the number of people who see it and increase your chances for help immensely if you post it to a new question.

[JoeM]

Back to your question...

What does your Frequency table look like? In order for your criteria to work, it must be a numeric (and not text) entry.

[Rzadziu]

Ok next time i will make new thread

You you're right again it was text field.

Thanks a lot