My Querie keeps duplicating

[Lucy02]

Hi

Please can some help me asap. I need to do a querie which joins two tables together. I have a telemarketing table and an appointment table. The idea is that when an appointment is made telemarketing should be done afterwards to the same company. Therefore I want to view all of the telemarketing done and all of the appointments done and then match companies which have had both an appointment and telemarketing. However when i do this if there has been more appointments made for one company that there has been telemarketing the telemarketing duplicates itself so that the two have the same amount done which is not what I want??? Does this make sense?? Can some one please help????

Thanks

 

[Brian1960]

Can you post your Query as it is difficult to help without it.

 

[Lucy02]

Example:

Appt Day, Date, Month Company Name Tele Day, Date, Month
Monday 1st Nov *Lucy* Wed 12th Nov
Tuesday 2nd Nov *Lucy* Wed 12th Nov
Wednesday 3rd Nov *Lucy* Wed 12th Nov

So what has happend is there have been 3 appointments made but only 1 telemarketing call has been made. However the querie is telling me that 3 calls were made on the 12th when really there was only 1??? Can this be solved???

Thanks ever so much for your help!!

 

[RV]

Lucy,

post your query = post your SQL statement.

RV

 

[Lucy02]

SELECT [Appointment Commentary].Day AS [Appointment Commentary_Day], [Appointment Commentary].Date AS [Appointment Commentary_Date], [Appointment Commentary].Month AS [Appointment Commentary_Month], [Appointment Commentary].[Company Name] AS [Appointment Commentary_Company Name], Tele.Day AS Tele_Day, Tele.Date AS Tele_Date, Tele.Month AS Tele_Month, Tele.[Company Name] AS [Tele_Company Name]
FROM [Appointment Commentary] LEFT JOIN Tele ON [Appointment Commentary].[Company Name] = Tele.[Company Name];

 

[ColinEssex]

You have had help and suggestions in your other posting of the same problem.
This is the trouble when you multi-post problems, people give up because they do not know if the problem is resolved on another thread

Col

 

[Lucy02]

I suppose that is fair enough colin but It just means that people will definatly see my problem and at least try to help me as I am really stuck! U don't have suggestions at all do you?! Please?!!

 

[Jon K]

In a query, you can't show 1 call if 3 appointments were made.
However, you can do it in a report if you set up a one-to-many relationship between the tables.


In table Tele, set Company Name as primary key.
In the other table, index the Company Name field as Yes (Duplicates OK).

Click on the Relationships button on the toolbar to set up a one-to-many relationship between the two tables.


In your query, change the LEFT JOIN to INNER JOIN.
(The left join implies there are appointments that do not relate to any calls, which violates a one-to-many relationship.)

Use the report wizard to build a report based on the query (See the report in the attached example.) You may need to modified the report to show the long field labels.


Note
If the calls and appointments are not in a one-to-many relationship, then you will need to use VBA code to update a temporary table, showing the call and appointments of one company in one row.
.

 

[Lucy02]

Hi John your reply to my problem was fantastic. The only problem I have now is that some companies had more than 1 telemarketing call so I cannot put the telemarketing company as a primary key as it does not allow duplicates. Is there anyway i can get round this at all?

Many thanks for all your help!!

 

[RV]

Is there anyway i can get round this at all

Yes there is.
You need to set up multiple tables as per John's suugestion.

RV

 

[Lucy02]

vba code???

Hi rv or john. Please could you help me with the vba code??