0

I have a report in Access 2013 that prints an equipment log. There is a bunch of dates listed for each piece of equipment. I wanted to only print the newest date for each piece of equipment. I have searched the internet and this site with no luck. So any suggestions will be greatly appreciated. My SQL statement is: SELECT dbo_eq_location_transfer_d.equipment_id, dbo_equipment.description, dbo_eq_location_transfer_d.transaction_no, dbo_eq_location_transfer_d.job_no, dbo_jobs.description, dbo_eq_location_transfer_d.date_booked, dbo_eq_location_transfer_d.delivery_time, dbo_eq_location_transfer_d.line_no, dbo_eq_location_transfer_d.row_modified_by, dbo_eq_location_transfer_d.comment FROM (dbo_eq_location_transfer_d INNER JOIN dbo_jobs ON dbo_eq_location_transfer_d.job_no = dbo_jobs.job_no) INNER JOIN dbo_equipment ON dbo_eq_location_transfer_d.equipment_no = dbo_equipment.equipment_no ORDER BY dbo_eq_location_transfer_d.equipment_id, dbo_eq_location_transfer_d.transaction_no;

The date_booked field is the date field I am trying narrow down. I have a simple SQL query that works and I have been trying copy that into the about SQL but cannot seem to get it to mesh. It is: SELECT [dbo_eq_location_transfer_d.equipment_no], Max(dbo_eq_location_transfer_d.date_booked) AS ["Newest Date"] FROM dbo_eq_location_transfer_d GROUP BY [dbo_eq_location_transfer_d.equipment_no];

Erik A
  • 31,639
  • 12
  • 42
  • 67
BurtG
  • 21
  • 4
  • Can you make an Access `GROUP BY` query which gives you the `Max()` date for each piece of equipment? – HansUp Apr 11 '14 at 18:42
  • I have tried that but I cannot seem to find where to do that? – BurtG Apr 11 '14 at 19:09
  • Hmmm, need to be certain of the question... Do you mean you only want one row for that equipment and it should show the most recent date? Or do you mean you want to print all rows for that equipment, but only PRINT the most current date? – Wayne G. Dunn Apr 11 '14 at 21:07
  • I want only want to print one row for the equipment and it should show the most recent date. – BurtG Apr 11 '14 at 21:44

2 Answers2

0

In your query set the date fields criteria to:

>Now()-30

This will show any dates for the last 30 days just change 30 to the number of days you want to see.

Mike
  • 144
  • 4
  • That works if the date is pretty current but I have some that have not had any activity for a couple of months. Can we adjusted the date answer to just take the newest date? – BurtG Apr 11 '14 at 19:01
0

Now that I understand your structure & data, here is what I did: (1) Create the following query to select only the most recent 'date_booked' for each 'equipment_no'; save the query with name '23020071_A':

SELECT dbo_eq_location_transfer_d.equipment_no, 
First(dbo_eq_location_transfer_d.transaction_no) AS FirstOftransaction_no, 
First(dbo_eq_location_transfer_d.job_no) AS FirstOfjob_no, 
First(dbo_eq_location_transfer_d.date_booked) AS FirstOfdate_booked
FROM (dbo_eq_location_transfer_d 
INNER JOIN dbo_jobs ON dbo_eq_location_transfer_d.job_no = dbo_jobs.job_no) 
INNER JOIN dbo_equipment ON dbo_eq_location_transfer_d.equipment_no = dbo_equipment.equipment_no
GROUP BY dbo_eq_location_transfer_d.equipment_no
ORDER BY First(dbo_eq_location_transfer_d.date_booked) DESC;

(2) I created the following query combining the new query with your existing query:

SELECT dbo_eq_location_transfer_d.equipment_id, dbo_equipment.description, 
dbo_eq_location_transfer_d.transaction_no, dbo_eq_location_transfer_d.job_no,
dbo_jobs.description, dbo_eq_location_transfer_d.date_booked, 
dbo_eq_location_transfer_d.delivery_time, dbo_eq_location_transfer_d.line_no, 
dbo_eq_location_transfer_d.row_modified_by, dbo_eq_location_transfer_d.comment
FROM 23020071_A INNER JOIN ((dbo_eq_location_transfer_d 
INNER JOIN dbo_jobs ON dbo_eq_location_transfer_d.job_no = dbo_jobs.job_no) 
INNER JOIN dbo_equipment ON dbo_eq_location_transfer_d.equipment_no = dbo_equipment.equipment_no) 
ON ([23020071_A].FirstOftransaction_no = dbo_eq_location_transfer_d.transaction_no) 
AND ([23020071_A].equipment_no = dbo_eq_location_transfer_d.equipment_no) 
AND ([23020071_A].FirstOfjob_no = dbo_eq_location_transfer_d.job_no)
ORDER BY dbo_eq_location_transfer_d.equipment_id, dbo_eq_location_transfer_d.transaction_no;

Now when I run the second query, it returns only the most recent row for that piece of equipment.

Wayne G. Dunn
  • 4,282
  • 1
  • 12
  • 24
  • I am using a query but I must be doing something wrong because I cannot get the Max keyword to work. Is there a certain way to do your suggestion? Thanks. – BurtG Apr 11 '14 at 20:24
  • Okay its got to be Friday. How do I update my question? Thanks. – BurtG Apr 11 '14 at 20:43
  • Well I tried that and it did not work but I am recreating the query one column at a time, with your suggestion, and it seems to be working. So there must be something in the original SQL it does not like. – BurtG Apr 11 '14 at 21:20
  • Well apparently it does not like "group by" on all fields because the minute I add the "transaction no" field it quits working. So I guess I will keep plugging away until I get it to work. – BurtG Apr 11 '14 at 21:28
  • It is a date field. No unusual properties. – BurtG Apr 14 '14 at 15:53
  • I do not get any message. I did do some more research and finally found at http://stackoverflow.com/questions/17592737/access-query-to-select-rows-that-are-the-max-of-one-column-but-unique-on-another a answer but my query combines three files and that is what I am having a problem with. – BurtG Apr 14 '14 at 19:32
  • In reviewing these Q & A's, 'Transaction No' is a DATE field??? I just created your 3 tables w/ fields, and guessed at sample data. I ran your posted SQL and it returned multiple rows. I then changed to 'Totals' and 'Max of Date_Booked' (no error, but not sure of your field types)... there are still 3 rows returned, but that is because of all the other unique values (Group By). So, you get no messages? You were able to use 'Totals' & 'Max'? Can you share some actual data values? – Wayne G. Dunn Apr 14 '14 at 20:23
  • Enter Equipment Number transaction_no job_no dbo_jobs.description date_booked delivery_time line_no row_modified_by dbo_equipment.description 4W1 10285 OSHOP ORANGE SHOP 2/26/2013 1/1/1900 7:00:00 AM 28 LINDA JOHN DEERE 4W1 19177 OSHOP ORANGE SHOP 8/30/2013 1/1/1900 7:00:00 AM 5 LINDA JOHN DEERE 4W1 24209 OSHOP ORANGE SHOP 1/3/2014 1/1/1900 7:00:00 AM 47 LINDA JOHN DEERE – BurtG Apr 14 '14 at 20:35
  • The first query runs okay but the second one gives me the error "The specified field "dbo_eq_location_transfer_d.transaction' could refer to more than one table listed in the FROM clause of your SQL statement". I finally got this to work at the end of the day by doing something similar to your query's. I created one query that gave the equipment id with just the newest day and then I combined it with the query that list all records and it works. I guess that is probably the only way to do this. – BurtG Apr 15 '14 at 13:58
  • Yes, since you need to get only most recent for one field, yet combine with many other fields (and tables), you need to eleminate the older records at some point. Strange that I got no error on the 2nd query. Did you mean to say 'transaction_no'? – Wayne G. Dunn Apr 15 '14 at 14:06
  • I reran the query and got the same error I described earlier. – BurtG Apr 15 '14 at 14:47
  • You didn't answer my question: "Did you mean to say 'transaction_no'?" The second SQL I provide only has ONE reference to the field you mention -- and it was qualified by the table name. Have you altered the SQL? If so, post ALL your current SQL. – Wayne G. Dunn Apr 15 '14 at 15:00
  • I copied and pasted your queries in my database and did exactly as you said. After I pasted the second query I pressed the run box and the message appeared on my screen. I copied that message word for word from the message box. – BurtG Apr 15 '14 at 15:22
  • I can't explain this because the field in the error you posted does not exist anywhere in the query. There IS a reference to 'Transaction_NO' but not to field 'Transaction'. Open the SQL in query designer and see if you can spot the problem because I sure don't. – Wayne G. Dunn Apr 15 '14 at 15:39
  • I cleared the query and redid my copy and paste and now it works. – BurtG Apr 15 '14 at 15:47