Solved

creating a table in access from query

Posted on 2014-04-01
16
320 Views
Last Modified: 2014-04-02
Experts,

I have not used access in a while and I want to create a table from the results of a query.
I have the following query, which I would like to create table from the results of
sum(medical_summary) , sum(medical_detail), sum(hospital_summary), sum(hospital_detail)

This is the query that I created.  If there is a better way to get the results please advise.

SELECT Count(*) AS MEDICAL_SUMMARY, 0 AS MEDICAL_DETAIL, 0 AS HOSPITAL_SUMMARY, 0 AS HOSPITAL_DETAIL
FROM (SELECT COUNT(*) FROM HIX_QPNY_MED_PROCESSED GROUP BY DOCNUM)
UNION
SELECT 0 AS MEDICAL_SUMMARY, COUNT(*) AS MEDICAL_DETAIL, 0 AS HOSPITAL_SUMMARY, 0 AS HOSPITAL_DETAIL
FROM(SELECT COUNT(*) FROM HIX_QPNY_MED_PROCESSED GROUP BY CLAIM)
UNION
SELECT 0 AS MEDICAL_SUMMARY, 0 AS MEDICAL_DETAIL, COUNT(*) AS HOSPITAL_SUMMARY, 0 AS HOSPITAL_DETAIL
FROM (SELECT COUNT(*) FROM HIX_QPNY_HOS_PROCESSED GROUP BY DOCNUM)
UNION SELECT 0 AS MEDICAL_SUMMARY, 0 AS MEDICAL_DETAIL, 0 AS HOSPITAL_SUMMARY, COUNT(*) AS HOSPITAL_DETAIL
FROM (SELECT COUNT(*) FROM HIX_QPNY_HOS_PROCESSED GROUP BY CLAIM);
0
Comment
Question by:morinia
[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
  • 7
  • 4
  • 3
  • +1
16 Comments
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 125 total points
ID: 39969054
You can use a Create Table query, which is essentially a "SELECT INTO" query. I believe your syntax would be:

SELECT Count(*) AS MEDICAL_SUMMARY, 0 AS MEDICAL_DETAIL, 0 AS HOSPITAL_SUMMARY, 0 AS HOSPITAL_DETAIL INTO YourNewTable
FROM (SELECT COUNT(*) FROM HIX_QPNY_MED_PROCESSED GROUP BY DOCNUM)
UNION
SELECT 0 AS MEDICAL_SUMMARY, COUNT(*) AS MEDICAL_DETAIL, 0 AS HOSPITAL_SUMMARY, 0 AS HOSPITAL_DETAIL
FROM(SELECT COUNT(*) FROM HIX_QPNY_MED_PROCESSED GROUP BY CLAIM)
UNION
SELECT 0 AS MEDICAL_SUMMARY, 0 AS MEDICAL_DETAIL, COUNT(*) AS HOSPITAL_SUMMARY, 0 AS HOSPITAL_DETAIL
FROM (SELECT COUNT(*) FROM HIX_QPNY_HOS_PROCESSED GROUP BY DOCNUM)
UNION SELECT 0 AS MEDICAL_SUMMARY, 0 AS MEDICAL_DETAIL, 0 AS HOSPITAL_SUMMARY, COUNT(*) AS HOSPITAL_DETAIL
FROM (SELECT COUNT(*) FROM HIX_QPNY_HOS_PROCESSED GROUP BY CLAIM);

Open in new window

0
 

Author Comment

by:morinia
ID: 39969067
Scott,

You gave me back the code I posted.  I also wanted to sum the above select statement and put into a table.  It is the syntax to create the table and sum that i need.
0
 
LVL 38

Assisted Solution

by:PatHartman
PatHartman earned 125 total points
ID: 39969482
Create the query using the QBE if you don't know the syntax.  

Choose the existing query rather than a table.  Change the query type from Select to Make Table.  Select the Sigma button to turn it into a Totals query.  Change the Group By's to Sum or whatever you want them to be.
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 85
ID: 39969591
You gave me back the code I posted
No, I added the INTO part, which would create the table. Look at the first line, towards the end.

If you need to then Sum those values, then just create a new query based on the table, and Sum the fields. YOu won't be able to do this in a single UNION query, however (although you could possibly do it with multiple stored queries).
0
 

Author Comment

by:morinia
ID: 39969631
Experts,

I am getting an action query cannot be used as a row source.
0
 
LVL 85
ID: 39969659
You'd have to tell us more than that.

Where are you running the queries? In the query designer, or somewhere else?
0
 

Author Comment

by:morinia
ID: 39969675
I am in access.  When I run the query it runs fine and gives me four rows of data.  However, I cannot save the results into a table.   If anyone knows another way to get the results it would be appreciated.  I have a table that has multiple claim numbers.  

An example of what I am trying to do is to count unique  claim numbers from a table that has multiples.  This is why I am grouping and then select count(*).
0
 
LVL 31

Assisted Solution

by:Helen Feddema
Helen Feddema earned 250 total points
ID: 39969788
Create a new query based on the query (presumably a select query) that returns the results you want.  The new query should be a make-table query, and you can enter the name of the table to be created by clicking the Make Table button.  I like to use the prefix "tmak" for a make-table query, matching the query's prefix "qmak".

Make-table query
0
 

Author Comment

by:morinia
ID: 39969896
I get an error on the make_query that tells me I can not use an action query to create a row.

If you look at my query it is considered an "action query".
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 39970188
What "action" is it performing?  Is it making a table already?  Is it updating or inserting or deleting?
0
 

Author Comment

by:morinia
ID: 39970281
From what I read on the internet, I think because it is reading in multiple rows in a query to get one number.

If I am correct it is from(Select count(*)HIX_QPNY_MED_PROCESSED GROUP BY DOCNUM) that is making it an action query.

SELECT Count(*) AS MEDICAL_SUMMARY, 0 AS MEDICAL_DETAIL, 0 AS HOSPITAL_SUMMARY, 0 AS HOSPITAL_DETAILFROM (SELECT COUNT(*) FROM HIX_QPNY_MED_PROCESSED GROUP BY DOCNUM);

The unions give me the numbers I want, I am just not able to do anything with the results.

I tried doing just the query above in a table and the subquery is a problem when trying to create a table.

I am wondering if it needs to be converted to VBA.
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 39970443
The query you posted is not an action query so you should be able to create a new query, choose this query, select all the columns, and then change the query type to make table.  Is that what you did?
0
 

Author Comment

by:morinia
ID: 39970536
yes that is what i did.

See attached
action-query.doc
0
 
LVL 31

Assisted Solution

by:Helen Feddema
Helen Feddema earned 250 total points
ID: 39970930
The query type should be clear if you look at the Query Tools group in the Ribbon:

Query Tools group
A totals query is a type of Select query, and you should be able to create a make-table query based on a Totals query.  Don't try to turn the Totals query into a make-table query; make a new query based on it.
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 39972149
morinia, the document you posted doesn't help us.  What are the data sources?  If they are tables, fine.  If they are queries, you need to look at those to see what they are doing.
0
 

Author Closing Comment

by:morinia
ID: 39972200
Experts,

I found that there were columns on the table that could be used to get the totals I was looking for.  This eliminated the group by subquery.

In addition, I found out that I could query from a query which eliminated the need for the table I was trying to make.

I was able to get what I wanted with a simple union query.

Thanks to all for your patience with me.  

You were all correct in your respective answers, it was me not knowing all the details of the data and complicating the query.

SELECT 'Hospital' AS CLAIMTYPE, 'Documents' AS Type , COUNT(*) AS Tally FROM HIX_QPNY_HOS_PROCESSED
UNION
SELECT 'Medical' AS CLAIMTYPE, 'Lines' AS Type , COUNT(*) AS Tally  FROM HIX_QPNY_MED_PROCESSED
UNION SELECT 'Medical' AS CLAIMTYPE,  'Documents' AS Type, (SELECT COUNT(*) FROM QRY_QPNY_MED_DOCUMENTS) AS Tally FROM HIX_QPNY_MED_PROCESSED;
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

615 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