Solved

creating a table in access from query

Posted on 2014-04-01
16
318 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
  • 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 36

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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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 36

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 36

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 36

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

828 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