MS Access SQL conditional Append

[Jamie]

hi newsgroup, can any one offer some advise?

I am trying to create a conditional append query along the following
logical manner:

INSERT INTO TableB
FROM TableA
WHERE TableB.Field2 <> TableA.Field2

The two fields I am comparing contain a reference number that isn't
unique (i.e. 10 records with same Field2 number. but different values
in the other fields). I want to append all the contents of Table1 that
have a Field2 value NOT found in TableB.

I am confused as to how I manage this without dropping any records
from TableA , i.e. duplicated Field2.

I tried a query in the format of the example above and Access prompts
me to enter a single Field2 value. I would like the Append query
condition to compare the tables rather than prompt for a variable.

Sorry if this is a bit confusing. I started reading about no
duplicates constraints, but I wasn't sure if that would apply only to
the conditional part of the statement, I need all the new records to
be appended even if they have duplicate (but NEW) Field2 values.

Any help or advise will be greatly appreciated.

Jamie

 

[Sylvain Lafontaine]

The easiest way of doing this would be to use the Not Exists() statement:

INSERT INTO TableB
FROM TableA
WHERE Not Exists (Select * from TableB where TableB.Field2 = TableA.Field2)

There are more complicated ways of doing this and that will possibly give
you a better performance like making a Left Join and checking for the Null
value but the method above should be more than enough for you.

Also, work on a copy of the database or replace the Insert Into with a
Select statement in order to test this first.

 

[NetworkTrade]

Well your sample code refers to Tables A and B but then you mention Table 1 -
so I don't think one can answer this without some clarification;

To append into Table B where Field 2 is equal but the "values" differ...

do you mean adding more records (the definition of append) or adding more
columns to a single record where Field 2 is X??

how many columns of values exist? are these two tables of identical
structure? is Field 2 = X going to appear multiple times in Table A or B
before the append??

As this all implies that your data tables are not normalized, I trust you
are aware and don't want a lecture on this point.....

 

[Jamie]

Thanks Sylvain. That's exactly what I was looking for. Testing with a
Select statement returns the expected records.

 

[Jamie]

hi again, I have another problem querying the data after the append
statement is run . The append statement works as hoped. The additional
data is being appended to the table. The problem refers to a form that
queries/updates the same table. The form contains a list (referencing
Field2 in the table), a sub form (datasheet) displays the remaining
details matching the Field2 record selected in form1.

The problem is a bit strange to me in that the new data is visible in
the list in the main form, but the when selected no data is displayed
in the sub form. This problem doesn't occur on the original records.
Any ideas? I appreciate its difficult to advise without more info
(being a newbie I am not sure what's the best info to supply). Both
forms query the same table.

i.e.
Form1 -> List1 -> datasource: SELECT Field2 FROM ReturnsFormTest;
SubForm1 -> multiple Text boxes -> datasource: SELECT
Field3,Field4,...,..., FROM ReturnsFormTest

The form works fine with the existing records. However the newly
appended data isn't being displayed in the sub form. Can any one
advise what could be causing the problem? At the moment I don't know
what to read up on first, I want to read more and understand the
principles better but I am not sure where to start.

cheers
jamie

 

[Sylvain Lafontaine]

Are you sure that you have made a refresh of the subform, too?

Maybe some problem with a duplicate key. Without knowing the exact detail
of your table along with some example of values of both types (work/don't
work), it's hard to tell you anything more.

You should repost your question in the microsoft.public.access.forms
newsgroup along with more details.

 

[Jamie]

I resolved the issue eventually thanks.

All feedback is greatly appreciated.