Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Access - Creating New Tables

Posted on 2009-04-06
6
Medium Priority
?
195 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

972 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