I have a database with a field named PULLED? that is either a "Y" or blank.

How do I write code to go through and delete all the people with a "Y" in
the PULLED? field, and put them into a new database in the same directory
called PEOPLE PULLED.db?

I started out, but got a little nervous because the query was writing
automatically to the :PRIV: directory... I was unsure just what checking I
should be using (for existing tables, etc.).

-b

There is a setting in paradox that deals with auxilliary tables.
And basically how I think of that is,
there can be some intermediate tables that are created by pdox,
or you can turn that setting off,
so that no tables are created,
and those queries run faster.

Examples : Inserted, Changed, Deleted

If you actually do a delete query,
and you dont have an aux table created,
then the records will just disappear.

Backing up data as you hone your method is always a good idea.
Or copying elsewhere until you perfect it.

Two query based solutions that spring to mind are :

Solution #1
Two queries and add table to history
Query#1 : Checkplus query with all records and add answer to a delete
history table
Query#2 : delete query and not care if there is an aux table

Solution#2
One delete query and add table to history
Query#1 :delete query that creates an aux table "deleted.db"
and then add "deleted.db" to a delete history table

Good luck
Robert Wiltshire









"Willard J. Hoppe" <billh (AT) nospam (DOT) brraspam.com> wrote

Bill,

I suggest:
1. Make a backup of your main table!!!
2. Create an empty Table named PeoplePulled, borrowing the Structure and
Family from the main table
3. Run a query on the main table, extracting all the fields in records
where PULLED = Y
4. Copy this ANSWER table into the Empty table.
5. Run a query on the main table, deleting all records where PULLED = Y

Mike


"Willard J. Hoppe" <billh (AT) nospam (DOT) brraspam.com> wrote

By default, when records are deleted in a query, does it always go to
:PRIV:deleted.db?

If it does, I can code to just copy that file to what I want, and I think
that will take care of the issue.

-b



"Willard J. Hoppe" <billh (AT) nospam (DOT) brraspam.com> wrote

"Willard J. Hoppe" wrote:

by default, yes..


do what Michael said.. query out the records, rename the table or add the
records, THEN delete the records.. that way, you can error trap along the way,
save your butt if the process fails in the middle, etc, etc., etc..

dee-fense.. dee-fense..


--
Steven Green - Waldorf Maryland USA

Diamond Software Group
http://www.diamondsg.com/main.htm
Paradox Support & Sales - Corel CTech Paradox
---------------------------------------------------
Diamond Sports Gems
http://www.diamondsg.com/gemsmain.htm
Trading Cards and other Sports Memorabilia
---------------------------------------------------

On Fri, 13 Jan 2006 16:56:49 -0500, "Willard J. Hoppe"
<billh (AT) nospam (DOT) brraspam.com> wrote:

The Term is ADD (after the first time if you don't create the Peopled
Pulled.db before hand)
as in Add :Priveleted.db to "People Pulled.db"

BTW I dislike tablenames that have spaces in their names or exceed 8.3
naming. It is just asking for problems down the road. P_pulled.db?

Steve U

Why not to use "subtract" table in that case?
Is there some "restrictions" to subtract table, because I have never
seen a post in which someone recommand it.
Régis

Steven Green a écrit :

Régis Duc Dodon wrote:

"subtract" is a different process.. subtract this from that, and see what's left in
that..


--
Steven Green - Waldorf Maryland USA

Diamond Software Group
http://www.diamondsg.com/main.htm
Paradox Support & Sales - Corel CTech Paradox
---------------------------------------------------
Diamond Sports Gems
http://www.diamondsg.com/gemsmain.htm
Trading Cards and other Sports Memorabilia
---------------------------------------------------

Typo... step 4 should say ADD the answer table... sorry...

If you intend running this procedure frequently, whereby any new PULLED=Y
records must transfer to the PeoplePulled table, then you may need to detect
potential "duplicate keys" in the PeoplePulled table (in advance), possible
structural changes to one of the tables which were not replicated in the
other one, etc...

- Mike

Willard J. Hoppe wrote:
Willard,

Sample code to do what you want.

method pushButton(var eventInfo Event)
var
DelQry Query
DataTbl, BuTbl, PulledTbl Table
oldname, newname String
endvar

; first backup your table

DataTbl.attach("your table name here")
BuTbl.attach("whatever you want to call backup")

if isTable(BuTbl) then
if msgQuestion("Copy table", "Overwrite " + BuTbl + "?") = "Yes" then
DataTbl.copy(BuTbl)
endIf

; now run your query

DelQry = Query

yourtablename.DB | Pulled |
Delete | not blank |

EndQuery

if not DelQry.executeQBE()
then
errorshow()
return
endif

; now rename it

oldtable = "riv:deleted.db"
newtable = "whatever you want to call it"
PulledTbl.attach(oldtable)
if isTable(newtable) then
if msgQuestion("Confirm", newtable+ " exists. Overwrite it?") =
"Yes" then
PulledTbl.rename(newtable)
endif
endMethod