Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2018
    Posts
    3

    Creating My First Macro

    Each month I receive an EXCEL spreadsheet, I am been entrusted to scrub the file cleaning up errors before its imported in a CRM.

    I haven't used Access in years since our organization deployed CRM and would love some help. My thought was to bring the file into access and develop a few macros. Here are a few basic things I had my sights on.

    If [description] contains *Dual* change [quantity] to 2

    If [description] contains *Refurbished* and [sku] is ABC123 change [sku] to 321CBA

    Next I need to detect duplicates

    If [auth number] is duplicated and [first name] is blank delete the row AKA record

    Look forward to hearing back from the group, thanks in advance for your help.



    R

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    BEFORE you start, use the query wizard to build a FIND DUPLICATES QUERY.
    give it the table and conditions.
    save qsFindDuplicates

    your macro will run queries

    openquery "quSetDUAL" ...[DESCRIPTION] LIKE "*DUAL*", set [qty]=2
    openquery "quSetReferb" ...[DESCRIPTION] LIKE "*Refurbished*" and [sku] ='ABC123', set [sku]='321CBA'

    use the qsFindDuplicates query to finish your requirements.

  3. #3
    Join Date
    Feb 2018
    Posts
    3
    Quote Originally Posted by ranman256 View Post
    BEFORE you start, use the query wizard to build a FIND DUPLICATES QUERY.
    give it the table and conditions.
    save qsFindDuplicates

    your macro will run queries

    openquery "quSetDUAL" ...[DESCRIPTION] LIKE "*DUAL*", set [qty]=2
    openquery "quSetReferb" ...[DESCRIPTION] LIKE "*Refurbished*" and [sku] ='ABC123', set [sku]='321CBA'

    use the qsFindDuplicates query to finish your requirements.
    I am lost sadly, I sent you a PM. I am in access 2016 and it looks like you are on 2010. I do not see a place to simply enter the string. I am presented with a builder like my kids see at code.org In the builder I do not see LIKE as an element. One thing to keep in mind is after we build the rules/macro I will need to run it against a new file each month hopefully with minimal effort.

  4. #4
    NTC is offline VIP
    Windows 10 Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    I'll flesh out a few aspects assumed in 256's post: what you need is fairly common and Access is a good tool for this.
    a. manually strip out extraneous title rows, etc off the excel so it is a pure grid of information
    b. import it into an Access table (let's call it Table 1)
    c. then apply your action queries to Table 1
    d. export Table 1 to a newly named Excel

    Your first two actions are Update Queries. Make them 2 part - first make a select query that assembles the correct rows. Save it with a name. Then make an Update Query that uses the Select Query as its source. This makes things a lot easier to set up and test.

    The duplicate is a bit more tricky because Access doesn't like Delete queries joined to an Aggregate query. Make an Aggregate Query (Sigma symbol looks like big E) to group and count the duplicates. Then create a Make Table query using the Aggregate as a source to write the dupes' key field to a new stand alone table. Then make a Delete query using Table 1 joined to the new Dupes table.

    oh - and none of this has anything to do with macros.......


    Your excel cannot change structure (names of columns or new columns). If it does one must reset up the link to it each time. It's okay if there are missing columns and if it is okay to ignore new columns then that is okay too.

    Finally; I don't think there is a 'reverse string' function in visual /vba; but I could be wrong about that and so you should do a search on that topic and see if it is there. I know some language do have that function. Without a reverse string function then you are going to have to create a lookup table for every one with its reverse and manage that as part of your query design.

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Finally; I don't think there is a 'reverse string' function in visual /vba
    there is the strreverse function?

    strreverse("abc")="cba"

  6. #6
    Join Date
    Feb 2018
    Posts
    3
    DUH, I thought I would be working with a macro of sorts. Here is what I see. I used a query wizard and build a simple query. Then I clicked on the designer and added the fields but not sure where to go next.



  7. #7
    NTC is offline VIP
    Windows 10 Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    good to know - thnks - although other than this post I've never needed it - will file it away as one never knows.......

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    New function for me, too.

    Be aware macros in Access are not the same as macros in Excel. Excel macros are really just VBA procedures. In Access, macros and VBA are very different.

    What you need to do is first get the SQL actions to work properly. Then can use code (macro or VBA) to automate the SQL executions.
    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.

  9. #9
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    although other than this post I've never needed it
    I've used it for hashing things like connection strings/passwords

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

Similar Threads

  1. Replies: 2
    Last Post: 01-02-2015, 06:55 PM
  2. Help in creating an automated macro/query
    By rexer231 in forum Macros
    Replies: 1
    Last Post: 06-02-2014, 10:05 AM
  3. Replies: 3
    Last Post: 08-16-2012, 11:16 AM
  4. Replies: 1
    Last Post: 06-28-2012, 08:34 PM
  5. Creating Macro from Module
    By Harley Guy in forum Modules
    Replies: 1
    Last Post: 11-08-2010, 07:44 AM

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