Solved

Access - Creating New Tables

Posted on 2009-04-06
6
188 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 119

Accepted Solution

by:
Rey Obrero earned 200 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 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 200 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 100 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 is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 92

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

910 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

20 Experts available now in Live!

Get 1:1 Help Now