using max/min etc. alongside other differing values

[rdougherty]

I need to know how access reads SQL that looks at data such as this:



Records are being updated by a report pull due to an avoidable manual process. This pull will time-out in an antiquated system we are forced to use. Therefore, to mitigate this we are pulling only certain dates of data to make sure it is the most likely to change over time... Well, I want to first combine all of the records together into a table with old record + new record, then use a query to select the most recent "append date" which I will add. If it is recent enough to have a sister record with a newer date, then it goes bye bye in the newly Grouped By table.

I need to obviously keep all new data associated with the duplciate record that contains the Max append date. Not, merely create a newly defined record containing components from the Max record, components from the other... get my point?

What I need to know for sure is, how Sequel will interpret using Max Append date alongside other fields that have differing values. How do I keep an existing record intact if that is not the default behavior?

Thank you,

edit: like this...

SELECT test_sql.[Record ID], First(test_sql.Cust_Name) AS FirstOfCust_Name, First(test_sql.Address) AS FirstOfAddress, First(test_sql.[Purchase Total]) AS [FirstOfPurchase Total], Max(test_sql.[Append Date]) AS [MaxOfAppend Date]FROM test_sql
GROUP BY test_sql.[Record ID];

[June7]

Review http://allenbrowne.com/subquery-01.html#TopN

[rdougherty]

I know all about sub-queries, lol. but thanks.

I just needed to know how Access is interpreting the above. Short version: Is Access selecting the First() value from the same record as the Max() value is determined? Does order matter? Any way to avoid a sub-query? I know how to make them, I just don't like doing it when it is not really necessary. Is it really necessary?

[June7]

No, it does not necessarily select First() from the same record as the Max(). That's why a subquery is needed to return the entire record. DMax() can be used in criteria but performance won't be any better.

[CJ_London]

Is Access selecting the First() value from the same record as the Max() value is determined?

possibly, but unlikely

Does order matter?

not for max, but first is the first in the recordset - without an order that can change each time the query is run

simple example

SELECT Max(Date) as MaxDate, First(Date) as FirstDate FROM myTable

will produce a different result to

SELECT Max(Date) as MaxDate, First(Date) as FirstDate FROM myTable ORDER BY Date

Any way to avoid a sub-query?

depends what you are trying to do - perhaps you can use another query instead (will generally be faster)

Assuming your recordID is a unique field, grouping makes no sense for your query since all records will be returned

How do I keep an existing record intact if that is not the default behaviour?

if you are trying to find the latest record for each contact based on the largest ID for a contact you might have

SELECT C.*
FROM tblContacts C
WHERE ID=dmax("ID","tblContacts","Name='" & Name & "'")

or

SELECT C.*
FROM tblContacts C
WHERE ID=(SELECT Max(ID) FROM tblContacts T WHERE Name='" & C.Name & "'")

or

SELECT C.*
FROM tblContacts C INNER JOIN (SELECT Max(ID) as MaxID FROM tblContacts GROUP BY Name) T ON C.ID=T.MaxID

[rdougherty]

thank you both!

So, I am thinking the cause of my confusion on this matter is that some queires, based on their Groupings, can always provide you the correct associated values with a Max() or Min() on another value, such as date. That has lead me to believe that it works when your grouping is different.

What I actually need now, is a better way to add duplicates that are unavoidably pulled again each week, append them to same dataset, then group on the latest value and pull in all associated values belonging to the kept record.

I am familiar with, and have implemented setups where we first check to see if a record exists, mark it, and then import all that don't already exist. I was hoping not to have to set up so many steps, and get it done in just a copule - append new records, run grouping query, create new t able with that.

(I am not able to update the whole set each pull, for reasons of system performance limitations)

[CJ_London]

I was hoping not to have to set up so many steps

it's should only be one query. If you still require help you will need to provide some example data to demonstrate what you are trying to do - and please use realistic examples, not 'apples and lemons'. From the sound of it, example data required from the file you want to import, example of the destination data and an example of what the destination should look like once the data has been imported.

[rdougherty]

turns out I need to aggregate my new import prior to bringing it into access, for row number reasons. If I bring them into Access first, I just get tons of DB bloat and for a very slow query that attempt to determine the max of over a million rows. Better to manually group data in Excel (pivot) and then import only the pivot, making the dataset smaller for processing. Then, it is simple to merely append only the new rows, and replace a couple aggregated values for the ones that are not new to the table.

Thanks, this has been educational/