?
Solved

Access - Creating New Tables

Posted on 2009-04-06
6
Medium Priority
?
198 Views
Last Modified: 2012-05-06
In the following, the SQL Code that is missing is creating a new table (D), from this Statement:

SELECT C.FieldID, C.FieldLastName, C.FieldFirstName, C.[Place of Service Category Code-Name], C.SubNmbr, C.[Patient ID], C.[Patient Last, First Name], C.[Patient Birth Date], C.[Original Service Date], Sum(C.[Cash Amount]) AS [SumOfCash Amount], C.[Procedure Code], C.[CPT Modifier 1], C.[Dx-1 Code-Name], C.Provider, C.[Original Plan Category], C.[Current Plan], C.[Original Payor], C.[Current Payor], C.[Procedure Units], C.[Transaction ID], C.[Service Area]

FROM C

GROUP BY C.FieldID, C.FieldLastName, C.FieldFirstName, C.[Place of Service Category Code-Name], C.SubNmbr, C.[Patient ID], C.[Patient Last, First Name], C.[Patient Birth Date], C.[Original Service Date], C.[Procedure Code], C.[CPT Modifier 1], C.[Dx-1 Code-Name], C.Provider, C.[Original Plan Category], C.[Current Plan], C.[Original Payor], C.[Current Payor], C.[Procedure Units], C.[Transaction ID], C.[Service Area];


I have tried placing the "INTO D" in various locations with no luck. Actually, I am finding in general, where to place the "INTO" Statement is not consistent. If anyone has some general rules of thumb to follow, it is  most apprecited.

Therefore, there are two requests:

1. Make Table D as a result of running the SQL Statement above
2. Offer any guidelines as to where an INTO statement will go depending on the SQL Statement

Thanks
0
Comment
Question by:tahirih
6 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 800 total points
ID: 24079083
try this

SELECT C.FieldID, C.FieldLastName, C.FieldFirstName, C.[Place of Service Category Code-Name], C.SubNmbr, C.[Patient ID], C.[Patient Last, First Name], C.[Patient Birth Date], C.[Original Service Date], Sum(C.[Cash Amount]) AS [SumOfCash Amount], C.[Procedure Code], C.[CPT Modifier 1], C.[Dx-1 Code-Name], C.Provider, C.[Original Plan Category], C.[Current Plan], C.[Original Payor], C.[Current Payor], C.[Procedure Units], C.[Transaction ID], C.[Service Area] INTO D
From C
GROUP BY C.FieldID, C.FieldLastName, C.FieldFirstName, C.[Place of Service Category Code-Name], C.SubNmbr, C.[Patient ID], C.[Patient Last, First Name], C.[Patient Birth Date], C.[Original Service Date], C.[Procedure Code], C.[CPT Modifier 1], C.[Dx-1 Code-Name], C.Provider, C.[Original Plan Category], C.[Current Plan], C.[Original Payor], C.[Current Payor], C.[Procedure Units], C.[Transaction ID], C.[Service Area];
0
 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 800 total points
ID: 24079094
SELECT C.FieldID, C.FieldLastName, C.FieldFirstName, C.[Place of Service Category Code-Name], C.SubNmbr, C.[Patient ID], C.[Patient Last, First Name], C.[Patient Birth Date], C.[Original Service Date], Sum(C.[Cash Amount]) AS [SumOfCash Amount], C.[Procedure Code], C.[CPT Modifier 1], C.[Dx-1 Code-Name], C.Provider, C.[Original Plan Category], C.[Current Plan], C.[Original Payor], C.[Current Payor], C.[Procedure Units], C.[Transaction ID], C.[Service Area]

INTO [D]

FROM C

GROUP BY C.FieldID, C.FieldLastName, C.FieldFirstName, C.[Place of Service Category Code-Name], C.SubNmbr, C.[Patient ID], C.[Patient Last, First Name], C.[Patient Birth Date], C.[Original Service Date], C.[Procedure Code], C.[CPT Modifier 1], C.[Dx-1 Code-Name], C.Provider, C.[Original Plan Category], C.[Current Plan], C.[Original Payor], C.[Current Payor], C.[Procedure Units], C.[Transaction ID], C.[Service Area];
0
 
LVL 15

Assisted Solution

by:MNelson831
MNelson831 earned 400 total points
ID: 24079096
With Insert:

Insert Into MyDestinationtable (DestinationField1, DestinationField2, etc) VALUES (Value1, Value2, etc)

OR

Insert Into MyDestinationTable (DestinationField1, DestinationField2, etc) Select SourceField1, SourceField2 From MySourceTable

OR

Insert Into MyExactCopyOfTable1 Select * from Table1


For select:(I think... I almost never use this one)

Select MyDataFields From MyTable Where MyConditions = True Into My SourceTable

0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 24079105
As cap1 and I both demonstrated, if you use an INTO clause, it always comes after the SELECT clause
but before the FROM clause.
0
 
LVL 15

Expert Comment

by:MNelson831
ID: 24079125
My bad, thanks MP and Cap.
0
 

Author Closing Comment

by:tahirih
ID: 31567115
Thank you everyone. I am getting more and more engaged with SQL in Access, and often find that incorporating high level rules helps alot.
0

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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.

Join & Write a Comment

Audit trails are very important in any system to hold people responsible for certain transactions and hold them to take ownership of their actions. This article is dedicated to all novice "Microsoft Access" developers.
Usually, rounding is performed by some power of 10 - to thousands, hundreds, tens, or integer - or to one, two, or more decimals. But rounding can also be done to a power of two, say, 16 or 64, or 1/32 or 1/1024, even for extreme values.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

569 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