Find record more table.........

[nicolareina]

good morning everyone.
I will briefly explain my problem.

HELP

in my database I have 2 tables (TBL_1 and TBL_2)

In the "Lista Sottoposti" form I asked if there was the possibility of finding the records in TBL_1 and TBL_2.

Once the record is found, clicking on the "Open" link will open the "Dettaglio Sottoposto" form

I have already tried with a union query, the problem arises that when in TBL_1 and TBL_2, there is a person with the same ID, then clicking the open button gives me an error.

 

[Gasman]

And that error is?
Try compiling the db first, before trying to run it.

I get variables not defined.

 

[Gasman]

Crossposted at https://www.accessforums.net/showthread.php?t=90966

 

[The_Doc_Man]

Part of the problem is that you have two identical tables. I looked. They are field-for-field identical. Structurally, there is no difference between TBL_1 and TBL_2. It is not at all clear WHY you have two tables for what is (structurally) the same thing. The implication is that they ARE the same thing but they represent different stages or states or phases of whatever it is that you are doing.

Whatever is the difference in meaning/purpose between the two tables, this is a theoretical mistake of the highest order. If there is a difference in status or something like that, what SHOULD be done is that instead of having TWO tables, you have ONE table and add a field so that you know a value that shows whatever it is that makes the two tables different.

Explain why you have the tables. However, here is the short answer to your original question: If you resolve this structural problem, your solution will only have one table to search, which has to make your technical problem easier.

Now, here is a SUGGESTION: Look up "database normalization" because that is the nature of your structural mistake. Your tables are not normalized and that is WHY you have to work harder on your search. Failure to properly normalize tables will multiply the amount of work you have to do in order to keep the tables properly updated. As an example, if you suddenly realized you needed a third table, you would now have THREE times the amount of work to maintain the tables. But if that status that required the third table just became another field in the (only) table, you modify that one table to contain the extra field and you are done.

Since this IS a database forum, you can look up "normalization" using the SEARCH button near the top right of the form. (Has a magnifying glass symbol.) On the other hand, if you want to look this up on the web, you will have to use "database normalization" because on the general web, you would "normalization" associated with half-a-dozen different topics, such as databases, mathematics, chemistry, medicine, and international relations. Start by reviewing .EDU sites but once you have the idea, go on to .COM sites. The reason I don't suggest the .COM sites as a starting point is that all too often, they have something to sell you that would be a distraction to your main question of normalization.

 

[plog]

You need to put aside this issue and focus on your real problem--you haven't set up your tables/fields properly. That process is called database normalization:

Database normalization - Wikipedia

en.wikipedia.org

Give that link a read. Then google a few tutorials, then apply what you have learned to your data. Then either the issue you posted about goes away or you can revisit it after you have properly structured your data.

Even with the language difference, when I opened Table1 I immediately knew things were wrong:

1. Multiple Yes/No fields. This is a sign you need a new table instead of jamming the data into the existing one. It was confirmed when I pasted one of the field names (Lunedi) into google and it confirmed you had named fields after days of the week. Field names should not be named as such.

2. Groups of fields. You shouldn't have fields named similiarly but suffixed with either numbers or names. This too requires a new table like issue #1. After learning the days of the week in Italian I kept seeing those names used in fields all over your table used as suffixes to field names. This is wrong too.

So, again, fix your table and then work on your forms. Otherwise you are just building a house on an improperly structured foundation.