2 table querie

[Lucy02]

Hi Guys

I have two tables appointments and telemarketing. I want to be able to view all companies which have had appointments AND telemarking. However when i have previously tried to do this if more appointments have been made to say the company called *lucy* than there has telemarketing the telemarketing will duplicate itself?? Does anyone know how to stop this from occurring.

E.g

Company Tele date, month, appt date, month
Lucy 12 nov 14 nov
Lucy 12 nov 15 nov

So here the telemarketing has only been done once on the 12th but the query is saying that 2 calls have been made on the 12th??!!

This is the SQL statement but I am a newbie to access and don't have a clue how to resolve this. Can anyone help please?
SELECT [Appointment Commentary].Day, [Appointment Commentary].Date, [Appointment Commentary].Month, Telemarketing.[Company Name], Telemarketing.Day, Telemarketing.Date, Telemarketing.Month
FROM [Appointment Commentary] LEFT JOIN Telemarketing ON [Appointment Commentary].[Company Name] = Telemarketing.[Company Name];

Thanks

 

[gromit]

Hi Lucy -

Seems like maybe you have your data and queries tangled up a bit...

Firstly, the query is not really saying that two calls are being made on the 12th. Your query is telling Access to match up all the records in Appts and Telemarketing a certain way and show the results. Your query says in effect: take all the records in Appts and match them together with all the records in Telemarketing, and the display the fields for them together. So you are seeing two different records from Appts matched up with the same record from Telemarketing. The "two calls" are just the same call being displayed twice.

So, the correct query will depend on the information that you are looking for. If you just want to know a list of companies that have been visited (appts) and called (telemarketing) then you could just use a WHERE clause in your SQL :

SELECT tblAppt.Company
FROM tblAppt INNER JOIN tblTelemarket ON tblAppt.Company = tblTelemarket.Company
WHERE ((tblAppt.Date)>#1/1/2001#) AND ((tblTelemarket.Date)>#1/1/2001#)
GROUP BY tblAppt.Company;

The GROUP BY clause works here because you are only showing the company name, and multiple matches will consolidate into a single display.

If on the other hand, you are looking for a list of companies that have been visited and called AND a list of the dates for the appointments and calls, then you need to have a format that Access can display that. A report would probably be the best response. (Your original query works here too, it just looks non-intuitive).

By the way, I'm wondering a little about some of your table structure. I would expect that Company Name would come from a third table (Companies) and that Appts and Telemarketing would link to the company name by a number (CompanyID) rather than the name.

hope this helps,

- gromit