Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA code to run Queries & Append to a Table (Office 97 SR2)

    Hi ACCESS Loungers:
    I have 3 ACCESS queries (pasted below & also in the attached file) that I will connect using union clauses. I need my users to run the combined query each month and have the output automatically append to a table that has the data output for the previous months. For example, suppose the data for the current month's file for project HNS is HNSIQC1001 and the Table that is queried has the same name. I would like to have the User click a button or, better yet Select a project name (there are a number of other Projects which will have the same setup) and a month year (like 1001) then select 'run'. The application would open the correct database, in this case HNSIQC (which would have the data up through Septeember 2001) and automatically link to the HNSIQC1001 file. The application would then run the union query, substituting 1001 for all places where 0901 appear. It woud then Append the output to a table in HNSIQC.mdb which had the results for the previous 9 months.
    Your assistance is appreciated.
    Stephen

    Qry0user
    SELECT HNSIQC0901.HCFACNTL, HNSIQC0901.FIELDNAME, HNSIQC0901.ORIGVAL, HNSIQC0901.ORIGREAB, HNSIQC0901.REABREAB
    FROM HNSIQC0901
    WHERE (((HNSIQC0901.FIELDNAME)="Abstraid") AND ((HNSIQC0901.ORIGREAB)="0") AND ((HNSIQC0901.REABREAB)="!"));

    QryAuser
    SELECT HNSIQC0901.HCFACNTL, HNSIQC0901.FIELDNAME, HNSIQC0901.ORIGVAL, HNSIQC0901.ORIGREAB, HNSIQC0901.REABREAB
    FROM HNSIQC0901
    WHERE (((HNSIQC0901.FIELDNAME)="Abstraid") AND ((HNSIQC0901.ORIGREAB)="A" or (HNSIQC0901.ORIGREAB)="B") AND ((HNSIQC0901.REABREAB)="!"));

    QRYCombine
    SELECT [Month2001] AS Expr1, HNSIQC0901.FIELDNAME, HNSIQC0901.HCFACNTL, HNSIQC0901.ORIGREAB, HNSIQC0901.REABREAB, HNSIQC0901.ORIGVAL, HNSIQC0901.REABVAL, HNSIQC0901.AJUDVAL, HNSIQC0901.DENOMCNT, HNSIQC0901.MATCHCNT, HNSIQC0901.FORM, HNSIQC0901.REASON, HNSIQC0901.TITLE, qry0user.ORIGVAL AS OUser, qryAuser.ORIGVAL AS AUser INTO HNS0901
    FROM (HNSIQC0901 INNER JOIN qry0user ON HNSIQC0901.HCFACNTL = qry0user.HCFACNTL) INNER JOIN qryAuser ON HNSIQC0901.HCFACNTL = qryAuser.HCFACNTL
    ORDER BY HNSIQC0901.HCFACNTL;
    <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA code to run Queries & Append to a Table (Office 97 SR2)

    What is it that you're asking for help on, creating the union query, substituting the database filename or table name, creating the button code, doing the linking, or what? All you've presented is three select queries. You're going to have to ask more specific questions to get focused help.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA code to run Queries & Append to a Table (Office 97 SR2)

    Sorry, if I rambled a little. I will try to be more specific. I can do the union queries and I can append the outputs to a table with the results from past months.
    Yes, I do need help creating the button and writing the code; code to do what? The following:

    1.Link to the new Database file: each month, the user will link to the source database for that month; the source's name will be ModIQCmmm.mdb, where Mod will be one of several modules (HNS,CC3,PNS,...) and mmm would be the 1st 3 letters of the month, Nov for November, etc. The files would all be in the same folder (where the folder names would be the same 3 digit abbreviations). It would be nice if the linking could be accomplished in a semiautomatic manner (i.e., the user would be asked to input the mmm for the next month that needed to have a link established); and
    2.Activate the queries and pass the mmm part of the Table name for the new month to the SQL statements
    Thanks
    Stephen

    "By doing just a little every day, I can gradually let the task overwhelm me."
    Ashleigh Brilliant - UC Berkeley 'street' philosopher
    <img src=/S/woof.gif border=0 alt=woof width=15 height=15>

Posting Permissions

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