Using Domain Aggregate expression in Update Query 2

[TheresAlwaysAWay]

I HATE it when something that should be easy turns into a can of worms!

I have two tables, AllVehicles and Dealers. Both have a common field, DealerID, which is the PK of Dealers table and just a simple data field on AllVehicles. I have a field in the Dealers table, 1stQuote, which is designed to hold the date that the first quote for one of that dealer's customers was entered into the DB. The date of interest is found in [AllVehicles].[QTDate].

That field has been in place for quite a while but the coding that was intended to populate it as the first quote was being written was weak and didn't always get it right. I've corrected those errors, and now I want to run an update query on the Dealers table that makes this value =Min([QTDate]) for every dealer in the DB. I have the two tables joined where equal on the DealerID field, but can not get it to perform as expected. I get messages such as "you tried to execute a query that does not include *expression* as part of an aggregate function" and others, depending on how I've configured my misguided attempts.

This must be do-able but I'm clearly missing it. Here's current failing SQL.

UPDATE AllVehicles INNER JOIN Dealers ON AllVehicles.DealerID = Dealers.DealerID SET Dealers.[1stQuote] = Min([QTDate]);

I know one of the wizards out there will probably be laughing at my ignorance, but I don't mind as long as you can point me in the right direction!

As always, thanks in advance.

 

[Andrzejek]

>DealerID, which is the PK of Dealers table and just a simple data field on AllVehicles
I hope DealerID is not just "a simple data field on AllVehicles", but it is a Foreign Key field. Right?

Your Update statement can be done, but it does not make much sense (to me)
In your Dealers table, your field: 1stQuote. What information does it hold?
First quote of the price of a vehicle? If so, this field belongs in AllVehicles table.



---- Andy

There is a great need for a sarcasm font.

 

[TheresAlwaysAWay]

Sorry. I thought I'd made it clear by the description, but it's a date field. Yes, the AllVehicles.DealerID field is the foreign key for Dealers.DealerID.

OK, let me try to explain again. Virtually every customer in our DB was referred by a Dealer. When a new dealer's name is entered into our db there are, of course, no related customer records at that point. For simplicity, a theoretical dealer was entered into our DB on 1/1/2011, but the first customer referred by that dealer called in and asked for an insurance quote on 2/15/2011. The Dealers.1stQuote field is designed to be populated at the time that first record is written and in this case should contain 2/15/2011. No matter that the dealer has been in our DB for 10 years and has sent 10,000 quotes, there is only one 1stQuote date for that dealer, and that date is associated with and must be stored under the appropriate DealerID in the Dealers table. The data on which it is based is stored in AllVehicles.QTDate, however, since all quote information is stored there, and that is why I'm trying to use Min([QTDate]) as the criterion to populate it.

Again, the code that was designed to populate this field at the time the first quote was written was weak and has been rewritten. What I'm trying to do right now is just run an update to correct the data throughout the entire DB so that we know everything is right moving forward.

 

[Andrzejek]

I am sure is could be done with one Update statement, but in simple terms, one shot deal, I would do it with a simple code:

Dim rstD As Recordset
Dim rstV As Recordset
Dim strSQL As String

strSQL = "Select Min(QTDate) As MinDate, DealerID From AllVehicles Group By DealerID"
rstV.Open strSQL

strSQL = "Select DealerID from Dealers"
rstD.Open strSQL

Do While Not rstD.EOF
    strSQL = "DealerID = " & rstD!DealerID .Value
    rstV.Filter = strSQL

    If rstV.RecordCount > 0 then
        strSQL = "Update Dealers Set 1stQuote = #" & rstV!MinDate.Value & "# WHERE DealerID = " & rstD!DealerID .Value
        Execute strSQL
    End If

    rstD.MoveNext
Loop

rstD.Close
Set rstD = NOthing

---- Andy

There is a great need for a sarcasm font.

 

[TheresAlwaysAWay]

Thank you. I will try it and let you know, but I'm curious as to why, even as a one-shot deal, an Update query would not work, or would not be your first choice. Is it because of the Min function?

 

[Andrzejek]

I did not say: "an Update query would not work", I just don't kow how to set it up for it to work in this case.

For me, an update statement is:[tt]
Update SomeTable
Set Afield = A_Value
Where Somefield = SomeOtherValue[/tt]

An example of getting a value from one table and update another table is here, but they do not deal with Min() function.

"would not be your first choice"
Well, my choice is what works for me and what I can do/know.


---- Andy

There is a great need for a sarcasm font.

 

[TheresAlwaysAWay]

Perfect answer! Thank you. I'll definitely run this and let you know.

I was focused on the update query as being the solution here for obvious reasons and I never even considered any other alternative.

 

[Andrzejek]

That's why TT is such a good source of ideas.
Just be advised - the code I gave you is just a 'concept', a 'pseudo-code', an idea, not a 'working' code.


---- Andy

There is a great need for a sarcasm font.

 

[TheresAlwaysAWay]

OK, I tried to run it and it failed on rstV.Open with the error message "Method or data member not found". Any suggestions?

 

[Andrzejek]

I told you - this is not a working code.

If you have never used recordsets in Access, read all about it here, here tat should get you going (I hope...)


---- Andy

There is a great need for a sarcasm font.

 

[TheresAlwaysAWay]

Thank you for all your help. I was struggling with the recordsets when the answer suddenly struck me.

I never had a problem using a select query to generate the first quote date, but when I tried to move it to an update query it just wouldn't cooperate. The answer was to use a Make Table query and create the 1stQuote field, and then just use an Update query to update the field with that value. The calculation had already been done while creating the table, and then the update was simple.

I really do appreciate all the thought and effort you put into this. The main thing is that the problem is solved and I can move on.

I am going to give you a star. It's the least I can do. Thank you again.

 

[dhookom]

For future reference you can try a simple update query (assuming DealerID is numeric):

UPDATE Dealers SET [1tQuote] = DMin("QtDate","AllVehicles","DealerID = " & DealerID)

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[TheresAlwaysAWay]

Thanks, Duane. I think that is the syntax I was missing. Like I said in my opening post, I knew it was simple but I just couldn't see it! I already finished the one-and-done operation I was trying to accomplish using the two-step solution I posted. It may have been clunky, but it worked.

But as you said, for future reference this is really helpful. Another star for you!