Query doubling value

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-*"));
 
T

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
 
R

Rpettis31

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-*"));
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

updatable query 1
Sum means empty query? 5
combining queries into 1 8
3 queries into 1 8
Zero instead of null in query 2
Strange problem using VB Code in Query 3
query that sum two tables 5
SUM in a UNION query 2

Top