Query grabs a set of chars from a field making a new field

[Guest]

I have a long set of data in a table called New_Export with just one field I
call Field1.

I want to grab the set of chars 1 thru 18 and make them the field Rec_Ident
in the table Clean_Export and then take the remaining information from the
record (chars 19 thru the end) and put them into Rec_Detail in table
New_Export

I want the original table data to remain the same.
I just want the new table populated.

 

[Guest]

INSERT INTO Clean_Export (Rec_Ident, Rec_Detail)
SELECT Left([Field1],18), Mid([Field1],19)
FROM New_Export

 

[Guest]

SELECT Left([Field1],18) AS Rec_Ident,
Mid([Field1],19) AS Rec_Detail
INTO Clean_Export
FROM New_Export
ORDER BY Left([Field1],18), Mid([Field1],19);

 

[Guest]

Thank you!

Now I need to compare Rec_Ident and Rec_Detail from Clean_Export against the
Last_Week table with the field names LW_RecIdent and LW_RecDetail.
LW_RecIdent. I believe I would have to set up the RecIdent fields as keys in
both tables and set up a relationship between them, is that all correct?

INSERT INTO Clean_Export (Rec_Ident, Rec_Detail)
SELECT Left([Field1],18), Mid([Field1],19)
FROM New_Export

I have a long set of data in a table called New_Export with just one field I
call Field1.

I want to grab the set of chars 1 thru 18 and make them the field Rec_Ident
in the table Clean_Export and then take the remaining information from the
record (chars 19 thru the end) and put them into Rec_Detail in table
New_Export

I want the original table data to remain the same.
I just want the new table populated.

 

[John Spencer]

You need to set up a query with the two tables related to each other by the
appropriate fields. You don't need to set the fields as key fields.

An sql statement to do this might look like:
SELECT *
FROM Clean_Export INNER JOIN Last_Week
On Clean_Export.Rec_Ident = Last_Week.LW_RecIdent
and Clean_Export.Rec_Detail = Last_Week.LW_RecDetail

Thank you!

Now I need to compare Rec_Ident and Rec_Detail from Clean_Export against
the
Last_Week table with the field names LW_RecIdent and LW_RecDetail.
LW_RecIdent. I believe I would have to set up the RecIdent fields as keys
in
both tables and set up a relationship between them, is that all correct?

INSERT INTO Clean_Export (Rec_Ident, Rec_Detail)
SELECT Left([Field1],18), Mid([Field1],19)
FROM New_Export

I have a long set of data in a table called New_Export with just one
field I
call Field1.

I want to grab the set of chars 1 thru 18 and make them the field
Rec_Ident
in the table Clean_Export and then take the remaining information from
the
record (chars 19 thru the end) and put them into Rec_Detail in table
New_Export

I want the original table data to remain the same.
I just want the new table populated.