Need to design best way to lookup data in query (rather than Dlookup)

[larpup]

I have a table with two fields that are indexed.
Table 1
Field 1 = ORD
Field 2 = LAX
Field 3 = $32.00
I have another table that needs to lookup the two fields from a
concatenated standpoint and return data (field 3) from table 1.
Table 2
Field 1 = ORDLAX

I'm a newbie at design in Access. Is the best way to design this is
having a compound key (field 1 and field 2) or, when creating a form
based on table 1, create a third field (in the after update event)
concatenate Field 1 & Field 2 .(The form would be based on table 1 of
course.)


Any advice will be greatly appreciated.

Lar

 

[Douglas J Steele]

Definitely use a compound key. Storing computed data is never a good idea.
If you found that a row had ORD for Field1 and LAX for Field2, but Field3
was ORDSFO, which one would be correct?

 

[Tom Ellison]

Dear Lar:

What you have is sufficient, but not ideal.

If you had two columns in Table 2 for this instead of one, you could build a
relationship and even indexes on it. Depending on what you do with it,
there could be some advantages to this. I'll cover those in a subsequent
post if you wish.

Here is a basic query answer:

SELECT *
FROM [Table 2] T2
INNER JOIN [Table 1] T1
ON T1.[Field 1] = LEFT(T2.[Field 1], 3)
AND T1.[Field 2] = MID(T2.[Field1], 4)

Tom Ellison

 

[larpup]

Doug,

Thanks for the response. Field 3 would have to be ORDLAX as it is a
concatenation of the Field 1 and Field 2. This is how my old database
worked and trying to get out of that way of thinking.

To answer your question, another form would have an entry of field 1
and Field 2, that is how it would look up the data.

Hope I'm being more clear here.

Lar

 

[larpup]

Tom,

The form looking up the data has two text fields... Say ORD in field 1
and LAX in field 2. It must lookup the record in Table 1 and return the
record...

In my old database I've used concatenated fields as the key field as
this is the unique identifier.

Lar

 

[Douglas J Steele]

My point was that if you construct an artificial key by concatenating the
two fields, it is possible for them to get out of synch.

As fellow Access MVP John Vinson likes to say "Storing calculated data
generally accomplishes only three things: it wastes disk space, it wastes
time (a disk fetch is much slower than almost any reasonable calculation),
and it risks data validity, since once it's stored in a table either the
Total or one of the fields that goes into the total may be changed, making
the value WRONG."

 

[John Vinson]

Tom,

The form looking up the data has two text fields... Say ORD in field 1
and LAX in field 2. It must lookup the record in Table 1 and return the
record...

In my old database I've used concatenated fields as the key field as
this is the unique identifier.

Some database software requires that you have a single field. Access
does not. You can link on one field, or two, or even on TEN fields.

Keep the fields separate, and use *both* of them to join in your
query. There is no benefit or need to create an artificially
concatenated field.

John W. Vinson[MVP]

 

[larpup]

Thank you very much Mr. Vinson,

You have shed good light on this subject for me. Two last questions
regarding the table I need to lookup data from.

So, I can make a single select statement, query parameter 1 (ORD) *AND*
parameter 2 (LAX) (*AND* parameter 3 - Customer ID (forgot to mention
that)) and quickly retreive the needed record.

Last question('s). I could have 10,000 records in the table which I
need to retrieve data from. Would it be wise to index all three fields
separately or would it be best to make a compound key based upon the
three fields in order that no duplicates are created?

Thank you for your advice. This is helping me make the transition
(slowly).

Regards,

Lar

 

[Douglas J Steele]

To ensure that no duplicates are entered, you must have a unique index based
on the 3 fields.

Depending on what sorts of queries you're going to be doing against the
data, you may want to add additional fields as well.

For example, assume that your unique index has Field1, Field2 and CustomerId
in that order. If you're going to sometimes be querying only on CustomerId,
without supplying Field1 and Field2, you should have an additional index on
CustomerId. If there's a chance that you may be querying without supplying a
value for Field1, you might want another index on Field2, or perhaps the
combination of FIeld2 and CustomerId.

 

[John Vinson]

Thank you very much Mr. Vinson,

You have shed good light on this subject for me. Two last questions
regarding the table I need to lookup data from.

So, I can make a single select statement, query parameter 1 (ORD) *AND*
parameter 2 (LAX) (*AND* parameter 3 - Customer ID (forgot to mention
that)) and quickly retreive the needed record.

You can do that perfectly well. For that matter, if you have two
tables - one with these three fields, and say a rates table with two
airports and information about the routing or fares between them, you
can create a Query joining the two tables, joining Origin to Origin,
Destination to Destination. If there is a suitable two-field index on
the routes table, this would look up the data for that pairing very
efficiently.

Last question('s). I could have 10,000 records in the table which I
need to retrieve data from. Would it be wise to index all three fields
separately or would it be best to make a compound key based upon the
three fields in order that no duplicates are created?

10,000 records is small. 10,000,000 is getting pretty big. <g>

As Douglas notes, you have a fair bit of flexibility in the indexing.
Any field which is used on its own for searching (e.g. you might want
to find all records with LAX as the destination) should have a
nonunique Index; the combination of fields which uniquely identify a
record should have a Primary Key index. This will make it unnecessary
to separately index the first field in the combination (Access will
just use the primary key index), but the other fields should have
their own separate indexes.

Access will *work* without the indexes - it'll just work a lot faster
and more efficiently with them!

Thank you for your advice. This is helping me make the transition
(slowly).

Good luck, and don't hesitate to post back.

John W. Vinson[MVP]

 

[larpup]

Douglas,

Thanks for the well given advice. Taken and works perfectly. Thank you.

Lar

 

[larpup]

John,

I very much appreciate your and everyone else's advice. I have applied
all that I have learned and my program is working perfectly.

Because of my previous database, I have quite a learning curve. I can
see that once an understanding and overview is achieved, it is a lot
easier to accomplish the same (and more) in Access.

Again, thank you for taking the time for this post, it is greatly
appreciated.

Lar