D
Doug Goodenough
Hi,
I have built an insert query to build one record in table DBO_RA_HISTORY
with data pulled from 3 other tables. The tables are all linked SQL Server
tables. The master table is DBO_RA and the others are optional child tables
DBO_IRO and DBO_RA_CORP_PANEL_RVW. The key column is RA_ID. Since I
always want to insert a row in DBO_RA_HISTORY regardless of rows existing in
either of the child tables I coded the insert statement with left joins on
the two child tables.
Here is the SQL…………
INSERT INTO dbo_RA_HISTORY ( RA_ID, created_date, RN_Deter, RN_Rvwr_ID,
RA_Type_ID, RN_Rmk, Dt_RN_Deter, Dt_RA_Submit_to_MD, Dt_RA_Submit_to_IRO,
Involved_RN_Ind, IRO_Reviewer, IRO_Solution_Rmk, IRO_Determination,
Dt_Recd_from_IRO, APPEAL_COORD_ID, MD_ID, ADMINISTRATOR_ID, Dt_of_Invitation,
Member_Attend_Ind, Rep_Attend_Ind, Attorney_Attend_Ind,
Representative_Description, Solution_Rmk, Determntn_ID, Dt_Response,
Dt_LstUpd, UserID_LstUpd )
SELECT dbo_RA.RA_ID, now() AS expr2, dbo_RA.RN_Deter, dbo_RA.RN_Rvwr_ID,
dbo_RA.RA_Type_ID, dbo_RA.RN_Rmk, dbo_RA.Dt_RN_Deter,
dbo_RA.Dt_RA_Submit_to_MD, dbo_RA.Dt_RA_Submit_to_IRO,
dbo_RA.Involved_RN_Ind, dbo_iro.IRO_Reviewer, dbo_iro.IRO_Solution_Rmk,
dbo_iro.IRO_Determination, dbo_iro.Dt_Recd_from_IRO,
dbo_RA_Corp_Panel_Rvw.APPEAL_COORD_ID, dbo_RA_Corp_Panel_Rvw.MD_ID,
dbo_RA_Corp_Panel_Rvw.ADMINISTRATOR_ID,
dbo_RA_Corp_Panel_Rvw.Dt_of_Invitation,
dbo_RA_Corp_Panel_Rvw.Member_Attend_Ind,
dbo_RA_Corp_Panel_Rvw.Rep_Attend_Ind,
dbo_RA_Corp_Panel_Rvw.Attorney_Attend_Ind,
dbo_RA_Corp_Panel_Rvw.Representative_Description,
dbo_RA_Corp_Panel_Rvw.Solution_Rmk, dbo_RA_Corp_Panel_Rvw.Determntn_ID,
dbo_RA_Corp_Panel_Rvw.Dt_Response, Now() AS Expr1, " " AS user_id
FROM (dbo_RA LEFT JOIN dbo_RA_Corp_Panel_Rvw ON dbo_RA.RA_ID =
dbo_RA_Corp_Panel_Rvw.RA_ID) LEFT JOIN dbo_iro ON dbo_RA.RA_ID = dbo_iro.RA_ID
WHERE dbo_RA.RA_ID=[Forms]![frmRoute_RA]![txtRAID];
When I run the query with a key value that has a rows in the master table
and the first child table (DBO_RA_CORP_PANEL_RVW) the insert works fine and
pulls data from both of these tables with nulls for the columns that would
have come from the other child table (DBO_IRO). When I run the query with a
key value with rows in the master and 2nd child table (DBO_IRO) I get a
“Record is Deleted†message and the insert does not occur. When I switch
the positions of the child tables in the from join statement then the insert
works for the DBO_IRO but not for the DBO_RA_CORP_PANEL_RVW. So, it does not
appear to be a data issue but the placement of the child tables in the join
statement. Any ideas what is happening here?
Thx very much,
Doug
I have built an insert query to build one record in table DBO_RA_HISTORY
with data pulled from 3 other tables. The tables are all linked SQL Server
tables. The master table is DBO_RA and the others are optional child tables
DBO_IRO and DBO_RA_CORP_PANEL_RVW. The key column is RA_ID. Since I
always want to insert a row in DBO_RA_HISTORY regardless of rows existing in
either of the child tables I coded the insert statement with left joins on
the two child tables.
Here is the SQL…………
INSERT INTO dbo_RA_HISTORY ( RA_ID, created_date, RN_Deter, RN_Rvwr_ID,
RA_Type_ID, RN_Rmk, Dt_RN_Deter, Dt_RA_Submit_to_MD, Dt_RA_Submit_to_IRO,
Involved_RN_Ind, IRO_Reviewer, IRO_Solution_Rmk, IRO_Determination,
Dt_Recd_from_IRO, APPEAL_COORD_ID, MD_ID, ADMINISTRATOR_ID, Dt_of_Invitation,
Member_Attend_Ind, Rep_Attend_Ind, Attorney_Attend_Ind,
Representative_Description, Solution_Rmk, Determntn_ID, Dt_Response,
Dt_LstUpd, UserID_LstUpd )
SELECT dbo_RA.RA_ID, now() AS expr2, dbo_RA.RN_Deter, dbo_RA.RN_Rvwr_ID,
dbo_RA.RA_Type_ID, dbo_RA.RN_Rmk, dbo_RA.Dt_RN_Deter,
dbo_RA.Dt_RA_Submit_to_MD, dbo_RA.Dt_RA_Submit_to_IRO,
dbo_RA.Involved_RN_Ind, dbo_iro.IRO_Reviewer, dbo_iro.IRO_Solution_Rmk,
dbo_iro.IRO_Determination, dbo_iro.Dt_Recd_from_IRO,
dbo_RA_Corp_Panel_Rvw.APPEAL_COORD_ID, dbo_RA_Corp_Panel_Rvw.MD_ID,
dbo_RA_Corp_Panel_Rvw.ADMINISTRATOR_ID,
dbo_RA_Corp_Panel_Rvw.Dt_of_Invitation,
dbo_RA_Corp_Panel_Rvw.Member_Attend_Ind,
dbo_RA_Corp_Panel_Rvw.Rep_Attend_Ind,
dbo_RA_Corp_Panel_Rvw.Attorney_Attend_Ind,
dbo_RA_Corp_Panel_Rvw.Representative_Description,
dbo_RA_Corp_Panel_Rvw.Solution_Rmk, dbo_RA_Corp_Panel_Rvw.Determntn_ID,
dbo_RA_Corp_Panel_Rvw.Dt_Response, Now() AS Expr1, " " AS user_id
FROM (dbo_RA LEFT JOIN dbo_RA_Corp_Panel_Rvw ON dbo_RA.RA_ID =
dbo_RA_Corp_Panel_Rvw.RA_ID) LEFT JOIN dbo_iro ON dbo_RA.RA_ID = dbo_iro.RA_ID
WHERE dbo_RA.RA_ID=[Forms]![frmRoute_RA]![txtRAID];
When I run the query with a key value that has a rows in the master table
and the first child table (DBO_RA_CORP_PANEL_RVW) the insert works fine and
pulls data from both of these tables with nulls for the columns that would
have come from the other child table (DBO_IRO). When I run the query with a
key value with rows in the master and 2nd child table (DBO_IRO) I get a
“Record is Deleted†message and the insert does not occur. When I switch
the positions of the child tables in the from join statement then the insert
works for the DBO_IRO but not for the DBO_RA_CORP_PANEL_RVW. So, it does not
appear to be a data issue but the placement of the child tables in the join
statement. Any ideas what is happening here?
Thx very much,
Doug