Appending new master rows to an existing master table

[Guest]

I have a table, AssetMaster that contains these fields in each row):

AssetID (primary key) AssetLocation AssetName AssetValue

I have a spreadsheet, that I import into a temporary table (AssetUpdate)
that contains:
- New Asset rows
- Existing asset rows with possibly some fields changed
- and it does NOT contain assets that should be deleted (ie, the importing
spreadsheet is a complete superset that should replace the existing
AssetMaster, so if an asset is NOT in the AssetUpdate tbl, it needs to be
deleted from the master). AssetUpdate contains these fields in each row:

AssetID (PK) AssetLocation AssetName AssetValue AssetDesc AssetOwner

(ie, two more fields than what I carry in the asset master table that I do
not need in Asset master)

I need to "refresh" the asset master table to:
- Include any asset rows not already in the file (append)
- Update any "changed" fields
- Delete rows from the AssetMaster NOT including in AssetUpdate

I was thinking I could do this:
- DROP all rows from AssetMaster
- Insert all rows from AssetUpdate (dropping fields I do not need)

however, this is a little kludgy. Is it possible to do this update in one
query? Note that eventually, I will make this a VBA routine, but for now,
just trying to get it to work using Access/Action queries (or some combo).
Any help/suggestions would be appreciated.

 

[John Spencer]

I don't understand why you think it is kludgy. That seems like a fairly
efficient method.

You could delete the AssetMaster table and rename the AssetUpdate table to
AssetMaster and just retain the extra fields (they probably don't take up
that much room). Of course if your AssetMaster table has indexes that you
want to use then you would need to set indexes in the table. All in all, I
would probably go with the solution you proposed.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

I may have to just take this approach. The problem with simply flipping the
tables comes down to volumes. I kept this example simple, but in reality the
Update table has dozens of columns I do not need, and ~ 80000 rows (so the
additional columns really "swell" the size of the table, so I have removed
them.

I had created a nice "update" and then "append" queries that worked
fine/fast. But then the problem becomes the deletions. In other words, if I
have 80000 rows in the update file, but the next day's file has only 79999
rows, I do not know how to get rid of just the 1 row (ideas of how to do that
in a query). I am trying to avoid not re-coding this whole thing, and
address just the deletion issue, if at all possible.

Thanks for responding!!

pat

 

[John Spencer]

Try the unmatched query wizard to identify the records that need to be
deleted. It should create a query that you can use as the basis for
deleting records. Your query should probably end up looking something like
the following. You will need to substitute the proper table names and
primary key field names.


DELETE DISTINCTROW MasterTable.PK
FROM MasterTable
WHERE MasterTable.PK in
(SELECT M.PK
FROM MasterTable as M LEFT JOIN UpdateTable as U
ON M.PK =U.PK
WHERE U.PK is Null)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Well...I gave it a shot (the SQL portion of this, anyway) and created this:

DELETE DISTINCTROW tblAsset.[EPR ID]
FROM tblAsset
WHERE tblAsset.[EPR ID] in
(SELECT tblAsset.[EPR ID]
FROM tblAsset as A LEFT JOIN tblAssetUpdate as U
ON A.[EPR ID] =U.[EPR ID]
WHERE U.[EPR ID] is Null)

Note: EPR ID is the primary key on both tables. However, it deleted every
record in my master table. I had it backed up, tho .

I will try the unmatch query approach, and see how that works. Wish me luck!

Pat

 

[Guest]

Ok...think I got it. This query worked:

DELETE DISTINCTROW tblAsset.[EPR ID]
FROM tblAsset
WHERE tblAsset.[EPR ID] in
(SELECT tblAsset.[EPR ID]
FROM tblAsset LEFT JOIN tblAssetUpdate ON tblAsset.[EPR ID] =
tblAssetUpdate.[EPR ID]
WHERE (tblAssetUpdate.[EPR ID]) Is Null);

Thanks for getting me pointed in the direction of the unmatched query...that
helped a ton! I ended up leveraging part of your query, as well as part of
the result of the unmatch query the wizard generated.

Thanks, John!

Well...I gave it a shot (the SQL portion of this, anyway) and created this:

DELETE DISTINCTROW tblAsset.[EPR ID]
FROM tblAsset
WHERE tblAsset.[EPR ID] in
(SELECT tblAsset.[EPR ID]
FROM tblAsset as A LEFT JOIN tblAssetUpdate as U
ON A.[EPR ID] =U.[EPR ID]
WHERE U.[EPR ID] is Null)

Note: EPR ID is the primary key on both tables. However, it deleted every
record in my master table. I had it backed up, tho .

I will try the unmatch query approach, and see how that works. Wish me luck!

Pat

Try the unmatched query wizard to identify the records that need to be
deleted. It should create a query that you can use as the basis for
deleting records. Your query should probably end up looking something like
the following. You will need to substitute the proper table names and
primary key field names.


DELETE DISTINCTROW MasterTable.PK
FROM MasterTable
WHERE MasterTable.PK in
(SELECT M.PK
FROM MasterTable as M LEFT JOIN UpdateTable as U
ON M.PK =U.PK
WHERE U.PK is Null)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Ok...this worked...it was a combo of what you sent me, plus the result of the
unmatched query wizard. Thanks for getting me pointed in the right direction.
I may have posted this twice...the web site is acting a bit slow). Here is
the resulting query that worked:

DELETE DISTINCTROW tblAsset.[EPR ID]
FROM tblAsset
WHERE tblAsset.[EPR ID] in
(SELECT tblAsset.[EPR ID]
FROM tblAsset LEFT JOIN tblAssetUpdate ON tblAsset.[EPR ID] =
tblAssetUpdate.[EPR ID]
WHERE (tblAssetUpdate.[EPR ID]) Is Null);

Thanks John

Pat

Well...I gave it a shot (the SQL portion of this, anyway) and created this:

DELETE DISTINCTROW tblAsset.[EPR ID]
FROM tblAsset
WHERE tblAsset.[EPR ID] in
(SELECT tblAsset.[EPR ID]
FROM tblAsset as A LEFT JOIN tblAssetUpdate as U
ON A.[EPR ID] =U.[EPR ID]
WHERE U.[EPR ID] is Null)

Note: EPR ID is the primary key on both tables. However, it deleted every
record in my master table. I had it backed up, tho .

I will try the unmatch query approach, and see how that works. Wish me luck!

Pat

Try the unmatched query wizard to identify the records that need to be
deleted. It should create a query that you can use as the basis for
deleting records. Your query should probably end up looking something like
the following. You will need to substitute the proper table names and
primary key field names.


DELETE DISTINCTROW MasterTable.PK
FROM MasterTable
WHERE MasterTable.PK in
(SELECT M.PK
FROM MasterTable as M LEFT JOIN UpdateTable as U
ON M.PK =U.PK
WHERE U.PK is Null)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Try the unmatched query wizard to identify the records that need to be
deleted. It should create a query that you can use as the basis for
deleting records. Your query should probably end up looking something like
the following. You will need to substitute the proper table names and
primary key field names.


DELETE DISTINCTROW MasterTable.PK
FROM MasterTable
WHERE MasterTable.PK in
(SELECT M.PK
FROM MasterTable as M LEFT JOIN UpdateTable as U
ON M.PK =U.PK
WHERE U.PK is Null)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Perhaps you may be able to help me solve a related question? I think it
would be good for tracking/rollback purposes, to move the records I am going
to delete to a transaction "history" file of some sort, and I can figure that
part out. However, I was wondering if there is a way to "count" the numbers
of rows I was going to delete, before doing so, and maybe popping up a
message saying "You are about to delete X rows." In this case, I want to be
able to control the messagebox content, rather than using standard Access
messages. I could pop this out either before deletion, or, perhaps, after
the deletion (still trying to decide if I want the user to be able to stop
it, or not). At the very least, a transaction log saying "X records deleted
on mm/dd/yyyy" would be nice. Ideas? Is that easy (ie, is there a result
code availble for querying after doCmd? Here is the delete query in my VBA:

' --------------------------------------------------------------------------
' Delete an asset master tbl rows no longer in master spreadsheet
' --------------------------------------------------------------------------

SQLStmt = "DELETE DISTINCTROW tblAsset.[EPR ID] " & _
"FROM tblAsset " & _
"WHERE tblAsset.[EPR ID] in " & _
"(SELECT tblAsset.[EPR ID] " & _
"FROM tblAsset LEFT JOIN tblAssetUpdate " & _
"ON tblAsset.[EPR ID] = tblAssetUpdate.[EPR ID]" & _
"WHERE (tblAssetUpdate.[EPR ID]) Is Null);"
DoCmd.RunSQL SQLStmt

Ideas?

 

[John Spencer]

If you just want a count of the records deleted, you could do

Dim DbAny as DAO.Database
Dim SQLStmt as String

SQLStmt = "DELETE DISTINCTROW tblAsset.[EPR ID] " & _
"FROM tblAsset " & _
"WHERE tblAsset.[EPR ID] in " & _
"(SELECT tblAsset.[EPR ID] " & _
"FROM tblAsset LEFT JOIN tblAssetUpdate " & _
"ON tblAsset.[EPR ID] = tblAssetUpdate.[EPR ID]" & _
"WHERE (tblAssetUpdate.[EPR ID]) Is Null);"
Set DbAny = CurrentDB()
Dbany.Execute SQLStmt, dbFailonerror
MsgBox "Deleted " & DbAny.RecordsAffected & " records"

If you want to be able to reverse this then you will need to do this all
inside a transaction. I'm sorry, but I don't have any code at hand
(right now) to show you the process. Try looking up Transaction in the
help or Google these groups for some help, or post a request asking for
sample code.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Perhaps you may be able to help me solve a related question? I think it
would be good for tracking/rollback purposes, to move the records I am going
to delete to a transaction "history" file of some sort, and I can figure that
part out. However, I was wondering if there is a way to "count" the numbers
of rows I was going to delete, before doing so, and maybe popping up a
message saying "You are about to delete X rows." In this case, I want to be
able to control the messagebox content, rather than using standard Access
messages. I could pop this out either before deletion, or, perhaps, after
the deletion (still trying to decide if I want the user to be able to stop
it, or not). At the very least, a transaction log saying "X records deleted
on mm/dd/yyyy" would be nice. Ideas? Is that easy (ie, is there a result
code availble for querying after doCmd? Here is the delete query in my VBA:

' --------------------------------------------------------------------------
' Delete an asset master tbl rows no longer in master spreadsheet
' --------------------------------------------------------------------------

SQLStmt = "DELETE DISTINCTROW tblAsset.[EPR ID] " & _
"FROM tblAsset " & _
"WHERE tblAsset.[EPR ID] in " & _
"(SELECT tblAsset.[EPR ID] " & _
"FROM tblAsset LEFT JOIN tblAssetUpdate " & _
"ON tblAsset.[EPR ID] = tblAssetUpdate.[EPR ID]" & _
"WHERE (tblAssetUpdate.[EPR ID]) Is Null);"
DoCmd.RunSQL SQLStmt

Ideas?

Try the unmatched query wizard to identify the records that need to be
deleted. It should create a query that you can use as the basis for
deleting records. Your query should probably end up looking something like
the following. You will need to substitute the proper table names and
primary key field names.


DELETE DISTINCTROW MasterTable.PK
FROM MasterTable
WHERE MasterTable.PK in
(SELECT M.PK
FROM MasterTable as M LEFT JOIN UpdateTable as U
ON M.PK =U.PK
WHERE U.PK is Null)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Thanks, John...works great. I have opted to give a forwarning, and then
execute it after letting them decide whether to continue. I have also
"pre-moved" the to-be-deleted records to a log file. For now, this works,
and since I am taking the rows field for field, it is easy enough to copy
them back from my "log" file. However, this Transaction thing is something I
have seen in the books I bought, and sounds like a better approach, in
general. I will definitely do some research if not for this, then my next
endeavor. Thanks a millions for your help and guidance!

Patk

If you just want a count of the records deleted, you could do

Dim DbAny as DAO.Database
Dim SQLStmt as String

SQLStmt = "DELETE DISTINCTROW tblAsset.[EPR ID] " & _
"FROM tblAsset " & _
"WHERE tblAsset.[EPR ID] in " & _
"(SELECT tblAsset.[EPR ID] " & _
"FROM tblAsset LEFT JOIN tblAssetUpdate " & _
"ON tblAsset.[EPR ID] = tblAssetUpdate.[EPR ID]" & _
"WHERE (tblAssetUpdate.[EPR ID]) Is Null);"
Set DbAny = CurrentDB()
Dbany.Execute SQLStmt, dbFailonerror
MsgBox "Deleted " & DbAny.RecordsAffected & " records"

If you want to be able to reverse this then you will need to do this all
inside a transaction. I'm sorry, but I don't have any code at hand
(right now) to show you the process. Try looking up Transaction in the
help or Google these groups for some help, or post a request asking for
sample code.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Perhaps you may be able to help me solve a related question? I think it
would be good for tracking/rollback purposes, to move the records I am going
to delete to a transaction "history" file of some sort, and I can figure that
part out. However, I was wondering if there is a way to "count" the numbers
of rows I was going to delete, before doing so, and maybe popping up a
message saying "You are about to delete X rows." In this case, I want to be
able to control the messagebox content, rather than using standard Access
messages. I could pop this out either before deletion, or, perhaps, after
the deletion (still trying to decide if I want the user to be able to stop
it, or not). At the very least, a transaction log saying "X records deleted
on mm/dd/yyyy" would be nice. Ideas? Is that easy (ie, is there a result
code availble for querying after doCmd? Here is the delete query in my VBA:

' --------------------------------------------------------------------------
' Delete an asset master tbl rows no longer in master spreadsheet
' --------------------------------------------------------------------------

SQLStmt = "DELETE DISTINCTROW tblAsset.[EPR ID] " & _
"FROM tblAsset " & _
"WHERE tblAsset.[EPR ID] in " & _
"(SELECT tblAsset.[EPR ID] " & _
"FROM tblAsset LEFT JOIN tblAssetUpdate " & _
"ON tblAsset.[EPR ID] = tblAssetUpdate.[EPR ID]" & _
"WHERE (tblAssetUpdate.[EPR ID]) Is Null);"
DoCmd.RunSQL SQLStmt

Ideas?

Try the unmatched query wizard to identify the records that need to be
deleted. It should create a query that you can use as the basis for
deleting records. Your query should probably end up looking something like
the following. You will need to substitute the proper table names and
primary key field names.


DELETE DISTINCTROW MasterTable.PK
FROM MasterTable
WHERE MasterTable.PK in
(SELECT M.PK
FROM MasterTable as M LEFT JOIN UpdateTable as U
ON M.PK =U.PK
WHERE U.PK is Null)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I may have to just take this approach. The problem with simply flipping
the
tables comes down to volumes. I kept this example simple, but in reality
the
Update table has dozens of columns I do not need, and ~ 80000 rows (so the
additional columns really "swell" the size of the table, so I have removed
them.

I had created a nice "update" and then "append" queries that worked
fine/fast. But then the problem becomes the deletions. In other words,
if I
have 80000 rows in the update file, but the next day's file has only 79999
rows, I do not know how to get rid of just the 1 row (ideas of how to do
that
in a query). I am trying to avoid not re-coding this whole thing, and
address just the deletion issue, if at all possible.

Thanks for responding!!

pat

:

I don't understand why you think it is kludgy. That seems like a fairly
efficient method.

You could delete the AssetMaster table and rename the AssetUpdate table
to
AssetMaster and just retain the extra fields (they probably don't take up
that much room). Of course if your AssetMaster table has indexes that
you
want to use then you would need to set indexes in the table. All in all,
I
would probably go with the solution you proposed.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have a table, AssetMaster that contains these fields in each row):

AssetID (primary key) AssetLocation AssetName AssetValue

I have a spreadsheet, that I import into a temporary table
(AssetUpdate)
that contains:
- New Asset rows
- Existing asset rows with possibly some fields changed
- and it does NOT contain assets that should be deleted (ie, the
importing
spreadsheet is a complete superset that should replace the existing
AssetMaster, so if an asset is NOT in the AssetUpdate tbl, it needs to
be
deleted from the master). AssetUpdate contains these fields in each
row:

AssetID (PK) AssetLocation AssetName AssetValue AssetDesc AssetOwner

(ie, two more fields than what I carry in the asset master table that I
do
not need in Asset master)

I need to "refresh" the asset master table to:
- Include any asset rows not already in the file (append)
- Update any "changed" fields
- Delete rows from the AssetMaster NOT including in AssetUpdate

I was thinking I could do this:
- DROP all rows from AssetMaster
- Insert all rows from AssetUpdate (dropping fields I do not need)

however, this is a little kludgy. Is it possible to do this update in
one
query? Note that eventually, I will make this a VBA routine, but for
now,
just trying to get it to work using Access/Action queries (or some
combo).
Any help/suggestions would be appreciated.