General discussion

  • Creator
    Topic
  • #2181201

    Access

    Locked

    by olufunsho.ayo ·

    Please HELP!!!…I am tryin to automate a cumbersome process I do on Access. What I do is that I run a query from two different tables. i then export these queries to an excel worksheet. i sort both queries by the “customer name” field. I then compare the “contract ID “field in one worksheet(query) with the “overall ID” field in the second worksheet (query). then I generate a third worksheet from these comparisons. this new worksheet contains all the “customer ID” for the first query that does not match any of the “overall ID” from the second worksheet

All Comments

  • Author
    Replies
    • #3187834

      Reply To: Access

      by sgt_shultz ·

      In reply to Access

      skip excel, do it all in access. then output to excel if you must to print report.
      think of your queries as tables, yes?
      you are saying these tables are related by customer name? or is overallid the unique customer identifier?
      anyhow, try query new make query wizard find unmatched records.
      did you look at changing yuor queiers to ‘make table’ querys if you find you can’t nest the queirs like i think you can…

    • #3187828

      Reply To: Access

      by olufunsho.ayo ·

      In reply to Access

      I am saying the two queries are related via the
      “contract Id”. however it called “contract id” on one query and called “overall contract id” on the other query. These 2 queries are from 2 different tables!.
      Thanks

    • #3056195

      Reply To: Access

      by pcpapa ·

      In reply to Access

      This can all be done in VBA. There’s no reason to generate the 1st 2 spreadsheets unless you need them for some reason. Instead use two recordsets. You can use the contract ID as your lookup and check for it in the other recordset using the FindFirst or Seek function. Then use the NoMatch property to generate a new table containing the info for those found without matches. Then you can generate a spreadsheet from the new table. Let me know if you need more details.

Viewing 2 reply threads