Simple Query that I can't figure out????

[pgh]

I thought this would be a simple Query; however I can't figure it out.


I have a list of Bank Transactions with a Description and dollar amount. I want to assign each transaction to a tenant based on one or more keywords that identifies that tenant. However I also want to exclude any transactions that contain an exclude-word for that tenant.
For example I may have a tenant called 'Sara' who has deposits that I can find by a Keyword of 'Sara'; however I want to exclude deposits that contain 'Sarah' because they belong to someone else. This is a simple example; however they can be much more random with many keywords and exclude words.
So I have the following 4 tables...

Bank Table

ID	Description	Amount
1	This was Pauls Payment	$100.00
2	This a Test Payment	$100.00
3	The Cat in The Hat	$100.00
4	Paul	$100.00
5	I Love Sara today	$100.00
6	Sarah pay	$100.00
7	This was Paul and Zoe's payment	$100.00
8	Zoe, Paula and Paul Waz Hair	$100.00
9	Paul Jones Payment	$100.00

Tenant Table

ID	FirstName	LastName
1	Paul	Jones
2	Sara	Jane
3	Honey	Cat
4	Test	LastName
5	Sarah	Smith

KeyWord Table

ID	TanantID	KeyWord
1	1	Paul
2	1	Higgins
3	2	Sara
4	3	Honey
5	3	Cat
6	4	Test
7	4	Last
8	4	Name
9	5	Sarah
10	5	Smith

ExcludeWord Table

ID	TanantID	ExcludeWord
1	1	Jones
2	1	Paula
3	2	Sarah
4	3	Dog
5	5	Sara

How do I create a Query to match up each Bank transaction to each Tenant? based on the Keyword and exclude word?

Here is an example how NOT to do it...

SELECT Bank.ID, Bank.Description, [Include-Exclude].KeyWord.TanantID, [Include-Exclude].KeyWord, InStr(1,[Description],[KeyWord],1) AS [Include#], [Include-Exclude].ExcludeWord.TanantID, [Include-Exclude].ExcludeWord, InStr(1,[Description],[ExcludeWord],1) AS [Exclude#]
FROM Bank, [Include-Exclude]
WHERE (((InStr(1,[Description],[KeyWord],1))>0) AND ((InStr(1,[Description],[ExcludeWord],1))=0));

I will be very impressed by whoever can solve this!


:-)

[June7]

Why don't transaction records have a foreign key to the appropriate tenant account? What happens if there is more than one Sarah or multiple Sara or multiple Paul - all are different people - how should Access know which account goes with which transaction?

When garbage goes in, garbage comes out.

[pgh]

Hi June7,

The purpose of the Query is so that I can assign a 'Tenant' ID to each Transaction. There may be multiple Sara or Paul tenant's however there transaction descriptions are always different, so it is not to hard to set up some rules to match the transactions based on 'Keywords' and 'Exclude Words'. Once I figure this out I can write an update query to assign a Tenant ID to every transaction based on the rules.

example....

Description:
Sara $100
Sara Smith $100

Rule:
Tenant-1 Sara has include word 'Sara' and exclude word 'Smith'
Tenant-2 Sara Smith has include word 'Smith'

etc etc.

[June7]

Consider this query:
SELECT Bank.TranID, Tenants.TenantID, Tenants.LastName, Tenants.FirstName, Bank.Description, KeyWords.KeyWord
FROM Bank, KeyWords INNER JOIN Tenants ON KeyWords.TenantID_Inc = Tenants.TenantID
WHERE (((Bank.Description) Like "*" & [KeyWord] & "*")) ORDER BY BankID;

The result is:

BankID	TenantID	LastName	FirstName	Description
1	1	Jones	Paul	This was Pauls Payment
2	4	LastName	Test	This a Test Payment
3	3	Cat	Honey	The Cat in The Hat
4	1	Jones	Paul	Paul
5	2	Jane	Sara	I Love Sara today
6	2	Jane	Sara	Sarah pay
6	5	Smith	Sarah	Sarah pay
7	1	Jones	Paul	This was Paul and Zoe's payment
8	1	Jones	Paul	Zoe, Paula and Paul Waz Hair
9	1	Jones	Paul	Paul Jones Payment

Now if the ExcludeWords table is included and criteria AND Description NOT LIKE "*" & [ExcludeWord] & "*" is applied, both transaction 6 rows will be eliminated as well as transaction 2.

Pattern matching can be complicated. Automation depends on consistency in structure. At some point, person might have to manually review and make a judgment, as with the 2 BankID 6 rows.