Appending Missing Dates

[Jerseynjphillypa]

I have two tables which are being linked into a database.

The first table is called "Sell Yes or No", where it lets me know if I Sell the item (Y) or not (N).

Next is a table called "Date Data". This table shows the item, DateEnter and the amount.

So If I sell a item (Y), and there is a date for that item in the date data, I want all the item that I sell that is a "Y" to have that same date and the amount to be 0 if it does not have an amount to it.

So I would like to see

Item	Sell	dateenter	amount
shoes	y	1/1/2012	2
Shirt	y	1/1/2012	0
Jeans	y	1/1/2012	0
shoes	y	2/1/2012	0
shirt	y	2/1/2012	2
jeans	y	2/1/2012	4

[Robeen]

It is not clear exactly what you need.
Could you describe in different words, please - so that it is easier to understand?
Give us more details of what you want.

[Jerseynjphillypa]

I would like for all Item that I Sell to all have the Same Amount of DateEnter. If the amount is not in the Date Data then the amount should be set to 0.

So for the three items I sell, (Shoes, Shirt and Jeans) they should have dates 1/1/2012 and 2/1/2012.

Shoes should have the amount of 2 from the Date Data with the date 1/1/2012. But Shirt and Jeans amount should be 0for 1/1/2012 since there was nothing in the Date Data.

So for shoes amount should be 0for 2/2/2012 since there was nothing in he Date Data. Shirt and Jeans amount should be 2 and 4 respectively for 2/1/2012 from the Date Data.

Please let me know if this is still unclear.

[June7]

Try:

1. Query1
SELECT [Date Data].DateEnter, [Sell Yes or No].Item, [Sell Yes or No].Sell
FROM [Date Data], [Sell Yes or No]
GROUP BY [Date Data].DateEnter, [Sell Yes or No].Item, [Sell Yes or No].Sell
HAVING ((([Sell Yes or No].Sell)="y"));

2. Query2
SELECT Query1.*, Nz([Amount],0) AS Amt
FROM [Date Data] RIGHT JOIN Query1 ON ([Date Data].DateEnter = Query1.DateEnter) AND ([Date Data].Item = Query1.Item);