Add multiples rows for same date together

W

Will

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.

Thoughts?

Thanks in adance!
 
W

Will

As a followup...

I forgot to include that there is also printed advertising been done
(newspapers, etc.) by the properties, so there is an additional field
for that. So the Vendor Leads table has the following fields:
LeadIDKey (key), Date, Property (linked from Property table), Vendor
(linked from Vendor's table), Print Phone leads, Internet Phone leads,
and Internet Click-through leads.

I wrote the following query to sum up the total lead count for each
row of data.

SELECT DISTINCTROW Properties.[Property Name], [Vendor
leads].Property, Format$([Vendor leads].Date,'yyyy/mm') AS [Year/
Month], [Vendor leads].[Print phone leads], [Vendor leads].[ILS Click
Leads], [Vendor leads].[ILS phone leads], Sum([Vendor leads].[Print
phone leads])+Sum([Vendor leads].[ILS Click leads])+Sum([Vendor leads].
[ILS phone leads]) AS [Vendor Leads Total]
FROM Properties INNER JOIN [Vendor leads] ON Properties.ID = [Vendor
leads].Property
GROUP BY Properties.[Property Name], [Vendor leads].Property, Format$
([Vendor leads].Date,'yyyy/mm'), [Vendor leads].[Print phone leads],
[Vendor leads].[ILS Click Leads], [Vendor leads].[ILS phone leads],
Year([Vendor leads].Date)*12+DatePart('m',[Vendor leads].Date)-1;

If anyone has any ideas about how to add up all of the rows for a
single property for a single month that would be what I need.
 
D

Douglas J. Steele

Try

SELECT Properties.[Property Name], [Vendor leads].Property,
Format$([Vendor leads].Date,'yyyy/mm') AS [Year/Month],
Sum(Nz([Vendor leads].[Print phone leads], 0)+Nz([Vendor leads].[ILS Click
leads],0)+
Nz([Vendor leads].[ILS phone leads], 0)) AS [Vendor Leads Total]
FROM Properties INNER JOIN [Vendor leads]
ON Properties.ID = [Vendor leads].Property
GROUP BY Properties.[Property Name], [Vendor leads].Property,
Format$([Vendor leads].Date,'yyyy/mm'), [Vendor leads].[Print phone leads],
[Vendor leads].[ILS Click Leads], [Vendor leads].[ILS phone leads]
 
D

Dirk Goldgar

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.
 
W

Will

Hi Doug,

When I use the exact query you provided, I get a concatenation...so if
my values were 14, 14 and 14 in the three leads columns the total is
141414. Also, the rows for the same month are not added together. ???

If make the modification below (I'm using Access 2003 for this
project, so that might be why), I get a correct summation for the
three fields, 42 in this example. Still no monthly sum for the same
month/same property though. Did I miss something?

SELECT Properties.[Property Name], [Vendor leads].Property, Format$
([Vendor leads].Date,'yyyy/mm') AS [Year/Month], Sum(Nz([Vendor leads].
[Print phone leads],0))+Sum(Nz([Vendor leads].[ILS Click leads],0))+Sum
(Nz([Vendor leads].[ILS phone leads],0)) AS [Vendor Leads Total]
FROM Properties INNER JOIN [Vendor leads] ON Properties.ID = [Vendor
leads].Property
GROUP BY Properties.[Property Name], [Vendor leads].Property, Format$
([Vendor leads].Date,'yyyy/mm'), [Vendor leads].[Print phone leads],
[Vendor leads].[ILS Click Leads], [Vendor leads].[ILS phone leads];

Thanks for your assistance.


Try

SELECT Properties.[Property Name], [Vendor leads].Property,
Format$([Vendor leads].Date,'yyyy/mm') AS [Year/Month],
Sum(Nz([Vendor leads].[Print phone leads], 0)+Nz([Vendor leads].[ILS Click
leads],0)+
Nz([Vendor leads].[ILS phone leads], 0)) AS [Vendor Leads Total]
FROM Properties INNER JOIN [Vendor leads]
ON Properties.ID = [Vendor leads].Property
GROUP BY Properties.[Property Name], [Vendor leads].Property,
Format$([Vendor leads].Date,'yyyy/mm'), [Vendor leads].[Print phone leads],
[Vendor leads].[ILS Click Leads], [Vendor leads].[ILS phone leads]

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)




As a followup...
I forgot to include that there is also printed advertising been done
(newspapers, etc.) by the properties, so there is an additional field
for that.  So the Vendor Leads table has the following fields:
LeadIDKey (key), Date, Property (linked from Property table), Vendor
(linked from Vendor's table), Print Phone leads, Internet Phone leads,
and Internet Click-through leads.
I wrote the following query to sum up the total lead count for each
row of data.
SELECT DISTINCTROW Properties.[Property Name], [Vendor
leads].Property, Format$([Vendor leads].Date,'yyyy/mm') AS [Year/
Month], [Vendor leads].[Print phone leads], [Vendor leads].[ILS Click
Leads], [Vendor leads].[ILS phone leads], Sum([Vendor leads].[Print
phone leads])+Sum([Vendor leads].[ILS Click leads])+Sum([Vendor leads].
[ILS phone leads]) AS [Vendor Leads Total]
FROM Properties INNER JOIN [Vendor leads] ON Properties.ID = [Vendor
leads].Property
GROUP BY Properties.[Property Name], [Vendor leads].Property, Format$
([Vendor leads].Date,'yyyy/mm'), [Vendor leads].[Print phone leads],
[Vendor leads].[ILS Click Leads], [Vendor leads].[ILS phone leads],
Year([Vendor leads].Date)*12+DatePart('m',[Vendor leads].Date)-1;
If anyone has any ideas about how to add up all of the rows for a
single property for a single month that would be what I need.- Hide quoted text -

- Show quoted text -
 
W

Will

Hi Doug, thanks for the input. I'm going to experiment with option #1
and see if I can get the data from its sources into the tables that
way. On the surface, it does seem to be a simpler approach by
treating each piece of data individually.

Thanks!
 
D

Douglas J. Steele

Biggest thing you missed is that I had

Sum(Nz([Vendor leads].[Print phone leads], 0)+Nz([Vendor leads].[ILS Click
leads],0)+Nz([Vendor leads].[ILS phone leads], 0)) AS [Vendor Leads Total]

whereas you have

Sum(Nz([Vendor leads].[Print phone leads],0))+Sum(Nz([Vendor leads].[ILS
Click leads],0))+Sum(Nz([Vendor leads].[ILS phone leads],0)) AS [Vendor
Leads Total]

In other words, you need to add the fields together, and sum that total, not
vice versa.

If you're still getting 141414, try

Sum(CLng(Nz([Vendor leads].[Print phone leads], 0))+CLng(Nz([Vendor
leads].[ILS Click leads],0))+CLng(Nz([Vendor leads].[ILS phone leads], 0)))
AS [Vendor Leads Total]

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi Doug,

When I use the exact query you provided, I get a concatenation...so if
my values were 14, 14 and 14 in the three leads columns the total is
141414. Also, the rows for the same month are not added together. ???

If make the modification below (I'm using Access 2003 for this
project, so that might be why), I get a correct summation for the
three fields, 42 in this example. Still no monthly sum for the same
month/same property though. Did I miss something?

SELECT Properties.[Property Name], [Vendor leads].Property, Format$
([Vendor leads].Date,'yyyy/mm') AS [Year/Month], Sum(Nz([Vendor leads].
[Print phone leads],0))+Sum(Nz([Vendor leads].[ILS Click leads],0))+Sum
(Nz([Vendor leads].[ILS phone leads],0)) AS [Vendor Leads Total]
FROM Properties INNER JOIN [Vendor leads] ON Properties.ID = [Vendor
leads].Property
GROUP BY Properties.[Property Name], [Vendor leads].Property, Format$
([Vendor leads].Date,'yyyy/mm'), [Vendor leads].[Print phone leads],
[Vendor leads].[ILS Click Leads], [Vendor leads].[ILS phone leads];

Thanks for your assistance.


Try

SELECT Properties.[Property Name], [Vendor leads].Property,
Format$([Vendor leads].Date,'yyyy/mm') AS [Year/Month],
Sum(Nz([Vendor leads].[Print phone leads], 0)+Nz([Vendor leads].[ILS Click
leads],0)+
Nz([Vendor leads].[ILS phone leads], 0)) AS [Vendor Leads Total]
FROM Properties INNER JOIN [Vendor leads]
ON Properties.ID = [Vendor leads].Property
GROUP BY Properties.[Property Name], [Vendor leads].Property,
Format$([Vendor leads].Date,'yyyy/mm'), [Vendor leads].[Print phone
leads],
[Vendor leads].[ILS Click Leads], [Vendor leads].[ILS phone leads]

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)




As a followup...
I forgot to include that there is also printed advertising been done
(newspapers, etc.) by the properties, so there is an additional field
for that. So the Vendor Leads table has the following fields:
LeadIDKey (key), Date, Property (linked from Property table), Vendor
(linked from Vendor's table), Print Phone leads, Internet Phone leads,
and Internet Click-through leads.
I wrote the following query to sum up the total lead count for each
row of data.
SELECT DISTINCTROW Properties.[Property Name], [Vendor
leads].Property, Format$([Vendor leads].Date,'yyyy/mm') AS [Year/
Month], [Vendor leads].[Print phone leads], [Vendor leads].[ILS Click
Leads], [Vendor leads].[ILS phone leads], Sum([Vendor leads].[Print
phone leads])+Sum([Vendor leads].[ILS Click leads])+Sum([Vendor leads].
[ILS phone leads]) AS [Vendor Leads Total]
FROM Properties INNER JOIN [Vendor leads] ON Properties.ID = [Vendor
leads].Property
GROUP BY Properties.[Property Name], [Vendor leads].Property, Format$
([Vendor leads].Date,'yyyy/mm'), [Vendor leads].[Print phone leads],
[Vendor leads].[ILS Click Leads], [Vendor leads].[ILS phone leads],
Year([Vendor leads].Date)*12+DatePart('m',[Vendor leads].Date)-1;
If anyone has any ideas about how to add up all of the rows for a
single property for a single month that would be what I need.- Hide
quoted text -

- Show quoted text -
 
W

Will

Thanks Doug and Dirk for your helpful assistance with this question.
In the end, I opted to rearrange the data into a more useful format
per Dirk's suggestion. I've been able to get very far with that
particular piece of this project since making that change.

Many thanks!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top