Append only new records

[bkirsch]

I am building a database and it requires duplicating some data (not my choice) but here is my problem. I have a main form with 3 tabbed subforms linked to the main. One of the tabs is for sales rep entering, one for billing and commission, and one for amortizing rev. The "Amortization" table uses a lot of the information entered in the sales rep table and I have been using an append query with the warnings turned off (i know its wrong to do) to append the necessary data. However, as the database grows it takes a long time for the append query to run.

Can someone please instruct me or possibly write me the code to append only new records if I add an appended yes/no box to the sales rep table?

[June7]

How does the form control the APPEND? Show your code and query. Would need Yes/No field in table, bind the box to the field, use an APPEND SELECT sql with the Yes/No field as filter criteria.

[bkirsch]

Im not really sure what you are looking for....I just created an append query and was using a button with a macro to run the query and hide warnings...?

[June7]

Okay, you have a query, how does it know which records are 'new'? You need some field to base filter on so only the 'new' records will be copied. Assuming you retain the copied data in both tables, the source table needs a field to tag it as already copied. This could be a Y/N or a date. The APPEND SELECT would take only those fields that are No or undated. Then an UPDATE on the source table to populate this field.

Post the code that executes this query and also post the SQL statement of this query. Or attach project.

I use only VBA, not macros.

[bkirsch]

I understand exactly what you are saying to do and undertand the UPDATE part but I do not know what an APPEND SELECT is...how do I enter the criteria for the query to only append data that is not checked in the Y/N box

[June7]

Sorry, it is INSERT SELECT. Check this http://www.1keydata.com/sql/sqlinsert.html

There is also a SELECT INTO syntax http://www.w3schools.com/sql/sql_select_into.asp

[bkirsch]

Im sorry this is so difficult for me to grasp and unfortunately I cannot upload the database but i really really appreciate all your help...

The query I currently have is called append to amortization details here is the sql view

INSERT INTO [1 Amort Details] ( Client, [Contract Number], [Start Date], [End Date], [Cash Or Trade], Event, [Partner Type], [Inventory Element], [Element $$ Allocated], [Other Notes], Escalator )
SELECT [1 Suite Deal Details].Company, [1 Suite Deal Details].[Contract Number], [1 Suite Deal Details].[Contract Start], [1 Suite Deal Details].[Contract End], [1 Suite Deal Details].[Cash/Trade], [1 Suite Deal Details].Event, [1 Suite Deal Details].[Partner Type], [1 Suite Deal Details].[Inventory Element], [1 Suite Deal Details].[Inventory $$ Allocated], [1 Suite Deal Details].[Other Notes], [1 Suite Deal Details].Escalator
FROM [1 Suite Deal Details];

"1 Suite Deal Details" has a Append Field which is a Y/N


Can you please guide me from here?

[June7]

Why can't upload db? Make copy, remove confidential info, run Compact & Repair, zip if large. That or export data to an Excel and post that.

Include a WHERE clause in the SELECT subquery.

WHERE [fieldname] = False;
or
WHERE [fieldname] = True;

whichever case is appropriate for how you manage that field.

Check means 'need to append' or 'already appended'? I suggest call the field Appended and append all those that are False, then run UPDATE query to change all to True. Then no need to manually check each box.

I should have asked earlier. Just why is this append necessary? You are receiving data from elsewhere? If you are receiving only new records, why would the append take longer as the db grows? If you aren't already appending only new records, what are you doing?

[bkirsch]

Ok I have uploaded the database and have an additional question if you dont mind helping me with that as well...

The "CLIENT" form is the primary form I am working off of...


1. The original question is how do I appened only new enteries from the "contract details" table to the "amortization" table. I realize this is a complete duplication of data but am being told this is the format I need to have. ??

2. I can't seem to get the "main form" to link to the three tabbed subforms when new records are added to "main form". Ideally I would like the contract number (autonumber in "main form") and client name to update three blank records in the three linked subforms...

Again thank you so much for your help. I am in kind of over my head with this project and really really really appreciate you helping.

[June7]

So 1 Suite Deal Details and 1 Amort Details tables can have many records for the same ContractNumber? A contract can have multiple start and end dates? Contracts are renewed?

I think you need another table for these fields. Then have Details tables link to this ContractsHistory table and stop replicating data.

[bkirsch]

sorry I should have explained more...

the "Client" form has client name as the primary key. The "main" form represent a contract and in some cases multiple contracts. the three tabbed subforms act as details for the "main" form. The renewal you referred to is just a status for commission purposes and the dates in that table are for billing.

[bkirsch]

When I add a new record to "main form"it blanks the three tabbed subforms to add new records to that. However, when I adding "main form" to "clients" as a subform the add new record function of main form doesnt blank the three tabbed forms.

and of course I still havent quite figured out the append only new from "contract details" to "amortization"

Thanks for all your help. I owe you BIG TIME.