Link one field to a field in another table (linked from another DB) if the Drop = A

[SusanK4305]

Can I link the "Action Date" form Table B to Table A "Intake Delay Date" if the "Action Type" from Table B = Delayed
Samething like this:
Link [Action Log].[Action Date]TO [Company].[Intake Delayed Date]If [Action Log].[Action Type]= "Delayed"


P.S. I know this is a poor code but maybe it will help explain.

[NeoPa]

Susan, all of your explanations make proper sense only when assuming a one-to-one link. Nowhere does any of your posts allow for multiple records to be taken into consideration.

Please try to explain precisely what updates you require in a way that makes clear how to handle all possible scenarios. For instance where there are many matching records and more than one where the [Action Type] is "Delayed" as well as more than one where the [Action Type] is "Completed" . If any of these are not possible due to constrictions on your data, then of course we need to know that too.

[SusanK4305]

Great Question! As far as I know there should be only one action type that = Delayed and only one action type that = Complete per person.
However lets go w/ the later (more than one action type that states Delayed and more that one that state Complete). Just in case it comes about later. What do you think would be the best cour of action to provent this from happening? FYI I can only have 1 personnel entry (on the Master DB- Table A).

[colintis]

Gosh...I wish I'm sitting right in front of you so we can make discussion with pen and paper(maybe easier to understand).

Anyway, is the "Action Date" form Table B has any other targets that would link to besides the Table A "Intake Delay Date"? If not, you can still make the link, only we make some codings to take some actions if "Action Type" from Table B = Delayed or not.

[SusanK4305]

The Action Date in Table B will also need to link or update (what ever I have to use or call it to get it to work) to other fields in Table A. All links must be based on what the Action Type is (i.e. you select Delayed for the action type in table B then Intake Delayed date in Table A will get the same date as table B for that action) Same goes for Complete, and New.



Table A.............. .......Table B
SSN (PK)........... ........SSN (PK and Relationship)
Name
File Received Date= 9/01/2010 ......... Action Type=New File and Action Date= 9/01/2010
Delayed Date= 9/05/2010........... .... Action Type=Delayed and Action Date= 09/05/2010
Completed Date= 09/10/2010..........A ction Type= Complete and Action Date= 09/10/2010

Table A needs to get their dates from table B if the action type matches the criteria.


I hope this helps explain. I real don't know how to any more and I am sure once worked out I will kick my self for not getting it. :)

[colintis]

So from table A, you want to combine 2 fields into one?
E.g. If Action Type = "New File", and Action Date = "9/1/2010". Then you store these 2 data into 1 field as "File Received Date = 9/1/2010"?

[NeoPa]

Sorry I've left this hanging somewhat. I haven't had much time recently to devote to the site (See NeoPa Absence).

I am trying to catch up with all my outstanding threads (I have quite a few so this may take some time).

[SusanK4305]

I can't access the link you posted, but from the title I don't think I need to.
I only have until the 19 Oct. 2010 to get this set up and would realy like to give a completed database to my boss at that point (seeing as I am the only one with a clue on how to set one up). I would be greatful for any information that I can complete this task. Thank you.

[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 :

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'))