Range relationships

[Capronzz]

FWIW - The classification of data is a pretty basic function on data
processing. Normally one has a table that specifies the range (argument) for
any given clasification: Thus
0 to 5 = A
6 15 = B
16 35 = C
36 38 = D and so forth.

Access file relationships are only partially implemented in that only
records having an EXACT (=) match are enabled to establish a relationship.

Having a transaction file of several thousand records and a classification
file of just under 70 and wishing to relate and classify (and recalssify) the
transactions I proceded as follows:

For the Classification file:
Sort by upper limit of the range
Serial number each record
Set up a one record "count" file
Set up one record 'Classification being worked on' file.
Query to 'pull' the first Clasification record.

For the Transaction file:
Query to process only those records not yet classified

Procedure:
Establish file relationships as if the exact match condition were to be
used.
- Open the code and change the match '='
to '<'.

Query to 'find' Transaction records less than the upper limit and then
Update Query to classify those records

Increment the 'count' by 1
Query to pull the next Classification record into place.
Use Macro to 'loop' through the procedure. Note that records classified
will now not be processed leaving higher values than the prior Classification
record.

Housekeeping and other considerations:

The upper limit of the range will want to be the start of the next
classification
Allow no 'gaps' in the Classification file
Since Access will disallow looping more that 25 times you will have to
Run the procedure multiple times or find a way around the limitation.
When setting up the macros it helps to sprinkle the Transaction file
with some 'exact' matches and run that way until you have everything working.
Then change the '=' to '<'. Thereafter you will not be able to access the
file relationships screen.
When the Classification file is changed you must resort and sequence
number it.
Set up a 'Query to remove the classifications to aid in debugging and
reclassification.

In essence what I have done is simply implemented the old 'flat file'
methodology of punched card days.
There are some messy aspects to the above and I have not cleaned
them up. Couldn't get 'Next Record' working so I used the counter method.

Still -- I have my data classified and can reclassify when needed. Serial
numbering of Classification file I do manualy at this point - which is sort
of bad.
I have to laugh though - when I started in the business in 1952
classificaiton of data was as simple as sorting the two files and loading up
the Collating Reproducing Machine. and pushing the start button.

 

[KARL DEWEY]

I think this simple way will do what you want. Here you have a
[RateTable-Mileage] with fields Rate, [MinMiles] And [MaxMiles].
MinMiles MaxMiles Rate
0 500 0.2075
501 1500 0.1582
1501 999999 0.1521

SELECT Travel.Name, Travel.Mileage, [RateTable-Mileage].Rate
FROM Travel, [RateTable-Mileage]
WHERE (((Travel.Mileage) Between [MinMiles] And [MaxMiles]));
You would have table with --
Low High Clasification
0 5 A
6 15 B
16 35 C
36 38 D

 

[John W. Vinson]

FWIW - The classification of data is a pretty basic function on data
processing. Normally one has a table that specifies the range (argument) for
any given clasification: Thus
0 to 5 = A
6 15 = B
16 35 = C
36 38 = D and so forth.

Access file relationships are only partially implemented in that only
records having an EXACT (=) match are enabled to establish a relationship.

That statement is incorrect. Access does indeed support "non equi joins";
however, you must go from the query design grid to the SQL window to implement
them.

SELECT maintable.fields...,
RangeTable.Classification
FROM maintable INNER JOIN RangeTable
ON maintable.argument >= RangeTable.Low
AND maintable.argument <= RangeTable.High
WHERE <other criteria>

works just fine.

 

[Capronzz]

Thank You John and Karl.
John, I would stand by my statement that "Access file relationships are only
partiallty implememted".
That it is neccessary to branch to another language (SQL) makes the point.
IN ACCESS the equal case is implemented. The other cases are not.
SQL is not Access. It is another language.
That Access uses or is compatable with SQL is irrelevant One uses a data
base system in part to avoid having to deal with a 'lower level' language.
Nonetheless - thanks for the procedure and if I need to classify again I''ll
fool around with SQL although I may try to use the 'Between' in a query in
combination with the 'Next' record. (Which I couldn't get to work for me) I
really don't want to have to get into SQL if I don't have to.

Again thanks for your input
zz

 

[John W. Vinson]

That it is neccessary to branch to another language (SQL) makes the point.
IN ACCESS the equal case is implemented. The other cases are not.
SQL is not Access. It is another language.

Well... that's not strictly true. The query design grid is not the query; it
IS JUST A TOOL to help you build SQL. In Access (not SQL/Server), the *real*
query is in SQL.

Note that SQL is not synonymous with the Microsoft product SQL/Server; it's
the language used in many database programs, including Oracle, MySQL, Sybase
and others. There are public standards for the definition of what constitutes
a SQL language; the various software companies may have different
interpretations or extensions of these standards, but you cannot assert that
SQL is "another language".

 

[Capronzz]

Well... that's not strictly true. The query design grid is not the query; it
IS JUST A TOOL to help you build SQL. In Access (not SQL/Server), the *real*
query is in SQL.

Note that SQL is not synonymous with the Microsoft product SQL/Server; it's
the language used in many database programs, including Oracle, MySQL, Sybase
and others. There are public standards for the definition of what constitutes
a SQL language; the various software companies may have different
interpretations or extensions of these standards, but you cannot assert that
SQL is "another language".

John:
The Access Manual that came with my purchase states (Access 2000 Page 116)
"SQL is a powerful database language used in -----" "If you know SQL you
can-------"
Not to be pendantic but two different things, Access and SQL, cannot be the
same thing.
The procedure you gave me was an SQL procedure.
I imagine VBA is used to write SQL?, but SQL is not VBA.
I would venture that most 'higher level' languages utilize 'lower level'
programming systems in their construction.

I noted that Access was implemented for only one case: that of an exact
match.
Were Access to have a range option added to it's file relationships I'm sure
it would be via a modified or new window where the appropriate parameters
could be entered. Such an approach would be consistent with the concept of
Access.

For what it's worth note tha my manual does not include reference to the SQL
commands you gave me.

Thanks for the comentary though. I would say we come at this issue from
different perspectives.
zz

 

[John W. Vinson]

Not to be pendantic but two different things, Access and SQL, cannot be the
same thing.

<shrug>

Of course they're not. Access and VBA are not the same thing either.

Both SQL and VBA are languages contained within, and available in, Access.

Access is not a query language. Access is a programming environment. Among the
tools available within that programming environment are Visual Basic for
Applications for Access, a JET SQL language interpreter, a query grid which is
an alternative method of creating JET SQL queries, and numerous other tools.

All I'm saying is that SQL is *NOT* a "foreign language" to Access. It's a
full part of the program.