Solved

Access - Create Table with All Fields From Original Table

Posted on 2009-04-04
3
220 Views
Last Modified: 2012-05-06
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
0
Comment
Question by:tahirih
3 Comments
 
LVL 7

Assisted Solution

by:cmrobertson
cmrobertson earned 100 total points
ID: 24067621
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
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 400 total points
ID: 24067668
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
 

Author Closing Comment

by:tahirih
ID: 31566578
Thank you both! All the experts have been wonderful, and ithe solutiond assist me with my responsibilities immensely.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now