Results 1 to 11 of 11
  1. #1
    repke95 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Mar 2019
    Posts
    5

    Combining 3 queries and then exporting to a csv file

    I currently have a database that consists of many unique 18 digit codes (millions of them) that are supplied by our client. As we receive orders we then add 4 items to each of those records based on the size of the order. So if someone one orders 10,000 coupons we do the following.

    A) using a query, we select and save 10k unused records
    B) using another query, we update those 10k records each with a 4 digit store number, a 5 digit art code, and a 6 digit job number
    C) using one more query, we gather those 10k records based on the newly assigned job number and export them to a csv file which is named to include the 5 digit art code, job number, and the part number pulled from the order

    I would like to be able to add those five items of information once and have those queries run consecutively and then output a csv file as described above. Is this doable? I am happy to pay to have this done since my Access skill set is pretty much non-existent and hoping to complete this in a week or so.



    Example of data that would be entered;

    Store number 1021
    Quantity 7600
    Master ID 95472
    Job number 238472
    Part 02

    Link to an exported csv file 95472-238472_02.csv
    https://1drv.ms/u/s!Ap_oFkEX81J0gbMs593pwQs0SCiEMg

    Link to a test database
    https://1drv.ms/u/s!Ap_oFkEX81J0gbMrA2OIbAFRqSxUlg

    Thanks for your help,
    Chris

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    UNION query:
    select * from Q1
    union
    select * from Q2
    union
    select * from Q3


    then export the query:
    docmd.TransferText acImportDelim ,"SpecName","qnUnionQry","c:\filename.csv",true

    note: you must build your SpecName so the file knows how, with what delimiters.
    the best way is to import a csv, click Advanced button, set the specs/fields/etc and Save spec name.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    @ranman256, I don't understand suggestion for UNION query as solution.

    @repke95, multiple SQL actions cannot be run in a single statement. Can, however, use code (macro or VBA) to automate sequentially executing actions.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    repke95 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Mar 2019
    Posts
    5
    Thanks to you both. If this project is a better fit for a macro or VBA approach, should I close this post and add it to one of those groups instead?

    Also - should I zip my db and re-attach?

    "To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window."

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Don't think need to start a new thread. Especially if you don't have anything new. When you have code with specific issue, then might start a new thread.

    Many readers will not download files from another site, only from forum. I did look at your database.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I looked at your dB and created some code. As long st the quantity of records is less than 9500, the code runs without stopping. More than 9500, the error that appears is "Exceeded MaxLocksPerFile".
    I went into my registry and changed 4 (i think) instances of "MaxLocksPerFile" from 9500 to 11000.

    I used a record set and looping to select the records, then an update query to "fill in the blanks".
    I changed the name of the table - I took out the spaces. Should never use spaces in object names.
    And I added a Boolean field named "Selected" to be able to select the quantity of records.

    Being that the dB is 195 MB (after C&R!), I am posting a link to my Box account.

    EDIT: I imported the original table and forgot to include the new field and rename the table. Here is the updated dB.
    https://app.box.com/s/qg2laslqwcu4y4168psbn8enuca5ivoy


    I did not add error handling - something you might want to think about..... and there is no export to CSV. Not hard to do, but you need something to do.


    Good luck with your project.....
    Last edited by ssanfu; 03-27-2019 at 05:10 PM.

  7. #7
    repke95 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Mar 2019
    Posts
    5
    Thanks Steve! I will take a look tonight. Many thanks.

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Woke up at 3:30am thinking about the dB.

    Might be able to use 2 saved queries and some VBA to get rid of the looping code.

    First query would select the records. Use VBA to modify the SQL - update the TOP number.
    The second query would use the first query and update the selected field.
    Then, in code, execute an update query to add the Store number, Master ID and Job number.
    Haven't tried it yet....but should work. Could be faster than code when quantity > 5000.



    **EDIT: **
    OK, this version uses queries (no VBA looping) to select the records. It DOES NOT require the MaxLocksPerFile property in the registry to be modified. (at least on my computer)

    https://app.box.com/s/xc1y799aommvk3drqe8nlp8s1az91x5q

  9. #9
    repke95 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Mar 2019
    Posts
    5
    Thanks Steve, I've passed on your latest post to our IT lead. Hopefully you will have more restful sleep tonight!

  10. #10
    repke95 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Mar 2019
    Posts
    5
    I passed on your input to our IT department and they came up with the attached which seems to work well.

    Thanks!

    https://1drv.ms/u/s!Ap_oFkEX81J0gbMxCLo4lPZi7RzUBw

  11. #11
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Two things that I avoid:
    1) Spaces in object names.... Apparently the name of the table is "Coded Coupons", which has a space.
    2) Constantly creating and deleting objects (in this case a query).
    Constantly creating and deleting the query will cause dB bloat and increases the possibility of corruption.
    This is why I (manually) created the query once, then just changed the SQL property of the saved query (plus it is faster).


    One other thing. There is a line in the code (by your IT dept) that is
    Code:
    Set qd = CurrentDb.CreateQueryDef("tmpExport", buildReportQuery)
    The rule is
    "If you create, it destroy it.
    If you open it, close it."
    Since "qd" was created (by the Set command), it should be destroyed before the sub ends.
    Should add a line:
    Code:
        ' CLEANUP
        DoCmd.Hourglass (False)
        Me.tbStatus = vbNullString
        Set qd = Nothing    '<<--  add this line
        MsgBox "done"
    End Sub



    Happy to help. It was an interesting problem...
    Good luck with your project.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Combining two queries
    By joeshig in forum Queries
    Replies: 4
    Last Post: 01-30-2017, 09:02 AM
  2. Exporting Multiple Queries to a single Text File
    By sam.eade in forum Import/Export Data
    Replies: 6
    Last Post: 05-13-2014, 09:24 AM
  3. Replies: 7
    Last Post: 08-05-2011, 10:59 AM
  4. Combining two queries
    By Adele in forum Queries
    Replies: 1
    Last Post: 07-16-2011, 12:17 AM
  5. combining two queries
    By camell in forum Queries
    Replies: 4
    Last Post: 03-04-2011, 02:41 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums