1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: ACCESS: Show second latest data and export data to excel from macro

Discussion in 'Business Applications' started by t_g2, Aug 11, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. t_g2

    t_g2 Thread Starter

    Joined:
    Aug 29, 2007
    Messages:
    59
    Hello,

    I have two problems:

    1.
    I have a lot of scanning data, all date and time stamped. All the scans show different status of shipped packages (all shipments have unique ID#, but there are multiple scans on route for all of them to track the packages). I want to find all the packages, for which there was "misdirected" scan, then for all of them, show the scan before. So, the end result would be all the scans that show for each package where it was misdirected (the terminal# of the scan before the misdirect scan).

    2.
    I want to automate this: run 5 queries every two hours between 8am and 10pm, copy and paste all results into Excel to specified areas (e.g. first query results to A1, second to A5000, third to A10000, etc. or onto different tabs), save as .csv to a specific folder, then send it as attachment to a given e-mail address. Even partial solution can be good.
    I only know macros (somewhat), not as much VBA, so I tried that with the TransferSpreadsheet command, but can't get it work.

    Any help is greatly appreciated.
     
  2. Sponsor

  3. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,617
    I think we can manage this, but I have some questions, why do you want to send the data to excel and then create a CSV file from that?
    Do you want to mark the ones that have been processed in some way so that they are not sent again?
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,492
    First Name:
    Hans
    I don't know why you have to use Excel, you could just create a query that selects what you need, export it as csv with a filename and location.
    To mail I have attached a vba module that you could use, I found it quite some time ago and use to do the mailing like you ask, I have a certain number of file in a folder, given the correct parameters, the file is attached to outlook and set to the given email addresses.

    Of course you will still need the procedure to initialize the process and then invoke one of the two functions in the attached module to have it mail.

    Give it a shot, I'm sure you can make it work.
     

    Attached Files:

  5. t_g2

    t_g2 Thread Starter

    Joined:
    Aug 29, 2007
    Messages:
    59
    Hello,

    Sorry, I had a different assignment for a couple days.
    So, no, I don't have to export it to Excel, I am just not sure what is the way or what is the best way of doing it. If I can export directly into csv, putting al five query results there, it is even better.

    Any ideas about the other problem? (Find second last record by date&time).
     
  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,492
    First Name:
    Hans
    For the second question I think you will have to do something with min and max values per date and time and see what you can get.
    You could try this in a query. I think for Access guru's on board it'll be a cinch.
     
  7. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,617
    When you say "putting al five query results there, it is even better." what are the other 5 results?
    The easiest way to achieve what you want is to run an Append query that appends the "Last" record to a temporary table and then use that table to exclude those records for the query that will do the exporting.
    Or if your field structure is right it may be possible to just use one filtered query.
    Can you provide some dummy data?
     
  8. t_g2

    t_g2 Thread Starter

    Joined:
    Aug 29, 2007
    Messages:
    59
    Sorry, I think I did not make it clear: the five queries are totally different, pulling data from different tables, so their data structure is different. So I can't append them all into one query. I will give you some data, probably tomorrow. Thanks for your help again.
     
  9. t_g2

    t_g2 Thread Starter

    Joined:
    Aug 29, 2007
    Messages:
    59
    I think I found another (easier for me) option for sending the results as attachment with the "SendObject" macro command.
    I was also thinking about doing the whole thing from the other side: creating macro that would import the query results in Excel, then save the whole thing as a csv. Both exporting in Access or importing in Excel are good if it works well.
     
  10. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,617
    SendObject or TransferText are both methods to try.
    But I need some dummy data to get the second to last Records.
     
  11. t_g2

    t_g2 Thread Starter

    Joined:
    Aug 29, 2007
    Messages:
    59
    OK, here is some sample. I did not find any "misdirect" scan data right now, but did some "bad address". So, I tried to do the same with those (can see them in the queries).
    Now, with the data attached, I am looking for only the records for those shipments that were scanned as "bad address" (scan code = bad address), but looking for only the records before the bad address scan (24 in total in the sample). If there are multiple bad address scans on the same piece along the shipping route, I would like to see all the scans before all of those. Thanks for any help.
     

    Attached Files:

  12. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,617
    I think this does what you want, run the query called "LastScanBeforeMisdir Output".
     

    Attached Files:

  13. t_g2

    t_g2 Thread Starter

    Joined:
    Aug 29, 2007
    Messages:
    59
    Yes, seems to be the right results. Thanks a lot!!!

    Any idea about the multiple queries into one csv file? I managed to send one out by e-mail, but it only has one query, not all of them.
     
  14. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,617
    The simplest way would be to append the 5 queries to a table and then export that to a csv file.
    However couldn't you create a report with the 5 queries on it and send that in an email directly from Access?
     
  15. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/942280

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice