How to Input data from one field base off another

[SusanK4305]

Ok sounds like a good idea to get that hiden code. I think my boss would like that.
Ref. to the error message: What do you think it is?

[SusanK4305]

I am not sure if an Append Query would work in some way. Do you think it might?

[colintis]

I just read your query, there is a problem within the SET area, I'll paste this part here so no one needs to scroll back up.

SET 
[tblDallas PSU Database].[To EOD Date] = 
   (select [Action Date] 
    FROM [tblAction Log] 
    WHERE [tblAction Log].[Social Security Number]= [tblDallas PSU Database].[Social Security Number]) 
And [U][tblDallas PSU Database].[Social Security Number] 
   [/U]---->[U]In[/U]<---- [U](select [tblAction Log].[Social Security Number] 
      FROM [tblAction Log] 
      WHERE [tblAction Log].[Action Type]="Sent to EOD");[/U]

Notice the bold words at the end, this is where error comes from. The SET is only to change the target field into something else (must use =) instead of getting the record (the IN you are using).

Therefore you have to make sure the target you want to update and the sub-query is matching an one-to-one condition, not one-to-many.

[Delerna]

Actually, you just highlighted something for me Colintis.

Susan
Back in Post 17 you had a
***At most one record can be returned by this subquery***

In Post 20 I explained what the error meant
and in post 21 I showed you what you needed to do

...
select max([Action Date]) 
...

In post 22 you didn't say exactly what you did but whatever it was, you said it worked.


Looking at your code in the subquery you haven't repeated whatever it was you did when you converted it to an update query.

The bold underlined part is not in your subquery

UPDATE [tblDallas PSU Database] 
   SET [tblDallas PSU Database].[To EOD Date] = 
(   select [B][U]MAX[/U]([/B] [Action Date])
    FROM [tblAction Log] 
    WHERE [tblAction Log].[Social Security Number]= [tblDallas PSU Database].[Social Security Number]) 
      And [tblDallas PSU Database].[Social Security Number] In 
(   select [tblAction Log].[Social Security Number] 
    FROM [tblAction Log] 
    WHERE [tblAction Log].[Action Type]="Sent to EOD"
);

[Delerna]

And now when checking out the query I notice that it has become a bit of a mess

try this

UPDATE [tblDallas PSU Database] 
SET [tblDallas PSU Database].[To EOD Date] = 
      (    select [B][U]MAX[/U]([/B] [Action Date])
           FROM [tblAction Log] 
           WHERE [tblAction Log].[Social Security Number]
               = [tblDallas PSU Database].[Social Security Number]
             AND [tblAction Log].[Action Type]="Sent to EOD"
      ) 
[B][U]WHERE[/U][/B] [tblDallas PSU Database].[Social Security Number] In 
(   select [tblAction Log].[Social Security Number] 
    FROM [tblAction Log] 
    WHERE [tblAction Log].[Action Type]="Sent to EOD"
);

I have bolded the changes I made


There are actually 2 subqueries here

The top one selects the value to update for each record
The most recent action date for the [Social Security Number]

the bottom one controls which records will be updated
Only update the records where there is a record in [tblAction Log] where [Action Type]="Sent to EOD"

This ensures that no record will be updated with a null value


Edit
Changes are bold and underlined

[Delerna]

Its hard for me to tell because I don't have the full story but you may not even need the second subquery.

But it probably wouldn't hurt to have it there any way.


Please please please keep a backup of your data prior to doing the update, especially considering that you are relatively inexperienced.
As I said earlier, there is no undo after an update query unless you have a backup

[colintis]

Delerna, I think we should putting underline instead of bolding the parts, as i can't even see the query is bold or not in the code tag...

[Delerna]

changes are bold and underlined

[Delerna]

Actually, seeing as it's a live database
The best thing to do would be to import the table and its data into another database and develop the update query there.



PS
Susan....have I instilled a healthy fear of developing and running unproven update queries on live data in you yet ? :)

I hope so, it is dangerous to become blaze about developing update queries on the fly with live data.;

I cringe with fear everytime I have to do it.
I don't believe you can be too cautious here.
Prove it works correctly before doing it on the live data

[SusanK4305]

Ok I will copy both and make a tester location. :)
FYI: The select query looked to be working I just had issues w/ the update part. I will try the new SQL and let ya'll know. Thank you so much for ya'lls help.

[SusanK4305]

Ok I have added the SQL you told me to and received the same error message. However this time I copied both database and set them a side so only I was in them. What am I doing wrong?

For the update Query all I did was make a blank query the opened it to the SQL view, pasted the code you gave me and ran it. The received the same Error message.

Code
UPDATE [tblDallas PSU Database]
SET [tblDallas PSU Database].[To EOD Date] =
( select MAX( [Action Date])
FROM [tblAction Log]
WHERE [tblAction Log].[Social Security Number]
= [tblDallas PSU Database].[Social Security Number]
AND [tblAction Log].[Action Type]="Sent to EOD"
)
WHERE [tblDallas PSU Database].[Social Security Number] In
( select [tblAction Log].[Social Security Number]
FROM [tblAction Log]
WHERE [tblAction Log].[Action Type]="Sent to EOD"

[SusanK4305]

FYI: The answer came from another post that I thought he gave up on. I posted it here just in case another may need it.

answered by NeoPa
If there are multiple possible versions of these records then you will need to decide how to determine which to use.

This SQL should work for taking the last date found in the sequence of the records as they come. NB. This is not the same as coming in date sequence necessarily. If there is only one of each then you shouldn't have the problem. Try it out and see how you get on with it :
Expand|Select|W rap|Line Numbers UPDATE [Table A] AS tA
INNER JOIN
[Table B] AS tB
ON tA.SSN = tB.SSN

SET tA.[Intake Delay Date] = IIf(tB.[Action Type] = 'Delayed'
, tB.[Action Date]
, tA.[Intake Delay Date])
, tA.[Intake Completed] = IIf(tB.[Action Type] = 'Done'
, tB.[Action Date]
, tA.[Intake Completed])

WHERE (tB.[Action Type] In('Delayed', 'Done'))

Now I am just trying to get the Query to run the update every 5 sec. Do ya'll know how to do that?

[Delerna]

Similar to post 12



Is there a form that is open all the time.
You could use the forms timer event to run the query.
Haven't done any access programming in a while.
Someone will come up with a better way



From the sound of things it is now becoming apparent that it might have been easier if the form where the users enter the action log should be automatically updating the tblDallas PSU Database tables date fields at the same time