UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Need Help Using Domain Aggregate Expression In Update Query, Access 2007    
 
   
bcmarshall
post Jul 12 2019, 08:25 PM
Post#1



Posts: 38
Joined: 17-July 10
From: Ventura, CA


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 the foreign key 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]);

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, imagine a theoretical dealer that 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.

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.
Go to the top of the page
 
RJD
post Jul 12 2019, 09:33 PM
Post#2


UtterAccess VIP
Posts: 9,911
Joined: 25-October 10
From: Gulf South USA


Hi: Okay, I'm not 100% sure I understand, but if you want to put the earliest QTDate from AllVehicles in 1stQuote for Dealers - for EACH Dealer, then perhaps this is what you want ...

UPDATE Dealers SET 1stQuote = DMin("[QTDate]","[AllVehicles]","[DealerID]=" & [DealerID]);

This assumes that DealerID is numeric.

This does not require a JOIN and looks up the Min date using a domain function DMin. Min by itself requires an aggregate query (GROUP BY) and that does not seem necessary here at all. The "join" occurs as each record in Dealers in treated and the WHERE condition in the DMin is satisfied.

See the demo attached. And see if it does what you want. Verify that the Dealers field is empty, run the UPDATE query, and check the new dates against the AllVehicles table records.

HTH
Joe
Attached File(s)
Attached File  DealerQuoteDate.zip ( 18.52K )Number of downloads: 0
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
bcmarshall
post Jul 13 2019, 03:06 AM
Post#3



Posts: 38
Joined: 17-July 10
From: Ventura, CA


Thank you, RJD. I was actually able to solve the problem myself.


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

I may have had to do this in two steps, but it's sort of a one-and-done thing anyway. I just need to run it once to make sure all the data is correct.

There was something about using the Min function within the Update structure that just didn't allow it to happen, but since I separated them all is well.

Thank you very much for helping me. I hadn't considered concatenating the [DealerID] field as you did, and that may have been the solution that I was missing, but I already had the problem corrected so I never tried it.
Go to the top of the page
 
RJD
post Jul 13 2019, 08:59 AM
Post#4


UtterAccess VIP
Posts: 9,911
Joined: 25-October 10
From: Gulf South USA


You are very welcome. Glad you found a solution.

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
dale.fye
post Jul 13 2019, 09:19 AM
Post#5



Posts: 129
Joined: 28-March 18
From: Virginia


I'm fundamentally against storing data like this that can easily be computed at run time.

If you need to view this field when you view a dealers record, I would simply have an unbound textbox on the Dealers form with a control source of:

=DMin("[QTDate]","[AllVehicles]","[DealerID]=" & Forms!frmDealers.txtDealerID);

This way you don't have to continually run the update query and never have to worry about this field being wrong, which can happen with stored values which should be computed.

Dale

--------------------
Dale Fye
Microsoft Access MVP 2013-2016
Developing Solutions, LLC
Go to the top of the page
 
bcmarshall
post Jul 13 2019, 04:58 PM
Post#6



Posts: 38
Joined: 17-July 10
From: Ventura, CA


Actually, if everything works as designed I'll never have to run the update query again. As a quote is written a count of existing quotes referred by that dealer is made. If the count is >0 nothing happens, but if the count is 0 it populates the 1stQuote field in Dealers table with the date from the quote. And in normal record writing/reviewing process the info is irrelevant and done behind the scenes. Its sole purpose is to give admin info about that dealer.

You're certainly right about one thing. A calculated value precludes any possibility of an incorrect entry, etc., but I honestly don't see that being any more problematic for this field than assuming the name or phone number would be wrong or get changed.

And there's one case in which we actually do use the data. A report is run at the beginning of every month showing those dealers whose 1stQuote date was during the previous month. Once identified, we can send a thank you letter, a promo gift, or anything else we consider appropriate. A calculated field would make that a bit more difficult.

At this point it's already done, but I'll definitely keep your advice in mind. Any time I need a field with data that can easily be computed at run time I'll revert back to the unbound control.

Thanks.
Go to the top of the page
 
tina t
post Jul 14 2019, 02:16 PM
Post#7



Posts: 6,022
Joined: 11-November 10
From: SoCal, USA


i agree with you that there's no good reason to not store the first quote date in the dealers' table, if it supports the business model. it's a minor duplication of data, but since it's only stored once and never changed, i can't think of a problem it would present. btw, i wouldn't consider the value to be calculated. it's raw data, not the result of manipulation of raw data. i do have one comment:

QUOTE
As a quote is written a count of existing quotes referred by that dealer is made. If the count is >0 nothing happens, but if the count is 0 it populates the 1stQuote field in Dealers table with the date from the quote.

that is one way to do it, and it's certainly not wrong. but you might make it a bit simpler. the field is only populated once in each dealer record. so i'd simply check to see if that field in the record of dealer "x" Is Null - if yes, populate, if no, do nothing.

depending on how your quote data entry form is set up, you may not need a domain aggregate function to count existing quote records. if you are using a combobox control or listbox control to choose/enter a specific dealer in the quote record, just add the dealer-first-quote-date field as a column in the combo/listbox control. you can do that without showing the column, by setting that column's width to zero. but the data is still there, and is available to be read in the form. so just refer to the correct column in the control and run your code according to its' value (remember that columns in combos and listboxes are zero-based; first column is (0), second column is (1), etc). something like

CODE
If Len(Me!MyCombo.Column(2) & "") = 0 Then
    ' run code, maybe an update query, to populate the field in the dealers' table
End If

' or

If IsNull(Me!MyCombo.Column(2)) Then...

if multiple quotes may be entered at a sitting, in the data entry form, it would probably be a good idea to add code to the control's Enter event to requery it.

FWIW. :)

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th August 2019 - 08:16 PM