Need help writing a query

[kdh]

Hi,

I have 4 columns in total

A: Advertiser_ID
B: Campaign_ID
C. Revenue
D: Sales_Date

An advertiser_Id may have many campaign_ids associated to it, so it's
possible to have multiple records on a given day.

What I need to do is to write a query to compare the sales data of a given
date to the previous day's data. What i would ideally want is the records of
those advertisers that had the biggest delta in revenue (say top 10 greatest
changes)

I'm unsure of how to to limit this data.

Thanks in advance

 

[MGFoster]

Hi,

I have 4 columns in total

A: Advertiser_ID
B: Campaign_ID
C. Revenue
D: Sales_Date

An advertiser_Id may have many campaign_ids associated to it, so it's
possible to have multiple records on a given day.

What I need to do is to write a query to compare the sales data of a given
date to the previous day's data. What i would ideally want is the records of
those advertisers that had the biggest delta in revenue (say top 10 greatest
changes)

I'm unsure of how to to limit this data.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Perhaps:

The first query gets the changes: qryRevenueChanges

SELECT advertiser_id, campaign_id, sales_date,
revenue - Nz((SELECT revenue FROM table_name
WHERE advertiser_id = A.advertiser_id AND campaign_id = A.campaign_id
AND sales_date = A.sales_date-1),0) As revenue_change
FROM table_name As A

The second query gets the TOP 10 changes:

SELECT DISTINCT TOP 10 advertiser_id, campaign_id, sales_date,
revenue_changes
FROM qryRevenueChanges
ORDER BY revenue_changes DESC

The query qryRevenueChanges will return zero when the sales_date is a
Monday. You can play around with the WeekDay() function to determine if
the sales_date is a Monday. If it is a Monday then you can subtract 3
instead of 1 to get the Friday sales_date data.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSZtLY4echKqOuFEgEQJNvwCg7MirLKnP5V8Uj99UBchCXmGTkiIAoOHy
HqgHe3wY0dJy53gCl3G2Htdw
=XiwY
-----END PGP SIGNATURE-----

 

[kdh]

Thanks for the help! I appreciate it. I am running into a problem getting
it to run.

Syntax error in query expression revenue-NZ((Select revenue From Sales by
Campaign Where advertiser_id= A.Advertiser_id And campaign_id = A.campaign_id
And sales_date = A.sales_date-1)0)'.

 

[John W. Vinson]

Thanks for the help! I appreciate it. I am running into a problem getting
it to run.

Syntax error in query expression revenue-NZ((Select revenue From Sales by
Campaign Where advertiser_id= A.Advertiser_id And campaign_id = A.campaign_id
And sales_date = A.sales_date-1)0)'.

Since you (unfortunately) took advantage of the ability to use blanks in table
and fieldnames, you must enclose any such field in square brackets. Otherwise
Access assumes that "Sales" is one thing, "by" is something else, and
"Campaign" is yet another something, since a blank is a delimiter.

Try

revenue-NZ((Select revenue From [Sales by Campaign] AS B Where advertiser_id=
A.Advertiser_id And campaign_id = A.campaign_id And B.sales_date =
A.sales_date-1), 0)

You also need a comma to set off the subquery from the 0 argument of the NZ
function.

 

[kdh]

Thanks for the explanation. It worked great!

Thanks for the help! I appreciate it. I am running into a problem getting
it to run.

Syntax error in query expression revenue-NZ((Select revenue From Sales by
Campaign Where advertiser_id= A.Advertiser_id And campaign_id = A.campaign_id
And sales_date = A.sales_date-1)0)'.

Since you (unfortunately) took advantage of the ability to use blanks in table
and fieldnames, you must enclose any such field in square brackets. Otherwise
Access assumes that "Sales" is one thing, "by" is something else, and
"Campaign" is yet another something, since a blank is a delimiter.

Try

revenue-NZ((Select revenue From [Sales by Campaign] AS B Where advertiser_id=
A.Advertiser_id And campaign_id = A.campaign_id And B.sales_date =
A.sales_date-1), 0)

You also need a comma to set off the subquery from the 0 argument of the NZ
function.

 

[kdh]

One more question, I promise. Is there an easy way to group by AdvId. Say i
just wanted to sum up the revenue difference among all the campaigns for a
given day.

Also, is there a way to select the top 10 greatest changes by day?

Thanks again for all the help

Thanks for the explanation. It worked great!

Thanks for the help! I appreciate it. I am running into a problem getting
it to run.

Syntax error in query expression revenue-NZ((Select revenue From Sales by
Campaign Where advertiser_id= A.Advertiser_id And campaign_id = A.campaign_id
And sales_date = A.sales_date-1)0)'.

Since you (unfortunately) took advantage of the ability to use blanks in table
and fieldnames, you must enclose any such field in square brackets. Otherwise
Access assumes that "Sales" is one thing, "by" is something else, and
"Campaign" is yet another something, since a blank is a delimiter.

Try

revenue-NZ((Select revenue From [Sales by Campaign] AS B Where advertiser_id=
A.Advertiser_id And campaign_id = A.campaign_id And B.sales_date =
A.sales_date-1), 0)

You also need a comma to set off the subquery from the 0 argument of the NZ
function.