• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 327
  • Last Modified:

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);
0
morinia
Asked:
morinia
  • 7
  • 4
  • 3
  • +1
4 Solutions
 
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
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now