Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2009

    Question Unanswered: query output to table?

    I need to know if there is a way in 2003 to create a table from the output of a query. I'm running a multiuser agent ranking database in access 2003 that is pulling data from 5 different reports on a daily basis and have set all the queries for the final output I want it to achieve, but it takes about 10-12 minutes for the queries to run, I'm wanting to move the queries to the backend and set the output into a replicated table since the data imputs are done once a day and the data is accessed 30-45 times a day. The only thing I've come up with so far uses 3 different macros and then I have to go in and set the tables that are output to a replicated state each day before doing synch. on the backend. I'm hoping to figure out a way to drop the data out of the table then refill it with the data from the query on a daily basis.

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    sounds like you want a make table query or queries
    back it up with some delete queries to clear out redundant data
    could be run as a batch process overnight so the data is sitting raring to go when you come into work in the morning.

    first thing to do is to build the process (a series of queries that do the business logic. whether thats one big make table query or a series which populate discrete tables is upto you.

    design out how you handle 'master' data from an external source.. I'd suggest master data comes from the master system... so you either update and insert as neccesary or cull all the local data and ovrwrite.

    design how you handle dead data

    having got your busienss logic sorted out consider putting it all together in a macro or function called from a macro and get it to run at a specific time using Windows scheduler to call Access from a batch file of command line using command line switches to invoke the macro/function

    it can be a bit dauting to set it all up, but take each step one at a time, prove it works and it will make life easier
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2009
    Provided Answers: 14
    SELECT Field1[, Field2[, ...]] INTO Newtable [IN ExternalDatabase] FROM source
    Where Source can be a Select Query i.e.,

    SELECT F1, F2 INTO Newtable FROM (SELECT F1, F2 FROM SourceTable WHERE ...)
    Have a nice day!

  4. #4
    Join Date
    Jul 2009
    Thanks Sinndho. One of those time when you know that the answer is right there, but you can't make your brain spit it out.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts