Solved Trying to query two tables but get too many records in results (1 Viewer)

[mkdrep]

I have (2) tables as shown in (tables.jpg) which have a contact name whose ID number is the same in each table. I have drawn a dotted arrow between each ContactID, which is a FK for [tbl-Contacts].[ContactID].
If I run a separate query using the ContactID # 8311 on one table at a time, my result is (40) calls for qry_AIAs-ContactNotes1 and (44) records in qry_JPNotes.

The issue arises when I want to find the calls notes in each table for ContactID. qry-Test-Calls-AIA is the underlying query and it results in (1760) records.

Hopefully there is an easy answer to this issue.

Thank

 

[jdraw]

I recommend you show the SQL for the queries. Or, better still, provide a copy of the database with enough data and instructions to repeat the queries.

 

[plog]

The easy answer is your JOINs are multiplying your results. If you have 3 records where ID=7 in tableA and 5 records where ID=7 in tableB and 2 records where ID=7 in tableC your final query is going to have 70 (7*5*2) records for ID=7. THat's whats happening to your records.

To really help we need answers from you: What are you trying to accomplish big picture. Don't talk about the steps you've taken that haven't worked, or even what those 2 sub-queries are suppose to help with in that aim (because my gut tells me they themselves are flawed). Tell us in plain english what you hope to end up with.

 

[mkdrep]

The easy answer is your JOINs are multiplying your results. If you have 3 records where ID=7 in tableA and 5 records where ID=7 in tableB and 2 records where ID=7 in tableC your final query is going to have 70 (7*5*2) records for ID=7. THat's whats happening to your records.

To really help we need answers from you: What are you trying to accomplish big picture. Don't talk about the steps you've taken that haven't worked, or even what those 2 sub-queries are suppose to help with in that aim (because my gut tells me they themselves are flawed). Tell us in plain english what you hope to end up with.

Thank you for your answer. I knew I was multiplying records but I didn't understand why.
All I'm trying to do it is keep track of all the call notes we have made for one individual. Unfortunately, I have call notes for an individual in each of the two tables. In essence there are (40) call notes in one table for an individual person and the same person has (44) call notes in the second table. I just want to get all (84) call notes in one query when my personnel want to see the call notes in one place.

 

[plog]

I just want to get all (84) call notes in one query when my personnel want to see the call notes in one place.

Most likely that means your notes need to all be in the same table. Why aren't they? What are the structures/fields of the tables the notes are in now?

 

[GaP42]

Given the current situation, with notes in two tables, you could have two separate queries and then combine into one report. I notice that you don't have a date by which to sort the notes either.
If the notes are to be presented in a form then either your will have two subforms - one of each query, or a subform based upon a UNION query - but this subform will have no valid sort order without a date for each note and the subform will not allow additional notes to be recorded.
Best to take on board @plog 's suggestion - combine the notes tables, ensuring there is a date/time assigned to the notes made.

 

[The_Doc_Man]

I knew I was multiplying records but I didn't understand why.

The syntax of SQL is a little bizarre when you bring in multiple tables. That is because SQL was designed based on SET theory, which deals in combinations and permutations (among other things).

If you write: SELECT bunch-of-fields FROM Table1, Table2 then you get what is called a Cartesian JOIN or a permutation JOIN. It takes every record from table 1 and pairs it off with every record in table 2 to give you every combination.

If you write something more like: SELECT bunch-of-fields FROM Table1 AS T1, Table2 AS T2 WHERE T1.X = T2.Y then you get a filtered JOIN based on the WHERE clause. However, IF it happens that a particular value is duplicated in either table, you will STILL get every combination that matches up according to the stated matching rules in your WHERE clause..

You can ALSO get this effect with SELECT bunch-of-fields FROM Table 1 AS T1 INNER JOIN Table2 AS T2 ON T1.X=T2.Y and that is called an inner join. IF both T1.X and T2.Y are unique, then you get only one combined record per value. In theory, you could use ... ON T1.X > T2.Y and that would also get you multiple returned combinations.

 

[Pat Hartman]

@mkdrep The others have given you the band-aid (union query) and the solution (merge into a single table).

So, if you need something today, use the band-aid but now that you know the problem, it is best to fix it permanently. When you create the new notes table, include a datetime field and a source field so you can tell which table the note came from if that matters. You can default to the datetime the record was entered which assumes that the entry will be made very soon after the contact is complete. OR, you can let the user specify. I have both fields. That lets the manager run a report to find out how much time elapsed between the contact and the recording of the note. It also feels like there is something else missing from the notes tables. They are both related to a contact record but what is the context for the notes? Do they relate to a customer or a project or something else?

Also, depending on your application, you might not want to allow changes to notes after they have been saved or you might give the user 24 hours for corrections before locking the note but during the open period, only the user who wrote the note would be allowed to change it. Medical record keeping is much more rigid regarding notes than most other apps.

When you merge the two tables, you can use a union query that orders the two tables by the autonumbers and userIDs so that is how they will be appended to the target table.

 

[mkdrep]

Given the current situation, with notes in two tables, you could have two separate queries and then combine into one report. I notice that you don't have a date by which to sort the notes either.
If the notes are to be presented in a form then either your will have two subforms - one of each query, or a subform based upon a UNION query - but this subform will have no valid sort order without a date for each note and the subform will not allow additional notes to be recorded.
Best to take on board @plog 's suggestion - combine the notes tables, ensuring there is a date/time assigned to the notes made.

Actually, I do have dates the notes were made but didn't want to include it in my question as I knew it had nothing to do with my issue. I think I'm just going to combine the tables.

 

[mkdrep]

Most likely that means your notes need to all be in the same table. Why aren't they? What are the structures/fields of the tables the notes are in now?

I'll combine the notes into one table.

 

[mkdrep]

@mkdrep The others have given you the band-aid (union query) and the solution (merge into a single table).

So, if you need something today, use the band-aid but now that you know the problem, it is best to fix it permanently. When you create the new notes table, include a datetime field and a source field so you can tell which table the note came from if that matters. You can default to the datetime the record was entered which assumes that the entry will be made very soon after the contact is complete. OR, you can let the user specify. I have both fields. That lets the manager run a report to find out how much time elapsed between the contact and the recording of the note. It also feels like there is something else missing from the notes tables. They are both related to a contact record but what is the context for the notes? Do they relate to a customer or a project or something else?

Also, depending on your application, you might not want to allow changes to notes after they have been saved or you might give the user 24 hours for corrections before locking the note but during the open period, only the user who wrote the note would be allowed to change it. Medical record keeping is much more rigid regarding notes than most other apps.

When you merge the two tables, you can use a union query that orders the two tables by the autonumbers and userIDs so that is how they will be appended to the target table.

I'll give what you suggest a try. I do have a date time field in each table, I just didn't show it in my original email as it didn't have anything to do with the duplication issue I had. The notes all relate to one "contact" but one table is for project related notes and the other table is for face to face calls that don't have anything to do with a project. I realize now that I should have just used one table for both instances, but I developed this program over the last (12) years and have just added pieces as I needed them. If I started designing my program now, I would make a lot of changes but it's just way too much work to change the program as it meets our groups needs. I'm just trying to clean up a few "behind the scenes" functions of my database to make it easier for my group to use. Thank you very much for your help!

 

[Pat Hartman]

I'd say that it would take less than an hour to merge the two tables and modify one of the note forms. You would add a type code if you wanted to be able to segregate the notes. I would also add a project field. Then you use the simplified schema to create the new functionality.

 

[GPGeorge]

I'll give what you suggest a try. I do have a date time field in each table, I just didn't show it in my original email as it didn't have anything to do with the duplication issue I had. The notes all relate to one "contact" but one table is for project related notes and the other table is for face to face calls that don't have anything to do with a project. I realize now that I should have just used one table for both instances, but I developed this program over the last (12) years and have just added pieces as I needed them. If I started designing my program now, I would make a lot of changes but it's just way too much work to change the program as it meets our groups needs. I'm just trying to clean up a few "behind the scenes" functions of my database to make it easier for my group to use. Thank you very much for your help!

Look up the concept of "technical debt" and consider the costs of continuing to nurse an older system vs investing time and your more recently acquired knowledge in renovating it.

 

[Pat Hartman]

Look up the concept of "technical debt" and consider the costs of continuing to nurse an older system vs investing time and your more recently acquired knowledge in renovating it.

Especially when the cost to repair a flaw is so low.