Cross reference Members for exclusion

Hello chrisyoco,

If you have an edit field with a barcode you just scanned in it you could do

procedure Form1_Button1_OnClick (Sender: TObject; var Cancel: boolean);
var
    i : Integer;
    excluded_scan : String;
begin
    excluded_scan := Form1.Edit1.Text;

    i := SQLExecute('SELECT COUNT(ScanExcluded) FROM Excluded WHERE ScanExcluded = "'+excluded_scan+'"');
        if i = 0 Then ShowMessage('No result')
        else ShowMessage('This person is excluded');
end;

But why two tables ? This is doing things the hard way don't you think ? What if you want to get the name of the person whose is excluded ? You need to perform a JOIN on the first table and something like :

SELECT
Members.Line2
FROM
Members
INNER JOIN Excluded ON Members.Barscan = Excluded.ScanExcluded

What I would have built is just one table like :

CREATE TABLE "main"."member" (
"id"  INTEGER PRIMARY KEY AUTOINCREMENT,
"member_names"  TEXT,
"member_scan"  TEXT,
"is_excluded"  INTEGER NOT NULL DEFAULT 0
)

This way, each time you save a duo member / barcode, the boolean field is_excluded is set by default to 0. Set it to 1 if the code is excluded.

Or if a member can have multiple scancodes, you could create two tables :
- a member table with names, and miscellaneous info (address, date of birth...)
- a code table with a boolean field and a reference to the member table

See the attached screenshot

Hope this helped a little

Cheers

Mathias