Append Query

[buckwheat]

Does anyone know if it is possible, and if so, how to automate an append query to run automatically when the database is open.



Thank you.

[JoeM]

One way:

Create a Macro in which you run this Append Query (using the "OpenQuery" Action).
Name the Macro "AutoExec".

Any Macro named "AutoExec" will execute automatically upon opening, as long as your Macro Security settings are set to allow it (you may want to set up the location of the Access Database as a Trusted Location so that it will automatically enable Macros and VBA).

[buckwheat]

Joe, This worked for me, thank you. A problem I just realized: if the user opens the database 3 times in one day . . . data will be triplicated if they accept yes, It really should only automate the first of each month, is it possible to get this specific with a macro or VBA

[JoeM]

It depends. If your data has some sort of unique identifier or Primary Key combination, if you set it up properly, it will only add the data once, as further attempts will result in Key field violations (no duplicates).

Or you may be able to set-up your Append Query to be an unmatched query (meaning it checks to see if the data already exists in your table before adding it).

If your data does not have "built-in" fields that allow you to do that, another option is to add a table (or a field within your table) that indicates the last time the job was run. If it was already run that day (or month), you could tell it not to run.
In order to do that, you may need to use VBA code instead of just Macros.

[buckwheat]

Unfortunately I inherited the database. There are two source tables for two different groups . . . both are links, so it's safe to say there are no primary keys established in the tables. There are also no relationships in the relationship window. What this append query does is add table #2 to table #1. Are you able to provide VBA to perform the append query set for the 1st of each month?

[JoeM]

Just do an unmatched query between Table2 and Table1, to find all the record in Table2 that are not already in Table1. Join on as many fields as you meed to ensure you have a valid check.
Then, use the unmatched query as the source of your append query.

[buckwheat]

I created the Unmatched query and used it as the source for the append query. I created the AutoExec and tested . . . it worked; however, when I closed the database and re-opened it, it went through the same process to append and doubled the line items.

[JoeM]

however, when I closed the database and re-opened it, it went through the same process to append and doubled the line items.

If your append query is truly based upon your unmatched query, and your unmatched query is set up properly, this shouldn't be possible.
Can you post the SQL code of your append query/unmatched query?

[buckwheat]

Sure. Could be on my part . . . never used Unmatched before. The goal is to have NOCLAIMG (114 records) added to NOCLAIM. The query ran showing 114 unmatched. Running the query once results with 114 records being added to NOCLAIM, which is correct. But isn't recognizing something when reopening the db

SELECT NOCLAIMG.PATID, NOCLAIMG.NAME__LAST, NOCLAIMG.INVNO, NOCLAIMG.GROUPNO, NOCLAIMG.STMTCD, NOCLAIMG.DRNO, NOCLAIMG.RECVBL_STA, NOCLAIMG.RESP_AMT_B, NOCLAIMG.AMOUNT_BAL, NOCLAIMG.SERVFROM, NOCLAIMG.PROCCODE, NOCLAIMG.AMOUNT, NOCLAIMG.CLAIMKEY, NOCLAIMG.STMTC2, NOCLAIMG.ENTRYDATE, NOCLAIMG.MONTH____E, NOCLAIMG.CURRENT_MO
FROM NOCLAIMG LEFT JOIN NOCLAIM ON NOCLAIMG.CLAIMKEY = NOCLAIM.CLAIMKEY
WHERE (((NOCLAIM.CLAIMKEY) Is Null));

[JoeM]

What is the "CLAIMKEY" field? What does it represent?
Is it an AutoNumber field?

When you run it a second time, and you are getting "duplicates", locate any duplicate set of records, and check to see if their "CLAIMKEY" field is the same or different.

[buckwheat]

ok . . . I re-did everything . . . claim key field is not automated and is blank in both tables. using just the date of service, last name, pat id, and group, should be enough; however, if I remove the claim key, the results return that only 5 records don't match, even when I'm looking at the main table and there are 114 from table 2 that do not appear in table 1

[JoeM]

using just the date of service, last name, pat id, and group, should be enough; however, if I remove the claim key, the results return that only 5 records don't match, even when I'm looking at the main table and there are 114 from table 2 that do not appear in table 1

That tells me that using those ("service, last name, pat id, and group") probably is not enough to identify a unique record, and you need to add more fields.
An easy way to check:
1. Create a query based on one of these tables with JUST those 4 fields in it.
2. Add any other field to the query.
3. Click on the Totals button to make it an Aggregate Query.
4. Under the "extra" field you added in step 2, change the Totals row value from "Group By" to "Count"
5. Under the criteria of this field, enter: > 1
6. View the results. This will show you all "service, last name, pat id, and group" combinations in your table that match more than one record.

You can keep adding other fields to it. When you have hti the right combination, this query shouldn't return any records.

[buckwheat]

ok,i'll give it a shot. your last statement is a little confusing, isn't the data that is unmatched supposed to return in the query results? . . . why wouldn't there be any records?

[JoeM]

I switched gears on you. The last set of instructions are NOT an unmatched query (notice if it only looking at one table!). This is a debugging technique to identify any flaws in your logic.
All that it is doing is seeing within a single table, if the combination of fields you choose uniquely identify a record. The COUNT function is counting how many records have each particular combination of those four field values.
If that combination of four fields really is unique to each and every record, the count will be 1 for every combination, so when you use criteria of "> 1", no records will be returned (which is what you want).
If it returns any records, then it means that there are multiple records with the combination of those 4 fields.

[ssanfu]

Or you could use a lower tech (aka brute force) method

Create a table that has one date/time field:
Table name = "LastAppend"
Field name = "AppendDate" (Date type)

   Dim d As DAO.Database
   Dim r As DAO.Recordset
   Dim DateToCheck As Date
   Dim sSQL As String

   Set d = CurrentDb

   'Get first of month
   DateToCheck = DateSerial(Year(Date), Month(Date), 1)

   sSQL = "SELECT [AppendDate] FROM LastAppend WHERE [AppendDate] = #" & DateToCheck & "#"
   Set r = d.OpenRecordset(sSQL)

   If r.BOF And r.EOF Then
      'this month not found - add the date
      r.AddNew
      r("AppendDate") = DateToCheck
      r.Update

      'execute append query
      DoCmd.OpenQuery "YourQueryName"

      '   Else
      '      MsgBox "Already appended data"
   End If

   r.Close
   Set r = Nothing
   Set d = Nothing

Called or run from the autoexec macro


New thought.... you could add a time stamp field if you wanted to track when the update was executed.......