How to check for sure that a table is a validation table?

[magnusstefan]

For "reverse engineering" in an undocumented MS Access database, where I want to check which tables are validation tables, I do this:


After located the "suspects" (for instance a table called "ShippingAgents" which could relate to a field "ShippingAgents" in a table "Orders", I go to the main table, in this example "Orders", and in the properties for the field Shippingagents I check the tab "Lookup".

If there are something like this in the field "rowsource": SELECT [ID], [Business] FROM [ShippingAgents] ORDER BY [Business];
Then case is closed. The table ShippingAgents is a validation table for the field ShippingAgents in the table Orders!

Right?

My question: Is there anything else that should be checked if the lookup tab is empty, just to be sure? Could there also, for instance, be any kind of usage of VBA code creating "validation table relation", a relation not seen in the lookup tab, but somewhere else?

Or can I safely assume, that after checking the lookup tab, I know for sure if there is a "validation thing" or not between table X and Y?

[ItsMe]

I do not use Lookup fields in my DB's so you would never find anything under the lookup tab in my tables. What I would look at are the queries and the VBA code.

[orange]

As ItsMe said, I do not use Lookup fields in my DB's tables. I also tend to use lookup tables, and I think you'll find that most people do. You may find that researching
-RECORDSOURCES of Forms or
-the relationships that each table is involved in
will give you some additional info regarding tables.

Sometimes validation is done in vba routines.

You may need a variety of methods to resolve all "lookup" type validation.

I have a function for reviewing/identifying relationships by table. It could be a starting point.

It produces this sort of info:

RELATION NAME :BOOKBOOKAuthors
        FROM TABLENAME: BOOK  TO TABLENAME: BOOKAuthors
             FieldName: BookId  ForeignFieldName: BookID

[magnusstefan]

I do not use Lookup fields in my DB's so you would never find anything under the lookup tab in my tables. What I would look at are the queries and the VBA code.

OK. But what kind of queries do you mean? The only queries that I know of, related to look-ups, are queries directly related to forms, as shown in this example. http://btabdevelopment.com/how-to-us...lookup-values/

But if there are no forms in the database you are researching, what kind of queries do we look for then? C

ould also please explain a bit further what kind of VBA-code I should look for when searching for lookups already present in a database?

[orange]

When you Normalize your tables, (and/or apply referential integrity) your tables are related on PK--FK. PK and FK -primary and foreign keys are most often represented by long integers- which are not readily understood by a user.

Consider, a Product used in an OrderDetail record, where ProductID 12345 shows in OrderDetail. As in the example you linked to, having a query that involves the ProductID and the ProductName will effectively look up the ProductName from the Product table based on the ProductId.
So if ProductID 12345 identifies ProductName Northwind Traders Dried Plums, that is using the query as a lookup.

[ItsMe]

But if there are no forms in the database you are researching, what kind of queries do we look for then?

Queries in Access can be Objects that are named and saved. You can find these objects in the Navigation Pane. If you look at them in design view, you can look at their joins to determine relationships of tables. As mentioned by Orange, tables are typically joined on their PK and FK. Also, you can create your own queries using VBA code. VBA code is located within Modules. There can be code behind objects like Forms and Reports. Code behind Forms and Reports are stored within Class Modules. Class Modules can also be created as stand alone Modules; You can create name and save a Class Module without a Form or Report. You can also create name and save Standard Modules.

Could also please explain a bit further what kind of VBA-code I should look for when searching for lookups already present in a database?

I do not know that I would be too concerned with finding lookups. If I am going to take the time to document a DB, I will consider all of the VBA and be primarily concerned with relations. Lookups will often have a semantic meaning. So, depending on where and how you apply the term, it can mean different things. For instance, lookup tables in the back of a reference book may differ from a lookup formula used by a mathematician or differ from a lookup function in Excel.

I would recommend creating an ERD based on the PK's and FK's of the tables. If your DB has only tables and no front end, it is possible your DB is not a Relational Database and does not have any Foreign Keys.

[orange]

magnusstefan,

If you want to post a copy of the database, I would look at it and make comments/suggestions.

[magnusstefan]

OK. Unfortunately the example in the link never shows the actual SQL-statement produced. So I will ask you if this example is equivalent in your opinion:
In a simply sales database with standard design you have, among others, the tables Products and Categories. We want to make a simple Query to show the productnames, and the productcategory for each Product. But in the Products table the Productcategory are only shown in numbers (1, 2, 3...). The actual categorydescription is in the table ProductCategory where the two only columns is CategoryID and Categorydescription. The field CategoryID is of course also present in the table Products.

If we write this SQL-statement and runs it: SELECT Categories.CategoryDescription, Products.ProductName
FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID;
we will get the result we wanted. And we have got it using an INNER JOIN going from the field CategoryID in one table to CategoryID in the other, but showing the result not as the categoryID but as the actual Categorydescription.

Is this basically the same thing: we actually do a "lookup", but this time we do not use it for building a report? And normally we do not call it a "lookup" we call it a Query with inner join.

Yes?
No?
Both yes and no?!

[magnusstefan]

OK!

If we leave the forms for the moment, thinking of databases that have no forms (yet), can you please explain what you mean with "the relationships that each table is involved in
will give you some additional info regarding tables" in this case? Are you perhaps referring to the one-to-many aspect, and that the validation table should be on the "one-side", and the data table where some field need validation should be on the "many-side"?

Can you also give any clue to have to find the "validation snippet" in a longer section of VBA-code?

About this: "I have a function for reviewing/identifying relationships by table." That seems useful generally, and please explain further! But of which use are the results when you are "hunting" for possible validation tables?

[ItsMe]

...But in the Products table the Productcategory are only shown in numbers (1, 2, 3...). The actual categorydescription is in the table ProductCategory...

You are going to need to identify the PK's and FK's. You should draw a picture of the table on paper. Your picture will be an entity relationship diagram. For instance, an item that is a part of inventory would be an entity. You should diagram the various entities and their attributes. The tables should define the various entities and their attributes. Draw a diagram of the tables.

To me a query is a query. I try not to think of the term "Lookup" if I can avoid it. A query will retrieve records from a database. A question asked of a database will determine how the query is stated.

[magnusstefan]

"Queries in Access can be Objects that are named and saved. You can find these objects in the Navigation Pane. If you look at them in design view, you can look at their joins to determine relationships of tables. As mentioned by Orange, tables are typically joined on their PK and FK."
Yes, all that I know very well.

"VBA code is located within Modules. There can be code behind objects like Forms and Reports. Code behind Forms and Reports are stored within Class Modules. Class Modules can also be created as stand alone Modules; You can create name and save a Class Module without a Form or Report. You can also create name and save Standard Modules."
This I DO NOT know very well. I do not know it at all, basically. OK, but since I bought "Microsoft Acccess 2013 Programming by Example with VBA, XML, and ASP" some months ago, I will know remove the dust from it and try to find a moment now and then working through the beginning...

"Lookups will often have a semantic meaning. So, depending on where and how you apply the term, it can mean different things. For instance, lookup tables in the back of a reference book may differ from a lookup formula used by a mathematician or differ from a lookup function in Excel."

I have noticed that, and one of the purposes with this discussion, from my point of view, is to investigate to what extent, and under which circumstances there are useful agreed definitions of the terms "look up", "validation", "validation table", "lookup table", etc., in the context of MS Access design and actual usage of MS Access databases.

"I would recommend creating an ERD based on the PK's and FK's of the tables."
Good recommendation, but I already do that.

"If your DB has only tables and no front end, it is possible your DB is not a Relational Database and does not have any Foreign Keys."
I understand your point if you speak of databases in use, but I also speak about databases in the context of various example databases taken from books about databasedesign, MS Access itself or SQL-code. And those databases, not seldom lack frontends like forms or reports.

[magnusstefan]

"You are going to need to identify the PK's and FK's. You should draw a picture of the table on paper. Your picture will be an entity relationship diagram. For instance, an item that is a part of inventory would be an entity. You should diagram the various entities and their attributes. The tables should define the various entities and their attributes. Draw a diagram of the tables."

I already do all those things.

"To me a query is a query. I try not to think of the term "Lookup" if I can avoid it. A query will retrieve records from a database. A question asked of a database will determine how the query is stated."

I feel that your point of view is most sensible in this matter, I understand it and I agree. The problem now for me in this very discussion is for me to find exactly what it is that makes us call the example from the link, the one which ends in a report, for a "look up", and not the Query example. That is since, if I am correct, we actually use the same kind of code in both examples.

I also will add that, as far as I can see there are two basic ways to use terms like "look up" and "validation" in database design and practice. And the problem is that those two ways are at the same time clearly different and connected. The first usage of the term seems to be basically connected to the production of reports. There you call it "lookup" if you from one value (1,2,3) connect to Another value you actually want to show (high, medium, low) in your report. The second usage is where you want to restrict which values to use, and that is typically in a form (but can also be values you choose in value lists in a parameter report.)

Am I right?

[magnusstefan]

That is most kind. I will came back and do that. (Just want to prepare you that I actually want to show two databases and compare them. And that is partly as a preparation for understanding a third database, much bigger, in an MS SQL Server milieu!)

[ItsMe]

You have a lot of comments there. I see a couple of questions, too. If I can add anything to help ...

In my databases I will prefix table names with tbl. I will also prefix table names with lst. If a table will behave like a list, for example a list of states, the table would be named something like 'lstStates'. Notice how I did not use the word 'lookup'? I also avoided the word 'validation'. Where do you draw the line? I can think of a time when a person filled out a questionnaire and the question regarding gender only had two possible answers. Not too long ago, it was unimaginable that questionnaires would have more than two options when answering that question, let alone more than three options. Maybe I should change the name of my lstGenders tables to tblGenders. And I am not trying to be political here. I am just trying to make a point that things change over time.

It is just semantics to me. Call it what you want. I call them lists of information that do not change often. But I might use lstStates in one DB and use tblStates in another. Yet, in another database, I might use enumerations. If there was a need to use a specific state within the VBA code, I might want to use enumerations.

To me, tables in relational databases are relations. If you want to understand the database, understand the relations. If you want to understand an application, understand the Business Rules. The code behind should provide insight to the business rules. If you want to make it more complicated than that, you can study up on Universal Modeling Language. After you get halfway finished with your UML diagram for one application, I will have built, maybe, five desktop applications.
http://www.uml.org/

[orange]

magnusstefan,

Regarding post #8

If we write this SQL-statement and runs it: SELECT Categories.CategoryDescription, Products.ProductName
FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID;
we will get the result we wanted. And we have got it using an INNER JOIN  going from the field CategoryID 
in one table to CategoryID in the  other, but showing the result not as the categoryID but as the actual Categorydescription.

Yes.

More info on lookup table