UPDATE/APPEND QUERIES IN ACCESS 2K3

S

Santa-D

I'll try to simplify this so it's easy to follow and understand.

I have a table [MAIN] that contains a large amount of data related to
various assets.
I receive an excel sheet from the supplier which I use to manage these
assets.
Currently I have a macro with VBA and a delete records query to copy
and paste the [MAIN] table into another MDB and append the date it was
copied. Delete all the records and import the spreadsheet into the
existing [MAIN] table.

What I want to do is the following:

* UPDATE records currently in [MAIN] with records in [MAIN_TEMP] which
is the import from excel
* APPEND records not found in [MAIN] but found in [MAIN_TEMP] and
* UPDATE [MAIN].[STATUS] to "9" where [MAIN].[ORDNO] <>
[MAIN_TEMP].[ORDNO]

[ORDNO] is the primary key

This is the VBA function to copy & paste the table into another MDB
file (backup)
------------------------------------------------------------------------------------------------------------------------------------------
Function veh_exporttable_Main()
DoCmd.SetWarnings WarningsOff

Dim desttable_main As String

desttable_main = "x - " & Format(Now(), "dd-mm-yy") & " - Old-Main"

DoCmd.CopyObject "V:\Data Sources\DoJ Fleet Management - Archives.mdb",
desttable_main, acTable, "MAIN"

DoCmd.SetWarnings WarningsOn
End Function
------------------------------------------------------------------------------------------------------------------------------------------

This is what I've got in the macro to import the data.

Set Warnings - No
RunCode - veh_exporttable_Main ()
OpenQuery - delete-all-records | View:Datasheet | Data Mode:Read Only
TransferSpreadsheet - Import | Type:Excel 8-10 | TableName:MAIN |
FileName:<path to file> | HasFieldNames:Yes | Range:
Set Warnings - Yes

------------------------------------------------------------------------------------------------------------------------------------------

I've created an update & an append query but I'm not sure if it'll work
as when I try to run the "update status 9" query and press run I get 0
records even tho I've added some dummy data so I know it won't join.

UPDATE MAIN INNER JOIN MAIN_TEMP ON MAIN.ORDNO = MAIN_TEMP.ORDNO SET
MAIN.[Reorder Status] = "9", MAIN.[Date Status Changed] = Date()
WHERE (((MAIN.ORDNO)<>[MAIN_TEMP].[ORDNO]));
 
V

Van T. Dinh

In the update Query, the combination of the Inner Join and the Where clause
basically means that no records will meed the combined criteria and be
selected for update!

If you "translate" to a criteria, you SQL string is equivalent to:

UPDATE MAIN, MAIN_TEMP
SET MAIN.[Reorder Status] = "9", MAIN.[Date Status Changed] = Date()
WHERE (MAIN.ORDNO = MAIN_TEMP.ORDNO)
AND (MAIN.ORDNO<>MAIN_TEMP.ORDNO);

and there is no way that a particular MAIN.ORDERNO can be both equal and
different from any ORDERNO in MAIN_TEMP.

I am not sure what you are trying to do but my guess is that you need a
(Left) Outer Join. Check Access Help on outer joins and see if it is
suitable for you.

--
HTH
Van T. Dinh
MVP (Access)



Santa-D said:
I'll try to simplify this so it's easy to follow and understand.

I have a table [MAIN] that contains a large amount of data related to
various assets.
I receive an excel sheet from the supplier which I use to manage these
assets.
Currently I have a macro with VBA and a delete records query to copy
and paste the [MAIN] table into another MDB and append the date it was
copied. Delete all the records and import the spreadsheet into the
existing [MAIN] table.

What I want to do is the following:

* UPDATE records currently in [MAIN] with records in [MAIN_TEMP] which
is the import from excel
* APPEND records not found in [MAIN] but found in [MAIN_TEMP] and
* UPDATE [MAIN].[STATUS] to "9" where [MAIN].[ORDNO] <>
[MAIN_TEMP].[ORDNO]

[ORDNO] is the primary key

This is the VBA function to copy & paste the table into another MDB
file (backup)
------------------------------------------------------------------------------------------------------------------------------------------
Function veh_exporttable_Main()
DoCmd.SetWarnings WarningsOff

Dim desttable_main As String

desttable_main = "x - " & Format(Now(), "dd-mm-yy") & " - Old-Main"

DoCmd.CopyObject "V:\Data Sources\DoJ Fleet Management - Archives.mdb",
desttable_main, acTable, "MAIN"

DoCmd.SetWarnings WarningsOn
End Function
------------------------------------------------------------------------------------------------------------------------------------------

This is what I've got in the macro to import the data.

Set Warnings - No
RunCode - veh_exporttable_Main ()
OpenQuery - delete-all-records | View:Datasheet | Data Mode:Read Only
TransferSpreadsheet - Import | Type:Excel 8-10 | TableName:MAIN |
FileName:<path to file> | HasFieldNames:Yes | Range:
Set Warnings - Yes

------------------------------------------------------------------------------------------------------------------------------------------

I've created an update & an append query but I'm not sure if it'll work
as when I try to run the "update status 9" query and press run I get 0
records even tho I've added some dummy data so I know it won't join.

UPDATE MAIN INNER JOIN MAIN_TEMP ON MAIN.ORDNO = MAIN_TEMP.ORDNO SET
MAIN.[Reorder Status] = "9", MAIN.[Date Status Changed] = Date()
WHERE (((MAIN.ORDNO)<>[MAIN_TEMP].[ORDNO]));
Click to expand...
 
S

Santa-D

Hi Van,

Thanks for your response. As you've advised I've had a look at the
Left Outer Join and from what it's stated in the help file, I've
manipulate the code and the results are:

UPDATE MAIN LEFT JOIN MAIN_TEMP ON MAIN.ORDNO = MAIN_TEMP.ORDNO SET
MAIN.[Reorder Status] = "9", MAIN.[Date Status Changed] = Date()
WHERE (((MAIN_TEMP.ORDNO) Is Null));

The end result is it worked!

In the update Query, the combination of the Inner Join and the Where clause
basically means that no records will meed the combined criteria and be
selected for update!

If you "translate" to a criteria, you SQL string is equivalent to:

UPDATE MAIN, MAIN_TEMP
SET MAIN.[Reorder Status] = "9", MAIN.[Date Status Changed] = Date()
WHERE (MAIN.ORDNO = MAIN_TEMP.ORDNO)
AND (MAIN.ORDNO<>MAIN_TEMP.ORDNO);

and there is no way that a particular MAIN.ORDERNO can be both equal and
different from any ORDERNO in MAIN_TEMP.

I am not sure what you are trying to do but my guess is that you need a
(Left) Outer Join. Check Access Help on outer joins and see if it is
suitable for you.

--
HTH
Van T. Dinh
MVP (Access)



Santa-D said:
I'll try to simplify this so it's easy to follow and understand.

I have a table [MAIN] that contains a large amount of data related to
various assets.
I receive an excel sheet from the supplier which I use to manage these
assets.
Currently I have a macro with VBA and a delete records query to copy
and paste the [MAIN] table into another MDB and append the date it was
copied. Delete all the records and import the spreadsheet into the
existing [MAIN] table.

What I want to do is the following:

* UPDATE records currently in [MAIN] with records in [MAIN_TEMP] which
is the import from excel
* APPEND records not found in [MAIN] but found in [MAIN_TEMP] and
* UPDATE [MAIN].[STATUS] to "9" where [MAIN].[ORDNO] <>
[MAIN_TEMP].[ORDNO]

[ORDNO] is the primary key

This is the VBA function to copy & paste the table into another MDB
file (backup)
------------------------------------------------------------------------------------------------------------------------------------------
Function veh_exporttable_Main()
DoCmd.SetWarnings WarningsOff

Dim desttable_main As String

desttable_main = "x - " & Format(Now(), "dd-mm-yy") & " - Old-Main"

DoCmd.CopyObject "V:\Data Sources\DoJ Fleet Management - Archives.mdb",
desttable_main, acTable, "MAIN"

DoCmd.SetWarnings WarningsOn
End Function
------------------------------------------------------------------------------------------------------------------------------------------

This is what I've got in the macro to import the data.

Set Warnings - No
RunCode - veh_exporttable_Main ()
OpenQuery - delete-all-records | View:Datasheet | Data Mode:Read Only
TransferSpreadsheet - Import | Type:Excel 8-10 | TableName:MAIN |
FileName:<path to file> | HasFieldNames:Yes | Range:
Set Warnings - Yes

------------------------------------------------------------------------------------------------------------------------------------------

I've created an update & an append query but I'm not sure if it'll work
as when I try to run the "update status 9" query and press run I get 0
records even tho I've added some dummy data so I know it won't join.

UPDATE MAIN INNER JOIN MAIN_TEMP ON MAIN.ORDNO = MAIN_TEMP.ORDNO SET
MAIN.[Reorder Status] = "9", MAIN.[Date Status Changed] = Date()
WHERE (((MAIN.ORDNO)<>[MAIN_TEMP].[ORDNO]));
Click to expand...
Click to expand...
 
S

Santa-D

I noticed that my APPEND query didn't work, it wanted to add 700 rows.
Based on the information you gave me before, I quickly realised that it
was the order of the SQL statement was vital.

This gave me 6 results.

FROM MAIN_TEMP LEFT JOIN MAIN ON MAIN.ORDNO = MAIN_TEMP.ORDNO
WHERE (((MAIN.ORDNO) Is Null));

This gave me 0 results

FROM MAIN LEFT JOIN MAIN_TEMP ON MAIN.ORDNO = MAIN_TEMP.ORDNO
WHERE (((MAIN.ORDNO) Is Null));

Thanks for your help, I've spent so many hours trying to figure this
out.
Thanks again!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using select query results as filter for append query 14
Main Switchboard can't append all the records in the append query 0
Update and append records in one table based on shared primary key 4
Disable Access Update Records warning 2
Update/Append Queries minus confirmation Dialouge 3
Delete, Append, Update Counts 2
Update External Database 0
best way to update table 2

Top