Query Problem - Getting Wrong Result

Probably need to create a join on RawItmName i.e. "FROM RawStock as RStk INNER JOIN Kit_Stock as KStk ON RawItmName" depending on how your data is set up...

Please show us some sample data from each original table.

It might also help if you showed us the entire function or an excerpt of code from the program itself.

You didn't join the tables so they cross joined.
Need to full specify the join

FROM RawStock as RStk INNER JOIN Kit_Stock as KStk ON RSTk.RawItmNameKStk.RawItmName

mlmcc

Tried but
RunTime Error
Join Expression Not Supported.

    Rs.Open "Select Distinct(RStk.RawItmName) AS Item_Name, sum(RStk.Quantity) - sum(KStk.Quantity) as Quantity_, RStk.ItmUnit from RawStock as RStk INNER JOIN Kit_Stock as KStk ON RSTk.RawItmName where RStk.SleePDate between CDATE('" & DTPickerFrom.Value & "') AND CDATE('" & DTPickerTo.Value & "') group by RStk.RawItmName, RStk.ItmUnit", con, adOpenKeyset, adLockOptimistic

Select allOpen in new window

This syntax is not correct: INNER JOIN Kit_Stock as KStk ON RSTk.RawItmName

It's hard for me to know the exact syntax without knowing your table structure, but it needs to be something like:

INNER JOIN ItemID ON RStk.ItemID = KStk.ItemID

This of course is assuming some level of normalization in your tables. If you could upload a copy of your database (with sensitive data removed) we could solve this much more quickly I'm sure...

Sorry, missed the =

FROM RawStock as RStk INNER JOIN Kit_Stock as KStk ON RSTk.RawItmName = KStk.RawItmName

mlmcc

Thanks mlmcc
but i am getting only those record which are transacted.

Like.. two records in RawStock and one record is in Kit_Stock
your suggested query showed me only one record status which is in kit_stock.. but not another one which is still waiting for transaction..

Try this

Rs.Open "Select RStk.RawItmName Item_Name, sum(RStk.Quantity) - sum(KStk.Quantity) as Quantity_, RStk.ItmUnit 
from RawStock as RStk LEFT OUTER JOIN Kit_Stock as KStk 
ON RSTk.RawItmName = KStk.RawItmName
where RStk.SleePDate between CDATE('" & DTPickerFrom.Value & "') AND CDATE('" & DTPickerTo.Value & "') 
group by RStk.RawItmName, RStk.ItmUnit
", con, adOpenKeyset, adLockOptimistic

Select allOpen in new window

Thanks Brichsoft
its giving me correct RStk.RawItmName
but the quantity for Transacted  item is correct except another item's quantity.

another item's quantity. is null.

hi,

what do u mean by "another item's quantity."
you mean to say Kit_Stock.quantity then

yes...this could be possible as we are using left outer join.
null will come if there are no transaction done with the item.

you can convert null into 0 by using isnull(qty,0)

Sir
Actually i want exact figure also if there are no transaction done with the item.
that figure should come from RawStock  ( sum(RStk.Quantity) )..

i am trying to prepare a stock sheet..

Point increased :-)

Getting run time error
wrong number of arguments used with function in query expression
sum(RStk.Quantity - isnull(KStk.Quantity,0))

Whwere are you using the SQL?

What database?

mlmcc

currently i am using ACCESS database but sooner will be switch to SQL Server 2008.

in my vb form i am using following code.

Private Sub Command1_Click()

if con.stat = 1 then con.close
con.open

Set Rs = Nothing
If Rs.State = 1 Then Rs.Close

''''''''*******Query suggested by Experts Exchange.

Rs.Open "Select RStk.RawItmName, sum(RStk.Quantity - isnull(KStk.Quantity,0)) as Quantity_, RStk.ItmUnit from RawStock as RStk LEFT OUTER JOIN Kit_Stock as KStk ON RSTk.RawItmName = KStk.RawItmName where RStk.SleePDate between CDATE('" & DTPickerFrom.Value & "') AND CDATE('" & DTPickerTo.Value & "') group by RStk.RawItmName, RStk.ItmUnit", con, adOpenKeyset, adLockOptimistic
    
    If Rs.RecordCount > 0 Then
        DataGrid1.Visible = True
        Set DataGrid1.DataSource = Rs
        Command2.Visible = True
    End If

Select allOpen in new window

Rs.Open "Select Distinct(RStk.RawItmName) AS Item_Name, sum(RStk.Quantity) - sum(KStk.Quantity) as Quantity_, RStk.ItmUnit
from RawStock as RStk, Kit_Stock as KStk
where
rstk.rowitemname=ktsk.rowitemname and
RStk.SleePDate between CDATE('" & DTPickerFrom.Value & "') AND CDATE('" & DTPickerTo.Value & "')
group by RStk.RawItmName, RStk.ItmUnit", con, adOpenKeyset, adLockOptimistic

Vadim,

Distinct is not a function, the proper syntax is    DISTINCT RStk.RawItmName
further the distinct is pointless, as you are grouping by that column, which makes it distinct automatically.
Both does no harm, so nothing serious, just sayin'.

But you eliminated the outer join - was that by accident? The IMO correct adaption is

Rs.Open "Select RStk.RawItmName, sum(RStk.Quantity) - isnull(sum(KStk.Quantity),0) as Quantity_, RStk.ItmUnit " & _
" from RawStock as RStk LEFT OUTER JOIN Kit_Stock as KStk " & _
" ON RSTk.RawItmName = KStk.RawItmName " & _
" where RStk.SleePDate between CDATE('" & DTPickerFrom.Value & "') AND CDATE('" & DTPickerTo.Value & "') " & _
" group by RStk.RawItmName, RStk.ItmUnit", con, adOpenKeyset, adLockOptimistic

Select allOpen in new window

CDATE is a MS Access function, and needs to get replaced with a convert(datetime, ...) for MSSQL.

Qlemo, you're 100% right, I have to admit that I did not try it, and simply inserted another WHERE clause in the right place.

Regarding the JOIN. I don't know whether it has to be outer join or inner, perhaps it was in the discussion and I missed it; but if it's INNER JOIN, then the simplest way is to specify it in the old-fashioned way, without the word JOIN at all: that is, instead of

FROM a INNER JOIN b ON a.c=b.c

to put

FROM a,b where a.c=b.c

which I believe is more agnostic about the dialect of SQL and will work in any relational database (so I did not eliminate it completely but replaced by rstk.rowitemname=ktsk.rowitemname)

Re. DISTINCT - I don't think it's even needed in this query, given that GROUP BY is already there.

The author does not say what is the problem in his last comment #a38425876, but since he requested attention, I thought that showing another way might be good idea. Perhaps if he clarified what is not right in the code in his comment, we will be able to help better. Something still does not work?

Looking at the code as posted, one can clearly see crude syntax errors that should be highlighted already in VB, such as if con.stat = 1 instead of if con.state = 1 , so if it's really posted like it is, no doubt it won't work.

...regarding the plans to upgrade to sql server - note that construct

between CDATE('" & DTPickerFrom.Value & "') AND CDATE('" & DTPickerTo.Value & "

will require revision, since SQL server does not know CDATE function.

I've requested that this question be deleted for the following reason:

The question has either no comments or not enough useful information to be called an "answer".

@  Brichsoft

I think your suggestion is working but need little changes.. let me test more ..
will be back soon as possible.