Squirt query result into new table

  • Thread starter Thread starter vjp2.at
  • Start date Start date
V

vjp2.at

THis must be simple but nothing in the right click
seemed to let me put the results of a query into a table instead of
first putting it into Excel.


- = -
Vasos Panagiotopoulos, Columbia'81+, Reagan, Mozart, Pindus, BioStrategist
http://www.panix.com/~vjp2/vasos.htm
---{Nothing herein constitutes advice. Everything fully disclaimed.}---
[Homeland Security means private firearms not lazy obstructive guards]
[Urb sprawl confounds terror] [Phooey on GUI: Windows for subprime Bimbos]
 
THis must be simple but nothing in the right click
seemed to let me put the results of a query into a table instead of
first putting it into Excel.
Click to expand...
You've lost me. We're not looking over your shoulder at your computer
screen. You need to explain clearly what you are trying to do.
For one thing, there is nothing that I can run in Access that forces my
results to go into Excel that I can think of. Of course, I have not been
developing in Access for several years so I might have missed something.

There are two ways the result of a query can be put into a table:
1. An insert (Append) query - it requires an existing table be in place. The
syntax is:
insert into tablename (<column list>)
select <column list> from ...

2. A Make-Table query - it fails if the table already exists. Its syntax is:
select <column list>
into New_Table_Name
from ...
 
Thanks. Is it possible to insert and create in the same step?

I am thinking to merge the earlier two tables by creating a new
one rather than concatenating one to the other.
(N A B + N C D -> N A B C D)

eg
Create and insert into t3
Select t1.n, t1.a, t1.b, t2.c, t2.d from t1 inner join t2 on t1.n=t2.n

But this will be a million records (all the fields of each record
would add up to 500 chars).






---APPENDIX---
(Many thanks for all your help. I will post my solutions
due to your help here:)

fullexport

SELECT redistr.newad, redistr.newed, qsen11.*
FROM qsen11 INNER JOIN redistr ON (qsen11.AD=redistr.oldAD) AND (qsen11.ED=redistr.oldED)
WHERE (redistr.newcd=3);

redistr

SELECT DISTINCT qvot12.ad AS newad, qvot12.ed AS newed,
qvot11.ad AS oldad, qvot11.ed AS olded,
qvot12.sd as newsd, qvotcd as newcd
FROM qvot12, [;database=c:\qvot11.mdb].qvot11;

polexgbase

SELECT PolExgAll.AD, PolExgAll.ED, PolExgAll.FonXcg
FROM PolExgAll INNER JOIN PolExgGrp ON (PolExgAll.CntXcg=PolExgGrp.MaxCntXcg) AND (PolExgAll.ED=PolExgGrp.ED) AND (PolExgAll.AD=PolExgGrp.AD)
WHERE PolExgGrp.MaxCntXcg>4 AND LEFT(PolExgAll.FonXcg,3)='718' AND MID(PolExgAll.FonXcg,6,1) not in('0','1','2');

polexgall

SELECT qvot12.AD, qvot12.ED, Left([qvot12.Phone],7) AS FonXcg, Count(*) AS CntXcg
FROM qvot12
WHERE [qvot12.Phone]>""
GROUP BY qvot12.AD, qvot12.ED, Left([qvot12.Phone],7);


polexggrp

SELECT AD, ED, Max(CntXcg) AS MaxCntXcg
FROM PolExgAll
GROUP BY AD, ED;

walklist

SELECT qvot12.LNAME, qvot12.FNAME, qvot12.HOUSENUM, qvot12.STREET, qvot12.APT, qvot12.phone
FROM qvot12
WHERE (qvot12.LASTVOTED>"2010") And qvot12.polPARTY="REP" and qvot12.sd=15
ORDER BY qvot12.STREET, qvot12.HOUSENUM, IIf(Val(Right([qvot12.HOUSENUM],1)) Mod 2,0,1), qvot12.APT;


extremewalklist

SELECT qvot12.LNAME, qvot12.FNAME, qvot12.HOUSENUM, qvot12.STREET
FROM qvot12
WHERE (((Len(Trim([qvot12].[apt] & "")))<1) AND ((qvot12.LASTVOTED)>"2010") AND ((qvot12.polPARTY)="REP") AND ((qvot12.sd)=11) AND ((qvot12.AD)=25) AND ((qvot12.BirthDate)>1985000 Or (qvot12.BirthDate)<19550000))
ORDER BY qvot12.STREET, qvot12.HOUSENUM, IIf(Val(Right([qvot12.HOUSENUM],1)) Mod 2,0,1), qvot12.APT;

walklistinnerjoin

SELECT qsen11.[LAST NAME], qsen11.[FIRST NAME], qsen11.HOUSE, qsen11.STREET, qsen11.APT, qsen11.PHONE
FROM qsen11 INNER JOIN new15sd ON (qsen11.AD = new15sd.AD) AND (qsen11.ED = new15sd.ED)
WHERE (((qsen11.g08)="Y") AND ((qsen11.[LAST VOTE])>2009) AND ((qsen11.PARTY)="R") AND ((qsen11.g09)="Y"))
ORDER BY qsen11.STREET, qsen11.HOUSE, IIf(Val(Right([qsen11.HOUSE],1)) Mod 2,0,1), qsen11.APT;



- = -
Vasos Panagiotopoulos, Columbia'81+, Reagan, Mozart, Pindus, BioStrategist
http://www.panix.com/~vjp2/vasos.htm
---{Nothing herein constitutes advice. Everything fully disclaimed.}---
[Homeland Security means private firearms not lazy obstructive guards]
[Urb sprawl confounds terror] [Phooey on GUI: Windows for subprime Bimbos]
 
Thanks. Is it possible to insert and create in the same step?
Click to expand...

Only with a Make-table query as I described.
I am thinking to merge the earlier two tables by creating a new
one rather than concatenating one to the other.
(N A B + N C D -> N A B C D)
Click to expand...

Why bother? Just create a view that joins them as John Vinson recommended in
his reply to your other thread. You should really respond to his questions
if you need further help with this
 
*+-Why bother? Just create a view that joins them as John Vinson recommended in
*+-his reply to your other thread. You should really respond to his questions
*+-if you need further help with this

Thanks. Yes, I am still unaccustomed to the idea of refering to a query like
it was a table, simple tho it is.


- = -
Vasos Panagiotopoulos, Columbia'81+, Reagan, Mozart, Pindus, BioStrategist
http://www.panix.com/~vjp2/vasos.htm
---{Nothing herein constitutes advice. Everything fully disclaimed.}---
[Homeland Security means private firearms not lazy obstructive guards]
[Urb sprawl confounds terror] [Phooey on GUI: Windows for subprime Bimbos]
 
*+-Why bother? Just create a view that joins them as John Vinson
recommended in
*+-his reply to your other thread. You should really respond to his
questions
*+-if you need further help with this

Thanks. Yes, I am still unaccustomed to the idea of refering to a
query like it was a table, simple tho it is.
Click to expand...
Well, if you continue working with relational databases, it will become
second nature. I have yet to encounter a database product that did not allow
views to be used as tables in sql queries.
Do you have the same issues with subqueries that generate derived tables? It
is certainly possible to write this instead of using a saved query:

select * from (
select t1.N, A, B,C,D from t1 join t2 on t1.N=t2.N) as q


The only difference is that you would have to retype that subquery in every
query you write that needs to use the results from it. Whereas if you create
a saved query (a view), you can simply used the saved query in all your
queries.
 
Many THanks. I learned IBM Mainframe SQL in 1986
and the word view wasn't obvious until you defined it in the last post.

Incidentally, does this at all explain why Access queries appear as tables in
OPenOffice? (or at least in 2008 it did).

- = -
Vasos Panagiotopoulos, Columbia'81+, Reagan, Mozart, Pindus, BioStrategist
http://www.panix.com/~vjp2/vasos.htm
---{Nothing herein constitutes advice. Everything fully disclaimed.}---
[Homeland Security means private firearms not lazy obstructive guards]
[Urb sprawl confounds terror] [Phooey on GUI: Windows for subprime Bimbos]
 
Many THanks. I learned IBM Mainframe SQL in 1986
and the word view wasn't obvious until you defined it in the last
post.

Incidentally, does this at all explain why Access queries appear as
tables in OPenOffice? (or at least in 2008 it did).
Click to expand...
I don't know, I've never used that. It makes sense, though - one of the
ANSI-SQL requirements is that the database engine is able to treat views as
tables.
 
Back
Top