Solved

Access - Creating New Tables

Posted on 2009-04-06
6
191 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) 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
Independent Software Vendors: 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!

 
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

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
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.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

738 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