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

[SusanK4305]

I have a DB table that is linked to my new DB. I can't edited the linked table. So I created another DB w/ additional data however I still need to pull data over from one to the other.

My DB has 2 fields
A: Action Type (this is a dropdown)and B: Action Date.
I need to pull the Action Date over to the linked table "Delay Date" if the Action Type = Delayed.

Also if I could get a 3rd field "Reason" to be filted by what is selected from Actiont Type. That would be great. Thank you!

[NeoPa]

Susan, I'm afraid it isn't very clear what you are hoping to achieve. Are you looking for linked data to be displayed on a form or for existing data from the old DB to be used to populate your new one?

In either case some basic information about the fields (and/or controls) involved would be useful.

Welcome to Bytes!

[SusanK4305]

I need to take the Action Date from my sub form "Intake" and place it in the Intake Delay field in the Master form "Main DB" the issue is that the Action Type (from the sub form sets the criteria.
If I select "Delayed" for the Action Type and put a date in the Action Date I need that date to link to the Intake Delayed field in th master form.

I hope I helped explain it. Thank you!

[SusanK4305]

table A (master)
table B (sub)
field A.1 (Intake Delay Date)
field A.2 (Intake Completed)
field B.1 (Action Type--this is a Dropdown)
field B.2 (Action date)

IF B.1= "Delayed" then B.2 will link to A.1

I also have to have IF B.1= "Done" then A.l is blank and B.2 (only for Done action) is linked to A.2

[NeoPa]

A simple answer to my questions would have been preferable. It's very hard to work with multiple versions of the explanation, all with differences. I'll do what I can though, as I'm sure you were trying to be helpful.

I must say that as what you're suggesting doesn't make a whole lot of sense in a database context, I'm finding it hard to guess exactly what you are looking for. Calculating a value to display is one thing, but saving that away automatically is another different thing entirely.

That first question really was quite important. It gives me a clearer understanding of what you might be expecting. Retrospectively updating all your internal (new DB) data from the external data is one possible approach, or simply linking in to the external data using a query is another. Unfortunately, your descriptions so far seem to include a bit of both. Hence the question, the answer to which could have told me what your experience doesn't allow you to yet. Does that make sense?

[SusanK4305]

No I need linked data from the new table to be linkeed to the old table. The old table is a company table (i.e. Master or Table A) and the new one in our groups data (i.e. Sub table or Table B.

Basic Field data:
Table A (company/ Master)has many text field w/ mask inputs for dates but I am looking mostly at just 2 of them. The "Intake Delay" and the "Intake Completed".

Table B (our group/ Sub)has only 4 fields "ID Auto Number", "Action Type" (this is the dropdown that is linked to another table for the list) and "Action Date" (this is a date/time field w/ an input masks.

The tables have a relationship by SSN.

Part 1: Can I get our "Action Date" field to copy/link to the companies "Intake Delayed" if our "Action Type" is selected as "Delayed"?

Part 2: If there is an "Action Type" that is selected as "Completed" for the same person it will remove the "Intake Delay" date from the companies table?

[NeoPa]

Everything you say is detailed, but I am struggling even to grasp the concept of what you're trying to ask. Detail, in the circumstances, is of no help whatsoever at this stage.

Why don't you let me lead and you just answer the questions asked. It really will be much easier that way. I'm assuming you need my help. I can only give you help when I understand your problem clearly. At this time I'm still not in that position as you haven't answered my question (which I'll reapeat here for clarity). It's not a question that can take a yes or no answer. The answer must be A or B.
Are you looking for :

1. Linked data to be displayed on a form?
2. Existing data from the old DB to be used to populate your new DB?

The solution will be markedly different depending on the answer to this simple question. It will make more of a difference than all the other details.

[SusanK4305]

B , but it is the data on the new one to populate to the old

[NeoPa]

Good. A surprise, but clear.

I was out all evening tonight but I haven't given up on this. I'll have another look tomorrow.

[NeoPa]

I've started to look at this again Susan from the new perspective. I was reading the OP (Original Post) when I noticed that you're unable to edit the linked table. I'm a bit confused by this. If you have no write access to this linked table, why are we talking about updating this table with data from the new (local) one?

[SusanK4305]

Data entry is fine to add in to old table but because other groups use the old one as well I can't revamp it all.

[colintis]

Susan, can you actually get into the source database where your link table is from? or why not group the additional data in another table, and a column reference that linked table's primary key? Then create a query to acts like a joined table.

[NeoPa]

Ah. The design cannot be edited but the data can. That's good to know.

I now know enough to know that we should be looking at running an UPDATE query to update the original table from your new one in the new database. I will look through your earlier posts again as I suspect much of what I need is already there. If I find something missing that I need I'll post to ask for it.

By the way, just to give a bit of background, had the answer been A then we would have been looking to work within VBA on a form. A different approach entirely.

[NeoPa]

I need to ask how the two tables are linked. There is something in post #6 about SSN, but There are no fields called SSN described anywhere, and nothing to indicate what type of relationship exists between them.

If I were guessing I'd say :
Table A (master - Table in original database)
Field A.0 (SSN)
Field A.1 (Intake Delay Date)
Field A.2 (Intake Completed)
Table B (sub - Table in new/local database)
Field B.0 (SSN)
Field B.1 (Action Type--this is a Dropdown)
Field B.2 (Action date)
and that [Table A] and [Table B] are linked in a one-to-one relationship such that each record in [Table A] has one, and only one, matching record in [Table B].

[SusanK4305]

Yes the lay out is correct, however it is a one-many relationship. If I pull up 000-00-0009 Jane Doe then i get both the basic info from the Table A and a running list of Jane's action from Table B (linked by SSN)but some of the basic info Table A is the same as mine Table B. i.e. Delayed and Intake Complete

[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..........Action 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'))