Solved

creating a table in access from query

Posted on 2014-04-01
16
319 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 84

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 37

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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 84
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 84
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 37

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 37

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 37

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

752 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