Need help with query

  • Thread starter Thread starter Mavig
  • Start date Start date
M

Mavig

I have a database that gets weekly updates. The data is appened everyweek to
the existing table. I want to search the entire table and find which product
has/had the biggest gain in sales at anytime from one week to the next week.
So the question is how do I compare a product with it's sale price for every
week and display the product with the biggest sales for a week to week
period.
 
Create a query in design view by placing two copies of the table in the space
above the grid. Access will add a sufix of '_1' to the second copy. Join
the tables on the product. Pull product from first table into the grid.
Pull the sales date from the second table and make a calculated field like
this --
Year_Week: Format([Table_1].[SalesDate], "yyyyww")
For criteria use --
Format(DateAdd("ww", 1,
.[SalesDate]), "yyyyww")
Then add a calculated field of the sales like this --
Price_Difference:
.[SalesDate] - [Table_1].[Sales]
Sort descending on the Price_Difference field.
 
Hi Thanks for your help. But I am still having a few problems with the query.
In the price_diff field I am getting a date. I am looking for the greatest
sales from one week to the next on every product. In the year_week field I
get for example "20084" not sure what this is suppose to do to help me.I
understand it is giving me the year and the week of the year.What am I
missing something??

Any more help would be greatly appreicated.

KARL DEWEY said:
Create a query in design view by placing two copies of the table in the space
above the grid. Access will add a sufix of '_1' to the second copy. Join
the tables on the product. Pull product from first table into the grid.
Pull the sales date from the second table and make a calculated field like
this --
Year_Week: Format([Table_1].[SalesDate], "yyyyww")
For criteria use --
Format(DateAdd("ww", 1,
.[SalesDate]), "yyyyww")
Then add a calculated field of the sales like this --
Price_Difference:
.[SalesDate] - [Table_1].[Sales]
Sort descending on the Price_Difference field.
--
KARL DEWEY
Build a little - Test a little


Mavig said:
I have a database that gets weekly updates. The data is appened everyweek to
the existing table. I want to search the entire table and find which product
has/had the biggest gain in sales at anytime from one week to the next week.
So the question is how do I compare a product with it's sale price for every
week and display the product with the biggest sales for a week to week
period.
Click to expand...
Click to expand...
 
Hi Again, I figure the price_diff problem. I use the sale date for the table.
I believe you meant the sales not sale date. I should of pick it up. Thanks,
but what is the purpose of the year_week field? the query is working now.

Where can I find out more info. about these type of expression.

Mavig said:
Hi Thanks for your help. But I am still having a few problems with the query.
In the price_diff field I am getting a date. I am looking for the greatest
sales from one week to the next on every product. In the year_week field I
get for example "20084" not sure what this is suppose to do to help me.I
understand it is giving me the year and the week of the year.What am I
missing something??

Any more help would be greatly appreicated.

KARL DEWEY said:
Create a query in design view by placing two copies of the table in the space
above the grid. Access will add a sufix of '_1' to the second copy. Join
the tables on the product. Pull product from first table into the grid.
Pull the sales date from the second table and make a calculated field like
this --
Year_Week: Format([Table_1].[SalesDate], "yyyyww")
For criteria use --
Format(DateAdd("ww", 1,
.[SalesDate]), "yyyyww")
Then add a calculated field of the sales like this --
Price_Difference:
.[SalesDate] - [Table_1].[Sales]
Sort descending on the Price_Difference field.
--
KARL DEWEY
Build a little - Test a little


Mavig said:
I have a database that gets weekly updates. The data is appened everyweek to
the existing table. I want to search the entire table and find which product
has/had the biggest gain in sales at anytime from one week to the next week.
So the question is how do I compare a product with it's sale price for every
week and display the product with the biggest sales for a week to week
period.
Click to expand...
Click to expand...
Click to expand...
 
Ok I now undestand all the code. Thank you very much. But I am having a
problem.
In the tblsalesdate and tbl_1salesdate I am getting the date multiple times,
for example 6/7/2008 -6/14/2008 will show up 3 -4 times. So what it does, it
will calc the difference for sales from 6/7 - 6/14 and give the right value,
then it will do it again for 6/7-6/14 but the value in the sale for 6/14 will
be zero and I will have a negative number in the pricediff. not sure why it
does it

Any help would be appreicated.


KARL DEWEY said:
Create a query in design view by placing two copies of the table in the space
above the grid. Access will add a sufix of '_1' to the second copy. Join
the tables on the product. Pull product from first table into the grid.
Pull the sales date from the second table and make a calculated field like
this --
Year_Week: Format([Table_1].[SalesDate], "yyyyww")
For criteria use --
Format(DateAdd("ww", 1,
.[SalesDate]), "yyyyww")
Then add a calculated field of the sales like this --
Price_Difference:
.[SalesDate] - [Table_1].[Sales]
Sort descending on the Price_Difference field.
--
KARL DEWEY
Build a little - Test a little


Mavig said:
I have a database that gets weekly updates. The data is appened everyweek to
the existing table. I want to search the entire table and find which product
has/had the biggest gain in sales at anytime from one week to the next week.
So the question is how do I compare a product with it's sale price for every
week and display the product with the biggest sales for a week to week
period.
Click to expand...
Click to expand...
 
what is the purpose of the year_week field?
That is so you can compare one week to the following week by adding 1 to the
year_week value as criteria.

--
KARL DEWEY
Build a little - Test a little


Mavig said:
Hi Again, I figure the price_diff problem. I use the sale date for the table.
I believe you meant the sales not sale date. I should of pick it up. Thanks,
but what is the purpose of the year_week field? the query is working now.

Where can I find out more info. about these type of expression.

Mavig said:
Hi Thanks for your help. But I am still having a few problems with the query.
In the price_diff field I am getting a date. I am looking for the greatest
sales from one week to the next on every product. In the year_week field I
get for example "20084" not sure what this is suppose to do to help me.I
understand it is giving me the year and the week of the year.What am I
missing something??

Any more help would be greatly appreicated.

KARL DEWEY said:
Create a query in design view by placing two copies of the table in the space
above the grid. Access will add a sufix of '_1' to the second copy. Join
the tables on the product. Pull product from first table into the grid.
Pull the sales date from the second table and make a calculated field like
this --
Year_Week: Format([Table_1].[SalesDate], "yyyyww")
For criteria use --
Format(DateAdd("ww", 1,
.[SalesDate]), "yyyyww")
Then add a calculated field of the sales like this --
Price_Difference:
.[SalesDate] - [Table_1].[Sales]
Sort descending on the Price_Difference field.
--
KARL DEWEY
Build a little - Test a little


:

I have a database that gets weekly updates. The data is appened everyweek to
the existing table. I want to search the entire table and find which product
has/had the biggest gain in sales at anytime from one week to the next week.
So the question is how do I compare a product with it's sale price for every
week and display the product with the biggest sales for a week to week
period.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top