I have a query based on two tables joined (inner join) by a text field
"Invoice" and a text field "Vendor". When I look at the results, I am
getting extra records because the query is not returning exact matches on the
invoice field. For example, the query is returning invoice "050205" from the
first table joined to Invoice "50205" in the second table. I don't
understand how the query is seeing this as an exact match. The tables are
Pervasive Data tables linked using the Timberline ODBC driver. Does anyone
know a work around? (When completed, this query will be a union query
combining 5 related tables from 6 data sources, so I really need the exact
match to work inside this query.)

Here's the SQL statement:

SELECT "ALD" AS DataFolder, MASTER_APM_INVOICEAld.Vendor,
MASTER_APM_DISTRIBUTIONAld.Invoice, MASTER_APM_INVOICEAld.Accounting_Date,
MASTER_APM_DISTRIBUTIONAld.Status, MASTER_APM_DISTRIBUTIONAld.Description,
MASTER_APM_DISTRIBUTIONAld.Expense_Account, MASTER_APM_DISTRIBUTIONAld.Amount
FROM MASTER_APM_INVOICEAld INNER JOIN MASTER_APM_DISTRIBUTIONAld ON
(MASTER_APM_INVOICEAld.Invoice = MASTER_APM_DISTRIBUTIONAld.Invoice) AND
(MASTER_APM_INVOICEAld.Vendor = MASTER_APM_DISTRIBUTIONAld.Vendor);

On Thu, 24 Aug 2006 11:41:01 -0700, PEGPgrm
<(E-Mail Removed)> wrote:

>For example, the query is returning invoice "050205" from the
>first table joined to Invoice "50205" in the second table. I don't
>understand how the query is seeing this as an exact match. The tables are
>Pervasive Data tables linked using the Timberline ODBC driver. Does anyone
>know a work around?

Not familiar with Pervasive, but it certainly appears as if Access is
interpreting the field as a number rather than as a Text datatype.
Check the table defininition and the linking process to ensure that
the fields are being seen as Text.

John W. Vinson[MVP]

Thanks, John. I did check that very first thing, it is set as text. Some of
the records contain alpha characters in the Invoice field, so it clearly
isn't a numeric field.

"PEGPgrm" wrote:

> I have a query based on two tables joined (inner join) by a text field
> "Invoice" and a text field "Vendor". When I look at the results, I am
> getting extra records because the query is not returning exact matches on the
> invoice field. For example, the query is returning invoice "050205" from the
> first table joined to Invoice "50205" in the second table. I don't
> understand how the query is seeing this as an exact match. The tables are
> Pervasive Data tables linked using the Timberline ODBC driver. Does anyone
> know a work around? (When completed, this query will be a union query
> combining 5 related tables from 6 data sources, so I really need the exact
> match to work inside this query.)
>
> Here's the SQL statement:
>
> SELECT "ALD" AS DataFolder, MASTER_APM_INVOICEAld.Vendor,
> MASTER_APM_DISTRIBUTIONAld.Invoice, MASTER_APM_INVOICEAld.Accounting_Date,
> MASTER_APM_DISTRIBUTIONAld.Status, MASTER_APM_DISTRIBUTIONAld.Description,
> MASTER_APM_DISTRIBUTIONAld.Expense_Account, MASTER_APM_DISTRIBUTIONAld.Amount
> FROM MASTER_APM_INVOICEAld INNER JOIN MASTER_APM_DISTRIBUTIONAld ON
> (MASTER_APM_INVOICEAld.Invoice = MASTER_APM_DISTRIBUTIONAld.Invoice) AND
> (MASTER_APM_INVOICEAld.Vendor = MASTER_APM_DISTRIBUTIONAld.Vendor);
>

On Thu, 24 Aug 2006 11:41:01 -0700, PEGPgrm
<(E-Mail Removed)> wrote:

>The tables are
>Pervasive Data tables linked using the Timberline ODBC driver.

You might want to check with Timberline then - the symptom certainly
sounds like it's inappropriately truncating leading zeroes as part of
the linking process.

I have to really question the original database designer's choice of
having two distinct ID's, both numeric in appearance, differing only
by a leading zero - that's tough for humans *or* computers!

John W. Vinson[MVP]

That's the problem with having users who aren't programmers. I guess we'll
have to banish them all to Siberia. This particular user is also fond of
putting special characters in key fields, like vendor id = H & R Block or
vendor id = **This**That**. I'm just a lowly IT grunt, so I have to deal
with what I get!

I will try calling Timberline, tho they will try to fob me off on a
consultant!

Thanks for your input.

"John Vinson" wrote:

> On Thu, 24 Aug 2006 11:41:01 -0700, PEGPgrm
> <(E-Mail Removed)> wrote:
>
> >The tables are
> >Pervasive Data tables linked using the Timberline ODBC driver.
>
> You might want to check with Timberline then - the symptom certainly
> sounds like it's inappropriately truncating leading zeroes as part of
> the linking process.
>
> I have to really question the original database designer's choice of
> having two distinct ID's, both numeric in appearance, differing only
> by a leading zero - that's tough for humans *or* computers!
>
> John W. Vinson[MVP]
>