Results 1 to 11 of 11
  1. #1
    ports883 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    7

    Automate Query

    Hi,

    I'm relatively new to Access and completely new to Access Macros.

    Every month I get 3 new reports (spreadsheets) that I import to an Access database. The reports are always identical in terms of field names, etc. The first thing I always need to do is create a query (link those 3 reports to a static (ie. unchanging) table with some references, and merge them).

    The database is organised as follows (categories on the left hand side):

    CommonTables
    - static table
    R1 (original reports from January)
    - table A
    - table B
    - table C
    R1_Queries
    - Query 1 (merge between the static table and R1 table A)
    - Query 2 (merge between the static table and R1 table B)
    R2 (original reports from February)
    - table A
    - table B
    - table C
    R2_Queries
    - Query 1 (merge between the static table and R2 table A)
    - Query 2 (merge between the static table and R2 table B)
    R3......


    Hopefully, you can see the pattern. Each month I'm doing the same queries merging the static table with a report (the reports are the same each month in terms of field names). How can I automate it (save the steps?)

    To be specific the things that I do in this query are:
    - create links between this month's report and the static table (every month the same in terms of field names)


    - choose the fields to be included
    - insert a column (calculated field? concatenating two other existing fields)

    Please advise.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The workbook names change? Are the sheet names always the same? The columns are always the same?

    Certainly automation can be done with enough code (most likely VBA, not macros).
    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.

  3. #3
    ports883 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    7
    No, Yes, Yes

    Thank you

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Do you want to synchronise the data in tables A and B with equivalent data in the static table?
    If so, won't that just result in duplication of data?

    What about table C which you've not mentioned?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    ports883 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    7
    Tables A,B,C come with information about learners

    Table A contains learner details including learner name / DOB, etc.
    Table B contains courses each learner is enrolled on (learners identified only by ID (need table A), campus is identified only by ID (need the static table), subcontractors id (need the static table))
    Table C is similar to B and for the sake of argument can be ignored for now

    The static data contains list of campuses where each course take place as well as subcontractors' details

    So the static table and A, B, C reports complement each other to form a full picture.

    the query contains Learner details, courses they are enrol on, campus details and the subcontractor details (if applicable).
    The content of the static table is not going to change

    Each month there are new learners enrolled, and existing learners might enrol on additional courses.

    Actually, apart from merging the data from the static table and A, B, C reports, I also need to compare eg. the latest Report A with report A from last month, to identify new learners (ie. the learners that were not in last month's Report A, etc. but that's a separate issue.



    Hope that clarifies

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    So set links to the workbooks one time. Whenever you get new ones, copy over the old ones and the links should hold.

    Run queries and whatever code needed to automate process.
    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.

  7. #7
    ports883 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    7
    Quote Originally Posted by June7 View Post
    So set links to the workbooks one time. Whenever you get new ones, copy over the old ones and the links should hold.

    Run queries and whatever code needed to automate process.
    So what you are saying, I should not import the A, B, C each month as new tables, I should just link them (replacing the linked files each month.) I would then set up a query once and it will update itself(new data) after replacing the workbooks? Am I correct?

  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
    I am suggesting that is one approach. Can work with the links as if they were tables (except cannot edit data in Excel workbook through link). However, if you prefer to import data without linking, that is also possible with VBA code. Review http://www.accessmvp.com/KDSnell/EXCEL_Import.htm
    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
    ports883 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    7
    Quote Originally Posted by June7 View Post
    I am suggesting that is one approach. Can work with the links as if they were tables (except cannot edit data in Excel workbook through link). However, if you prefer to import data without linking, that is also possible with VBA code. Review http://www.accessmvp.com/KDSnell/EXCEL_Import.htm
    Thanks. Will research it.

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Suggest you read this extended article http://www.mendipdatasystems.co.uk/s...a-1/4594514001 on my website.
    This details different methods of synchronising data including cases where data is from Excel so may have no primary key field
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  11. #11
    ports883 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    7
    Thank you. Will do.

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

Similar Threads

  1. Automate Query Refresh
    By AJ_25 in forum Access
    Replies: 3
    Last Post: 04-30-2018, 08:57 AM
  2. Automate Query Creation
    By dr4ke in forum Queries
    Replies: 8
    Last Post: 07-11-2012, 02:39 AM
  3. Automate Emails from Query - SendObject
    By Kennertoy in forum Access
    Replies: 5
    Last Post: 10-04-2011, 08:50 PM
  4. Replies: 0
    Last Post: 03-15-2011, 07:35 AM
  5. How to automate a access query ... Help me
    By kusamharsha in forum Programming
    Replies: 0
    Last Post: 02-25-2009, 09: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