Access - Create Table with All Fields From Original Table

The following SQL Statement is effective in creating three new ID/Name Fields from one original field, and inserting into a new table C.

Unfortunately, it does not code to include all the other original fields from Table A into Table C. Please offer a modification to this code:

select Val([Provider]) as FieldID,Trim(Mid([Provider], InStr([Provider], "-") + 1, InStr([Provider], ",") - 1 - InStr([Provider], "-"))) as FieldLastName,Trim(Mid([Provider], InStr([Provider], ",") + 1, InStrRev([Provider], " ") - 1 - InStr([Provider], ",") + 1)) as FieldFirstName INTO C
From A

Thanks
tahirihAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
just add an asterisk (*) and a comma ( , )


select *, Val([Provider]) as FieldID,Trim(Mid([Provider], InStr([Provider], "-") + 1, InStr([Provider], ",") - 1 - InStr([Provider], "-"))) as FieldLastName,Trim(Mid([Provider], InStr([Provider], ",") + 1, InStrRev([Provider], " ") - 1 - InStr([Provider], ",") + 1)) as FieldFirstName INTO C
From A
0
 
cmrobertsonConnect With a Mentor Commented:
you will simply need to add the additional fields to your list if you do not use the as option they will use the same field names in the new table is all.
0
 
tahirihAuthor Commented:
Thank you both! All the experts have been wonderful, and ithe solutiond assist me with my responsibilities immensely.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.