[libreoffice-users] Re: [Base} Compare contents of similar records on two sheets

Am 12.04.2012 15:23, Preston Smith wrote:
> Hello,
>
> I have asked this question previously in the context of two spreadsheets.
>
> I know now that I can migrate my spreadsheets to BASE and with minimal
> fuss make it work for me.
>
> I have two sets of records. My Master database and a monthly update that
> reflects additions, deletions and changes accumulated by another party
> over the past month along with unchanged records from the previous month.
>
> I need to compare the two sets of information to update my Master file
> which is more comprehensive then the monthly update. I do not want to
> make these comparisons manually - there are in excess of 2000 records in
> each file.
>
> Each record is of the format
> Field 1. Master Number (assigned by another agency)
> Fields 2-30. Various data formats
>
> If a record, as determined by the Master Number field, exists on both
> sheet 1 and sheet 2, I would like to be able to compare the similar
> fields, field by field.

SELECT * FROM "Master" WHERE "ID" IN (SELECT "ID" FROM "Slave")
Then create a form on "Master" and a subform on "Slave" for an easy eye 
ball comparison.

If the field contents of one record are not
> identical to the corresponding field in the similar record in the other
> file, I would like to be able to have this record brought to my
> attention with anomalies highlighted

second subform:
SELECT * FROM "Master", "Slave"
WHERE "Master"."ID"="Slave"."ID"
AND(    "Master"."F1"<>"Slave"."F1"
        OR "Master"."F2"<>"Slave"."F2"
        OR "Master"."F3"<>"Slave"."F3"
        OR "Master"."F4"<>"Slave"."F4"
        OR "Master"."F5"<>"Slave"."F5"
        OR "Master"."F6"<>"Slave"."F6"
        OR "Master"."F7"<>"Slave"."F7"
        OR "Master"."F8"<>"Slave"."F8"
        OR ...)
> If a record, as determined by its Master Number, is in one sheet and not
> in the other I would like this anomaly brought to my attention also
SELECT * FROM "Master" WHERE "ID" NOT IN (SELECT "ID" FROM "Slave")

> Is there a way that this can be done?
>
> Thanks,
> Preston



--
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted