Access VBA help

[June7]

I forgot how tricky it is to select records on negative criteria. Need a unique identifier field. An autonumber will serve.



SELECT Main.* FROM Main WHERE NOT ID IN (SELECT Main.ID FROM Main, Sparr WHERE Mail LIKE "*" & sparrord & "*");

[knarfreppep]

Hello All!

Sometimes it may be possible to eliminate a problem rather than spend time trying to solve it.

Isn't it possible in this example to modify the table designs so this can be solved with two tables and one update query?

I've a table named tblRecords which has two fields (strItem and ysnFilterOut) and three records ("Bill@Hotmail.com", False; "Fred@Bloggs.com.au", False; "Johny@sky.com", False).

I've another table named tblExclusions which has one field (strExclude) and two records ("Hotmail"; "Johny").

When I run the code below, records in tblRecords become ("Bill@Hotmail.com", True; "Fred@Bloggs.com.au", False; "Johny@sky.com", True).

I can then simply Filter tblRecords to show/hide the excluded records (i.e. those with ysnFilterOut = True).

Net result: fewer tables and less code to maintain and reduced execution time.

Public Sub gsubMarkExcludedRecords()
DoCmd.SetWarnings False
Docmd.RunSQL "UPDATE tblRecords SET tblRecords.ysnFilterOut = True WHERE ((((SELECT Top 1 strExclude FROM tblExclusions WHERE Not InStr(strItem,strExclude)=0))=True))"
DoCmd.SetWarnings True
End Sub

[June7]

I don't really understand need for either inserting records to another table or updating a field.

[knarfreppep]

We may have different perceptions of what the poster wants to achieve.

tblExclusions is just an easily-maintained list of strings not wanted in email addresses in the main table.

The field I update allows records not wanted to be filtered away without the need to maintain a table of 'filtered out' records as well as a table of 'live' records.

The model I suggested allows easy 'resurrection' of records if a tblExclusions record ever needs to be deleted.

As Jacobowsky says in Me and the Colonel, "In life, there are always two possibilities".

[June7]

Right, select records from Main where there is not a match in Sparr.

The reason for this is not clear to me. What needs to be done with the selected records? Why save to another table and not just use the query for whatever - like sending emails?

[knarfreppep]

I suspect the poster may still be on the way to understanding the principles of a normalised dB.

I take the point I suspect you are making about why I propose including the ysnFilterOut field rather than relying on a query joining with tblExclusions ... but, as the table grows, performance will be an issue and updating ysnFilterOut can be done in down time.

[June7]

Certainly, large dataset will be slower and preferable to maintain a field than juggle records between tables. How frequent and when this update would be needed depends on various factors.

[knarfreppep]

Indeed ... and all well beyond the scope of our present interlocution ... bye for now.