Query doubling value

[Rpettis31]

I have a query and for some reason when I run it my value on my forecast
field is doubled. So if there is a value for a month of 11,000 when I run
the query the value shows as 22,000. It does not do this on all the lines so
I am not sure what I am doing wrong.

SELECT tblInventoryTEST.Item, tblInventoryTEST.Reason,
Sum(tblForecasts.[avail-qty]) AS [SumOfavail-qty], tblInventoryTEST.Netting,
tblInventoryTEST.OnPO, ([Netting]+[OnPO])-Sum([orig-qty]) AS Balance
FROM (tblForecasts INNER JOIN tblInventoryTEST ON tblForecasts.item =
tblInventoryTEST.Item) INNER JOIN tblOpenPOs ON tblInventoryTEST.Item =
tblOpenPOs.Item
WHERE (((tblForecasts.[fcst-date])=#3/1/2009#) AND ((tblOpenPOs.[Due
Dt])>#2/1/2009#))
GROUP BY tblInventoryTEST.Item, tblInventoryTEST.Reason,
tblInventoryTEST.Netting, tblInventoryTEST.OnPO
HAVING (((tblInventoryTEST.Item) Not Like "DI-*"));

 

[Tom van Stiphout]

On Tue, 9 Dec 2008 15:11:01 -0800, Rpettis31

Temporarily put the PKs of all tables in the Select and GroupBy
clause. You'll find that you're doubling up because of some table on
the Outer side of the relation.

-Tom.
Microsoft Access MVP

 

[Rpettis31]

These are linked tables from Excel.
I joined all the tables via the item number.

Still having the same issue.

On Tue, 9 Dec 2008 15:11:01 -0800, Rpettis31

Temporarily put the PKs of all tables in the Select and GroupBy
clause. You'll find that you're doubling up because of some table on
the Outer side of the relation.

-Tom.
Microsoft Access MVP

I have a query and for some reason when I run it my value on my forecast
field is doubled. So if there is a value for a month of 11,000 when I run
the query the value shows as 22,000. It does not do this on all the lines so
I am not sure what I am doing wrong.

SELECT tblInventoryTEST.Item, tblInventoryTEST.Reason,
Sum(tblForecasts.[avail-qty]) AS [SumOfavail-qty], tblInventoryTEST.Netting,
tblInventoryTEST.OnPO, ([Netting]+[OnPO])-Sum([orig-qty]) AS Balance
FROM (tblForecasts INNER JOIN tblInventoryTEST ON tblForecasts.item =
tblInventoryTEST.Item) INNER JOIN tblOpenPOs ON tblInventoryTEST.Item =
tblOpenPOs.Item
WHERE (((tblForecasts.[fcst-date])=#3/1/2009#) AND ((tblOpenPOs.[Due
Dt])>#2/1/2009#))
GROUP BY tblInventoryTEST.Item, tblInventoryTEST.Reason,
tblInventoryTEST.Netting, tblInventoryTEST.OnPO
HAVING (((tblInventoryTEST.Item) Not Like "DI-*"));