two unrelated tables and parameter query

[Seito]

Hy,

first of all, I'm new to this site and rather lost ! So I apologize if this topic is already active somewhere. Admins, please move it if so...

So, to my challenge.

I have this database for calling center. And there are a few pickles to chew.

First of all, I want to built parameter query to give a telemarketer a choice which/whose contacts he wants/must call.

So I built form with combo boxes. I have 3 combo boxes from which only 2 give relevant parameters (which type of prospects and which employee is in contact with them) and one is user friendly way of choosing which part of country we wish to call (instead of tipping UTM numbers directly, telemarketer has a choice to choose city and dLookup function then fills appropriate UTM numbers in designated fields.)

Everything fine until I wanted to built query which would use given parameters and give me results I want.

Problem is I have 2 unrelated tables in which I store prospects. I have table tblContactReferral and tblContactQuestionnaire. And first combo box in my form allows employee to choose which contacts will he call. Referral or Questionnaire OR BOTH (if he leaves it empty).

BIG QUESTION:
How to tell query which data source to chose based on parameter?

I TRIED THIS:
I gave each table another column named TYPE. And gave it default value 1 in tblContactReferral and default value 2 in tblContactQuestionnaire. And then in query design view choose both fields and linked them to parameter value in form (which saved 1 as a value if employee choose Referral and 2 if Questionnaires were chosen).

I thought this would filter data nicely. Since query runs (if I'm not mistaking) from left to right, it would first choose the TYPE (based on 1, 2 or null) and then filter appropriate records also on following parameters (like employee who is in contact with customer and UTM location in country).

I GOT THIS:
A mess! It seams like query didn't choose between contacts but somehow combine them.

Let's say I have 5 records in tblContactsReferral (ID autonumber 20, 21, 22, 23, 24) and 4 in tblContactsQuestionnaire (ID autonumber 1,2,3,4). Records 20,21,1,3 are from employee John Doe1 (ID_Employee is 1) and rest of records 22,23,24,2,4 are from John Doe2 (ID_Employe is 2).

If I try to get contacts that are only REFERRALS and from JOHN DOE1 I get:
ID_Referral ID_Questionnaire TYPE_Referral TYPE_Questionnaire EMPLOYEE_Referral EMPLOYE_Questionnaire
20 1 1 2 1 1
20 2 1 2 1 2
20 3 1 2 1 1
20 4 1 2 1 2
21 1 1 2 1 1
21 2 1 2 1 2
21 3 1 2 1 1
21 4 1 2 1 2


Any thoughts on that?

As I see it query correctly filtered only EMPLOYEE_Referral, where I choose to get only JOHN DOE1's referrals. And there are no JOHN DOE2's referrals in results.

EMPLOYEE_Questionnaire alternating pattern of 1 and 2 baffles me. But after looking in tblContactsQuestionnaire I saw records there are alternating in same way. First is from John Doe1 and second from John Doe2 and so on. And that corresponds with ID_Questionnaire which goes in same order 1,2,3,4 for each record of ID_Referral (20 and 21).

What I do not understand is how come TYPE_Questionnaire was not filtered? And why records are combined? Why first record 20 (in ID_Referral) gets ALL the records in tblContactQuestionnaire and then also again a same thing for record 21!



How can I get only results I want. I should get:
ID_Referral ID_Questionnaire TYPE_Referral TYPE_Questionnaire EMPLOYEE_Referral EMPLOYE_Questionnaire
20 1 1
21 1 1

With empty fields in columns ID_Questionnaire, TYPE_Questionnaire and EMPLOYE_Questionnaire!



Any help or guidance would be much appreciated!

THANK YOU!

[Robeen]

Can you post the SQL of your query?

[Seito]

Naturally. Let me explain/translate names here:

tblKontaktANKETA - tblContactQuestionnaire
tblKontaktPRIPOROCILA - tblContactReferral
ID_KontaktANKETA - ID_Questionnaire
ID_KontaktPRIPOROCILA - ID_Referral
TIP - TYPE
ANKETAR - EMPLOYE_Questionnaire
AGENT - EMPLOYEE_Referral

SELECT tblKontaktANKETA.ID_KontaktANKETA, tblKontaktPRIPOROCILA.ID_KontaktPRIPOROCILA, tblKontaktANKETA.TIP, tblKontaktPRIPOROCILA.TIP, tblKontaktANKETA.ANKETAR, tblKontaktPRIPOROCILA.AGENT
FROM tblKontaktANKETA, tblKontaktPRIPOROCILA
WHERE (((tblKontaktANKETA.TIP)=[Forms]![frmKontaktTERMINIRANJE]![NavigationSubform].[Form]![cboTIP_KONTAKTA] Or (tblKontaktANKETA.TIP) Like [Forms]![frmKontaktTERMINIRANJE]![NavigationSubform].[Form]![cboTIP_KONTAKTA] Is Null) AND ((tblKontaktANKETA.ANKETAR)=[Forms]![frmKontaktTERMINIRANJE]![NavigationSubform].[Form]![cboAgent] Or (tblKontaktANKETA.ANKETAR) Like [Forms]![frmKontaktTERMINIRANJE]![NavigationSubform].[Form]![cboAgent] Is Null)) OR (((tblKontaktPRIPOROCILA.TIP)=[Forms]![frmKontaktTERMINIRANJE]![NavigationSubform].[Form]![cboTIP_KONTAKTA] Or (tblKontaktPRIPOROCILA.TIP) Like [Forms]![frmKontaktTERMINIRANJE]![NavigationSubform].[Form]![cboTIP_KONTAKTA] Is Null) AND ((tblKontaktPRIPOROCILA.AGENT)=[Forms]![frmKontaktTERMINIRANJE]![NavigationSubform].[Form]![cboAgent] Or (tblKontaktPRIPOROCILA.AGENT) Like [Forms]![frmKontaktTERMINIRANJE]![NavigationSubform].[Form]![cboAgent] Is Null));

If anything is not clear let me know. I can post whole database with sample data to test.

TNX!