Adding a row to a table using only one field from another table 1

[rmork]

I'm trying to add rows to an already existing table using only one or two fields from another table.
If the value that I'm importing is allready in the table, I want it to skip this field. I want all the other fields in the new row to be empty. Can this be done with a query? Primary key is autonumber...

I've been trying to solve this for quite some time now, but I must be missing something...

 

[r937]

If the value that I'm importing is allready in the table, I want it to skip this field.

could you be more specific please? which value? which table? which field?


to answer your question, yes, it's possible, but you haven't given enough details for us to help you


r937.com | rudy.ca

 

[rmork]

Sorry... I'll try to be more specific

I'm importing from a tekstfile into a new table. I would like to uptdate tableb with fields in tableA...

TableA:
InvoiceID (PK)(Autonumber)
InvoiceNumber
ProjectNumber
InvoiceAmnt
PaidDate

TableB:
ProjectNumber
InvoiceNumber1
InvoiceAmnt1
PaidDate1
InvoiceNumber2
InvoiceAmnt2
PaidDate2
InvoiceNumber3
InvoiceAmnt3
PaidDate3
etc. up to inv. 7

For every invoiceNumber, PaidDate and invoiceAmnt in tableB I would like to add a row in tableA.
Some of the invoicec are already in there, and these i would like to skip...

How to do this with query??

 

[rmork]

Forgot to say that there are other columns in TableA that are suppposed to be empty after the update... They will be filled in by the user..

 

[r937]

I would like to uptdate tableb with fields in tableA...

For every invoiceNumber, PaidDate and invoiceAmnt in tableB I would like to add a row in tableA.

those are contradictory, so i'm going to go with the second one

run the following queries one after the other...

select InvoiceNumber1 as InvoiceNumber
     , InvoiceAmnt1   as InvoiceAmnt
     , PaidDate1      as PaidDate
  into temptable
  from TableB
left outer
  join TableA
    on TableA.InvoiceNumber = TableB.InvoiceNumber1
 where TableA.InvoiceNumber is null

insert
  into temptable
select InvoiceNumber[red]2[/red]
     , InvoiceAmnt[red]2[/red]
     , PaidDate[red]2[/red]
  from TableB
left outer
  join TableA
    on TableA.InvoiceNumber = TableB.InvoiceNumber[red]2[/red]
 where TableA.InvoiceNumber is null

repeat for 3,4,5,6,7

insert
  into TableA
     ( InvoiceNumber
     , InvoiceAmnt
     , PaidDate )
select *
  from temptable

r937.com | rudy.ca

 

[rmork]

Thank you r937!
I'm really excited, can't wait to get home and try it out... I'll let you know. (Sorry for the typo earlier)