Relationships Question (1 Viewer)

nstratton

Registered User.
Local time
Today, 05:25
Joined
Aug 30, 2015
Messages
85
I am creating a database where one of the functions is to display a contact rep's address.

I have created tables for the different components of the address (i.e. States and Countries) and have set the relationships to the contact table as appropriate. In the states and countries tables there is a two letter unique identifier (primary key) and the full name similar to below.
Code:
StateID  State
AL       Alabama
AK       Alaska

In the contact table, is there a way to show the full name and not the 2 letter abbreviation? This isn't necessarily a problem for states but for countries it would be useful to have the full name.
 

plog

Banishment Pending
Local time
Today, 05:25
Joined
May 11, 2011
Messages
11,611
Yes, but why?

Tables are the foundation of your database house. You don't ask the cement truck to die the concrete blue because that's goes best with the theme of your basement. Same for tables--they don't have to look pretty because you will be building on top of them.

Store the ID in the table, then whenever you want to display the full state name you use a query to pull it. Users shouldn't interact with tables, they should interact with form which then interact with tables. So there's no point in having the full name show at the table level.
 

nstratton

Registered User.
Local time
Today, 05:25
Joined
Aug 30, 2015
Messages
85
I should've known to use a query. I knew about keeping the end user away from tables but didn't think about how it doesn't have to look good. Makes sense to leave everything as is there since it will rarely, if ever be seen.

As a side question that I think still relates:
Would you be able to update the contact table if necessary doing this? I've had issues before using a multi-table query and not being able to update the recordset via a form.

EDIT
Since not every country is going to have a "state" per se, I would want to leave the state field blank in the contact table and only provide a country. However, when not adding anything to the state field and running the query, the record with the state field blank gets left out. I'm fairly certain this is a straightforward fix but I cannot wrap my head around it at the moment.
 
Last edited:

plog

Banishment Pending
Local time
Today, 05:25
Joined
May 11, 2011
Messages
11,611
I don't understand your question concerning updating, specifically why it would be done via a query. A single update of a contact's info (new phone number, new email, address change) would be done via a form which would directly be based on the table. A query doesn't come into play.

What scenario did you envision?
 

nstratton

Registered User.
Local time
Today, 05:25
Joined
Aug 30, 2015
Messages
85
I would have a form that displayed all the information: name, address, phone numbers, etc. Say the contact's business moves locations for one reason or another. I would want to be able to update the information from this form. The form would be based on a query with the contact table, state table, and country table (state and country so I could see full name instead of abbreviations).

I have been basing my forms off queries instead of tables since I am typically pulling information from at least 2 two tables such as I am doing now.
I understand what you are saying in basing the form off the table, that makes sense to do that but I lose the full name of state/country doing it that way
 

plog

Banishment Pending
Local time
Today, 05:25
Joined
May 11, 2011
Messages
11,611
Forms that allow interaction with data (add/edit/delete) should be based on tables. If you have a contacts table, it shoudl have a form. If you have a business table, it should have a form, if you have a state table...

1 table, 1 form.

When you are assigning a state to a contact, you would have a drop down on the Contact form. Since the form is for the contact, it would be based off that table. The drop down the user uses to select which state the contact is in however would be based off the state table. It would show the full state name, but store the state ID.
 

nstratton

Registered User.
Local time
Today, 05:25
Joined
Aug 30, 2015
Messages
85
So it is basically query-form to view info & table-form to edit info. That makes sense. This is only my 2nd database project ever so I'm still learning the typical conventions like that. Everything I have learned so far has been on the fly and what an experience it has been
 

plog

Banishment Pending
Local time
Today, 05:25
Joined
May 11, 2011
Messages
11,611
Yes. I'd actually say query-report to view data and table-form to edit.
 

DBApprentice

On Error GoTo AWF *****:
Local time
Today, 07:25
Joined
Dec 24, 2018
Messages
150
Yes. I'd actually say query-report to view data and table-form to edit.

Plog I was told to have Queries underlying Forms but since you are saying it should have Tables I got confused... :confused:
 

plog

Banishment Pending
Local time
Today, 05:25
Joined
May 11, 2011
Messages
11,611
Yup, and that's what I try and avoid with my advice. Which is why my rule is that forms that interact with data (add/edit/delete) is to have them be based on tables. You will never go wrong with that.

Queries can be used as the basis of forms that interact with data (add/edit/delete). However, search this forum for "operation must use an updatable query" and you will find tons of people who tried to base forms on queries and pulled their hair out trying to make it work. You can't use just any query as the basis of a form and people not well versed in the ins and outs of Access (e.g. the type who post on forums for help) often encounter unnecessary complications when they try.

So my advice is to just use tables. You can still create the exact forms you want and they just work every time.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:25
Joined
Feb 28, 2001
Messages
26,996
But just to bring up a variant point for consideration...

In a split database, regardless of whether the links to the back end are drive-letter/path or URS format, SOME times I have found it necessary to build a query that is, in essence, a SINGLE TABLE query. I.e. it has every field from the source table and no other information from other tables. Doesn't even need an ORDER BY or a WHERE clause.

You know you have this problem when you open the form linked to a BE table and the form asks where to locate that table. When you check the links, they are right. You can open the linked table from the navigation panel. But the form cannot. IF, however, you build that query and make IT the recordsource for the same form, it works silently without error. There is no issue of non-updatable queries.

I don't specifically recommend doing it either way. I'm just pointing out that IF Access gives you that "where is the table" problem when using a table as your recordsource, the solution might be a single-table query that quietly finds the table in the BE without an annoying dialog pop-up. I was never able to find out why that happens, but the solution is simple enough.
 

isladogs

MVP / VIP
Local time
Today, 10:25
Joined
Jan 14, 2017
Messages
18,186
I'm also going to express a different viewpoint.

Forms (and reports) should just contain the fields required for their purpose.
The only times you should base a form on a complete table is when you require all the fields and all the records from that table.
At other times, forms should be based on a SQL statement or saved query containing just the fields required.

Doing this means the form will load faster and be more responsive in use.
The SQL statement may be based on one or more tables as required.

Clearly you do need to ensure a query/SQL statement made from several tables remains editable but as long as you follow specific guidelines that is rarely an issue. A form/subform approach should be used to manage such cases

Allen Browne has an excellent guide to the main causes of read only queries: http://allenbrowne.com/ser-61.html
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:25
Joined
Feb 19, 2002
Messages
42,970
I agree. Forms should be based on queries to reduce the columns and rows selected to the absolute minimum. Occasionally, you might also want to join to a lookup table. If you do this, just be careful to lock the form fields that show the lookup data so that the user doesn't accidentally change it.
@nstratton,
I think you should be using combo boxes on your form. That way, the combo can be bound to the ID field but show the long field.
 

Users who are viewing this thread

Top Bottom