Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Query Problem - Getting Wrong Result

Posted on 2012-09-20
24
Medium Priority
?
410 Views
Last Modified: 2012-10-24
Hi
i have two table with same structure.
and i am trying  to calculate data from bellow query , to get actual available stock.
but it show me wrong result like
Item_Name  Quantity_  ItmUnit
Crystal              -4             GRAMS
STONES             -4             KG
STONES          495             GRAMS
BURFIL              0              KG

It supposed to be

Item_Name  Quantity_  ItmUnit
Crystal                6             GRAMS
STONES               1             KG
STONES           500             GRAMS
BURFIL                5             KG

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.SleePDate between CDATE('" & DTPickerFrom.Value & "') AND CDATE('" & DTPickerTo.Value & "') group by RStk.RawItmName, RStk.ItmUnit", con, adOpenKeyset, adLockOptimistic

Open in new window

0
Comment
Question by:crystal_Tech
  • 7
  • 3
  • 3
  • +5
21 Comments
 
LVL 11

Expert Comment

by:Michael Vasilevsky
ID: 38419473
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...
0
 
LVL 13

Expert Comment

by:themrrobert
ID: 38419535
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.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 38419684
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
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 1

Author Comment

by:crystal_Tech
ID: 38419897
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

Open in new window

0
 
LVL 11

Expert Comment

by:Michael Vasilevsky
ID: 38420127
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...
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 38420277
Sorry, missed the =

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

mlmcc
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 38420491
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..
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 38420813
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

Open in new window

0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 38421923
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.
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 38422043
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)
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 38422185
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 :-)
0
 
LVL 19

Accepted Solution

by:
Bhavesh Shah earned 2000 total points
ID: 38422321
try this

Rs.Open "Select RStk.RawItmName Item_Name, 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
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 38422374
Getting run time error
wrong number of arguments used with function in query expression
sum(RStk.Quantity - isnull(KStk.Quantity,0))
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 38424006
Whwere are you using the SQL?

What database?

mlmcc
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 38425876
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

Open in new window

0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 38430146
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
0
 
LVL 72

Expert Comment

by:Qlemo
ID: 38430284
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

Open in new window

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

Expert Comment

by:Vadim Rapp
ID: 38430856
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.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 38430860
...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.
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38529908
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".
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 38520253
@  Brichsoft

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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question