Counts in the parent and child

[miked123]

Hi,

I have a parent/child pair of tables that I need a count of both instances.

The parent Contact Log has these fields
LogDate,LogTime,LogType

The child table has followup Calls to the parent.
LogDate,LogTime.

I need to count the instances of calls by Date and Time
Of inital calls and followup calls.
where LogType=1

I worked out the crosstab, but the counts are high.

I created a view with a Union like this
Select LogDate,LogTime,LogType
from Tab1
Where LogType=1
Union ALL
LogDate,LogTime,1 << Need 3 fields for the union
From Tab2

Is it the ALL that might be causing the problem? Or am I just off base with my approach?

Thanks Mike Davis
MSsql, VB and Crystal Reports Developer

 

[Ngolem]

can these tables be linked at all???...how do you relate one table to another

 

[miked123]

Yes, they have a PrimaryKey-ForeignKey Relation.

Tab2.FK=Tab1.PK

Mike Davis
MSsql, VB and Crystal Reports Developer

 

[miked123]

These individual Queries return the proper totals. But I need a query or view that returns LogDate,LogTime So that the Date is my column and Time is my row and I summarize on Count of Date.

Select StaffName,Count(LogDate) as Count
From Tab1
Where Tab1.LogType=1
Group by StaffName


Select StaffName,Count(t2.LogDate) as Count
From Tab2 t2
Where Tab1.PK=Tab2.FK and
Tab1.LogType=1
Group By StaffName
Thanks again for any Ideas,
Mike Mike Davis
MSsql, VB and Crystal Reports Developer

 

[Ngolem]

I think your problem is that you are linking two tables with a many/many situation so you will get bad data returned in a simple report.

What I might do is use only one table in a main report and the other in a subreport linked to the main treport by the primary keys

then pass the total from the subreport to the main report via a shared variable