Append Query

[SlowDriver]

Hi IT experts,

I would like to copy ID from one table to another with relationship in mind. The following 3 tables are involved.

Travel_tbl: contain projectsubID, and ProviderID which I am using to link travel_tbl to projectAct_tbl and Providers_tbl
projectAct_tbl: contain the projectActivityID that I need to append or copy to travel table for records that match the individual
Providers_tbl

the goal

Example, 3 individuals will be in one task. projectsub4555 the projectactivityID for each of the 3 individuals in this task are 456666,822222,226222

currrent issue: the projectsub is OK, the problem is that with the query I wrote, it will append the projectactivityID of the first individual and paste it for all of instead of copying the projectactivityID of the right indiviudal using the JHEDID as identifier.

That is why I linked Travel_tbl to projectAct_tbl using the projectsub to identify the task then I linked the field Udid14 to JHEDID to identify the individuals.

UPDATE (Travel_tbl LEFT JOIN ProjectAct_tbl ON Travel_tbl.[Sort9] = ProjectAct_tbl.ProjectSub) LEFT JOIN Providers_tbl ON Travel_tbl.Udid14 = Providers_tbl.JHEDID SET Travel_tbl.ProjectActivityID = [ProjectAct_tbl].[ProjectActivityID];


Please let me know what I can do to get this resolved.

 

[SlowDriver]

Hi,

Any feedback......

 

[SlowDriver]

do I have to pay to get feedback? please let me know.

 

[dhookom]

I can't understand your question. Try to rephrase your question with sample records before and after.

Duane
Hook'D on Access
MS Access MVP

 

[SlowDriver]

in the database, we generate requests that contain projectsub and JHEDID. projectsub is for the event and JHEDID is for the individuals.

hundered of requests comes out from the system. at the end of the month, data comes back which include the projecrsub and JHEDID under different name sort9 and Udid14.

some of the data comming back does not contain any. this is not belong to us. I import the data back to the system in a table called Travel_tbl.

Now, I need to link the data from projectAct_tbl to travel_tbl using projectActivityID which does not exisit in travel_tbl. The way i thought I can get to it is by linking the projecrsub and JHEDID to projectAct_tbl and provider_tbl. now, I want to copy the projectActivityID to the travel_tbl so I can do the link and continue with the work needed.

UPDATE (Travel_tbl LEFT JOIN ProjectAct_tbl ON Travel_tbl.[Sort9] = ProjectAct_tbl.ProjectSub) LEFT JOIN Providers_tbl ON Travel_tbl.Udid14 = Providers_tbl.JHEDID SET Travel_tbl.ProjectActivityID = [ProjectAct_tbl].[ProjectActivityID];

The problem I am getting is that if you look at the projectactivityID after updating with the above query, I will copy the first one and paste to all individuals regardless of the Udid14 linked to JHEDID. so the first w records are right, the third one and on have the wrong projectactivityID

Udid14 ProjectActivityID Trip Number
RALTMAN7 11465 3993
RALTMAN7 11465 3993
GCOLE4 11465 3993
GCOLE4 11465 3993
DDANG2 11465 3993
DDANG2 11465 3993
DDANG2 11465 3993
DDANG2 11465 3993
AFRAKE 11465 3993
AFRAKE 11465 3993
SHASHAM1 11465 3993
SHASHAM1 11465 3993
KKHANNA2 11465 3993
KKHANNA2 11465 3993
KKHANNA2 11465 3993
KKHANNA2 11465 3993
KKHANNA2 11465 3993
KKHANNA2 11465 3993
KKHANNA2 11465 3993


Hope this explain my issue.


thanks

 

[dhookom]

I still can't get past the lack of capitalization and misspelling. Some context about the data would be helpful.

Can't you just say something like:
I have a table named projectAct_tbl with fields ProjectSub (not projecrsub) and JHEDID. These fields store ...

Each month I get a table named ... the fields Sort9 and Udid14 which relate to the projectAct_tbl fields of ... and ...

My primary keys of these tables are...

My actual records look like (note the use of TGML to align the columns)

Udid14      ProjectActivityID [Trip Number]
RALTMAN7    11465             3993
RALTMAN7    11465             3993
GCOLE4      11465             3993
GCOLE4      11465             3993
DDANG2      11465             3993
DDANG2      11465             3993
DDANG2      11465             3993
DDANG2      11465             3993
AFRAKE      11465             3993
AFRAKE      11465             3993
SHASHAM1    11465             3993
SHASHAM1    11465             3993
KKHANNA2    11465             3993
KKHANNA2    11465             3993
KKHANNA2    11465             3993
KKHANNA2    11465             3993
KKHANNA2    11465             3993
KKHANNA2    11465             3993
KKHANNA2    11465             3993

Duane
Hook'D on Access
MS Access MVP