creating a table in access from query

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);
moriniaAdvanced Analytics AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
moriniaAdvanced Analytics AnalystAuthor Commented:
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
PatHartmanCommented:
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
moriniaAdvanced Analytics AnalystAuthor Commented:
Experts,

I am getting an action query cannot be used as a row source.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You'd have to tell us more than that.

Where are you running the queries? In the query designer, or somewhere else?
0
moriniaAdvanced Analytics AnalystAuthor Commented:
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
Helen FeddemaCommented:
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
moriniaAdvanced Analytics AnalystAuthor Commented:
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
PatHartmanCommented:
What "action" is it performing?  Is it making a table already?  Is it updating or inserting or deleting?
0
moriniaAdvanced Analytics AnalystAuthor Commented:
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
PatHartmanCommented:
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
moriniaAdvanced Analytics AnalystAuthor Commented:
yes that is what i did.

See attached
action-query.doc
0
Helen FeddemaCommented:
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
PatHartmanCommented:
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
moriniaAdvanced Analytics AnalystAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.