UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Data Mismatched In Queries, Access 2013    
 
   
MonteyBurns
post Oct 10 2018, 09:54 PM
Post#1



Posts: 45
Joined: 11-June 18



Well I have tried to figure out a solution before turning to this forum but for everything I have tried nothing worked.

This is a contact database. I have a 3 forms, one for Company and one for contacts and then I created a sub form for the Notes. This one form is placed on the Company form and the Organization form (Neither will be opened at the same time). The notes form was created with the intent the users would be able to search for keywords within a notes field (LongText). This table contains (NoteID, Note, CompanyID, ContactID, Type). The search feature works to some degree but it's not flawless.

I can do the search for a keyword and it will locate the record(s) with the keyword search criteria. However there is a mismatch with some of the information from a sub query. I have narrowed it down to the ContactID field. When there is no value in the ContactID field I get mismatched results when I try to do a sub query to obtain the additional information such as Company Name and/or Contact Name. For example, a keyword search for "Batman" retrieves the correct word and the correct NoteID, Note, CompanyID, ContactID and Type. Then I have a sub query based off of the main query to match the CompanyID to the Company Name and the Contact name if it exists. This is where the mismatch in data would occur, all the information in first query is correct, but the Company name would be a different company (although the ID's match). It should return ACME Inc, but it's pulling up Bob's Towing.

I know it's the ContactID field but i'm not sure on how to handle the null field when it does exist. If I omit the ContactID, in the second query, I can get the correct Company name.

I need to have the Contact person associated with the note if that is where the keyword is.
Is there something missing? Could it be a relationship issue? Am I trying to do too much in one step?

Any help or advice is appreciated.

Cheers
Montey

Go to the top of the page
 
John Vinson
post Oct 10 2018, 10:52 PM
Post#2


UtterAccess VIP
Posts: 4,277
Joined: 6-January 07
From: Parma, Idaho, US


Please post the SQL of the queries, and the relationships of the tables.

--------------------
John W. Vinson
Wysard of Information
Go to the top of the page
 
MonteyBurns
post Oct 11 2018, 08:12 PM
Post#3



Posts: 45
Joined: 11-June 18



Sorry for delay.

MAIN QUERY

SELECT TBL_Notes.Note_ID, TBL_Notes.Notes, TBL_Notes.Co_ID, TBL_Notes.Contact_ID
FROM (TBL_Company INNER JOIN TBL_Contacts ON TBL_Company.[Co_ID] = TBL_Contacts.[Co_ID]) INNER JOIN TBL_Notes ON TBL_Contacts.[Contact_ID] = TBL_Notes.[Note_ID]
WHERE (((TBL_Notes.Notes) Like "*" & [Forms]![FRM_Main]![txtSearchNotes] & "*"));



Sub Query off of the main query

SELECT QRY_NotesTextSearch.Note_ID, QRY_NotesTextSearch.Notes, QRY_NotesTextSearch.Org_ID, QRY_NotesTextSearch.Contact_ID, [TBL_Company].[Co_ID], [TBL_Company].[CoName], TBL_Contacts.Contact_ID, TBL_Contacts.FirstName, TBL_Contacts.LastName
FROM (QRY_NotesTextSearch INNER JOIN TBL_Company ON QRY_NotesTextSearch.Org_ID=[TBL_Company].[Co_ID]) INNER JOIN TBL_Contacts ON ([TBL_Company].[Co_ID]=TBL_Contacts.[Co_ID]) AND (QRY_NotesTextSearch.Contact_ID = TBL_Contacts.Contact_ID);
This post has been edited by MonteyBurns: Oct 11 2018, 08:13 PM
Go to the top of the page
 
John Vinson
post Oct 15 2018, 04:47 PM
Post#4


UtterAccess VIP
Posts: 4,277
Joined: 6-January 07
From: Parma, Idaho, US


I think this is the problem:

SELECT TBL_Notes.Note_ID, TBL_Notes.Notes, TBL_Notes.Co_ID, TBL_Notes.Contact_ID
FROM (TBL_Company INNER JOIN TBL_Contacts ON TBL_Company.[Co_ID] = TBL_Contacts.[Co_ID]) INNER JOIN TBL_Notes
ON TBL_Contacts.[Contact_ID] = TBL_Notes.[Note_ID]
WHERE (((TBL_Notes.Notes) Like "*" & [Forms]![FRM_Main]![txtSearchNotes] & "*"));

I'm guessing the colored line should be

ON TBL_Contacts.[Contact_ID] = TBL_Notes.[Contact_ID]

I presume that Contact_ID is the primary key of Contacts (uniquely identifying the contact) and Note_ID is the primary key of Notes, uniquely identifying which (of possibly many) Notes are related to this contact. There should be a foreign key (not primary key) Contact_ID in the Notes table pointing to the TBL_Contacts record of whoever made the comment.

(edited to be more discursive)

--------------------
John W. Vinson
Wysard of Information
Go to the top of the page
 
MonteyBurns
post Oct 15 2018, 08:16 PM
Post#5



Posts: 45
Joined: 11-June 18



Thanks

Your presumptions are correct. I will give this a try tomorrow and let you know how it works out.

Montey
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th December 2018 - 08:17 PM