Compare values from one month to next

[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!

 

[KARL DEWEY]

UNTESTED --
SELECT [YourQuery].[Lead Date], [YourQuery].[Property], [YourQuery].[Total
Vendor Leads], [YourQuery_1].[Total Vendor Leads]
FROM [YourQuery] INNER JOIN [YourQuery_1] ON [YourQuery].[Property] =
[YourQuery_1].[Property]
WHERE [YourQuery_1].[Lead Date] = DateAdd("m", 1,[YourQuery].[Lead Date]);

 

[Will]

Hi Karl,

Yes that was it!

So I have qry_part1 and qry_part2 as:

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;

Then another query like such:

SELECT qry_TotalVendorLeads_byMonth_byProperty_part1.[Lead Date],
qry_TotalVendorLeads_byMonth_byProperty_part1.Property,
qry_TotalVendorLeads_byMonth_byProperty_part1.[Total Vendor Leads] AS
[Current Month Leads], qry_TotalVendorLeads_byMonth_byProperty_part2.
[Total Vendor Leads] AS [Previous Month Leads], ([Current Month Leads]-
[Previous Month Leads]) AS [Change From Last Month]
FROM qry_TotalVendorLeads_byMonth_byProperty_part1 INNER JOIN
qry_TotalVendorLeads_byMonth_byProperty_part2 ON
qry_TotalVendorLeads_byMonth_byProperty_part1.Property =
qry_TotalVendorLeads_byMonth_byProperty_part2.Property
WHERE (((qry_TotalVendorLeads_byMonth_byProperty_part2.[Lead Date])
=DateAdd("m",-1,[qry_TotalVendorLeads_byMonth_byProperty_part1].[Lead
Date])));

The only change I found that was needed was changing "1" to "-1". I
added some labels to make the data easier to interpret. I also wrote
a query using -12. I've been able to interegrate the monthly
difference values into a larger query that gathers the data for the
report just fine for the monthly differences. That larger query also
gathers information another, seperate source so that there is one row
per month per property.

All I have left now is to figure out how to get the difference of this
month/this year from this month/last year to show up without dropping
out all the non-matching rows (i.e. those without a yearly change
value)...

Thanks Karl!

UNTESTED --
SELECT [YourQuery].[Lead Date], [YourQuery].[Property], [YourQuery].[Total
Vendor Leads], [YourQuery_1].[Total Vendor Leads]
FROM [YourQuery] INNER JOIN [YourQuery_1] ON [YourQuery].[Property] =
[YourQuery_1].[Property]
WHERE [YourQuery_1].[Lead Date] = DateAdd("m", 1,[YourQuery].[Lead Date]);

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!- Hide quoted text -

- Show quoted text -