Will said:
I have a table that tracks lead information from Internet advertising
for real estate. Fields include: LeadIDKey (key), Date, Property,
Vendor (linked from Vendor's table), Phone leads, and Click-through
leads.
Each property might be avertising with mutliple vendors, thus for a
given month there could be 2, 3 or more rows of data for a single
property. The vendor would be different on each row, as well as the
unique lead data.
What I need to do is total the lead data for a single property for a
single month across all vendors. I can total the lead data on each
row, but I haven't figured out how to total up all of the lead data
for a single property for a single month over multiple rows.
It seems to me that your table structure should probably be different to
start with, so that instead of this:
Current Structure
-----------------------
LeadIDKey (key)
LeadDate (renamed from "Date")
Property
Vendor
Phone leads
Click-through leads
.... you would have this:
Alternative Structure #1
--------------------------------
LeadIDKey (key)
LeadDate
Property
Vendor
LeadType ('Phone' or 'Click-Through', maybe)
LeadCount
.... or maybe even just this:
Alternative Structure #2
--------------------------------
LeadIDKey (key)
LeadDate
Property
Vendor
LeadType ('Phone' or 'Click-Through', maybe)
In both of my suggested alternatives, you make the type of lead another
field in the table, so rather than having two separate fields in each
record -- one for each lead type -- you have a separate record for each lead
type. In the last alternative structure, there is no count of leads -- each
record represents one lead.
Both of the alternatives allow you to query totals by lead type very easily,
or query lead totals regardles of type. Which structure you would use
depends a bit on how you get the data: if you are entering data from a
source that gives you "Date 3/18/2009, Property X, Vendor Y, 10 Phone Leads,
15 Click-Through leads", it's probably easier to use Alternative #1. If
your source comes in as ""Date 3/18/2009, Property X, Vendor Y, Phone
lead" -- that is, one lead at a time -- then it's easier to use Alternative
#2.
Suppose you were using Alternative Structure #1. Then you could have
queries with SQL like these:
How many leads of each type per vendor per property per month?
SELECT
MonthName(Month(LeadDate)) As MonthReceived,
Property,
Vendor,
LeadType,
Sum(LeadCount) As Leads
FROM tblLeads
GROUP BY
MonthName(Month(LeadDate)),
Property,
Vendor,
LeadType;
How many leads of all types per vendor per property per month?
SELECT
MonthName(Month(LeadDate)) As MonthReceived,
Property,
Vendor,
Sum(LeadCount) As Leads
FROM tblLeads
GROUP BY
MonthName(Month(LeadDate)),
Property,
Vendor;
How many leads of all types per property per month?
SELECT
MonthName(Month(LeadDate)) As MonthReceived,
Property,
Sum(LeadCount) As Leads
FROM tblLeads
GROUP BY
MonthName(Month(LeadDate)),
Property;
On the other hand, if you were using Alternative Structure #2, you would
simply substitute the Count function instead of the Sum function in queries
that are otherwise very similar:
How many leads of each type per vendor per property per month?
SELECT
MonthName(Month(LeadDate)) As MonthReceived,
Property,
Vendor,
LeadType,
Count(*) As Leads
FROM tblLeads
GROUP BY
MonthName(Month(LeadDate)),
Property,
Vendor,
LeadType;
How many leads of all types per vendor per property per month?
SELECT
MonthName(Month(LeadDate)) As MonthReceived,
Property,
Vendor,
Count(*) As Leads
FROM tblLeads
GROUP BY
MonthName(Month(LeadDate)),
Property,
Vendor;
How many leads of all types per property per month?
SELECT
MonthName(Month(LeadDate)) As MonthReceived,
Property,
Count(*) As Leads
FROM tblLeads
GROUP BY
MonthName(Month(LeadDate)),
Property;
Note: It's probably the case that you want to group by month and year, not
just month, in which case the above SQL would need to be modified, but I
kept it simple for these examples.