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