Comparing Query Results in two columns

[Alchemist61]

Hi Guys,
I need some help with a Query I am running from two seperate tables. One
column has Quantity shipped to date and another column has quantity shipped
this month. If a particular item did not shipped this month, it will not show
up in the Quantity shipped to date column although this item was shipped
previously. I do not know how to set the formula in the criteria row to
return a zero for the month this item was not shipped and the running
quantity to date in the other column. Please Advice.
I have tried the join properties with no success.
Thanks

 

[Clif McIrvin]

Post the SQL from your query and someone will quite likely be able to
see what you need.

 

[Alchemist61]

Thanks. Dont know if this will confuse the forum for I am very new th this
stuff.
My email is (e-mail address removed)

SELECT smsMTDqry.Categories, smsMTDqry.CarClass, smsMTDqry.Location,
smsMTDqry.SumOfQuantity, smsYTDqry.SumOfQuantity, smsYTDqry.Comments
FROM smsYTDqry INNER JOIN smsMTDqry ON (smsYTDqry.Categories =
smsMTDqry.Categories) AND (smsYTDqry.CarClass = smsMTDqry.CarClass) AND
(smsYTDqry.Location = smsMTDqry.Location);

 

[Jeff Boyce]

You are telling Access to only find records that match in both tables.

I thought you were interested in finding the "match" (so to speak) when
there were NO records for the month.

Maybe you could look at using a Left Join (all of one table, and any that
match from the other)...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Alchemist61]

Hey Jeff,
Thanks for your help. I did revised the Query and saw what I was doing
wrong. I did create the outer left join relationship and it works beautiful
except that it returns some fields as blank when there is nothing shipped for
that particular item for that month. How do I enter an expression in the
query to convert the blank fields into zeros?
Thanks