Solved Indexing a foreign key in the child table

[kentgorrell]

relationships can be linked in completely different ways depending on the goal of the query

I don't get what you mean, can you give an example?

 

[Pat Hartman]

A join between two tables/queries/views is sometimes referred to as a relationship but that is not the same as a permanent relationship which is used to enforce RI and is defined using the relationships window.

It is the joins you create in a query that ebs called relationships. Joins are not permanent. They do not enforce RI, the only rule is that the joining columns be of compatible data types. Frequently we have data that needs to be cleaned up or converted so we might create unusual joins. We might join firstName in your input table to lastName in your permanent table because the data entry clerk was sloppy about how he entered data in his spreadsheet, so this join helps us to ferret out duplicates. Or you might join StateAbbr to the abbreviation or to the state name, again to clean up sloppy data entry. I had one client who was sloppy about how Addr1 and Addr2 were used. Sometimes Addr1 contained the name of the building or the company and Addr2 contained the street address and other times Addr1 contained the street address and Addr2 contained the name of the building. I took me almost more time to clean up this client's initial data than it took to create the application. The problem is that most clients don't have the creativity to understand how to clean up their name and address spreadsheets so it is really hard for me to avoid doing it for them. I have learned to charge separately though.

 

[Pat Hartman]

I really only work with SQL Server dbs so I haven't used Lookups since Access Web Apps.

And you do not want to start now. Table level lookups require table maintenance to modify them when they use value lists as their RowSources. That is an extra burden on application maintenance. When they use tables, they are treated like every other relationship.

I think Jet/ACE limits the number of indexes/relationships for a table to 32 and the number of columns in an index to 10 (or something like that). SQL Server has larger limits.

Unless I am 100% certain that I won't need to modify a value list, I use a table. The only exception is if the items in the value list control program flow. In that case, I have to modify the program to change the program flow so it makes no difference if I also have to modify the value list.

Long, long ago, in a technology i don't use any more, I solved the problem of "simple lookup tables". I created a mini-app that lets me use one table, two forms, and two reports to manage ALL of my simple lookup tables. By simple, I mean, they don't have "extra" data. The columns are always consistent
TableID
ItemID
Seq
ShortName
LongName
ActiveYN
LastChangeDT
LastChangeBy

This mini-app has been recreated in several different platforms over the years and currently exists as Access forms/reports. You can use Jet/ACE as your RDBMS or whatever floats your boat.
I create a query for each table. The query sorts by ActiveYN, Seq, LongName, ShortName. Sorting by the Active flag first pushes the inactive items to the bottom of the list but doesn't remove them. If the Seq is blank, the list is sorted alphabetically. The user makes the choice. Sometimes they want to have most frequently used option at the top, other times they just want the list to be alpha to make it easy for the user to find items. You do need code to prevent the user from selecting an inactive item if he adds a record or updates the ItemID. Changes to existing records do not check the Active flag so as long as the user doesn't change the selected ItemID, the old value can stay with the record.

Table Maintenance Mini-app

When you have several lookup tables in an application, it becomes a pain to create separate tables and maintenance forms to manage the contents. This sample is an application that I first developed over 40 years ago, long before Access was a twinkle in Bill's eye. It was constructed using...

www.access-programmers.co.uk

 

[kentgorrell]

A join between two tables/queries/views is sometimes referred to as a relationship but that is not the same as a permanent relationship which is used to enforce RI and is defined using the relationships window.

so when he said "Relationship" he really meant "Join".
a relationship is a database constraint while a join is just SQL and can be adhoc
much easier to follow if we, as professional developers, used the correct nomenclature.

 

[kentgorrell]

Table level lookups require table maintenance to modify them

If you can't modify them using VBA then I can't use them. In my applications, schema modifications are all done by code. No-one manually hacks into a BE to modify objects. That would be contrary to the principles of SDLC.

Access forms do some pretty amazing but unintuitive things. LIke allow you to sort a continuous or datasheet form on a column in a combo box that doesn't exist in the form's RS.

For example, a combo box bound to Customer ID, column(0) width = 0 so you don't see the ID while Customer Name in Column(1) is visible but not in the form's recordset only in the control's rowsource. Yet Access allows the user to sort on Customer Name. Even if the Customer ID is not a Lookup field.

that's amazing! and another example of why we put indexes on FKs.

 

[kentgorrell]

sloppy about how Addr1 and Addr2 were used.

I'm rather inclined to use one address field which is a free for all that may include Street Name, Street Number, Building Name, PO Box or whatever. This field allows multiple lines.
Then separate fields for City, Post/Zip Code, State and Country which are validated from lists.

 

[Pat Hartman]

Two fields gives the user better options for print layout without including crlf embedded in the text.

 

[kentgorrell]

Two fields gives the user better options for print layout without including crlf embedded in the text.

good point although I'm so far not having an issue with Chr(!0), Chr(30) being embedded.

With two fields I used to concatenate them with a vbNewLine anyway and use the control's Can Grow on reports to handle layout. Do you have a better method to handle printing when you have two fields but one is empty?

 

[Pat Hartman]

To handle concatenating multiple fields one or more of which could be null, you make use of the way the plus (+) acts differently from the ampersand (&) when used as concatenation operator.

a + b ends up as null if either a or b is null whereas
a & b ends up showing a or b or both.

FirstName & (" " + MidName) & " " & LastName -- This renders the name with all three parts or with just two parts without ending up with an extra space.

 

[kentgorrell]

Wow, I just had a flashback to when I first learned about concatenation with + and &. I recall when and where I was. I was on a beach. It was 1994. Same time I learned about 'Or Is Null'. I just can't remember the name of the book. More of a booklet as I recall. I keep thinking Speed Ferret but that was a find and replace utility.