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.

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
..

"PatK" <(E-Mail Removed)> wrote in message
news:40512B4D-B1FE-472C-ABE1-(E-Mail Removed)...
>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.

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" wrote:

> 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
> ..
>
> "PatK" <(E-Mail Removed)> wrote in message
> news:40512B4D-B1FE-472C-ABE1-(E-Mail Removed)...
> >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.
>
>
>

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
..

"PatK" <(E-Mail Removed)> wrote in message
news:FDFF88F4-A958-4C8E-B06B-(E-Mail Removed)...
>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" wrote:
>
>> 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
>> ..
>>
>> "PatK" <(E-Mail Removed)> wrote in message
>> news:40512B4D-B1FE-472C-ABE1-(E-Mail Removed)...
>> >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.
>>
>>
>>

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
"John Spencer" wrote:

> 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
> ..
>
> "PatK" <(E-Mail Removed)> wrote in message
> news:FDFF88F4-A958-4C8E-B06B-(E-Mail Removed)...
> >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" wrote:
> >
> >> 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
> >> ..
> >>
> >> "PatK" <(E-Mail Removed)> wrote in message
> >> news:40512B4D-B1FE-472C-ABE1-(E-Mail Removed)...
> >> >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.
> >>
> >>
> >>
>
>
>

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!



"PatK" wrote:

> 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
> "John Spencer" wrote:
>
> > 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
> > ..
> >
> > "PatK" <(E-Mail Removed)> wrote in message
> > news:FDFF88F4-A958-4C8E-B06B-(E-Mail Removed)...
> > >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" wrote:
> > >
> > >> 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
> > >> ..
> > >>
> > >> "PatK" <(E-Mail Removed)> wrote in message
> > >> news:40512B4D-B1FE-472C-ABE1-(E-Mail Removed)...
> > >> >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.
> > >>
> > >>
> > >>
> >
> >
> >

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


"PatK" wrote:

> 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
> "John Spencer" wrote:
>
> > 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
> > ..
> >
> > "PatK" <(E-Mail Removed)> wrote in message
> > news:FDFF88F4-A958-4C8E-B06B-(E-Mail Removed)...
> > >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" wrote:
> > >
> > >> 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
> > >> ..
> > >>
> > >> "PatK" <(E-Mail Removed)> wrote in message
> > >> news:40512B4D-B1FE-472C-ABE1-(E-Mail Removed)...
> > >> >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" wrote:

> 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
> ..
>
> "PatK" <(E-Mail Removed)> wrote in message
> news:FDFF88F4-A958-4C8E-B06B-(E-Mail Removed)...
> >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" wrote:
> >
> >> 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
> >> ..
> >>
> >> "PatK" <(E-Mail Removed)> wrote in message
> >> news:40512B4D-B1FE-472C-ABE1-(E-Mail Removed)...
> >> >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.
> >>
> >>
> >>
>
>
>

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" wrote:

> 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
> ..
>
> "PatK" <(E-Mail Removed)> wrote in message
> news:FDFF88F4-A958-4C8E-B06B-(E-Mail Removed)...
> >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" wrote:
> >
> >> 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
> >> ..
> >>
> >> "PatK" <(E-Mail Removed)> wrote in message
> >> news:40512B4D-B1FE-472C-ABE1-(E-Mail Removed)...
> >> >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.
> >>
> >>
> >>
>
>
>

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
'====================================================


PatK wrote:
> 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" wrote:
>
>> 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
>> ..
>>
>> "PatK" <(E-Mail Removed)> wrote in message
>> news:FDFF88F4-A958-4C8E-B06B-(E-Mail Removed)...
>>> 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" wrote:
>>>
>>>> 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
>>>> ..
>>>>
>>>> "PatK" <(E-Mail Removed)> wrote in message
>>>> news:40512B4D-B1FE-472C-ABE1-(E-Mail Removed)...
>>>>> 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.
>>>>
>>>>
>>
>>