Mutual Numbers And Detect Conference Call - Please Help I am begging
- Thread starter tronturbo
- Start date
Bad news, I cannot follow your explanation. Good news, I don't need to--I just need to see data. Let's focus on issue #1 for now. Provide 2 sets of data:
A. Starting sample data from your table(s). Provide table and field names and enough sample data to cover all cases.
B. Expected results when you feed in the data from A. Show me what data you expect to end up with when you feed the data from A into this yet to be built query.
Again, no explanations needed, just data--2 sets beginning and ending.
A. Starting sample data from your table(s). Provide table and field names and enough sample data to cover all cases.
B. Expected results when you feed in the data from A. Show me what data you expect to end up with when you feed the data from A into this yet to be built query.
Again, no explanations needed, just data--2 sets beginning and ending.
Thank you soooooooooooo much for your reply I really really appreciate that. As you instructed I have make 2 sets. Please see the attachment. Thanks
Last edited:
I'm lost. Is that both issues or just issue #1 like I asked? Because each file you sent has tblExpected. You have 2 expected data sets in there.
Again, let's focus on one issue. What exactly am I looking at in each file?
Again, let's focus on one issue. What exactly am I looking at in each file?
Sorry If I make you confused. I apologize. Please take a look Set 1.mdb for issue # 1 right now and thanks again. Appreciated!
Thanks plog, JHB just solve the issue. Will you also please take a look Set 2 for issue #2. Will appreciate and thanks for your time.
Forget that I figured it out---because it has only 1 contact, to be in the query it must have more than one unique A_Number in tblGeneral.
To accomplish this, you will need subqueries. Paste the below SQL into a query and call it 'Mutual_sub1":
It gets all the unique A & B number permutations. Next, paste the below SQL into a new query and call it 'Mutual_sub2':
That will get all B numbers that have more than 1 unique A number assigned to them. Finally use the below SQL to get the results you want:
Let me know if that doesn't work. I'm working on issue #2 now.
To accomplish this, you will need subqueries. Paste the below SQL into a query and call it 'Mutual_sub1":
Code:
SELECT tblGeneral.B_Number, tblGeneral.A_Number
FROM tblGeneral
GROUP BY tblGeneral.B_Number, tblGeneral.A_Number;It gets all the unique A & B number permutations. Next, paste the below SQL into a new query and call it 'Mutual_sub2':
Code:
SELECT Mutual_sub1.B_Number
FROM Mutual_sub1
GROUP BY Mutual_sub1.B_Number
HAVING (((Count(Mutual_sub1.A_Number))>1));That will get all B numbers that have more than 1 unique A number assigned to them. Finally use the below SQL to get the results you want:
Code:
TRANSFORM Count(tblGeneral.A_Number) AS CountOfA_Number
SELECT tblGeneral.B_Number
FROM tblGeneral INNER JOIN Mutual_sub2 ON tblGeneral.B_Number = Mutual_sub2.B_Number
GROUP BY tblGeneral.B_Number
PIVOT tblGeneral.A_Number;Let me know if that doesn't work. I'm working on issue #2 now.
I was able to generate the expected results in issue #2. But, I'm not convinced its correct--please test it on a larger dataset and manually check other examples. This too will require a sub-query, paste the below SQL into a new query and name it 'OverlappedCalls_sub1':
It finds all overlapping calls. The below SQL will produce the results you want:
Paste that into a new query. Again, be sure to test it on more data than you gave me. If it does fail, give me the sample data you used to determine it failed so I can correct it.
Code:
SELECT tblConference.*, iif(tblConference.Call_Start_DateTime<tblConference_1.Call_Start_DateTime, tblConference.Call_Start_DateTime, tblConference_1.Call_Start_DateTime) AS InitialCall
FROM tblConference INNER JOIN tblConference AS tblConference_1 ON (tblConference.Call_End_DateTime>=tblConference_1.Call_Start_DateTime) AND (tblConference.Call_Start_DateTime<=tblConference_1.Call_End_DateTime) AND (tblConference.A_Number = tblConference_1.A_Number)
WHERE tblConference.B_Number <> tblConference_1.B_Number;It finds all overlapping calls. The below SQL will produce the results you want:
Code:
SELECT OverlappedCalls_sub1.A_Number, OverlappedCalls_sub1.B_Number, OverlappedCalls_sub1.Call_Type, OverlappedCalls_sub1.Call_Start_DateTime, OverlappedCalls_sub1.Call_End_DateTime, OverlappedCalls_sub1.Duration, IIf([tblConference].[Call_Type]="Out-Call","Call-Waiting","Call-Conference") AS Result
FROM OverlappedCalls_sub1 INNER JOIN tblConference ON (OverlappedCalls_sub1.InitialCall = tblConference.Call_Start_DateTime) AND (OverlappedCalls_sub1.A_Number = tblConference.A_Number)
GROUP BY OverlappedCalls_sub1.A_Number, OverlappedCalls_sub1.B_Number, OverlappedCalls_sub1.Call_Type, OverlappedCalls_sub1.Call_Start_DateTime, OverlappedCalls_sub1.Call_End_DateTime, OverlappedCalls_sub1.Duration, IIf([tblConference].[Call_Type]="Out-Call","Call-Waiting","Call-Conference");Paste that into a new query. Again, be sure to test it on more data than you gave me. If it does fail, give me the sample data you used to determine it failed so I can correct it.
Heads off sir, I really really appreciate for that. We need people like you in this world to be honest. However, I just tried in big db like 1200+ records but I am facing some issue.
1- That Records includes In-SMS, Out-SMS, GPRS, In-MMS and Out-MMS and Query shows these records as well
2- After executing query it shows duplicate records (Please see Duplicate.png file) but there is no such duplication on main table
I made Expected Report for matching the result. Please see the attachment sir and thanks for your time.
Last edited:
