Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3919
  • Last Modified:

MS Access - Update field with subquery

Hi,

The following is the case:
Table1: INVOICES
Table2: INVOICEDETAILS
INVOICES contains an autoinc-field "ID" and a integer-field "Invoiceno"
INVOICEDETAILS: only has a reference to INVOICES->"Invoiceno"
On record in INVOICES can have multiple references in table INVOICEDETAILS.
I've added a new field "InvoiceID" to INVOICEDETAILS.

Question:
How can I insert the corresponding "ID" from table INVOICES into the field "InvoiceID" in INVOICEDETAILS?

I've tried to do it with following SQL, but that doesn't work.
UPDATE INVOICEDETAILS T1 SET T1.InvoiceID = (Select T2.ID FROM INVOICES T2 WHERE T2.Invoiceno = T1.Invoiceno);

Can anybody give me a solution for this

Thank you very much,
Stef
0
Stef Merlijn
Asked:
Stef Merlijn
  • 3
  • 2
  • 2
  • +2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
you should not be inserting the auto ID from INVOICES to InvoiceID in INVOICEDETAILS.

if you want to have an InvoiceID in INVOICEDETAILS, just make it an autoNumber

you can establish the relationship the two tables INVOICES  and INVOICEDETAILS
using the Invoiceno.

0
 
Stef MerlijnDeveloperAuthor Commented:
The Invoiceno will be reset each year, so it will not be unique.
Therefor I added the autoinc-field "ID" to INVOICES.
Now I want to recreate the relationship between INVOICES and INVOICEDETAILS.
0
 
harfangCommented:
UPDATE INVOICEDETAILS T1 SET T1.InvoiceID = (Select T2.ID FROM INVOICES T2 WHERE T2.Invoiceno = T1.Invoiceno);

For this query to work, INVOICES.InvoiceNo must have a unique index.

> The Invoiceno will be reset each year, so it will not be unique.

In that case, you cannot do that, as an InvoiceNo does not correspond to an ID. You will have to break down your table by years, set the index on InvoiceNo to be unique and run your query for each table.

In the future, you can drop the field InvoiceNo from your details table, and reset the realtionship to be on ID.

Good luck!
(°v°)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Rey Obrero (Capricorn1)Commented:
<The Invoiceno will be reset each year, so it will not be unique.>

an invoice number must be unique, that is what i know. you can not reuse an invoice number.

so you must be thinking along the line of resetting the base number and include the year as part of the invoice number, in which case your invoice number will still be unique.

0
 
ZuZuPetalsCommented:
UPDATE INVOICEDETAILS INNER JOIN INVOICES ON INVOICEDETAILS.Invoiceno = INVOICES.Invoiceno SET INVOICEDETAILS.InvoiceID = [INVOICES].[ID];
0
 
BadotzCommented:
ZuZuPetals: Please, no shouting.
0
 
ZuZuPetalsCommented:
Yes, SHOUTING is never polite, but for the record I wasn't shouting, just excersising the standard convention of displaying reserved or keyword in caps.

SELECT field1, field2 FROM mytable WHERE.... etc...

It just happens that the author has chosen to use all caps for their TABLE and (some) FIELD names in this case and I simply echoed them in the same case for ease of read by the author.
0
 
BadotzCommented:
Echoed, eh? Then perhaps your response should have been sdrwkcab?
0
 
Stef MerlijnDeveloperAuthor Commented:
Hi ZuZuPetals,
You may shout everytime, whenever you come up with such good answers.
Thank you very much. It's all I needed.
0
 
ZuZuPetalsCommented:
You're WELCOME! (jk)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now