Solved

Access - Creating New Tables

Posted on 2009-04-06
6
190 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 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

830 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