Recursive(?) Query

M

MikeC

I'm currently in the process of developing a new report for an existing
production A2K3 application. The primary purpose of the application is to
reconcile bank deposit transactions against G/L transactions...analogous to
a check book balancing application.

The report I'm developing needs to show previously matched transactions that
cross date boundaries. I believe a query or procedure can be developed for
this purpose, but the existing table design is not making the job easy for
me. I need some help to point me in the right direction.

The tblMatched table looks like this:

BID (Text)*
BDate (Datetime)*
BAmt (Currency)
GLID_1 (Text)**
GLID_2 (Long)**
GLAmt (Currency)
GLDate (Datetime)

*BID and BDate serve as a combination key for uniquely identifying bank
records.

**GLID_1 and GLID_2 serve as a combination key for uniquely identifying G/L
records.

The tblMatched table identifies all bank and G/L transactions that were
previously matched against one another. Records within this table may
represent matches of:

1) 1 bank record to 1 G/L record
2) 1 bank record to many G/L records
3) Many bank records to 1 G/L record
4) Many bank records to many G/L records

(NOTE: The matching process allows matches ONLY IF the total bank amount
equals the total G/L amount for each matched group.)

Scenario #4 is the one that is giving me trouble. The relationships for
groups of "many-to-many" matched records appear to be recursive. For each
"many-to-many" match, I believe I will need to:

1) Find a set of records having a GLDate that falls within a date range.
2) Uniquely identify the BID and BDate for each bank record within this
group.
3) Independent of date, uniquely identify any additional records that may
have been matched to these BID and BDate combinations.
4) Somehow repeat steps 2 and 3 until all records within a "many-to-many"
matched group have been identified.

Below is a relatively simple example Of 3 bank records matched to 2 G/L
records:

Here are the 3 bank records (BID, BDate, BAmt):

1) 23, 7/27/2007, $25.00
2) 41, 7/29/2007, $75.00
3) 67, 8/03/2007, $1,000.00

Group Total: $1,100.00

Here are the 2 G/L records (GLID_1, GLID_2, GLAmt, GLDate):

1) CBA, 4, $850.00, 7/26/2007
2) ABC, 1, $250.00, 8/01/2007

Group Total: $1,100.00

Here's how the matches are represented in the tblMatched table:

1) 23, 7/27/2007, $25.00, CBA, 4, $850.00, 7/26/2007
2) 41, 7/29/2007, $75.00, CBA, 4, $850.00, 7/26/2007
3) 67, 8/03/2007, $1,000.00, CBA, 4, $850.00, 7/26/2007
4) 67, 8/03/2007, $1,000.00, ABC, 1, $250.00, 8/01/2007

If we search for records having a GLDate between 7/01/2007 and 7/31/2007, we
will initially find the first 3 tblMatched records shown above. However,
the 4th tblMatched record would be excluded if the query only uses a simple
date filter. The 4th record also needs to be included because it is part of
the "many-to-many" matched group.

How do I create a query or procedure that will capture all records involved
in a "many-to-many" match?

Some matches involve more than 30 bank and G/L transactions, so the above
example is one of the easier ones.

Once all records involved in the relevant matches have been identified, then
I will be in a position to evaluate which records have crossed a month-end
boundary.
 
G

Gary Walter

MikeC said:
I'm currently in the process of developing a new report for an existing
production A2K3 application. The primary purpose of the application is to
reconcile bank deposit transactions against G/L transactions...analogous
to a check book balancing application.

The report I'm developing needs to show previously matched transactions
that cross date boundaries. I believe a query or procedure can be
developed for this purpose, but the existing table design is not making
the job easy for me. I need some help to point me in the right direction.

The tblMatched table looks like this:

BID (Text)*
BDate (Datetime)*
BAmt (Currency)
GLID_1 (Text)**
GLID_2 (Long)**
GLAmt (Currency)
GLDate (Datetime)

*BID and BDate serve as a combination key for uniquely identifying bank
records.

**GLID_1 and GLID_2 serve as a combination key for uniquely identifying
G/L records.

The tblMatched table identifies all bank and G/L transactions that were
previously matched against one another. Records within this table may
represent matches of:

1) 1 bank record to 1 G/L record
2) 1 bank record to many G/L records
3) Many bank records to 1 G/L record
4) Many bank records to many G/L records

(NOTE: The matching process allows matches ONLY IF the total bank amount
equals the total G/L amount for each matched group.)

Scenario #4 is the one that is giving me trouble. The relationships for
groups of "many-to-many" matched records appear to be recursive. For each
"many-to-many" match, I believe I will need to:

1) Find a set of records having a GLDate that falls within a date range.
2) Uniquely identify the BID and BDate for each bank record within this
group.
3) Independent of date, uniquely identify any additional records that may
have been matched to these BID and BDate combinations.
4) Somehow repeat steps 2 and 3 until all records within a "many-to-many"
matched group have been identified.

Below is a relatively simple example Of 3 bank records matched to 2 G/L
records:

Here are the 3 bank records (BID, BDate, BAmt):

1) 23, 7/27/2007, $25.00
2) 41, 7/29/2007, $75.00
3) 67, 8/03/2007, $1,000.00

Group Total: $1,100.00

Here are the 2 G/L records (GLID_1, GLID_2, GLAmt, GLDate):

1) CBA, 4, $850.00, 7/26/2007
2) ABC, 1, $250.00, 8/01/2007

Group Total: $1,100.00

Here's how the matches are represented in the tblMatched table:

1) 23, 7/27/2007, $25.00, CBA, 4, $850.00, 7/26/2007
2) 41, 7/29/2007, $75.00, CBA, 4, $850.00, 7/26/2007
3) 67, 8/03/2007, $1,000.00, CBA, 4, $850.00, 7/26/2007
4) 67, 8/03/2007, $1,000.00, ABC, 1, $250.00, 8/01/2007

If we search for records having a GLDate between 7/01/2007 and 7/31/2007,
we will initially find the first 3 tblMatched records shown above.
However, the 4th tblMatched record would be excluded if the query only
uses a simple date filter. The 4th record also needs to be included
because it is part of the "many-to-many" matched group.

How do I create a query or procedure that will capture all records
involved in a "many-to-many" match?

Some matches involve more than 30 bank and G/L transactions, so the above
example is one of the easier ones.

Once all records involved in the relevant matches have been identified,
then I will be in a position to evaluate which records have crossed a
month-end boundary.
Click to expand...
Hi Mike,

I may have misunderstood, but I suggest "divide-and-conquer"

1) Find a set of records having a GLDate that falls within a date range.
2) Uniquely identify the BID and BDate for each bank record within this
group.

qryPreFilter

SELECT DISTINCT
#7/1/2007# AS FiltMin,
#7/31/2007# AS FiltMax,
t.BID,
t.BDate
FROM
tblMatched AS t
WHERE
t.GLDate BETWEEN #7/1/2007# AND #7/31/2007#;



3) Independent of date, uniquely identify any additional records that may
have been matched to these BID and BDate combinations.
4) Somehow repeat steps 2 and 3 until all records within a "many-to-many"
matched group have been identified

SELECT
t.BID,
t.BDate,
t.BAmt,
t.GLID_1,
t.GLID_2,
t.GLAmt,
t.GLDate,
(t.GLDate < Q.FiltMin
OR
t.GLDate > Q.FiltMax) AS OutOfRange
FROM
tblMatched AS t
INNER JOIN
qryPreFilter AS Q
ON
t.BID = Q.BID
AND
t.BDate = Q.BDate;

Like I said, I may have misunderstood...

good luck,

gary

..
 
G

Gary Walter

Gary Walter" said:
Hi Mike,

I may have misunderstood, but I suggest "divide-and-conquer"

1) Find a set of records having a GLDate that falls within a date range.
2) Uniquely identify the BID and BDate for each bank record within this
group.

qryPreFilter

SELECT DISTINCT
#7/1/2007# AS FiltMin,
#7/31/2007# AS FiltMax,
t.BID,
t.BDate
FROM
tblMatched AS t
WHERE
t.GLDate BETWEEN #7/1/2007# AND #7/31/2007#;



3) Independent of date, uniquely identify any additional records that may
have been matched to these BID and BDate combinations.
4) Somehow repeat steps 2 and 3 until all records within a "many-to-many"
matched group have been identified

SELECT
t.BID,
t.BDate,
t.BAmt,
t.GLID_1,
t.GLID_2,
t.GLAmt,
t.GLDate,
(t.GLDate < Q.FiltMin
OR
t.GLDate > Q.FiltMax) AS OutOfRange
FROM
tblMatched AS t
INNER JOIN
qryPreFilter AS Q
ON
t.BID = Q.BID
AND
t.BDate = Q.BDate;

Like I said, I may have misunderstood...
Click to expand...

the only other alternative I could think you
might want was to change second query to
get distinct group of

BID/BDate/GLID_1/GLID_2

qryPreMatch

SELECT
DISTINCT
t.BID,
t.BDate,
t.GLID_1,
t.GLID_2,
Q.FiltMin,
Q.FiltMax
FROM
tblMatched AS t
INNER JOIN
qryPreFilter AS Q
ON
t.BID = Q.BID
AND
t.BDate = Q.BDate;

then join qryPreMatch to tblMatched

SELECT
t.BID,
t.BDate,
t.BAmt,
t.GLID_1,
t.GLID_2,
t.GLAmt,
t.GLDate,
(t.GLDate < Q.FiltMin
OR
t.GLDate > Q.FiltMax) AS OutOfRange
FROM
tblMatched AS t
INNER JOIN
qryPreMatch AS Q
ON
t.BID = Q.BID
AND
t.BDate = Q.BDate
AND
t.GLID_1 = Q.GLID_1
AND
t.GLID_2 = Q.GLID_2;

without testing on your actual data, I cannot
think through whether this is a redundant step...

good luck,

gary
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query filtering by Aggregate 3
Query Help 2
deleting duplicates but with a condition 5
Help with Query 12
Query Records of Different Formats 1
delete query wth a condition 1
unmatched record query question 2
unique and optimized pairs of records - no dupes. 6

Top