Help for newbie please

[HMC]

Hi,

Can some kind soul help with pointing me in the right direction to a problem below?

In my database (property database), I have some old properties which are now sold. There are relationships between property, rent received, billing, lessees, etc. I want to be able to keep the details of the 'sold' properties.(i.e., who owned it, what they paid in the past, when they paid etc. in case I need it, but don't want it shown any longer on the main working database). I want to be able to delete the properties that are sold from the main table. Any ideas how I go about this? Hope I've explained myself ok.

Thanks

HMC

 

[rainman89]

you can do an append query then a delete to place the "sold" properties in another table...

 

[BarryMK]

Are you entering/displaying your data on a form? If so you could add a yes/no field to your main table, add a check box called say Archive linked to the yes/no field nd use the underlying query to filter out all records where the field is True.

 

[Rabbie]

If the info is in several tables you will have to give thought as to how you move the data.

 

[HMC]

rainmain,

thanks for the reply. I thought perhaps append, and was looking at this, still confused a bit though. If I do an append query, does this then allow me to 'append' all the other details and will I have to undo relationships to delete certain records, or does append take care of all this?

Thanks

HMC

 

[HMC]

thanks to all for your replies. Pause for thought. The records I want to delete or rather before that place in another table 'sold' for history/archive purposes, are already linked relationship with other tables. This is causing confusion and difficulties. The yes/no box looks like a good option. Basically, all the properties are linked to a rent received table (aswell as others). I do not want to loose this info, but don't need the ones linked to the sold properties.

So basically, I'd be happy having:
tproperties
trents
tlessees
tbilling etc. with added
tsoldproperties
so that the sold properties are taken out of tproperties but I can still see what was paid under trents, who owned it, under tlessees. I have a PropertyForm which shows tproperties,trents,arrears,tlessees,tconsents,tbilling, and which I work on, so I'd like to get the sold properties out of here but kept somewhere else.

 

[rainman89]

well like everyone else says... if you have info in more than one table then you probably wont want to do an append. I agreed with Barry about the field, i originaly had an append/delete procedure in my office timemanagement system, and ran into MANY MANY problems
IMO i wouldn't do the append, but that is an option if you so see fit to follow that path.

 

[HMC]

I'm a bit worried about losing data by doing an append. Barry's suggestion might fit.

If Barry, you could tell me when you say, "so you could add a yes/no field to your main table, which table would you suggest or can it be done on a main property form I have which shows mosts of the tables? Also you say,"add a check box called say Archive linked to the yes/no field nd use the underlying query to filter out all records where the field is True." Can you point me towards a tutorial, as I'm a bit confused as to how to 'filter out the specific records I want". Many thanks to all the replies.

 

[The_Doc_Man]

Here's the straight skivvy. If you want history and have multi-table relational integrity, you have a VERY hard row to hoe to actually purge old records. You would do FAR better to merely develop queries that, for searching active properties, don't look at any that are marked "NOT ACTIVE." Which means a simple yes/no field might be enough to be the basis for a query-based filter.

Then, when you want historical details, you use a DIFFERENT query that ignores the state of the ACTIVE/NOT ACTIVE flag - or shows it but doesn't suppress anything.

 

[BarryMK]

It would be hard (and foolish) to argue with the Doc Man he's laid it out plain and clear.

As for the yes no field I'd put it in your main table. Just include the field in the query that is driving your form and add a check box to the form with that field as its recordsource.

In the criteria grid for the query add the yes/no field (see screen dump of a query design grid) in this case the query will exclude all records from that table where the check box has not been ticked.

 

[anthonys]

property database

Working in the property industry i feel your pain.
I'm not able to add much more but can emphasise the point to not go down the append route. If a property is sold or withdrawn you can copy the details to another table(s) but my experiance says that users will want to copy it back to live section once the property comes back onto the market.

What i do in my agency database is have a status field on the property table (Available, sold, under offer and withdrawn) and my datasource query for the property form excludes sold and withdrawn. My search screen then has an option to include sold properties which changes the datasource query to include Sold and withdrawn.

Keeping the property details in the same table also makes reporting much easier when trying to show the amount of property handled over a period of time.

Hope this helps and best of Luck!!

Ant