W
Will
Hello all,
I have a vendor leads table that has entries and stucture shown below
(field names somewhat compressed to save space here):
LeadID Lead Date Prop. Vendor Type Lead Value
177 2/1/2008 42 A Click 2
178 3/1/2008 42 A Click 4
179 4/1/2008 42 A Click 3
180 5/1/2008 42 A Click 4
191 2/1/2008 42 A Phone 13
192 3/1/2008 42 A Phone 6
193 4/1/2008 42 A Phone 7
194 5/1/2008 42 A Phone 13
I was helped a few days ago by Doug and Dirk with this table and wound
up with this query to sum up all leads for a property for a month:
SELECT tbl_VendorLeads.[Lead Date], tbl_VendorLeads.Property, Sum
(tbl_VendorLeads.[Lead Value]) AS [Total Vendor Leads]
FROM tbl_VendorLeads
WHERE (((tbl_VendorLeads.[Lead Date])>Date()-426.125))
GROUP BY tbl_VendorLeads.[Lead Date], tbl_VendorLeads.Property;
I've got the WHERE statement in there as I'm only interested in seeing
leads for a certain amount of time back from the current day (maximum
of 14 months).
So resultant output of the data above would be like after running the
query:
Lead Date Property Total Vendor Leads
2/1/2008 42 15
3/1/2008 42 10
4/1/2008 42 10
5/1/2008 42 17
What I wanted to ask was how could I also calculate the change from
one month to the previous month? For example, the change reflected
for the 5/1/2008 data would be +7 (as compared to 4/1/2008). The
change for 3/1/2008 would be -5 (as compared to 2/1/2008). I need to
also be able to show these monthly changes in the report that this
query feeds into.
Thanks in advance for your assistance!
I have a vendor leads table that has entries and stucture shown below
(field names somewhat compressed to save space here):
LeadID Lead Date Prop. Vendor Type Lead Value
177 2/1/2008 42 A Click 2
178 3/1/2008 42 A Click 4
179 4/1/2008 42 A Click 3
180 5/1/2008 42 A Click 4
191 2/1/2008 42 A Phone 13
192 3/1/2008 42 A Phone 6
193 4/1/2008 42 A Phone 7
194 5/1/2008 42 A Phone 13
I was helped a few days ago by Doug and Dirk with this table and wound
up with this query to sum up all leads for a property for a month:
SELECT tbl_VendorLeads.[Lead Date], tbl_VendorLeads.Property, Sum
(tbl_VendorLeads.[Lead Value]) AS [Total Vendor Leads]
FROM tbl_VendorLeads
WHERE (((tbl_VendorLeads.[Lead Date])>Date()-426.125))
GROUP BY tbl_VendorLeads.[Lead Date], tbl_VendorLeads.Property;
I've got the WHERE statement in there as I'm only interested in seeing
leads for a certain amount of time back from the current day (maximum
of 14 months).
So resultant output of the data above would be like after running the
query:
Lead Date Property Total Vendor Leads
2/1/2008 42 15
3/1/2008 42 10
4/1/2008 42 10
5/1/2008 42 17
What I wanted to ask was how could I also calculate the change from
one month to the previous month? For example, the change reflected
for the 5/1/2008 data would be +7 (as compared to 4/1/2008). The
change for 3/1/2008 would be -5 (as compared to 2/1/2008). I need to
also be able to show these monthly changes in the report that this
query feeds into.
Thanks in advance for your assistance!