Numeric Joins in Queries

[Kitty]

I am attempting to run a query against our corporate
Oracle database. In that database, the branch field is a
decimal.

I want to have a table where I can input the branches I
want. That values in the source table would be used as
parameters for several queries in an application.

I've tried formatting the FieldSize in the source table as
decimal with the same properties as that in the Oracle
database.

The query will not return records. The Access help file
states that numeric fields can be joined only if the
FieldSize properties are Byte, Integer or Long Integer.

Does this mean I'm out of luck, and will have to manually
input the branches I want into each query definition (the
branches change each month, and there are several queries
involved).

Thanks for any help you can provide.

 

[Van T. Dinh]

Do you mean the BranchID is actually a decimal number and
not Text that looks like a decimal number?

I can't see of any reason for a decimal number oe even
Text that looks like decimal number??? Unless the
database designer use the integral (whole) part as the,
says, StateID and the fractional (decimal) as the actual
BranchID in the State. Even in this case, it is a bad
design since it violates the First Normal rule.

Check and make sure (and let us know) the Field type in
Oracle and what each part means before proceding further.

HTH
Van T. Dinh
MVP (Access)

 

[Kitty]

When I use Access to look at the table design of the
Oracle table, for Branch it says...

FieldSize = Decimal
Precision = 3
Scale = 0
Decimal Places = Auto

All FieldSize properties for numbers in the database are
decimal with varying precisions.

It's a pain as the "find unmatched" and "find duplicates"
queries won't work on those fields, either.

 

[Van T. Dinh]

Actually, I don't trust Access interpretation of data type. I find that on
occasions Access interprets both MS-SQL Server int and seeded int as
AutoNumber (seeded int is correct but plain int is not).

Use Oracle software to (similar to MS-SQL Server's Enterprise Manager) to
check the data type.

What is the meaning of the decimal number?

 

[Kitty]

I don't have access to the Oracle software. The data
dictionary provided by the database folks describes the
field as decimal.

I'm not sure what you're asking for with the "meaning" of
the decimal number.

 

[Van T. Dinh]

As you noted, using a decimal number to identify a "branch" (branch
office???, tree branch??? <smile>) is at least unusual if not outright odd.
Whoever devised this scheme must have attached some meaning to the decimal
number. Like I posted previously, the designer might have meanings attached
to different parts of the decimal number, i.e. the integral part means
something and the fractional part means something else and together they
identify the "branch".

 

[Kitty]

Branches are locations. One to three digit numbers
identify them. There are no "real" decimals involved;
they're all whole numbers.

I found a workaround--Instead of joining the branch table
in my Access database to the table in the Oracle database,
I put [tblBranch]![Branch_Nbr] in the criteria of the
query.

Works that way, and I can use the table as criteria for
multipile queries.

Thanks for your help.

 

[Van T. Dinh]

You see now why I asked you to check in Oracle directly
rather than via Access because Acess may interpret the
data type incorrectly. Most likely, the Field is of a
whole number data type in Oracle and not decimal...

Glad you found a solution but I am sure you can use even
decimal in joins since decimal is really a scaled whole
number provided that the linking Fields from both Tables
are of the same type!

HTH
Van T. Dinh
MVP (Access)