Last date value from records

  • Thread starter Thread starter Zack Barresse
  • Start date Start date
Z

Zack Barresse

Hello,

Still learning Access, am used to Excel/VBA. I asked this question at
MdbMakers.com (formerly AccessVBA.com) and it's still not working right for
me. (Link: http://www.mdbmakers.com/forums/showthread.php?t=19767)

I have a query I want to produce. There are multiple fields in a table
(Soil_Samples), one of which is a SampleDate field. There is only one
sample per Field per SampleDate, so no duplicates there. I want to
show/print all of the last SampleDate's for each Field sampled (record).

If you follow the thread linked above I almost have it all, it gets very
close. I'd appreciate any help you can give. I've switched all of this
data over from Excel and am having a hard time getting what I want.
Previous to this I had not used Access before. The people at my work are
starting to want this more and more. Thanks for looking.
 
Dear Zack:

If all you need is just the date for the most recent sample, use an
aggregate (Totals) query and use the MAX() of the date.

In the query design grid, select the table and then the desired columns. At
the top, click on the "sigma" (a Greek letter looks like an 'M' on its
side). A new row of selections appears. Choose Max() for the date column.

This may be a start for you on this.

Tom Ellison
 
Hi Tom,

Thanks for the reply. This does work, yes, but when I add more fields to it
to return for the results from the table (7 additional fields) I get
multiple dates. I'm not really sure where to go from here. Anything you
can add to help would be greatly appreciated. Let me know if you need more
info.
 
Dear Zack:

One purpose of a "totals" query is to combine multiple rows into one, and to
have columns across these subsets of rows that are "aggregated" with
functions like SUM, COUNT, MAX, and MIN.

Those columns not aggregated are GROUPed. This means that every combination
of values in those columns that are grouped will form a subset of the rows
within which the aggregated columns are evaluated.

Sounds like a bunch of hyper-technical gunk, right?

OK, make it simple.

You have a soil sample for a field. So you see:

Field Most Recent Sample Date
1 5/19/2005
2 6/03/2005

Now, you add a column, say, who took the sample, by initials. Now, if there
have been 3 samples last year taken by 3 different people, what do you want
to see in the column for the person? Likely you want to see the one who
took the sample that was most recent. Is this right? Is this the kind of
result you want of all 7 of those additional fields you mention?

This will take a different query altogether. Rather than just finding what
that date is, we must use that date to find a whole row of data from which
to return those 7 columns.

If this is a correct assessment, please do this. Open the query in design
view and switch to SQL View. Post the text of the query back here. I'll
work from that to get you the kind of thing you want.

Make sure you send the malfunctioning version of the query that shows the 7
additional fields you want.

Tom Ellison
 
Yes Tom, that is spot on, exactly what I'm looking for in your description.
Here is the SQL for two fields in my query ...

SELECT DISTINCT Max(Soil_Samples.SampleDate) AS MaxOfSampleDate,
Soil_Samples.Field
FROM Soil_Samples
GROUP BY Soil_Samples.Field;

When I add the other fields, this is what it looks like ...

SELECT DISTINCT Max(Soil_Samples.SampleDate) AS MaxOfSampleDate,
Soil_Samples.Field, Soil_Samples.Crop, Soil_Samples.NO3_1ft,
Soil_Samples.NO3_2ft, Soil_Samples.NO3_3ft, Soil_Samples.NO3_4ft,
Soil_Samples.NO3_5ft, Soil_Samples.NH4_1ft
FROM Soil_Samples
GROUP BY Soil_Samples.Field, Soil_Samples.Crop, Soil_Samples.NO3_1ft,
Soil_Samples.NO3_2ft, Soil_Samples.NO3_3ft, Soil_Samples.NO3_4ft,
Soil_Samples.NO3_5ft, Soil_Samples.NH4_1ft;

Does this get you what you need? Let me know if you need anything else.
Thanks for your time Tom, much appreciated. :)
 
Dear Zack:

SELECT SampleDate, Field, Crop, NO3_1ft, NO3_2ft,
NO3_3ft, NO3_4ft, NO3_5ft, NH4_1ft
FROM Soil_Samples T
WHERE SampleDate =
(SELECT MAX(T1.SampleDate)
FROM Soil_Samples T1
WHERE T1.Field = T.Field)

I think this should do it.

The technique is know as a correlated subquery (the last 3 lines of code).
It uses aliases T and T1 for the same table, so the query operates
independently on two distinct copies of the table on which it is abase.

Tom Ellison
 
That absolutely does it for me Tom! Thank you very much! Now comes the
hard part - understanding your solution. :)

I can't thank you enough. I would have never gotten that on my own. Much
appreciated. Let me know if I can ever do anything *Excel'ly* for you. :)
 
Dear Zack:

The explanation is there there are two queries running on the same table.

For every row produced from the "outer" query, the inner query is consulted
to find the most recent sample date for that field. This date is then used
to filter out all the samples for other dates.

Tom Ellison
 
Thanks for the explanation. It's quite a bit to chew, especially for an
Access newb like myself. Can I ask you another quick question? If I run a
query and change some records in that query, will it change the data in the
orignial table? Afaik, this is the case. Please correct me if I'm wrong.

Tom, your help has been invaluable to me. Thank you very much for your
service!

-Zack Barresse, MVP: Excel
 
Dear Zack:

Buy me a beer at the next summit, OK?

Some queries are "updatable" others are not. What we have just worked on
would be, I'm quite sure.

Generally, if it isn't updatable it won't let you change things at all. If
it lets you change, then it's saving the changes.

Tom Ellison
 
Buy me a beer at the next summit, OK?

You got it. ;)
Some queries are "updatable" others are not. What we have just worked on
would be, I'm quite sure.

Generally, if it isn't updatable it won't let you change things at all.
If it lets you change, then it's saving the changes.

Tom Ellison
Click to expand...

Thanks for that Tom. :)
 
Back
Top