Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Queries / April 2006

Tip: Looking for answers? Try searching our database.

Trying to create a table with a query in sql

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Hal2604 - 19 Apr 2006 16:03 GMT
I am trying to use TOP N to create a table and access does not like my code
can someone help me.

   Dim Num As Long
   Num = 2
   DoCmd.RunSQL "INSERT INTO tblRptFreeSpotList(LastName, FirstName,
EmailAddr, ParkingLot, PreferLot, DH)" & _
       "SELECT TOP " & Num & " MasterList.LastName, MasterList.FirstName,
MasterList.EmailAddr, MasterList.ParkingLot, MasterList.PreferLot, MasterList.
DH FROM MasterList" & _
       "WHERE MasterList.PreferLot = " & "'" & Forms!frmMain!txtPreferLot &
"'" & _
       "ORDER BY MasterList.DH"

Thank you
Jerry Whittle - 19 Apr 2006 17:46 GMT
Should  be a SELECT INTO and look something like:

SELECT Admin2.Name, Admin2.Number
INTO AdminNew
FROM Admin2;
Signature

Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> I am trying to use TOP N to create a table and access does not like my code
> can someone help me.
[quoted text clipped - 11 lines]
>
> Thank you
Hal2604 - 19 Apr 2006 18:37 GMT
How do I get the top values?  The statement you gave me doesn't keep the top
values.  The error I am getting when I run it in access says "Syntax error in
From Clause"  

Thanks!
John Spencer - 19 Apr 2006 20:30 GMT
Assumption is that you are getting a syntax error.  You really need to tell
us what error messages you are getting rather than "Access does not like my
code".

Missing a space between tblRptFreeSpotList and the list of fields
Missing a space between the list of fields and SELECT
Missing a space between MasterList and WHERE
Missing a space before ORDER BY  (by the way, the ORDER BY is probably
useless in an insert query)

   Dim Num As Long, StrSQL as String
   Num = 2

   StrSQL = "INSERT INTO tblRptFreeSpotList  (LastName, FirstName,
EmailAddr, ParkingLot, PreferLot, DH)" & _
       " SELECT TOP " & Num & " MasterList.LastName, MasterList.FirstName,
MasterList.EmailAddr, MasterList.ParkingLot, MasterList.PreferLot,
MasterList.DH FROM MasterList" & _
       " WHERE MasterList.PreferLot = " & "'" & Forms!frmMain!txtPreferLot
&
"'" & _
       " ORDER BY MasterList.DH"

DoCmd.RunSQL StrSQL

There may be other problems in the SQL statement.  I find it is best for me
to build the SQL statement in a variable and then Debug.print it to see if
it looks correct.  IF it does, I copy it into a new query and attempt to run
it.  If it runs, I comment out the debug.print strSQL statement and let code
run.

>I am trying to use TOP N to create a table and access does not like my code
> can someone help me.
[quoted text clipped - 13 lines]
>
> Thank you
Hal2604 - 20 Apr 2006 12:08 GMT
Thank you for your help.  You fixed my problem.  It was the missing spaces.
It works wonderfully now.

Holly
John Spencer - 20 Apr 2006 13:10 GMT
I'm glad that fixed the problem.  And please remember the advice on how to
test your SQL statement.  It will end up saving you a lot of headaches.

> Thank you for your help.  You fixed my problem.  It was the missing
> spaces.
> It works wonderfully now.
>
> Holly
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.