Masters... PLEASE HELP!!

[sipi41]

I'm sorry to bother you but I have a query that is breaking my head

I have a table like this... (which say the name of a location, the product return and which week, especified as 01.02 which means that is year1, week 2... etc... so... for each week i have a record for the location)

LOCATION NAME --- WEEK RETURNS
---------------------------------------------------


Location #1 ----- 01.22 ----- 3
Location #2 ----- 01.22 ----- 1
Location #3 ----- 01.22 ----- 5
---------------------------------------------------
Location #1 ----- 01.23 ----- 8
Location #2 ----- 01.23 ----- 0 *
Location #3 ----- 01.23 ----- 3
---------------------------------------------------
Location #1 ----- 01.24 ----- 0
Location #2 ----- 01.24 ----- 0 *
Location #3 ----- 01.24 ----- 3
---------------------------------------------------
Location #1 ----- 01.25 ----- 1
Location #2 ----- 01.25 ----- 0 *
Location #3 ----- 01.25 ----- 4

WHAT I WANT is to have the posibility to list from this table ONLY the locations that in the past 3 weeks have 0 return (which I marked with *), in the example I have the location #2... because that's the only one that for the last 3 weeks have 0 returns. IS THIS POSSIBLE?

THANKS FOR YOUR HELP!!!

[jya]

Is the Week field a text field?
Do you want to automatically have the query take the last three weeks or can you enter the last week as part of a parameter query?
Are you familiar with a Total query?

[sipi41]

I have several fields... but I will use those three only... and yes... I would like the query to automatically take the last three weeks... thanks for your help!

[jya]

Create a Total query.
Use 'Group By' for Location Name.
Select 'Sum' for Returns. Under the criteria for Returns, I would enter 0.
Create a Week Number column by extracting the last two values from the Week field.

ex. Right([Week],2)

This assumes that the Week field is a text field and that the first weeks are stored as two digits ex. 02 for week 2. For the Week Number column, select 'Where' and enter your criteria.

ex. Between [Max] and [Max] - 2

If you run this query 'as is' you will be prompted for a Max value. If you need the query to automatically take the last week, you could create a separate Total query that takes the 'Max' value of the right two digits.

I have not done any extensive testing with this, but it should get you started. This example is based on all of the data being in the same year. To take into account the year, you will need to extract and add the Year information as well.