combining tables

[dingleberry]

Hey All,
Question: I have two tables. One has dates:
[pre]WE Date
2/28/2015
3/7/2015
3/14/2015
3/21/2015
3/28/2015
4/4/2015
4/11/2015
4/18/2015[/pre]

One has Sales figures:
[pre]Store Dept UPC Description SUB_CATEGORY SumOfQty     SumOfCost     SumOfRetail $     WE Date
3009 BAKERY 73314723576 BUN HAMBURGER WHITE BAKERY-CENTRAL-BUNS 4 $3.88 $11.56 4/18/2015
3009 BAKERY 73314723576 BUN HAMBURGER WHITE BAKERY-CENTRAL-BUNS 31 $30.07 $89.59 4/11/2015
3009 BAKERY 73314723576 BUN HAMBURGER WHITE BAKERY-CENTRAL-BUNS 1 $0.97 $2.89 4/4/2015
3009 BAKERY 73314723576 BUN HAMBURGER WHITE BAKERY-CENTRAL-BUNS 19 $18.43 $54.91 3/21/2015
3009 BAKERY 73314723576 BUN HAMBURGER WHITE BAKERY-CENTRAL-BUNS 35 $33.95 $101.15 3/14/2015
3009 BAKERY 73314723576 BUN HAMBURGER WHITE BAKERY-CENTRAL-BUNS 39 $37.83 $112.71 3/7/2015
3009 BAKERY 73314723576 BUN HAMBURGER WHITE BAKERY-CENTRAL-BUNS 1 $0.97 $2.89 2/28/2015[/pre]

I can link on WE Date as a common field however if you look closely you will see no data for W/E 3/28 in the sales figures. With the following query, I can get all dates listed but of course because there is no sales data for W/E 3/28, the date shows up but all other fields are empty after running

SELECT dates.[WE Date], shrink.UPC, shrink.[SumOfRetail $    ]
FROM dates LEFT JOIN shrink ON dates.[WE Date] = shrink.[WE Date];

Returns

[pre]WE Date UPC SumOfRetail $    
2/28/2015 73314723576 $2.89
3/7/2015 73314723576 $112.71
3/14/2015 73314723576 $101.15
3/21/2015 73314723576 $54.91
3/28/2015
4/4/2015 73314723576 $2.89
4/11/2015 73314723576 $89.59
4/18/2015 73314723576 $11.56
[/pre]

But what I am hoping to achieve is for that row on w/e 3/28 to include the same sku and a value of 0 for SumOfRetail $.

Before I go, I should also include that there are multiple UPC's in the master database so just using a Nz([UPC],"73314723576") function would probably fail because any other UPC's that I have this issue for would then be populated with that string. I am wondering if there is a way that Access could populate it with the UPC as it's looping through that one UPC for all dates.

 

[SkipVought]

Hi,

No, there's no looping.

So why force the reader to see page upon page of irrelevant NOTHING?

You would effectively need your Date table to ALSO have a UPC column.

 

[dhookom]

dingleberry,
Is your object to show every possible SKU for every [WE Date]? If so, you can create a Cartesian query with SQL like:

[highlight #FCE94F]qcarDateUPC[/highlight][highlight #FCE94F][/highlight]

SELECT DISTINCT [WE Date], UPC
FROM  dates, shrink

Then create a query like:

SELECT qcarDateUPC.[WE Date], qcarDateUPC.UPC, Nz(shrink.[SumOfRetail $    ],0) as SumOfRetail
FROM qcarDateUPC LEFT JOIN shrink ON qcarDateUPC.[WE Date] = shrink.[WE Date] and qcarDateUPC.UPC = shrink.UPC;

BTW: how did you ever end up with a field name like [pre][SumOfRetail $ ][/pre]?

Duane
Hook'D on Access
MS Access MVP