Reg : Counting Rows from Multiple tables ??[RESOLVED]

[kishore.kr]

hi guys,

i am creating a report for which i am not able to produce a query. 3 tables are involved in this qry and they are salesperson, first_contact and repeated_contact.

Relationship
=========
primary table --> secondary table
~~~~~~~~~~~~~~~~~~~~~~
1. salesperson -->first_contact
salesperson --> repeated_contact

2. first_contact --> repeated_contact

when a salesperson contacts a client(first time) the details will be added in the first_contact table along with his code(salesperson_code). if the same client is contacted again(may be by the same salesperson or by another which is not important at this point), the details of the contact will be added in the repeated_contact table along with his code(salesperson_code).

now i have to retrieve datewise reports of salesperson(date of contact is also added in both table). that is the number of clients a salesperson has contacted on a particular day. so the data should be retrieved from both the first_contact and repeated_contact tables. (i tried with group and joins, but i could not figure it out clearly). so guys please help me out.

actually i figured an alternative ie., retrieving data of both the table and putting in a temporary table and from there make the count. but if there is any other way(sql) then i would prefer that.

-- Kishore..

[Ecniv]

either a subquery in the fields bit (one per each table) or subquery in the tables section (not sure which would be best for you)

1. salesperson -->first_contact
salesperson --> repeated_contact

2. first_contact --> repeated_contact

Something like this may work...

SELECT 
   tblSalesPersons.SalesPersonID, 
   qrySalesToDates.TheDate, 
   qrySubFC.TotalFC, 
   qrySubRC.TotalRC, 
   nz([TotalFC],0)+nz([TotalRC],0) AS TotalFCRC
FROM 
((tblSalesPersons LEFT JOIN 
   (select 
      [tblFirstContacts].[salespersonid], 
      format([updatedon],'yyyymmdd') as [TheDate] 
    from [tblFirstContacts] 
   union 
    select 
      [tblRepeatedContact].[salespersonid], 
      format([updatedon],'yyyymmdd') as [TheDate] 
    from [tblRepeatedContact]) AS qrySalesToDates 
ON tblSalesPersons.SalesPersonID = qrySalesToDates.salespersonid) 
LEFT JOIN 
   (select 
      [tblFirstContacts].[salespersonid], 
      format([updatedon],'yyyymmdd') as FCUDate, 
      count([tblFirstContacts].[salespersonid]) as [TotalFC] 
   from [tblFirstContacts]
   group by 
       [tblFirstContacts].[salespersonid], 
       format([updatedon],'yyyymmdd')) AS qrySubFC 
ON (qrySalesToDates.TheDate = qrySubFC.FCUDate) AND (qrySalesToDates.salespersonid = qrySubFC.salespersonid)) 
LEFT JOIN 
   (select 
      [tblRepeatedContact].[salespersonid], 
      format([updatedon],'yyyymmdd') as RCUDate, 
      count([tblRepeatedContact].[salespersonid]) as [TotalRC] 
   from [tblRepeatedContact]
   group by [tblRepeatedContact].[salespersonid], format([updatedon],'yyyymmdd')) AS qrySubRC 
ON (qrySalesToDates.TheDate = qrySubRC.RCUDate) AND (qrySalesToDates.salespersonid = qrySubRC.salespersonid)

Nasty huh?
Seems to work though. Might be over the top and probably could be trimmed down to give better performance.

Edit:
What I did:
1) table of sales people
2) a sub query to union the formatted date and salesID from both the First contact and repeated contact tables (to give a list of dates... may need to enclose this with another query to ensure no duplicate dates...)
3) a sub query to count the first contacts by date and salespersonid
4) as (3) but for repeated contact
5) Use query builder to put it all together

[kishore.kr]

hi ecniv,

i tried the query but it is not giving exact results. i will try it for somemore time and let you know. thank you for the query.

-- Kishore...

[kishore.kr]

hi ecniv,

i got the concept of your qry and developed it. i got the required result. thanks a lot.
I am not willing to close the thread bcos., what i got is just the summary. but i need details also so i'll try for that and then close the thread. if i need help then i'll definetely get back to you people. again thanks a lot ..

-- Kishore...

[Ecniv]

No problem.
It is only one way of getting the results you want, so you may want to think up other methods as a comparison (speed/coding/time taken etc)

[kishore.kr]

hi guys,
i got the required result. spl thanks to ecniv

-- Kishore...