Display a field value based on grouping

[Moose74]

Morning all ,

Bit stumped and not sure if this is actually possible.

I have a table which holds details of reports created by different people.
I want to display the last report created by each person so....

I've "group By" 'd the person_index and used Max to get the last date of their report as so -

SELECT inv_reports.person_index, Max(inv_reports.date_of_report) AS MaxOfdate_of_report
FROM inv_reports
GROUP BY inv_reports.person_index;

the problem is i need to display a third field in the query, "report_number". I'm not sure how to include this field in my query to display the report_number for the matching record of maxofdate_of_report.

Any help much appriciated!
Away to grab some more coffee and scratch head some more!

 

[Alansidman]

I did this with two queries.

First one named Query11

SELECT Employees.FirstName, Max(Employees.RDate) AS MaxOfRDate
FROM Employees
GROUP BY Employees.FirstName;

Second one based upon first

SELECT Query11.FirstName, Query11.MaxOfRDate, Employees.RNr
FROM Query11 INNER JOIN Employees ON Query11.MaxOfRDate = Employees.RDate;

Change it up to reflect your field names and table names.

Alan

 

[Dhamdard]

What do you mean by report_number? If this field is already in your table so drag and drop it the next field in your query. If it's not in your table, and you want to count the reports for that user, then do a count of records. In query design, create an elias (field) i.e Number Of Reports:count("*")

 

[Moose74]

Hi Alan, That works great thanks but with one small issue.
If the employee has more than one report on that day it gives multiple rows for that employee in the second query.

only way i can think of is to have time and date in the field to seperate them out but the users manually enter the date for the report in as its sometimes backdated. So i'd need to have them enter in a time as well....

I did this with two queries.

First one named Query11

SELECT Employees.FirstName, Max(Employees.RDate) AS MaxOfRDate
FROM Employees
GROUP BY Employees.FirstName;

Second one based upon first

SELECT Query11.FirstName, Query11.MaxOfRDate, Employees.RNr
FROM Query11 INNER JOIN Employees ON Query11.MaxOfRDate = Employees.RDate;

Change it up to reflect your field names and table names.

Alan

 

[Moose74]

What do you mean by report_number? If this field is already in your table so drag and drop it the next field in your query. If it's not in your table, and you want to count the reports for that user, then do a count of records. In query design, create an elias (field) i.e Number Of Reports:count("*")

Hi,

report_number is a unique field with a number for the report that the user has written. A text field. What i am trying to do is identify the last report each user has created not a count of the number of reports.

So i need to group the users and get the max date, but problem comes up when a user has created more than one report in a day as i only want to have 1 row per user.

Thanks

 

[Dhamdard]

I hope your date field tracks full date (dd/mm/yy hh:mm:ss). Using max on such a field value will give you the last submitted report.

 

[Moose74]

At the moment no it doesn't as the user can enter that date the report was created rather than it being automatically created. (these are offsite reports so sometimes the user enters them into the system 2 or 3 days after they have actually carried out the work that needs to be recorded.)

it maybe that i will need to have them enter a time or have a time automatically entered. Since the time isn't needed i could just use the time that the report was created and tag that onto the date they enter, that would make it unique and maxable i guess....

I hope your date field tracks full date (dd/mm/yy hh:mm:ss). Using max on such a field value will give you the last submitted report.

 

[Alansidman]

Getting more than one will be an issue. Time will solve part of the problem, but that could be problematic to get the users to enter the proper time for the date. Appears that will be a training issue.

Alan

 

[Moose74]

Problem solved!


made a third query which pulls in the results from the second.
in this query have grouped the person_index and selected LAST on all the other fields. Gives me 1 row per person!

That should do the trick , thanks guys for your help!

At the moment no it doesn't as the user can enter that date the report was created rather than it being automatically created. (these are offsite reports so sometimes the user enters them into the system 2 or 3 days after they have actually carried out the work that needs to be recorded.)

it maybe that i will need to have them enter a time or have a time automatically entered. Since the time isn't needed i could just use the time that the report was created and tag that onto the date they enter, that would make it unique and maxable i guess....

 

[Moose74]

Getting more than one will be an issue. Time will solve part of the problem, but that could be problematic to get the users to enter the proper time for the date. Appears that will be a training issue.

Alan

Yup training / PEBKAC issue , considering i've had some of the users / techs phone me up late at night asking how to turn off their laptop the level of competency is pretty low and even the hit them with a big stick approach doesn't work too well!

the third query does the trick in giving me one row per person, and for display purposes it will do!

Cheers for you help!