Joining two queries into one.

G

Guest

I have tried to make one query out of two but the final total somehow comes
up more than the actual figures.
First query:
SELECT Test.Model, Test.Plant, Sum(Test.[Total Produced]) AS [SumOfTotal
Produced]
FROM Test
GROUP BY Test.Model, Test.Plant, Test.[Void Entry]
HAVING (((Test.Plant)=41) AND ((Test.[Void Entry]) Not Like Yes));

Second Query (based off of the first):
SELECT [Total Produced 041].Model, Test.Plant, [Total Produced
041].[SumOfTotal Produced], Sum([Test Details].Rejects) AS SumOfRejects,
Sum([Rejects]/[SumOfTotal Produced]) AS [Proportion %]
FROM (Test INNER JOIN [Total Produced 041] ON Test.Model = [Total Produced
041].Model) INNER JOIN [Test Details] ON Test.Id = [Test Details].Id
GROUP BY [Total Produced 041].Model, Test.Plant, [Total Produced
041].[SumOfTotal Produced], Test.[Void Entry]
HAVING (((Test.Plant)=41) AND ((Test.[Void Entry]) Not Like Yes));

I have tried the following and somehow end up with a higher number in the
SumofTotal Produced than it is supposed to be. This happens intermittenly on
several records (total of 8 of 23 records).

SELECT Test.Model, Test.Plant, Sum(Test.[Total Produced]) AS [SumOfTotal
Produced], Sum([Test Details].Rejects) AS SumOfRejects
FROM Test LEFT JOIN [Test Details] ON Test.Id = [Test Details].Id
GROUP BY Test.Model, Test.Plant, Test.[Void Entry]
HAVING (((Test.Plant)=41) AND ((Sum([Test Details].Rejects))>0) AND
((Test.[Void Entry]) Not Like Yes));

Please help!
 
M

Michel Walsh

Hi,

One (or many) of the tables (other than Test) has duplicated values under
their field implied in the ON clause.

A possible solution is to be sure these duplicated values are intended, and
if so, which record (among the duplicated ones) has to be used.

Hoping it may help,
Vanderghast, Access MVP
 
G

Guest

I am new at the SQL features and outer joins so these questions could sound
stupid for the experienced user.

If they are duplicated (reject numbers) they should not be duplicating the
total produced number.

So if the linked table has two entries it could be adding the first tables
total twice? If this is so, can I put right join or how do I stop it from
re-recording the initial data from the first table?

If I understand it correctly there will be duplicate ID numbers for each
Test.ID because multiple numbers can be given as rejected under one part
number.

Michel Walsh said:
Hi,

One (or many) of the tables (other than Test) has duplicated values under
their field implied in the ON clause.

A possible solution is to be sure these duplicated values are intended, and
if so, which record (among the duplicated ones) has to be used.

Hoping it may help,
Vanderghast, Access MVP

James Kendall said:
I have tried to make one query out of two but the final total somehow comes
up more than the actual figures.
First query:
SELECT Test.Model, Test.Plant, Sum(Test.[Total Produced]) AS [SumOfTotal
Produced]
FROM Test
GROUP BY Test.Model, Test.Plant, Test.[Void Entry]
HAVING (((Test.Plant)=41) AND ((Test.[Void Entry]) Not Like Yes));

Second Query (based off of the first):
SELECT [Total Produced 041].Model, Test.Plant, [Total Produced
041].[SumOfTotal Produced], Sum([Test Details].Rejects) AS SumOfRejects,
Sum([Rejects]/[SumOfTotal Produced]) AS [Proportion %]
FROM (Test INNER JOIN [Total Produced 041] ON Test.Model = [Total Produced
041].Model) INNER JOIN [Test Details] ON Test.Id = [Test Details].Id
GROUP BY [Total Produced 041].Model, Test.Plant, [Total Produced
041].[SumOfTotal Produced], Test.[Void Entry]
HAVING (((Test.Plant)=41) AND ((Test.[Void Entry]) Not Like Yes));

I have tried the following and somehow end up with a higher number in the
SumofTotal Produced than it is supposed to be. This happens intermittenly
on
several records (total of 8 of 23 records).

SELECT Test.Model, Test.Plant, Sum(Test.[Total Produced]) AS [SumOfTotal
Produced], Sum([Test Details].Rejects) AS SumOfRejects
FROM Test LEFT JOIN [Test Details] ON Test.Id = [Test Details].Id
GROUP BY Test.Model, Test.Plant, Test.[Void Entry]
HAVING (((Test.Plant)=41) AND ((Sum([Test Details].Rejects))>0) AND
((Test.[Void Entry]) Not Like Yes));

Please help!
Click to expand...
Click to expand...
 
M

Michel Walsh

Hi,


A cross join mixes together all records of the two implied tables. If tableA
has three records,

a
b
c

and if tableB has four records, with values 1, 2, 3 and 4, the cross join
produces:

a 1
a 2
a 3
a 4
b 1
b 2
b 3
b 4
c 1
c 2
c 3
c 4


Now, if the initial tableA was having a, a and c, rather than a, b and c,
for values, we will still get 12 records, just change, in the previous
result, all occurrences of b with a. Note that (a 1) would be doubled.

So, if we don't want that to occur, we have to

- either be sure there is no duplicated values in the original table (ie,
not two "a" in tableA). For an INNER JOIN, it is enough to have an index not
allowing duplicated values on the fields mentioned in the ON clause,


- either use two queries. one with the join and a DISTINCT, but no
aggregate; then, the second query calling the first one, with GROUP and
aggregate:

SELECT DISTINCT a.f1, a.f2, a.f3, b.g1, b.g2, b.3 FROM a INNER JOIN b
ON ....
saved as q1, then:

SELECT a.f1, SUM(a.f2), SUM(b.f3)
FROM q1
GROUP a.f1



Hoping it may help,
Vanderghast, Access MVP



James Kendall said:
I am new at the SQL features and outer joins so these questions could sound
stupid for the experienced user.

If they are duplicated (reject numbers) they should not be duplicating the
total produced number.

So if the linked table has two entries it could be adding the first tables
total twice? If this is so, can I put right join or how do I stop it from
re-recording the initial data from the first table?

If I understand it correctly there will be duplicate ID numbers for each
Test.ID because multiple numbers can be given as rejected under one part
number.

Michel Walsh said:
Hi,

One (or many) of the tables (other than Test) has duplicated values under
their field implied in the ON clause.

A possible solution is to be sure these duplicated values are intended,
and
if so, which record (among the duplicated ones) has to be used.

Hoping it may help,
Vanderghast, Access MVP

James Kendall said:
I have tried to make one query out of two but the final total somehow
comes
up more than the actual figures.
First query:
SELECT Test.Model, Test.Plant, Sum(Test.[Total Produced]) AS
[SumOfTotal
Produced]
FROM Test
GROUP BY Test.Model, Test.Plant, Test.[Void Entry]
HAVING (((Test.Plant)=41) AND ((Test.[Void Entry]) Not Like Yes));

Second Query (based off of the first):
SELECT [Total Produced 041].Model, Test.Plant, [Total Produced
041].[SumOfTotal Produced], Sum([Test Details].Rejects) AS
SumOfRejects,
Sum([Rejects]/[SumOfTotal Produced]) AS [Proportion %]
FROM (Test INNER JOIN [Total Produced 041] ON Test.Model = [Total
Produced
041].Model) INNER JOIN [Test Details] ON Test.Id = [Test Details].Id
GROUP BY [Total Produced 041].Model, Test.Plant, [Total Produced
041].[SumOfTotal Produced], Test.[Void Entry]
HAVING (((Test.Plant)=41) AND ((Test.[Void Entry]) Not Like Yes));

I have tried the following and somehow end up with a higher number in
the
SumofTotal Produced than it is supposed to be. This happens
intermittenly
on
several records (total of 8 of 23 records).

SELECT Test.Model, Test.Plant, Sum(Test.[Total Produced]) AS
[SumOfTotal
Produced], Sum([Test Details].Rejects) AS SumOfRejects
FROM Test LEFT JOIN [Test Details] ON Test.Id = [Test Details].Id
GROUP BY Test.Model, Test.Plant, Test.[Void Entry]
HAVING (((Test.Plant)=41) AND ((Sum([Test Details].Rejects))>0) AND
((Test.[Void Entry]) Not Like Yes));

Please help!
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

This is the query that is not working.

SELECT DISTINCTROW Test.Model, Test.Plant, Sum(Test.[Total Produced]) AS
[SumOfTotal Produced], Nz(Sum([Test Details].Rejects),0) AS [Sum Of Rejects]
FROM Test LEFT JOIN [Test Details] ON Test.Id = [Test Details].Id
WHERE (((Test.Date) Between [Forms]![F: Date Range]![Start Date] And
[Forms]![F: Date Range]![End Date]))
GROUP BY Test.Model, Test.Plant, Test.[Void Entry]
HAVING (((Test.[Void Entry])<>Yes));

Records are picking up the first entry on as many times as there are second
linked table records when it should be doing it only once. The duplicate
numbers are necessary. So you are saying that I have to split this up
correct? If I understand what you are saying this is needed and to remove
all sum functions into the second query? Another stupid question...What is
an aggregate? Being self-taught does have it disadvantages. :-(

Michel Walsh said:
Hi,


A cross join mixes together all records of the two implied tables. If tableA
has three records,

a
b
c

and if tableB has four records, with values 1, 2, 3 and 4, the cross join
produces:

a 1
a 2
a 3
a 4
b 1
b 2
b 3
b 4
c 1
c 2
c 3
c 4


Now, if the initial tableA was having a, a and c, rather than a, b and c,
for values, we will still get 12 records, just change, in the previous
result, all occurrences of b with a. Note that (a 1) would be doubled.

So, if we don't want that to occur, we have to

- either be sure there is no duplicated values in the original table (ie,
not two "a" in tableA). For an INNER JOIN, it is enough to have an index not
allowing duplicated values on the fields mentioned in the ON clause,


- either use two queries. one with the join and a DISTINCT, but no
aggregate; then, the second query calling the first one, with GROUP and
aggregate:

SELECT DISTINCT a.f1, a.f2, a.f3, b.g1, b.g2, b.3 FROM a INNER JOIN b
ON ....
saved as q1, then:

SELECT a.f1, SUM(a.f2), SUM(b.f3)
FROM q1
GROUP a.f1



Hoping it may help,
Vanderghast, Access MVP



James Kendall said:
I am new at the SQL features and outer joins so these questions could sound
stupid for the experienced user.

If they are duplicated (reject numbers) they should not be duplicating the
total produced number.

So if the linked table has two entries it could be adding the first tables
total twice? If this is so, can I put right join or how do I stop it from
re-recording the initial data from the first table?

If I understand it correctly there will be duplicate ID numbers for each
Test.ID because multiple numbers can be given as rejected under one part
number.

Michel Walsh said:
Hi,

One (or many) of the tables (other than Test) has duplicated values under
their field implied in the ON clause.

A possible solution is to be sure these duplicated values are intended,
and
if so, which record (among the duplicated ones) has to be used.

Hoping it may help,
Vanderghast, Access MVP

I have tried to make one query out of two but the final total somehow
comes
up more than the actual figures.
First query:
SELECT Test.Model, Test.Plant, Sum(Test.[Total Produced]) AS
[SumOfTotal
Produced]
FROM Test
GROUP BY Test.Model, Test.Plant, Test.[Void Entry]
HAVING (((Test.Plant)=41) AND ((Test.[Void Entry]) Not Like Yes));

Second Query (based off of the first):
SELECT [Total Produced 041].Model, Test.Plant, [Total Produced
041].[SumOfTotal Produced], Sum([Test Details].Rejects) AS
SumOfRejects,
Sum([Rejects]/[SumOfTotal Produced]) AS [Proportion %]
FROM (Test INNER JOIN [Total Produced 041] ON Test.Model = [Total
Produced
041].Model) INNER JOIN [Test Details] ON Test.Id = [Test Details].Id
GROUP BY [Total Produced 041].Model, Test.Plant, [Total Produced
041].[SumOfTotal Produced], Test.[Void Entry]
HAVING (((Test.Plant)=41) AND ((Test.[Void Entry]) Not Like Yes));

I have tried the following and somehow end up with a higher number in
the
SumofTotal Produced than it is supposed to be. This happens
intermittenly
on
several records (total of 8 of 23 records).

SELECT Test.Model, Test.Plant, Sum(Test.[Total Produced]) AS
[SumOfTotal
Produced], Sum([Test Details].Rejects) AS SumOfRejects
FROM Test LEFT JOIN [Test Details] ON Test.Id = [Test Details].Id
GROUP BY Test.Model, Test.Plant, Test.[Void Entry]
HAVING (((Test.Plant)=41) AND ((Sum([Test Details].Rejects))>0) AND
((Test.[Void Entry]) Not Like Yes));

Please help!
Click to expand...
Click to expand...
Click to expand...
 
M

Michel Walsh

Hi,


An aggregate is an evaluation applied on a set, like SUM, MIN, MAX, AVG,
.....



I would try:

SELECT DISTINCT Test.Model, Test.Plant,
Test.[Void Entry], Test.[Total Produced], Test Details].Rejects
FROM Test LEFT JOIN [Test Details] ON Test.Id = [Test Details].Id
WHERE (((Test.Date) Between [Forms]![F: Date Range]![Start Date] And
[Forms]![F: Date Range]![End Date]))


and save the query. The DISTINCT would remove duplicated records. Then, I
would use the just saved query:


SELECT Model, Plant,
Sum([Total Produced]) AS [SumOfTotal Produced],
Nz(Sum(Rejects),0) AS [Sum Of Rejects]
FROM savedQuery
GROUP BY Model, Plant, [Void Entry]
HAVING (((Test.[Void Entry])<>Yes));




Your statement was removing duplicated rows ONCE the groups and aggregation
(SUM) had been made. Here, we did it, but before grouping and summing.



Hoping it may help,
Vanderghast, Access MVP




James Kendall said:
This is the query that is not working.

SELECT DISTINCTROW Test.Model, Test.Plant, Sum(Test.[Total Produced]) AS
[SumOfTotal Produced], Nz(Sum([Test Details].Rejects),0) AS [Sum Of
Rejects]
FROM Test LEFT JOIN [Test Details] ON Test.Id = [Test Details].Id
WHERE (((Test.Date) Between [Forms]![F: Date Range]![Start Date] And
[Forms]![F: Date Range]![End Date]))
GROUP BY Test.Model, Test.Plant, Test.[Void Entry]
HAVING (((Test.[Void Entry])<>Yes));

Records are picking up the first entry on as many times as there are
second
linked table records when it should be doing it only once. The duplicate
numbers are necessary. So you are saying that I have to split this up
correct? If I understand what you are saying this is needed and to remove
all sum functions into the second query? Another stupid question...What
is
an aggregate? Being self-taught does have it disadvantages. :-(

Michel Walsh said:
Hi,


A cross join mixes together all records of the two implied tables. If
tableA
has three records,

a
b
c

and if tableB has four records, with values 1, 2, 3 and 4, the cross join
produces:

a 1
a 2
a 3
a 4
b 1
b 2
b 3
b 4
c 1
c 2
c 3
c 4


Now, if the initial tableA was having a, a and c, rather than a, b and
c,
for values, we will still get 12 records, just change, in the previous
result, all occurrences of b with a. Note that (a 1) would be doubled.

So, if we don't want that to occur, we have to

- either be sure there is no duplicated values in the original table (ie,
not two "a" in tableA). For an INNER JOIN, it is enough to have an index
not
allowing duplicated values on the fields mentioned in the ON clause,


- either use two queries. one with the join and a DISTINCT, but no
aggregate; then, the second query calling the first one, with GROUP and
aggregate:

SELECT DISTINCT a.f1, a.f2, a.f3, b.g1, b.g2, b.3 FROM a INNER JOIN
b
ON ....
saved as q1, then:

SELECT a.f1, SUM(a.f2), SUM(b.f3)
FROM q1
GROUP a.f1



Hoping it may help,
Vanderghast, Access MVP



James Kendall said:
I am new at the SQL features and outer joins so these questions could
sound
stupid for the experienced user.

If they are duplicated (reject numbers) they should not be duplicating
the
total produced number.

So if the linked table has two entries it could be adding the first
tables
total twice? If this is so, can I put right join or how do I stop it
from
re-recording the initial data from the first table?

If I understand it correctly there will be duplicate ID numbers for
each
Test.ID because multiple numbers can be given as rejected under one
part
number.

:

Hi,

One (or many) of the tables (other than Test) has duplicated values
under
their field implied in the ON clause.

A possible solution is to be sure these duplicated values are
intended,
and
if so, which record (among the duplicated ones) has to be used.

Hoping it may help,
Vanderghast, Access MVP

message
I have tried to make one query out of two but the final total somehow
comes
up more than the actual figures.
First query:
SELECT Test.Model, Test.Plant, Sum(Test.[Total Produced]) AS
[SumOfTotal
Produced]
FROM Test
GROUP BY Test.Model, Test.Plant, Test.[Void Entry]
HAVING (((Test.Plant)=41) AND ((Test.[Void Entry]) Not Like Yes));

Second Query (based off of the first):
SELECT [Total Produced 041].Model, Test.Plant, [Total Produced
041].[SumOfTotal Produced], Sum([Test Details].Rejects) AS
SumOfRejects,
Sum([Rejects]/[SumOfTotal Produced]) AS [Proportion %]
FROM (Test INNER JOIN [Total Produced 041] ON Test.Model = [Total
Produced
041].Model) INNER JOIN [Test Details] ON Test.Id = [Test Details].Id
GROUP BY [Total Produced 041].Model, Test.Plant, [Total Produced
041].[SumOfTotal Produced], Test.[Void Entry]
HAVING (((Test.Plant)=41) AND ((Test.[Void Entry]) Not Like Yes));

I have tried the following and somehow end up with a higher number
in
the
SumofTotal Produced than it is supposed to be. This happens
intermittenly
on
several records (total of 8 of 23 records).

SELECT Test.Model, Test.Plant, Sum(Test.[Total Produced]) AS
[SumOfTotal
Produced], Sum([Test Details].Rejects) AS SumOfRejects
FROM Test LEFT JOIN [Test Details] ON Test.Id = [Test Details].Id
GROUP BY Test.Model, Test.Plant, Test.[Void Entry]
HAVING (((Test.Plant)=41) AND ((Sum([Test Details].Rejects))>0) AND
((Test.[Void Entry]) Not Like Yes));

Please help!
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks, you helped set me in the right direction. After a few test runs and
alot of failures and adjustments this is what came out to work.

First Query:

SELECT Test.Model, Test.Plant, Sum(Test.[Total Produced]) AS [SumOfTotal
Produced]
FROM Test
WHERE (((Test.Date) Between [Forms]![F: Plant Code]![Beg Date] And
[Forms]![F: Plant Code]![End Date]))
GROUP BY Test.Model, Test.Plant, Test.[Void Entry]
HAVING (((Test.Plant)=[Forms]![F: Plant Code]![Plant Code]) AND ((Test.[Void
Entry]) Not Like Yes));

Followed by this query:

SELECT [Total Produced by Plant].Model, Test.Plant, [Total Produced by
Plant].[SumOfTotal Produced], Nz(Sum([Test Details].[Rejects]),0) AS
SumOfRejects
FROM (Test INNER JOIN [Total Produced by Plant] ON Test.Model = [Total
Produced by Plant].Model) LEFT JOIN [Test Details] ON Test.Id = [Test
Details].Id
WHERE (((Test.Date) Between [Forms]![F: Plant Code]![Beg Date] And
[Forms]![F: Plant Code]![End Date]))
GROUP BY [Total Produced by Plant].Model, Test.Plant, [Total Produced by
Plant].[SumOfTotal Produced], Test.[Void Entry]
HAVING (((Test.Plant)=[Forms]![F: Plant Code]![Plant Code]) AND ((Test.[Void
Entry]) Not Like Yes));

Thanks again. I appreciate it.
James Kendall

Michel Walsh said:
Hi,


An aggregate is an evaluation applied on a set, like SUM, MIN, MAX, AVG,
.....



I would try:

SELECT DISTINCT Test.Model, Test.Plant,
Test.[Void Entry], Test.[Total Produced], Test Details].Rejects
FROM Test LEFT JOIN [Test Details] ON Test.Id = [Test Details].Id
WHERE (((Test.Date) Between [Forms]![F: Date Range]![Start Date] And
[Forms]![F: Date Range]![End Date]))


and save the query. The DISTINCT would remove duplicated records. Then, I
would use the just saved query:


SELECT Model, Plant,
Sum([Total Produced]) AS [SumOfTotal Produced],
Nz(Sum(Rejects),0) AS [Sum Of Rejects]
FROM savedQuery
GROUP BY Model, Plant, [Void Entry]
HAVING (((Test.[Void Entry])<>Yes));




Your statement was removing duplicated rows ONCE the groups and aggregation
(SUM) had been made. Here, we did it, but before grouping and summing.



Hoping it may help,
Vanderghast, Access MVP




James Kendall said:
This is the query that is not working.

SELECT DISTINCTROW Test.Model, Test.Plant, Sum(Test.[Total Produced]) AS
[SumOfTotal Produced], Nz(Sum([Test Details].Rejects),0) AS [Sum Of
Rejects]
FROM Test LEFT JOIN [Test Details] ON Test.Id = [Test Details].Id
WHERE (((Test.Date) Between [Forms]![F: Date Range]![Start Date] And
[Forms]![F: Date Range]![End Date]))
GROUP BY Test.Model, Test.Plant, Test.[Void Entry]
HAVING (((Test.[Void Entry])<>Yes));

Records are picking up the first entry on as many times as there are
second
linked table records when it should be doing it only once. The duplicate
numbers are necessary. So you are saying that I have to split this up
correct? If I understand what you are saying this is needed and to remove
all sum functions into the second query? Another stupid question...What
is
an aggregate? Being self-taught does have it disadvantages. :-(

Michel Walsh said:
Hi,


A cross join mixes together all records of the two implied tables. If
tableA
has three records,

a
b
c

and if tableB has four records, with values 1, 2, 3 and 4, the cross join
produces:

a 1
a 2
a 3
a 4
b 1
b 2
b 3
b 4
c 1
c 2
c 3
c 4


Now, if the initial tableA was having a, a and c, rather than a, b and
c,
for values, we will still get 12 records, just change, in the previous
result, all occurrences of b with a. Note that (a 1) would be doubled.

So, if we don't want that to occur, we have to

- either be sure there is no duplicated values in the original table (ie,
not two "a" in tableA). For an INNER JOIN, it is enough to have an index
not
allowing duplicated values on the fields mentioned in the ON clause,


- either use two queries. one with the join and a DISTINCT, but no
aggregate; then, the second query calling the first one, with GROUP and
aggregate:

SELECT DISTINCT a.f1, a.f2, a.f3, b.g1, b.g2, b.3 FROM a INNER JOIN
b
ON ....
saved as q1, then:

SELECT a.f1, SUM(a.f2), SUM(b.f3)
FROM q1
GROUP a.f1



Hoping it may help,
Vanderghast, Access MVP



I am new at the SQL features and outer joins so these questions could
sound
stupid for the experienced user.

If they are duplicated (reject numbers) they should not be duplicating
the
total produced number.

So if the linked table has two entries it could be adding the first
tables
total twice? If this is so, can I put right join or how do I stop it
from
re-recording the initial data from the first table?

If I understand it correctly there will be duplicate ID numbers for
each
Test.ID because multiple numbers can be given as rejected under one
part
number.

:

Hi,

One (or many) of the tables (other than Test) has duplicated values
under
their field implied in the ON clause.

A possible solution is to be sure these duplicated values are
intended,
and
if so, which record (among the duplicated ones) has to be used.

Hoping it may help,
Vanderghast, Access MVP

message
I have tried to make one query out of two but the final total somehow
comes
up more than the actual figures.
First query:
SELECT Test.Model, Test.Plant, Sum(Test.[Total Produced]) AS
[SumOfTotal
Produced]
FROM Test
GROUP BY Test.Model, Test.Plant, Test.[Void Entry]
HAVING (((Test.Plant)=41) AND ((Test.[Void Entry]) Not Like Yes));

Second Query (based off of the first):
SELECT [Total Produced 041].Model, Test.Plant, [Total Produced
041].[SumOfTotal Produced], Sum([Test Details].Rejects) AS
SumOfRejects,
Sum([Rejects]/[SumOfTotal Produced]) AS [Proportion %]
FROM (Test INNER JOIN [Total Produced 041] ON Test.Model = [Total
Produced
041].Model) INNER JOIN [Test Details] ON Test.Id = [Test Details].Id
GROUP BY [Total Produced 041].Model, Test.Plant, [Total Produced
041].[SumOfTotal Produced], Test.[Void Entry]
HAVING (((Test.Plant)=41) AND ((Test.[Void Entry]) Not Like Yes));

I have tried the following and somehow end up with a higher number
in
the
SumofTotal Produced than it is supposed to be. This happens
intermittenly
on
several records (total of 8 of 23 records).

SELECT Test.Model, Test.Plant, Sum(Test.[Total Produced]) AS
[SumOfTotal
Produced], Sum([Test Details].Rejects) AS SumOfRejects
FROM Test LEFT JOIN [Test Details] ON Test.Id = [Test Details].Id
GROUP BY Test.Model, Test.Plant, Test.[Void Entry]
HAVING (((Test.Plant)=41) AND ((Sum([Test Details].Rejects))>0) AND
((Test.[Void Entry]) Not Like Yes));

Please help!
Click to expand...
Click to expand...
Click to expand...
 

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

Query not showing desired results 4
Enter Parameter Value Message Pops Up 4
Combining 2 queries..I am loosing my mind... 3
Difficult Query; Trying to Show only One Record for only Two Field 1
Sums Way to big 7
Incorrect sum in MS Access query 1
Execute multiple crosstab queries with one parameter 2
Crosstab query help please 1

Top