INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

combining tables

combining tables

(OP)
Hey All,
Question: I have two tables. One has dates:
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 

One has Sales figures:
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 

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

CODE --> SQL

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

Returns

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
		 

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.

RE: combining tables

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.

RE: combining tables

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

qcarDateUPC

CODE --> SQL

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


Then create a query like:

CODE --> SQL

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
[SumOfRetail $    ] 
?

Duane
Hook'D on Access
MS Access MVP

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close