query error

[pat67]

Hi, I am running a query based on 3 queries

q1 has this

PurchDoc Item Rel Vendor Vendor name Order Type PO Date
Material Short Text MRPCn Del Date Deliv Date PO Quantity GR
Quantity QtyRed RemQty PD release Reschdate CC Created on Exc Net
Order Value ID
46353228 190 F 10000019 RA LALLI NB 11/6/2006
109-0718-13-101 PASSACINGHIA AK7 6/13/2013
44 42 0 0 1/2/2010 11,396.00
14632


q2 has this

PurchDoc Item Rel Vendor Vendor name Order Type PO Date
Material Short Text MRPCn Del Date Deliv Date PO Quantity GR
Quantity QtyRed RemQty PD release Reschdate CC AB date Exc Net
Order Value ID
46353228 190 F 10000019 RA LALLI NB 11/6/2006
109-0718-13-101 PASSACINGHIA AK7 6/13/2013 6/13/2013
44 42 12 0 1/2/2010 11/21/2012 AB 3/9/2010 10
11,396.00 14638
46353228 190 F 10000019 RA LALLI NB 11/6/2006
109-0718-13-101 PASSACINGHIA AK7 6/13/2013 6/13/2013
44 42 14 0 1/2/2010 11/21/2012 AB 3/9/2010 10
11,396.00 14636
46353228 190 F 10000019 RA LALLI NB 11/6/2006
109-0718-13-101 PASSACINGHIA AK7 6/13/2013 6/13/2013
44 42 18 0 1/2/2010 11/21/2012 AB 3/9/2010 10
11,396.00 14637



q3 has this


PurchDoc Item Rel Vendor Vendor name Order Type PO Date
Material Short Text MRPCn Del Date Deliv Date PO Quantity GR
Quantity QtyRed RemQty PD release Reschdate CC CH Date Exc Net
Order Value ID
46353228 190 F 10000019 RA LALLI NB 11/6/2006
109-0718-13-101 PASSACINGHIA AK7 6/13/2013 6/13/2013
44 42 10 2 1/2/2010 11/21/2012 CH 3/9/2010 10
11,396.00 14635
46353228 190 F 10000019 RA LALLI NB 11/6/2006
109-0718-13-101 PASSACINGHIA AK7 6/13/2013 6/13/2013
44 42 14 0 1/2/2010 CH 3/9/2010 11,396.00
14633
46353228 190 F 10000019 RA LALLI NB 11/6/2006
109-0718-13-101 PASSACINGHIA AK7 6/13/2013 6/13/2013
44 42 18 0 1/2/2010 CH 3/9/2010 11,396.00
14634



then i run this query

SELECT q1.PurchDoc, q1.Item, q1.Rel, q1.Vendor, q1.[Vendor name], q1.
[PO Date], q1.Material, q1.[Short Text], q1.MRPCn, q1.[Del Date], q1.
[PO Quantity], q1.[GR Quantity], ([q1]![PO Quantity]-[q1]![GR
Quantity]) AS [Open Qty], q1.[PD release], IIf(IsNull([q1]!
[Exc]),IIf(IsNull([q2]![Exc]),IIf(IsNull([q3]![Exc]),Null,[q3]![Exc]),
[q2]![Exc]),[q1]![Exc]) AS [Exc Code], IIf(IsNull([q1]!
[Reschdate]),IIf(IsNull([q2]![Reschdate]),IIf(IsNull([q3]!
[Reschdate]),Null,[q3]![Reschdate]),[q2]![Reschdate]),[q1]!
[Reschdate]) AS [Resch Date], q2.[AB Date], q3.[CH Date], q1.[Net
Order Value], (([q1]![Net Order Value]/[q1]![PO Quantity])*[Open Qty])
AS [Open Value], Date() AS [Extraction Date]
FROM (q1 LEFT JOIN q2 ON (q1.Item=q2.Item) AND
(q1.PurchDoc=q2.PurchDoc)) LEFT JOIN q3 ON (q2.Item=q3.Item) AND
(q2.PurchDoc=q3.PurchDoc)
GROUP BY q1.PurchDoc, q1.Item, q1.Rel, q1.Vendor, q1.[Vendor name], q1.
[PO Date], q1.Material, q1.[Short Text], q1.MRPCn, q1.[Del Date], q1.
[PO Quantity], q1.[GR Quantity], q1.[PD release], q2.[AB Date], q3.[CH
Date], q1.[Net Order Value], q1.Exc, q2.Exc, q3.Exc, q1.Reschdate,
q2.Reschdate, q3.Reschdate
ORDER BY q1.Vendor;


I am getting this duplicate value

PurchDoc Item Rel Vendor Vendor name PO Date Material
Short Text MRPCn Del Date PO Quantity GR Quantity Open Qty PD
release Exc Code Resch Date AB Date CH Date Net Order Value
Open Value Extraction Date
46353228 190 F 10000019 RA LALLI 11/6/2006 109-0718-13-101
PASSACINGHIA AK7 6/13/2013 44 42 2 1/2/2010
10 11/21/2012 3/9/2010 3/9/2010 $11,396.00 $518.00
3/19/2010
46353228 190 F 10000019 RA LALLI 11/6/2006 109-0718-13-101
PASSACINGHIA AK7 6/13/2013 44 42 2 1/2/2010
10 11/21/2012 3/9/2010 3/9/2010 $11,396.00 $518.00
3/19/2010


I should only get on entry not two.

Any ideas?

 

[John Spencer]

I would suspect that some of the fields you are grouping by and not showing
have a different value for the rows returned. Try displaying those fields.
You can determine if you need them in the query or not.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Hi, I am running a query based on 3 queries

then i run this query

SELECT q1.PurchDoc, q1.Item, q1.Rel, q1.Vendor, q1.[Vendor name], q1.
[PO Date], q1.Material, q1.[Short Text], q1.MRPCn, q1.[Del Date], q1.
[PO Quantity], q1.[GR Quantity], ([q1]![PO Quantity]-[q1]![GR
Quantity]) AS [Open Qty], q1.[PD release], IIf(IsNull([q1]!
[Exc]),IIf(IsNull([q2]![Exc]),IIf(IsNull([q3]![Exc]),Null,[q3]![Exc]),
[q2]![Exc]),[q1]![Exc]) AS [Exc Code], IIf(IsNull([q1]!
[Reschdate]),IIf(IsNull([q2]![Reschdate]),IIf(IsNull([q3]!
[Reschdate]),Null,[q3]![Reschdate]),[q2]![Reschdate]),[q1]!
[Reschdate]) AS [Resch Date], q2.[AB Date], q3.[CH Date], q1.[Net
Order Value], (([q1]![Net Order Value]/[q1]![PO Quantity])*[Open Qty])
AS [Open Value], Date() AS [Extraction Date]
FROM (q1 LEFT JOIN q2 ON (q1.Item=q2.Item) AND
(q1.PurchDoc=q2.PurchDoc)) LEFT JOIN q3 ON (q2.Item=q3.Item) AND
(q2.PurchDoc=q3.PurchDoc)
GROUP BY q1.PurchDoc, q1.Item, q1.Rel, q1.Vendor, q1.[Vendor name], q1.
[PO Date], q1.Material, q1.[Short Text], q1.MRPCn, q1.[Del Date], q1.
[PO Quantity], q1.[GR Quantity], q1.[PD release], q2.[AB Date], q3.[CH
Date], q1.[Net Order Value], q1.Exc, q2.Exc, q3.Exc, q1.Reschdate,
q2.Reschdate, q3.Reschdate
ORDER BY q1.Vendor;


I am getting this duplicate value

 

[pat67]

I would suspect that some of the fields you are grouping by and not showing
have a different value for the rows returned.  Try displaying those fields.
You can determine if you need them in the query or not.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Hi, I am running a query based on 3 queries

then i run this query

SELECT q1.PurchDoc, q1.Item, q1.Rel, q1.Vendor, q1.[Vendor name], q1.
[PO Date], q1.Material, q1.[Short Text], q1.MRPCn, q1.[Del Date], q1.
[PO Quantity], q1.[GR Quantity], ([q1]![PO Quantity]-[q1]![GR
Quantity]) AS [Open Qty], q1.[PD release], IIf(IsNull([q1]!
[Exc]),IIf(IsNull([q2]![Exc]),IIf(IsNull([q3]![Exc]),Null,[q3]![Exc]),
[q2]![Exc]),[q1]![Exc]) AS [Exc Code], IIf(IsNull([q1]!
[Reschdate]),IIf(IsNull([q2]![Reschdate]),IIf(IsNull([q3]!
[Reschdate]),Null,[q3]![Reschdate]),[q2]![Reschdate]),[q1]!
[Reschdate]) AS [Resch Date], q2.[AB Date], q3.[CH Date], q1.[Net
Order Value], (([q1]![Net Order Value]/[q1]![PO Quantity])*[Open Qty])
AS [Open Value], Date() AS [Extraction Date]
FROM (q1 LEFT JOIN q2 ON (q1.Item=q2.Item) AND
(q1.PurchDoc=q2.PurchDoc)) LEFT JOIN q3 ON (q2.Item=q3.Item) AND
(q2.PurchDoc=q3.PurchDoc)
GROUP BY q1.PurchDoc, q1.Item, q1.Rel, q1.Vendor, q1.[Vendor name], q1.
[PO Date], q1.Material, q1.[Short Text], q1.MRPCn, q1.[Del Date], q1.
[PO Quantity], q1.[GR Quantity], q1.[PD release], q2.[AB Date], q3.[CH
Date], q1.[Net Order Value], q1.Exc, q2.Exc, q3.Exc, q1.Reschdate,
q2.Reschdate, q3.Reschdate
ORDER BY q1.Vendor;

I am getting this duplicate value

Any ideas?- Hide quoted text -

- Show quoted text -

the problem is that the fields i am grouping and not showing are only
there because when i did not include them, the query errored. because
of the 2 iif statements

 

[pat67]

I would suspect that some of the fields you are grouping by and not showing
have a different value for the rows returned.  Try displaying those fields.
You can determine if you need them in the query or not.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Hi, I am running a query based on 3 queries
then i run this query
SELECT q1.PurchDoc, q1.Item, q1.Rel, q1.Vendor, q1.[Vendor name], q1.
[PO Date], q1.Material, q1.[Short Text], q1.MRPCn, q1.[Del Date], q1.
[PO Quantity], q1.[GR Quantity], ([q1]![PO Quantity]-[q1]![GR
Quantity]) AS [Open Qty], q1.[PD release], IIf(IsNull([q1]!
[Exc]),IIf(IsNull([q2]![Exc]),IIf(IsNull([q3]![Exc]),Null,[q3]![Exc]),
[q2]![Exc]),[q1]![Exc]) AS [Exc Code], IIf(IsNull([q1]!
[Reschdate]),IIf(IsNull([q2]![Reschdate]),IIf(IsNull([q3]!
[Reschdate]),Null,[q3]![Reschdate]),[q2]![Reschdate]),[q1]!
[Reschdate]) AS [Resch Date], q2.[AB Date], q3.[CH Date], q1.[Net
Order Value], (([q1]![Net Order Value]/[q1]![PO Quantity])*[Open Qty])
AS [Open Value], Date() AS [Extraction Date]
FROM (q1 LEFT JOIN q2 ON (q1.Item=q2.Item) AND
(q1.PurchDoc=q2.PurchDoc)) LEFT JOIN q3 ON (q2.Item=q3.Item) AND
(q2.PurchDoc=q3.PurchDoc)
GROUP BY q1.PurchDoc, q1.Item, q1.Rel, q1.Vendor, q1.[Vendor name], q1.
[PO Date], q1.Material, q1.[Short Text], q1.MRPCn, q1.[Del Date], q1.
[PO Quantity], q1.[GR Quantity], q1.[PD release], q2.[AB Date], q3.[CH
Date], q1.[Net Order Value], q1.Exc, q2.Exc, q3.Exc, q1.Reschdate,
q2.Reschdate, q3.Reschdate
ORDER BY q1.Vendor;
I am getting this duplicate value
Any ideas?- Hide quoted text -

- Show quoted text -

the problem is that the fields i am grouping and not showing are only
there because when i did not include them, the query errored. because
of the 2 iif statements- Hide quoted text -

- Show quoted text -

i should tell you that when i take those iif statements out, the data
is fine. the problem is that i need to see a resch. date and an exc
code.

 

[John Spencer]

So, group by the expression you are using instead of by the fields.

SELECT q1.PurchDoc, q1.Item, q1.Rel, q1.Vendor, q1.[Vendor name]
, q1.[PO Date], q1.Material, q1.[Short Text], q1.MRPCn, q1.[Del Date]
, q1.[PO Quantity], q1.[GR Quantity]
, ([q1]![PO Quantity]-[q1]![GR Quantity]) AS [Open Qty], q1.[PD release],

IIf(IsNull([q1]![Exc]),IIf(IsNull([q2]![Exc]),IIf(IsNull([q3]![Exc]),Null,[q3]![Exc]),
[q2]![Exc]),[q1]![Exc]) AS [Exc Code]

, IIf(IsNull([q1]![Reschdate]),IIf(IsNull([q2]![Reschdate]),IIf(IsNull([q3]!
[Reschdate]),Null,[q3]![Reschdate]),[q2]![Reschdate]),[q1]!
[Reschdate]) AS [Resch Date]

, q2.[AB Date], q3.[CH Date], q1.[NetOrder Value]
, [q1]![Net Order Value]/[q1]![PO Quantity]*[Open Qty] AS [Open Value]
, Date() AS [Extraction Date]
FROM (q1 LEFT JOIN q2 ON (q1.Item=q2.Item) AND
(q1.PurchDoc=q2.PurchDoc)) LEFT JOIN q3 ON (q2.Item=q3.Item) AND
(q2.PurchDoc=q3.PurchDoc)

GROUP BY q1.PurchDoc, q1.Item, q1.Rel, q1.Vendor, q1.[Vendor name], q1.
[PO Date], q1.Material, q1.[Short Text], q1.MRPCn, q1.[Del Date], q1.
[PO Quantity], q1.[GR Quantity], q1.[PD release], q2.[AB Date], q3.[CH
Date], q1.[Net Order Value]
, IIf(IsNull([q1]![Exc]),IIf(IsNull([q2]![Exc]),
IIf(IsNull([q3]![Exc]),Null,[q3]![Exc]), [q2]![Exc]),[q1]![Exc])

, IIf(IsNull([q1]![Reschdate]),IIf(IsNull([q2]![Reschdate]),IIf(IsNull([q3]!
[Reschdate]),Null,[q3]![Reschdate]),[q2]![Reschdate]),[q1]!
[Reschdate])
ORDER BY q1.Vendor;

Also, you could use the NZ function and simplify your expressions significantly.
NZ([q1]![Exc],NZ([q2]![Exc],[q3]![Exc])) as [Exc Code]
NZ([q1]![Reschdate],NZ([q2]![Reschdate],[q3]![Reschdate])) as [Resch Date]

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[pat67]

So, group by the expression you are using instead of by the fields.

SELECT q1.PurchDoc, q1.Item, q1.Rel, q1.Vendor, q1.[Vendor name]
, q1.[PO Date], q1.Material, q1.[Short Text], q1.MRPCn, q1.[Del Date]
, q1.[PO Quantity], q1.[GR Quantity]
, ([q1]![PO Quantity]-[q1]![GR Quantity]) AS [Open Qty], q1.[PD release],

IIf(IsNull([q1]![Exc]),IIf(IsNull([q2]![Exc]),IIf(IsNull([q3]![Exc]),Null,[­q3]![Exc]),
[q2]![Exc]),[q1]![Exc]) AS [Exc Code]

, IIf(IsNull([q1]![Reschdate]),IIf(IsNull([q2]![Reschdate]),IIf(IsNull([q3]!
[Reschdate]),Null,[q3]![Reschdate]),[q2]![Reschdate]),[q1]!
[Reschdate]) AS [Resch Date]

, q2.[AB Date], q3.[CH Date], q1.[NetOrder Value]
, [q1]![Net Order Value]/[q1]![PO Quantity]*[Open Qty] AS [Open Value]
, Date() AS [Extraction Date]
FROM (q1 LEFT JOIN q2 ON (q1.Item=q2.Item) AND
(q1.PurchDoc=q2.PurchDoc)) LEFT JOIN q3 ON (q2.Item=q3.Item) AND
(q2.PurchDoc=q3.PurchDoc)

GROUP BY q1.PurchDoc, q1.Item, q1.Rel, q1.Vendor, q1.[Vendor name], q1.
[PO Date], q1.Material, q1.[Short Text], q1.MRPCn, q1.[Del Date], q1.
[PO Quantity], q1.[GR Quantity], q1.[PD release], q2.[AB Date], q3.[CH
Date], q1.[Net Order Value]
, IIf(IsNull([q1]![Exc]),IIf(IsNull([q2]![Exc]),
IIf(IsNull([q3]![Exc]),Null,[q3]![Exc]), [q2]![Exc]),[q1]![Exc])

, IIf(IsNull([q1]![Reschdate]),IIf(IsNull([q2]![Reschdate]),IIf(IsNull([q3]!
[Reschdate]),Null,[q3]![Reschdate]),[q2]![Reschdate]),[q1]!
[Reschdate])
ORDER BY q1.Vendor;

Also, you could use the NZ function and simplify your expressions significantly.
NZ([q1]![Exc],NZ([q2]![Exc],[q3]![Exc])) as [Exc Code]
NZ([q1]![Reschdate],NZ([q2]![Reschdate],[q3]![Reschdate])) as [Resch Date]

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

i should tell you that when i take those iif statements out, the data
is fine. the problem is that i need to see a resch. date and an exc
code.- Hide quoted text -

- Show quoted text -

ok. let me try that

 

[pat67]

So, group by the expression you are using instead of by the fields.

SELECT q1.PurchDoc, q1.Item, q1.Rel, q1.Vendor, q1.[Vendor name]
, q1.[PO Date], q1.Material, q1.[Short Text], q1.MRPCn, q1.[Del Date]
, q1.[PO Quantity], q1.[GR Quantity]
, ([q1]![PO Quantity]-[q1]![GR Quantity]) AS [Open Qty], q1.[PD release],

IIf(IsNull([q1]![Exc]),IIf(IsNull([q2]![Exc]),IIf(IsNull([q3]![Exc]),Null,[­q3]![Exc]),
[q2]![Exc]),[q1]![Exc]) AS [Exc Code]

, IIf(IsNull([q1]![Reschdate]),IIf(IsNull([q2]![Reschdate]),IIf(IsNull([q3]!
[Reschdate]),Null,[q3]![Reschdate]),[q2]![Reschdate]),[q1]!
[Reschdate]) AS [Resch Date]

, q2.[AB Date], q3.[CH Date], q1.[NetOrder Value]
, [q1]![Net Order Value]/[q1]![PO Quantity]*[Open Qty] AS [Open Value]
, Date() AS [Extraction Date]
FROM (q1 LEFT JOIN q2 ON (q1.Item=q2.Item) AND
(q1.PurchDoc=q2.PurchDoc)) LEFT JOIN q3 ON (q2.Item=q3.Item) AND
(q2.PurchDoc=q3.PurchDoc)

GROUP BY q1.PurchDoc, q1.Item, q1.Rel, q1.Vendor, q1.[Vendor name], q1.
[PO Date], q1.Material, q1.[Short Text], q1.MRPCn, q1.[Del Date], q1.
[PO Quantity], q1.[GR Quantity], q1.[PD release], q2.[AB Date], q3.[CH
Date], q1.[Net Order Value]
, IIf(IsNull([q1]![Exc]),IIf(IsNull([q2]![Exc]),
IIf(IsNull([q3]![Exc]),Null,[q3]![Exc]), [q2]![Exc]),[q1]![Exc])

, IIf(IsNull([q1]![Reschdate]),IIf(IsNull([q2]![Reschdate]),IIf(IsNull([q3]!
[Reschdate]),Null,[q3]![Reschdate]),[q2]![Reschdate]),[q1]!
[Reschdate])
ORDER BY q1.Vendor;

Also, you could use the NZ function and simplify your expressions significantly.
NZ([q1]![Exc],NZ([q2]![Exc],[q3]![Exc])) as [Exc Code]
NZ([q1]![Reschdate],NZ([q2]![Reschdate],[q3]![Reschdate])) as [Resch Date]

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

i should tell you that when i take those iif statements out, the data
is fine. the problem is that i need to see a resch. date and an exc
code.- Hide quoted text -

- Show quoted text -

I am getting and error that says invaling use of a . ! or () in the
IIf(IsNull([q1]![Reschdate]),IIf(IsNull([q2]!
[Reschdate]),IIf(IsNull([q3]!
[Reschdate]),Null,[q3]![Reschdate]),[q2]![Reschdate]),[q1]!
[Reschdate]) AS [Resch Date] statement

i can't seem to find it

 

[pat67]

So, group by the expression you are using instead of by the fields.

SELECT q1.PurchDoc, q1.Item, q1.Rel, q1.Vendor, q1.[Vendor name]
, q1.[PO Date], q1.Material, q1.[Short Text], q1.MRPCn, q1.[Del Date]
, q1.[PO Quantity], q1.[GR Quantity]
, ([q1]![PO Quantity]-[q1]![GR Quantity]) AS [Open Qty], q1.[PD release],

IIf(IsNull([q1]![Exc]),IIf(IsNull([q2]![Exc]),IIf(IsNull([q3]![Exc]),Null,[­­q3]![Exc]),
[q2]![Exc]),[q1]![Exc]) AS [Exc Code]

, IIf(IsNull([q1]![Reschdate]),IIf(IsNull([q2]![Reschdate]),IIf(IsNull([q3]!
[Reschdate]),Null,[q3]![Reschdate]),[q2]![Reschdate]),[q1]!
[Reschdate]) AS [Resch Date]

, q2.[AB Date], q3.[CH Date], q1.[NetOrder Value]
, [q1]![Net Order Value]/[q1]![PO Quantity]*[Open Qty] AS [Open Value]
, Date() AS [Extraction Date]
FROM (q1 LEFT JOIN q2 ON (q1.Item=q2.Item) AND
(q1.PurchDoc=q2.PurchDoc)) LEFT JOIN q3 ON (q2.Item=q3.Item) AND
(q2.PurchDoc=q3.PurchDoc)

GROUP BY q1.PurchDoc, q1.Item, q1.Rel, q1.Vendor, q1.[Vendor name], q1.
[PO Date], q1.Material, q1.[Short Text], q1.MRPCn, q1.[Del Date], q1.
[PO Quantity], q1.[GR Quantity], q1.[PD release], q2.[AB Date], q3.[CH
Date], q1.[Net Order Value]
, IIf(IsNull([q1]![Exc]),IIf(IsNull([q2]![Exc]),
IIf(IsNull([q3]![Exc]),Null,[q3]![Exc]), [q2]![Exc]),[q1]![Exc])

, IIf(IsNull([q1]![Reschdate]),IIf(IsNull([q2]![Reschdate]),IIf(IsNull([q3]!
[Reschdate]),Null,[q3]![Reschdate]),[q2]![Reschdate]),[q1]!
[Reschdate])
ORDER BY q1.Vendor;

Also, you could use the NZ function and simplify your expressions significantly.
NZ([q1]![Exc],NZ([q2]![Exc],[q3]![Exc])) as [Exc Code]
NZ([q1]![Reschdate],NZ([q2]![Reschdate],[q3]![Reschdate])) as [Resch Date]

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

- Show quoted text -

I am getting and error that says invaling use of a . ! or () in the
IIf(IsNull([q1]![Reschdate]),IIf(IsNull([q2]!
[Reschdate]),IIf(IsNull([q3]!
[Reschdate]),Null,[q3]![Reschdate]),[q2]![Reschdate]),[q1]!
[Reschdate]) AS [Resch Date]   statement

i can't seem to find it- Hide quoted text -

- Show quoted text -

Ok. I fixed where there were spaces. now I am getting an error saying
"you tried to execute a query that does not include the expression
'IIf(IsNull([q1]![Exc]),IIf(IsNull([q2]![Exc]),IIf(IsNull([q3]!
[Exc]),Null,[­q3]![Exc]),[q2]![Exc]),[q1]![Exc]) AS [Exc Code] ' as
part of the aggregate function.

any suggestions?

 

[John Spencer]

As a guess you should not have the words "AS [Exc Code]" in the Group By
clause. If something does not work (especially if you have an syntax error)
it helps to post the SQL statement by copying and pasting it.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[pat67]

As a guess you should not have the words "AS [Exc Code]" in the Group By
clause.  If something does not work (especially if you have an syntax error)
it helps to post the SQL statement by copying and pasting it.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County



- Show quoted text -

i used the sql you gave me above.

 

[John Spencer]

If it doesn't work then I am stumped as to the cause of the problem. I don't
understand why you are using a totals query at all since I don't see any
summing, averaging, minimum, or maximum.

Why not use something like the following:

SELECT DISTINCT q1.PurchDoc, q1.Item, q1.Rel, q1.Vendor, q1.[Vendor name]
, q1.[PO Date], q1.Material, q1.[Short Text], q1.MRPCn, q1.[Del Date]
, q1.[PO Quantity], q1.[GR Quantity]
, ([q1]![PO Quantity]-[q1]![GR Quantity]) AS [Open Qty], q1.[PD release],

, NZ([q1]![Exc],NZ([q2]![Exc],[q3]![Exc])) as [Exc Code]
, NZ([q1]![Reschdate],NZ([q2]![Reschdate],[q3]![Reschdate])) as [Resch Date]

, q2.[AB Date], q3.[CH Date], q1.[NetOrder Value]
, [q1]![Net Order Value]/[q1]![PO Quantity]*[Open Qty] AS [Open Value]
, Date() AS [Extraction Date]
FROM (q1 LEFT JOIN q2 ON (q1.Item=q2.Item) AND
(q1.PurchDoc=q2.PurchDoc)) LEFT JOIN q3 ON (q2.Item=q3.Item) AND
(q2.PurchDoc=q3.PurchDoc)

ORDER BY q1.Vendor;

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

pat67 wrote:

 

[pat67]

If it doesn't work then I am stumped as to the cause of the problem.  Idon't
understand why you are using a totals query at all since I don't see any
summing, averaging, minimum, or maximum.

Why not use something like the following:

SELECT DISTINCT q1.PurchDoc, q1.Item, q1.Rel, q1.Vendor, q1.[Vendor name]
, q1.[PO Date], q1.Material, q1.[Short Text], q1.MRPCn, q1.[Del Date]
, q1.[PO Quantity], q1.[GR Quantity]
, ([q1]![PO Quantity]-[q1]![GR Quantity]) AS [Open Qty], q1.[PD release],

, NZ([q1]![Exc],NZ([q2]![Exc],[q3]![Exc])) as [Exc Code]
, NZ([q1]![Reschdate],NZ([q2]![Reschdate],[q3]![Reschdate])) as [Resch Date]

, q2.[AB Date], q3.[CH Date], q1.[NetOrder Value]
, [q1]![Net Order Value]/[q1]![PO Quantity]*[Open Qty] AS [Open Value]
, Date() AS [Extraction Date]
FROM (q1 LEFT JOIN q2 ON (q1.Item=q2.Item) AND
(q1.PurchDoc=q2.PurchDoc)) LEFT JOIN q3 ON (q2.Item=q3.Item) AND
(q2.PurchDoc=q3.PurchDoc)

ORDER BY q1.Vendor;

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County



pat67 wrote:- Hide quoted text -

- Show quoted text -

That last one works, but i still get duplicates. It's hard to explain.
When i extract the data into access, the data is in rows i.e. 3 rows
or more for every Po and line item. what i am trying to do is take the
information specific to each row and put it together with the
information that is the same from each row in order to make one row or
maybe tow if there are two deliveries or so. the issue is when some
data is missing, i get bad info out of access. in essence i am trying
to correct the problems we have in our erp system in access. thanks
for your help. i will continue to try and figure it out.

 

[John Spencer]

If you used SELECT DISTINCT then you should have no duplicate rows. A
duplicate would have every field value equal to the corresponding field.

If one field in a row is slightly different then there is not a duplicate as
far as Distinct is concerned. I hope you can find a solution to your problem.

And Null values can cause problems. You could try to force all nulls to some
specific value using the NZ function.

Perhaps something like the following will help you solve your problem.
NZ(MyTable.MyText,"") as MyText
Nz(MyTable.MyNumber,0) as MyNumber
Nz(MyTable.MyDate,#1/1/1899#) as MyDate

Or specifically -
NZ(NZ([q1]![Exc],NZ([q2]![Exc],[q3]![Exc])),"") as [Exc Code]

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

If it doesn't work then I am stumped as to the cause of the problem. I don't
understand why you are using a totals query at all since I don't see any
summing, averaging, minimum, or maximum.

Why not use something like the following:

SELECT DISTINCT q1.PurchDoc, q1.Item, q1.Rel, q1.Vendor, q1.[Vendor name]
, q1.[PO Date], q1.Material, q1.[Short Text], q1.MRPCn, q1.[Del Date]
, q1.[PO Quantity], q1.[GR Quantity]
, ([q1]![PO Quantity]-[q1]![GR Quantity]) AS [Open Qty], q1.[PD release],

, NZ([q1]![Exc],NZ([q2]![Exc],[q3]![Exc])) as [Exc Code]
, NZ([q1]![Reschdate],NZ([q2]![Reschdate],[q3]![Reschdate])) as [Resch Date]

, q2.[AB Date], q3.[CH Date], q1.[NetOrder Value]
, [q1]![Net Order Value]/[q1]![PO Quantity]*[Open Qty] AS [Open Value]
, Date() AS [Extraction Date]
FROM (q1 LEFT JOIN q2 ON (q1.Item=q2.Item) AND
(q1.PurchDoc=q2.PurchDoc)) LEFT JOIN q3 ON (q2.Item=q3.Item) AND
(q2.PurchDoc=q3.PurchDoc)

ORDER BY q1.Vendor;

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County



pat67 wrote:- Hide quoted text -

- Show quoted text -

That last one works, but i still get duplicates. It's hard to explain.
When i extract the data into access, the data is in rows i.e. 3 rows
or more for every Po and line item. what i am trying to do is take the
information specific to each row and put it together with the
information that is the same from each row in order to make one row or
maybe tow if there are two deliveries or so. the issue is when some
data is missing, i get bad info out of access. in essence i am trying
to correct the problems we have in our erp system in access. thanks
for your help. i will continue to try and figure it out.