Linking two tables using SQL with Mid function?

[Guest]

Dear Access gurus,

We have an Access database that contains tables that get their data from
another financial system. We are creating a query which needs to link two
tables together. However, although they contain some similar information,
they don't have a primary key linking them to enable them to "talk together"
(and there are no intermediary tables to help achieve this either).

The issue we have is that in the 1st table (CTRONADM_AP_VOUCHERS) it
contains a VOUCHER_ID field of 9 digits (eg. 000000092), and in the 2nd table
(CTRONADM_QUEUE_DATA) it contains a OBJ_ID field of 12 digits (eg.
001000000092). These two fields need to link (as they are the only fields
that directly relate) but as they have different string lengths they won't!
The 1st field of 9 characters should match up with the last 9 characters of
the 2nd field (eg. it should ignore the first 3 characters which represent
the organisation code).

Someone from our finance software company has suggested we use a Mid
function to achieve this (but provided no other information). Although we
can understand how a mid function could extract the 9 characters using a
calculated field in a query, we're not sure how to use this to enable linking
of the tables to extract relevant data between the two tables.

Do we need to use an SQL command to achieve this, and if so, what would we
need to write in SQL to extract the relevant data and/or achieve a link
between the two tables? At present our SQL query code looks like this:

SELECT CTRONADM_QUEUE_DATA.QUEUE_ID, CTRONADM_QUEUE_DATA.USER_ID,
CTRONADM_QUEUE_DATA.CREATE_DATE, Now() AS Expr1,
DateDiff("d",CTRONADM_QUEUE_DATA.CREATE_DATE,[Expr1]) AS Expr2,
CTRONADM_WFPO_APPROVER_CODE.LONG_DESCRIPTION, CTRONADM_OBJECT_DATA.OBJ_ID,
CTRONADM_AP_VOUCHERS.VOUCHER_ID
FROM CTRONADM_AP_VOUCHERS, CTRONADM_OBJECT_DATA INNER JOIN
(CTRONADM_QUEUE_DATA INNER JOIN CTRONADM_WFPO_APPROVER_CODE ON
CTRONADM_QUEUE_DATA.USER_ID = CTRONADM_WFPO_APPROVER_CODE.CODE_ID) ON
CTRONADM_OBJECT_DATA.WF_ID = CTRONADM_QUEUE_DATA.WF_ID
WHERE (((CTRONADM_QUEUE_DATA.QUEUE_ID)="APRVVOU"));


Any assistance would be greatly appreciated

Regards

JD2

 

[Marshall Barton]

We have an Access database that contains tables that get their data from
another financial system. We are creating a query which needs to link two
tables together. However, although they contain some similar information,
they don't have a primary key linking them to enable them to "talk together"
(and there are no intermediary tables to help achieve this either).

The issue we have is that in the 1st table (CTRONADM_AP_VOUCHERS) it
contains a VOUCHER_ID field of 9 digits (eg. 000000092), and in the 2nd table
(CTRONADM_QUEUE_DATA) it contains a OBJ_ID field of 12 digits (eg.
001000000092). These two fields need to link (as they are the only fields
that directly relate) but as they have different string lengths they won't!
The 1st field of 9 characters should match up with the last 9 characters of
the 2nd field (eg. it should ignore the first 3 characters which represent
the organisation code).

Someone from our finance software company has suggested we use a Mid
function to achieve this (but provided no other information). Although we
can understand how a mid function could extract the 9 characters using a
calculated field in a query, we're not sure how to use this to enable linking
of the tables to extract relevant data between the two tables.

Do we need to use an SQL command to achieve this, and if so, what would we
need to write in SQL to extract the relevant data and/or achieve a link
between the two tables? At present our SQL query code looks like this:

SELECT CTRONADM_QUEUE_DATA.QUEUE_ID, CTRONADM_QUEUE_DATA.USER_ID,
CTRONADM_QUEUE_DATA.CREATE_DATE, Now() AS Expr1,
DateDiff("d",CTRONADM_QUEUE_DATA.CREATE_DATE,[Expr1]) AS Expr2,
CTRONADM_WFPO_APPROVER_CODE.LONG_DESCRIPTION, CTRONADM_OBJECT_DATA.OBJ_ID,
CTRONADM_AP_VOUCHERS.VOUCHER_ID
FROM CTRONADM_AP_VOUCHERS, CTRONADM_OBJECT_DATA INNER JOIN
(CTRONADM_QUEUE_DATA INNER JOIN CTRONADM_WFPO_APPROVER_CODE ON
CTRONADM_QUEUE_DATA.USER_ID = CTRONADM_WFPO_APPROVER_CODE.CODE_ID) ON
CTRONADM_OBJECT_DATA.WF_ID = CTRONADM_QUEUE_DATA.WF_ID
WHERE (((CTRONADM_QUEUE_DATA.QUEUE_ID)="APRVVOU"));

Yes, a non-equi join can only be done in SQL view. I can't
seem to unravel your query (especially in all caps), but a
simple example might be:

SELECT . . .
FROM CTRONADM_AP_VOUCHERS As V
INNER JOIN CTRONADM_QUEUE_DATA As Q
ON V.VOUCHER_ID = Mid(Q.OBJ_ID, 4)


It may not be as fast as a equi join because an index on the
OBJ_ID field can't be used.

 

[Jamie Collins]

SELECT . . .
FROM CTRONADM_AP_VOUCHERS As V
INNER JOIN CTRONADM_QUEUE_DATA As Q
ON V.VOUCHER_ID = Mid(Q.OBJ_ID, 4)

It may not be as fast as a equi join because an index on the
OBJ_ID field can't be used.

SELECT . . .
FROM CTRONADM_AP_VOUCHERS As V
INNER JOIN CTRONADM_QUEUE_DATA As Q
ON V.VOUCHER_ID = Mid(Q.OBJ_ID, 4)

Could this, then, be one of those situations where maintaining a
indexed 'computed' column of Mid(Q.OBJ_ID, 4) -- enforced via a
validation rule, of course -- may be worthwhile i.e. denormalizing for
improved performance?

Jamie.

--

 

[Jamie Collins]

SELECT . . .
FROM CTRONADM_AP_VOUCHERS As V
INNER JOIN CTRONADM_QUEUE_DATA As Q
ON V.VOUCHER_ID = Mid(Q.OBJ_ID, 4)

Could this, then, be one of those situations where maintaining a
indexed 'computed' column of Mid(Q.OBJ_ID, 4) -- enforced via a
validation rule, of course -- may be worthwhile i.e. denormalizing for
improved performance?

In case you missed my point:

CREATE TABLE CTRONADM_AP_VOUCHERS
(
VOUCHER_ID VARCHAR(8) NOT NULL UNIQUE
)
;
CREATE TABLE CTRONADM_QUEUE_DATA
(
OBJ_ID VARCHAR(12) NOT NULL UNIQUE,
OBJ_ID_VOUNCHER_PART VARCHAR(8) NOT NULL,
CHECK (OBJ_ID_VOUNCHER_PART = MID(OBJ_ID, 4))
)
;
CREATE INDEX idx__CTRONADM_QUEUE_DATA__OBJ_ID_VOUNCHER_PART
ON CTRONADM_QUEUE_DATA (OBJ_ID_VOUNCHER_PART)
;
SELECT *
FROM CTRONADM_AP_VOUCHERS As V
INNER JOIN CTRONADM_QUEUE_DATA As Q
ON V.VOUCHER_ID = Q.OBJ_ID_VOUNCHER_PART
;

Jamie.

--

 

[Marshall Barton]

Jamie Collins wrote:
[snip]

In case you missed my point:

[snip]

I didn't miss your point, but since you already know the
answer, I didn't see any point to responding. If the OP
runs into a performance issue, then alternative approaches
can be discussed in a meaningful context.

 

[Jamie Collins]

Jamie Collins wrote:

[snip]>In case you missed my point:

[snip]

I didn't miss your point
but since you already know the
answer, I didn't see any point to responding.

By 'you' I meant 'the group', for which I expect a near 100% non-

If the OP
runs into a performance issue, then alternative approaches
can be discussed in a meaningful context.

[Confused] But wasn't it you (Marshall) who raised the issue of
performance? What, am I not supposed to discuss a point you raised?
What would a meaningful context be, then?

For the record, I do no 'already know the answer': my knowledge of the
engine is scant, the issue of denormalizing for performance is
controversial, I don't know if I've made a misstatement, typo or
otherwise failed to make my point clear, etc. Perhaps I'm guilty of
over-analyzing, though <g>.

Jamie.

--

 

[Marshall Barton]

Jamie Collins wrote:

[snip]>In case you missed my point:

[snip]

I didn't miss your point
but since you already know the
answer, I didn't see any point to responding.

By 'you' I meant 'the group', for which I expect a near 100% non-

If the OP
runs into a performance issue, then alternative approaches
can be discussed in a meaningful context.

[Confused] But wasn't it you (Marshall) who raised the issue of
performance? What, am I not supposed to discuss a point you raised?
What would a meaningful context be, then?

For the record, I do no 'already know the answer': my knowledge of the
engine is scant, the issue of denormalizing for performance is
controversial, I don't know if I've made a misstatement, typo or
otherwise failed to make my point clear, etc. Perhaps I'm guilty of
over-analyzing, though <g>.

Jamie, I consider your question to be rhetorical. IMO,
denormalization for performance issues is always a question,
for which the answer is an emphatic NO 99.99% of the time.
The rare case where is it appropriate depends on all kinds
of factors that are unique to each situation and I just
don't know how to discuss the pros and cons without having
those factors available.

 

[Matt Barker]

In fact, it can be done in MS Access, but it is not really recommended, since the MS Access query interface is not designed to accept function statements in your join definitions.

But there is a way:

1. Build your query in design view and establish your joins.
2. Now go to view the query in SQL.
3. Within the SQL statement, type in your function statements where the two tables in question need to be joined. Your function statements should ensure that the values to be joined are equal on both sides through the functions return values.
4. Now run the query without saving it. Your query will run more slowly but should produce the expected result if you have adulterated your SQL correctly.

NOTE: IF you try to save the query with the embedded function calls in it, MS Access won't let you as Design view does not know how to express it. Something that Microsoft will need to change in subsequent versions of MS Office Access!

Dear Access gurus,

We have an Access database that contains tables that get their data from
another financial system. We are creating a query which needs to link two
tables together. However, although they contain some similar information,
they don't have a primary key linking them to enable them to "talk together"
(and there are no intermediary tables to help achieve this either).

The issue we have is that in the 1st table (CTRONADM_AP_VOUCHERS) it
contains a VOUCHER_ID field of 9 digits (eg. 000000092), and in the 2nd table
(CTRONADM_QUEUE_DATA) it contains a OBJ_ID field of 12 digits (eg.
001000000092). These two fields need to link (as they are the only fields
that directly relate) but as they have different string lengths they won't!
The 1st field of 9 characters should match up with the last 9 characters of
the 2nd field (eg. it should ignore the first 3 characters which represent
the organisation code).

Someone from our finance software company has suggested we use a Mid
function to achieve this (but provided no other information). Although we
can understand how a mid function could extract the 9 characters using a
calculated field in a query, we're not sure how to use this to enable linking
of the tables to extract relevant data between the two tables.

Do we need to use an SQL command to achieve this, and if so, what would we
need to write in SQL to extract the relevant data and/or achieve a link
between the two tables? At present our SQL query code looks like this:

SELECT CTRONADM_QUEUE_DATA.QUEUE_ID, CTRONADM_QUEUE_DATA.USER_ID,
CTRONADM_QUEUE_DATA.CREATE_DATE, Now() AS Expr1,
DateDiff("d",CTRONADM_QUEUE_DATA.CREATE_DATE,[Expr1]) AS Expr2,
CTRONADM_WFPO_APPROVER_CODE.LONG_DESCRIPTION, CTRONADM_OBJECT_DATA.OBJ_ID,
CTRONADM_AP_VOUCHERS.VOUCHER_ID
FROM CTRONADM_AP_VOUCHERS, CTRONADM_OBJECT_DATA INNER JOIN
(CTRONADM_QUEUE_DATA INNER JOIN CTRONADM_WFPO_APPROVER_CODE ON
CTRONADM_QUEUE_DATA.USER_ID = CTRONADM_WFPO_APPROVER_CODE.CODE_ID) ON
CTRONADM_OBJECT_DATA.WF_ID = CTRONADM_QUEUE_DATA.WF_ID
WHERE (((CTRONADM_QUEUE_DATA.QUEUE_ID)="APRVVOU"));


Any assistance would be greatly appreciated

Regards

JD2

Yes, a non-equi join can only be done in SQL view. I can't
seem to unravel your query (especially in all caps), but a
simple example might be:

SELECT . . .
FROM CTRONADM_AP_VOUCHERS As V
INNER JOIN CTRONADM_QUEUE_DATA As Q
ON V.VOUCHER_ID = Mid(Q.OBJ_ID, 4)


It may not be as fast as a equi join because an index on the
OBJ_ID field can't be used.

[snip]
[snip]

I didn't miss your point, but since you already know the
answer, I didn't see any point to responding. If the OP
runs into a performance issue, then alternative approaches
can be discussed in a meaningful context.

On Wednesday, July 25, 2007 4:46 AM Jamie Collins wrote:

By 'you' I meant 'the group', for which I expect a near 100% non-
response rate <g>.


[Confused] But wasn't it you (Marshall) who raised the issue of
performance? What, am I not supposed to discuss a point you raised?
What would a meaningful context be, then?

For the record, I do no 'already know the answer': my knowledge of the
engine is scant, the issue of denormalizing for performance is
controversial, I don't know if I've made a misstatement, typo or
otherwise failed to make my point clear, etc. Perhaps I'm guilty of
over-analyzing, though <g>.

Jamie.

--

 

[Bob Barrows]

In fact, it can be done in MS Access, but it is not really
recommended, since the MS Access query interface is not designed to
accept function statements in your join definitions.

Well, that's not the real reason it's not recommended ... it's not
recommended because these types of joins, called non-equi joins, are usually
very poor performers since they will usually force table scans vs. the use
of indexes. However, sometimes they are the only way to get a job done so we
will recommend them when needed.

NOTE: IF you try to save the query with the embedded function calls
in it, MS Access won't let you as Design view does not know how to
express it. Something that Microsoft will need to change in
subsequent versions of MS Office Access!

Err ... that's simply not true as well. I have many saved queries that use
non-equi joins. The only restriction is that they need to open in SQL View
instead of Design View. This Design-view restriction has been present since
Access 95, so I tend to doubt that it will change in the future

I also have to wonder why you are resurrecting a thread that was resolved
three years ago and simply repeating the same advice that was given back
then. Didn't you read the stuff that you quoted?

 

[David W. Fenton]

Well, that's not the real reason it's not recommended ... it's not
recommended because these types of joins, called non-equi joins,
are usually very poor performers since they will usually force
table scans vs. the use of indexes. However, sometimes they are
the only way to get a job done so we will recommend them when
needed.

A non-equi join does not be definition prevent use of the evidence.
It's only a join on an expression that does that.