Query doubling value

  • Thread starter Thread starter Rpettis31
  • Start date Start date
R

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-*"));
 
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
 
These are linked tables from Excel.
I joined all the tables via the item number.

Still having the same issue.
Tom van Stiphout said:
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-*"));
Click to expand...
Click to expand...
 
Back
Top