v lookup and between

[ajdesigns]

Hi
I have a workbook where 3 sheets contain sales data different products (sheet 1, product 1, in weekly buckets ie w/ending 4/12/04,w/e 12/12/04 and so on), the fourth sheet contains the orders data for all three products, but these are random dates not in the same date buckets.I need to know what orders there were for the sales periods ie weekly buckets.

 

[SkipVought]

Hi,

YOU can see your sheet 4, but WE cannot.

Please post a sample of data from sheet 4.

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[ajdesigns]

sorry
This is a sample of the data in each sales table,
Product A2283J01
W/C W/E Sales
05/12/2004 11/12/2004 5101
12/12/2004 18/12/2004 4113
19/12/2004 25/12/2004 2106
26/12/2004 01/01/2005 574
02/01/2005 08/01/2005 2814
09/01/2005 15/01/2005 4467
16/01/2005 22/01/2005 2319
23/01/2005 29/01/2005 2037
30/01/2005 05/02/2005 2559
06/02/2005 12/02/2005 4395

Product A2300C51
W/C W/E Sales
05/12/2004 11/12/2004 6132
12/12/2004 18/12/2004 4113
19/12/2004 25/12/2004 2106
26/12/2004 01/01/2005 574
02/01/2005 08/01/2005 2814
09/01/2005 15/01/2005 4467
16/01/2005 22/01/2005 2319
23/01/2005 29/01/2005 2037
30/01/2005 05/02/2005 2559
06/02/2005 12/02/2005 4395

product A2304C51
W/C W/E Sales
05/12/2004 11/12/2004 10228
12/12/2004 18/12/2004 9255
19/12/2004 25/12/2004 4958
26/12/2004 01/01/2005 3230
02/01/2005 08/01/2005 15630
09/01/2005 15/01/2005 10975
16/01/2005 22/01/2005 12724
23/01/2005 29/01/2005 17412
30/01/2005 05/02/2005 10432
06/02/2005 12/02/2005 9504

while this is a sample of the orders data
product date_entered order_qty
A2300C51 02/11/2004 00:00 1404
A2304C51 02/11/2004 00:00 1512
A2300C51 03/11/2004 00:00 1404
A2300C51 04/11/2004 00:00 6876
A2283J01 08/11/2004 00:00 3816
A2300C51 08/11/2004 00:00 1044
A2300C51 08/11/2004 00:00 6876
A2304C51 08/11/2004 00:00 2448
A2304C51 08/11/2004 00:00 3888
A2304C51 11/11/2004 00:00 828
A2283J01 16/11/2004 00:00 7200
A2300C51 16/11/2004 00:00 504
A2300C51 16/11/2004 00:00 3312
A2304C51 16/11/2004 00:00 720
A2304C51 16/11/2004 00:00 1512
A2304C51 16/11/2004 00:00 5004
A2304C51 16/11/2004 00:00 7056
A2300C51 17/11/2004 00:00 3024
A2283J01 19/11/2004 00:00 864
A2300C51 19/11/2004 00:00 972
A2300C51 19/11/2004 00:00 2160
A2304C51 19/11/2004 00:00 1404
A2304C51 19/11/2004 00:00 2484
A2304C51 19/11/2004 00:00 3023
A2283J01 25/11/2004 00:00 2808
A2283J01 25/11/2004 00:00 3728

I need to match the orders with the sales for the same period.

 

[SkipVought]

First of all, I can't find any data that correlates in your example.

2. I reorganized your PRODUCT data into ONE SHEET with the following headings
[tt]
Product WC WE Sales
[/tt]
on a sheet named Products

Another sheet named Sales

I removed the slashes -- reaks HAVOC!

3. I did a simple MS Query (menu item Data/Get External Data/Excel Files - MY WORKBOOK - sheet Products, [next],[next],[next],select EDIT query and [finish]

In the query editor I added the Sales sheet (table), joined the table on Product, dragged in the sales column data I wanted, and added a Criteria:
[tt]
S.date_entered between P.`WC` and P.`WE`
[/tt]
VOLA!

all in about 15 minutes.

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[ajdesigns]

I run the query as you suggested 30 records returned (10 per product)which is correct, but as soon as I enter the criteria sales.date_entered between Product.WC and P.WE then rerun the query I am dropping down to 20 returned loosing a third of the data.
any thoughts

 

[SkipVought]

Here's what you can to to PROVE that your query is working as you wish.

In the next column adjacent to the query return resultset, enter the formula, starting in row 2
[tt]
=IF(AND(E2>=B2,E2<=C2),1,0)
[/tt]
where column
E is the Sales.date_entered
B is Products.WC
C is Products.WE

and then see what the SUM of that column is. You should get a 1, every time the sales date is between the WC & WE dates.

BTW, with the data that you supplied, I got THREE rows returned.

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[ajdesigns]

Hi Skip
When I try to run the query now in excel it goes on forever and locks the prog, so I decided to try it in access.
Every thing is working apart from:
If there are no orders for a period in the sales table the sales data does not show. Do you know how to force the sales data to show ?say with a zero for orders.
Here's an example.
Product WC WE Sales SumOforder_qty
A2283J01 05/12/2004 11/12/2004 5101 32681
A2283J01 12/12/2004 18/12/2004 4113 4752
A2283J01 02/01/2005 08/01/2005 2814 3744
A2283J01 09/01/2005 15/01/2005 4467 4104
A2283J01 16/01/2005 22/01/2005 2319 4140
A2283J01 30/01/2005 05/02/2005 2559 3204
A2300C51 05/12/2004 11/12/2004 6132 28723
A2300C51 12/12/2004 18/12/2004 11164 15984
A2300C51 09/01/2005 15/01/2005 7725 2520
A2300C51 16/01/2005 22/01/2005 3910 3096
A2300C51 23/01/2005 29/01/2005 5886 4176
A2300C51 30/01/2005 05/02/2005 9882 2670
A2300C51 06/02/2005 12/02/2005 5099 1008
A2304C51 05/12/2004 11/12/2004 10228 60582
A2304C51 12/12/2004 18/12/2004 9255 6012
A2304C51 02/01/2005 08/01/2005 15630 4242
A2304C51 09/01/2005 15/01/2005 10975 540
A2304C51 16/01/2005 22/01/2005 12724 4896
A2304C51 23/01/2005 29/01/2005 17412 1512
A2304C51 30/01/2005 05/02/2005 10432 5718

As you can see ther are date ranges missing eg:
19/12/04 to 25/12/04 and 26/12/04 to 01/01/05

 

[SkipVought]

If there are no orders for a period in the sales table the sales data does not show

Is that really suprising? Of course there is no data! Why would you want to see NOTHING?

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}