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

 

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