Update table and delete only certain rows

[Irina]

Hello,
i am not sure if this is possible.
I have a table that gets filled with various information from linked tables
in the same database. I use this table for reports. I also have a form that
allows
users to fill in more information in that same table for reports.
The issue that I am having is that i have to delete the table and repopulate
it again
every time my database is open because all the linked tables are linked to a
paradox database - the main one used by the department.
So, the question is, how to keep the data the users are entering and
re-populate the table at the same time?
can anyone help me?

 

[Tom van Stiphout]

On Mon, 17 Dec 2007 06:25:01 -0800, Irina

If I understand you correctly the Reports table is filled from two
sources: PDox, and the User. You want to keep the User records. Users
only add records; they don't update PDox records.

If the above is correct, you would want to add a new field "Source"
and set it either to PDox or User. Then when it's time to refresh the
PDox records:
delete * from Reports
where Source='PDox'

-Tom.

 

[Irina]

Thank you for that information
i dont think that it will work
The whole record is getting pulled from paradox, all fields except for 3
(family info, children and spouse) are not editable, only those 3 are
if I add another field source to the reports table,then how am I going to mark
it user or pdox, since it applies to the whole record?

 

[Tom van Stiphout]

On Mon, 17 Dec 2007 08:42:01 -0800, Irina

OK, now I understand better.
I would keep the user information in a separate table:
PDoxID, familyInfo, children, spouse
Then you can delete all pdox records at will; the user information
still exists in the new table.
To see both data sets combined, create a query that joins both tables
on the PDoxID column.

-Tom.

 

[Irina]

Thanks Tom.
I have 3615 records in the report table and 3615 in the family info
i am joining tables on individualID - it is the same in both tables
and i keep getting 3 times the records in the query 10845
i have tried inner join, then left to right, then right to left and I still
get 10845 records
any ideas on that?

 

[Tom van Stiphout]

On Mon, 17 Dec 2007 09:39:00 -0800, Irina

What is the primary key on each of these tables?
My guess is that it's over more than one field. Then you need to join
by all of those fields.
Or you don't have a PK. That's REALLY BAD and should be fixed right
away.

-Tom.