Access Queries across linked tables

February 6, 2008 8:53 AM

I need to query data from another table that I'm already writing to.

I've got two tables (tracking and SO_InvoiceHeader). Tracking holds six fields (id, trackingno, salesorder, cost, packageno, and invoiceno). SO_InvoiceHeader hold a ton of fields, bu I'm only interested in two for this case (Invoice_No, and SalesOrderNo).

SO_InvoiceHeader.InvoiceNo is the primary key. I want to fill Tracking.InvoiceNo with that data, by looking it up with whatever data is entered in Tracking.salesorder.

I'm on Access 2003, and the SO_InvoiceHeader is a linked table. I'm not that familar with writing queries, so +1000 internets to the person who can help me out with this.

posted by chrisfromthelc to computers & internet (6 comments total)

UPDATE tracking t INNER JOIN SO_InvoiceHeader i ON i.SalesOrderNo = t.salesorder SET t.invoiceno = i.Invoice_No

Make sure t.salesorder is indexed, or you'll slow your query down.
posted by mkb at 8:57 AM on February 6, 2008

That's the thing. There's a possiblilty that tracking.salesorder may not be unique (multiple shipments for an order).

So will that still work?
posted by chrisfromthelc at 9:00 AM on February 6, 2008

Access also spits that query out as being a circular reference.
posted by chrisfromthelc at 9:05 AM on February 6, 2008

UPDATE invoice INNER JOIN tracking ON invoice.salesorder = tracking.salesorder SET tracking.invnum = [invoice].[invnum];

I've just tried this based on what I understand and mkb's query works fine for me (ie Access runs it fine) - see screenshot of the query builder. Not only does it run, it seems to do what i think you want to... Every record with the salesorder number in tracking is updated to the invoice number from invoices with the same salesorder num.
posted by jontyjago at 9:32 AM on February 6, 2008

I don't do Access, but if that doesn't work for you perhaps something like

UPDATE tracking t SET invoiceno = (SELECT invoice_no FROM so_invoiceheader h WHERE h.salesorderno = t.salesorder)

(perhaps with a "WHERE invoiceno IS NULL" or some other restriction in the outer UPDATE statement so you're not needlessly updating every record every time)... this assumes that you don't have multiple invoices off a single sales order - if you do, then you can't look up a unique invoice # from the sales order # in the tracking record...
posted by russm at 10:36 PM on February 6, 2008

There's a possiblilty that tracking.salesorder may not be unique (multiple shipments for an order).

So you mean that there will multiple entries in tracking with the same value in salesorder? Yes, that will work. The same row from SO_InvoiceHeader will be appended to each matching row in tracking.
posted by mkb at 6:12 AM on February 7, 2008