comparing records database

[yknot]

Hi,

I have a database which holds records for fields "partno" and "quantity". At the end of each calendar quarter I want to know which parts are no longer in stock when compared to the list of parts of 12/31.

So what I would like to accomplish is to "select" the records of i.e. 3/31 and "compare" them to the records of 12/31.

I would like to "save" the found set in a separate table within the same database.

Any help is much appreciated

yknot

[dkemme]

Seems like a lot of work moving them around. Why not leave them in the table they exist in but mark them in some way to indicate part no longer available. With this mark, you could exclude them from the user yet still have them in the database if the become available again or if you need to find out what someone ordered years ago. You could create a calculation field to mark them as unavailable with a relationship based upon stock that would change immediately when part no longer available.

Otherwise, use the above relationship to find the date part became unavailable, check to see if any records found, switch to the table used to archive, import the found records, switch back to previous table, delete all found records. Be sure to test above carefully, you don't want to delete records if all not perfect.

[yknot]

Thanks dkemme

Will try your suggestions but need some time to do so

Regards

[aaa]

Export to or Import from your found set other file.

[yknot]

Let me try it another way: I wrote a script which screwed up some of the data which resides in the "quantity" field of the table "Parts". Before I ran the script I made a backup of the table so I am not in trouble. I could simply restore the compromised table by replacing it with the backup.

However; I want to find out which records have been compromised. Both tables have the identical number of records and each record has a unique number. I want to compare the records in the two tables and find the records where the data in the "quantity" field of table "Parts-Old" does not match the that of table "Parts-New"

[MoonShadow]

Be sure your backup copy has a different file name. Then in your current file, create a file reference to your backup copy.

Then join them in your graph on the unique part number (and any other fields which are required to make it unique). Then in your main file (Parts New table), create an unstored calculation number with:

Quantity <> PartsOld::Quantity

Those with a 1 will be your errors.

[yknot]

Thanks MoonShadow - This makes sense; would this work if there were an unequal number of records in the two tables?

yknot

[MoonShadow]

Since you will be joining the two tables (in two different files) on the unique part number, it doesn't matter how many records are involved - or even if they are in the same order. All that is required is to make sure the relationship is unique one-record to one-record and your results will be consistent.

Always test these types of comparison relationships by placing fields from both table on your layout. I color one set of fields (the related - your backup file) side. Create the layout based upon the file with the most records. Then you can see the holes. If you base your layout upon the table with the fewer records, you might miss something.

[yknot]

Ok - Thank you

yknot