Problem on multiple table query.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
SELECT Master_Style.Style, Master_Style.Cost, mdb_phy.Loc AS [Loc Phy],
Sum(mdb_phy.Qty) AS [Phy Qty], [mdb-sys].Loc AS [Loc Sys], Sum([mdb-sys].Qty)
AS [Sys Qty]
FROM mdb_phy RIGHT JOIN ([mdb-sys] RIGHT JOIN Master_Style ON
[mdb-sys].Style = Master_Style.Style) ON mdb_phy.Style = Master_Style.Style
GROUP BY Master_Style.Style, Master_Style.Cost, mdb_phy.Loc, [mdb-sys].Loc
HAVING (((mdb_phy.Loc)=20) AND (([mdb-sys].Loc)=20));

Let me explain about the data in 3 tables involved. master_style contain 2
field, style
and cost. Style is product code.

mdb_sys contain the system inventory balance many warehouse location. The
field "loc" represent the warehouse location.

mdb_phy contain the physical inventory balance that we gathered on physical
stock count.

I need to show a query that shows system inventory and physical inventory
group by style. Since some of the style will only exist either in mdb_sys or
mdb_Phy, I use master_sytle table for the style and the cost. Below are the
filed in the query that I intend to have:-

field 1: Style (from table master_style)
field 2: Cost (from table master_style)
field 3: Loc Phy (from table mdb_phy)
field 4: Phy Qty (from table mdb_phy)
field 5: Loc Phy (from table mdb_sys)
field 6: Phy Qty (from table mdb_sys)

If you notice I have set the location to "20". I would like to print the
query one by one location.
After print the query for location 20, then I will change it to other
location and
run the query again.

With the query that I have created the result is totally wrong. The total
qty in mdb_phy instead of 4800, it shown as 20,000 and in mdb_sys instead of
6000 shonw as 25,000. I do not know where is my mistake.

Pls advise. Thank You very much in advance.

Mark Magesen
In peace we live, together we achieve :)
 
It appears to me that you may be trying to do too much in one query.
I suggest you first check the select and join to be sure you are not getting
unexpected duplicate records in the query result. Join can create duplicates
if the relations and join are not done properly.
Then add the sum and grouping functions one at a time.
You can have a form where you specify the style of interest, passing the
value into the query for the criteria.
I hope these suggestions help
Hugh
 
Back
Top