update query

  • Thread starter Thread starter Lori2836 via AccessMonster.com
  • Start date Start date
L

Lori2836 via AccessMonster.com

Please help.

I import a weekly text file and need to bump it up against a Main table.
How would I set a query up to:

Check the Main table....if PO # exists, then update the qty and date fields...
.....if it doesn't exist, add the new PO# and information into the Main table.



Thanks!
 
Two queries? An update query and an append query

UNTESTED AIRCODE follows.

UPDATE Main INNER JOIN Import
ON Main.PO = Import.PO
SET Main.Qty = Import.Qty
Main.SaleDate = Import.SaleDate

INSERT INTO Main (PO, Qty, SaleDate)
SELECT Import.PO, Import.Qty, Import.SaleDate
FROM Import LEFT JOIN Main
ON Import.PO = Main.PO
WHERE Main.PO is Null
 
Thanks John. Are you saying I should put this code into the SQL?

John said:
Two queries? An update query and an append query

UNTESTED AIRCODE follows.

UPDATE Main INNER JOIN Import
ON Main.PO = Import.PO
SET Main.Qty = Import.Qty
Main.SaleDate = Import.SaleDate

INSERT INTO Main (PO, Qty, SaleDate)
SELECT Import.PO, Import.Qty, Import.SaleDate
FROM Import LEFT JOIN Main
ON Import.PO = Main.PO
WHERE Main.PO is Null
Please help.
Click to expand...
[quoted text clipped - 7 lines]
Thanks!
Click to expand...
Click to expand...
 
Those are SQL queries. If you look at the SQL view of a query you will see
text that looks like this. The "grid" view is just an graphic interface
that will write these text statements.

FIRST BACKUP your data.
SECOND, backup your data.!!!

If you are building these queries using the grid.
Update query
--Add Both tables to the query
-- join on PO number
-- put the main table quantity and date fields into the grid
-- Select Query: Update from the menu
-- In the Update To Line: Type [ImportTableName].[QuantityField] and
[ImportTableName].[DateFieldName]

Insert Query
--Add Both tables to the query
-- join on PO number
-- double-click on the join line and select All from IMPORT table and
matching from the MAIN Table
-- put the IMPORT table fields into the grid
-- put the PO number field from Main into the grid and set its criteria to
IS NULL
-- Select Query: Append from the menu and in the dialog box select the MAIN
Table
-- Select the correct fields in the APPEND TO line


Lori2836 via AccessMonster.com said:
Thanks John. Are you saying I should put this code into the SQL?

John said:
Two queries? An update query and an append query

UNTESTED AIRCODE follows.

UPDATE Main INNER JOIN Import
ON Main.PO = Import.PO
SET Main.Qty = Import.Qty
Main.SaleDate = Import.SaleDate

INSERT INTO Main (PO, Qty, SaleDate)
SELECT Import.PO, Import.Qty, Import.SaleDate
FROM Import LEFT JOIN Main
ON Import.PO = Main.PO
WHERE Main.PO is Null
Please help.
Click to expand...
[quoted text clipped - 7 lines]
Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top