Trying to write a query that finds unique records

[Dustin Ventin]

I am trying to write a query that returns a fairly simple set of information.
I have linked two tables together, I want to show data from both. I also
include a couple fields which will always be the same. Unfortunitely,
because (I guess) my join is not specific enough, I wind up with seven copies
of two different records, resulting the 14 records when I want 2.

I can "Group By", and that returns 2 recrods. However, then I want too sum
the values in two of the fields. If I select "Sum", the query returns the
sum of all 14 records, which obviously is a completely incorrect calculation.

I know I'm doing something wrong. I tried DISTINCTROW to select unique rows
only, but that doesn't change anything in the slightest. The code for the
SQL query is as follows:

SELECT DISTINCTROW tblPrimaveraDetail.PD_ActivityID AS ACT,
tblPrimaveraDetail.PD_ActivityDescription AS TITLE,
tblEstimateDetail.ED_OHandPTotal AS BC, '1' AS BQ, "OVERHEAD & PROFIT" AS RES
FROM tblEstimateDetail INNER JOIN tblPrimaveraDetail
ON tblEstimateDetail.ED_Primavera_ID = tblPrimaveraDetail.PD_ActivityID
WHERE
(((tblPrimaveraDetail.PD_UProj_ID)=[forms]![frmProjectMain]![txtUProj_ID]));

Ideas? Thanks!

Dustin

 

[Jeff L]

Try just DISTINCT not DISTINCTROW.

 

[Dustin Ventin]

This returns the appropriate rows, thanks!

However, sometimes the rows will differ only by the field I want to sum. In
these cases, I want to... well... sum the fields together. However, when I
tell the query to group and sum on ED_OHandPTotal (for example), it adds
together all 14 rows of erronious data.

Any ideas?

Thanks!

Dustin

Try just DISTINCT not DISTINCTROW.

I am trying to write a query that returns a fairly simple set of information.
I have linked two tables together, I want to show data from both. I also
include a couple fields which will always be the same. Unfortunitely,
because (I guess) my join is not specific enough, I wind up with seven copies
of two different records, resulting the 14 records when I want 2.

I can "Group By", and that returns 2 recrods. However, then I want too sum
the values in two of the fields. If I select "Sum", the query returns the
sum of all 14 records, which obviously is a completely incorrect calculation.

I know I'm doing something wrong. I tried DISTINCTROW to select unique rows
only, but that doesn't change anything in the slightest. The code for the
SQL query is as follows:

SELECT DISTINCTROW tblPrimaveraDetail.PD_ActivityID AS ACT,
tblPrimaveraDetail.PD_ActivityDescription AS TITLE,
tblEstimateDetail.ED_OHandPTotal AS BC, '1' AS BQ, "OVERHEAD & PROFIT" AS RES
FROM tblEstimateDetail INNER JOIN tblPrimaveraDetail
ON tblEstimateDetail.ED_Primavera_ID = tblPrimaveraDetail.PD_ActivityID
WHERE
(((tblPrimaveraDetail.PD_UProj_ID)=[forms]![frmProjectMain]![txtUProj_ID]));

Ideas? Thanks!

Dustin

 

[Jeff L]

The concept should be the same
Select Distinct Field1, Field2, Field3, Sum(ED_OHandPTotal )

From Table1

Where Whatever
Group by Field1, Field2, Field3

If you try to output ED_OHandPTotal and Sum(ED_OHandPTotal ), it will
not work because, as you said, the field you want to sum
(ED_OHandPTotal ) is different on all your rows.

Hope that helps!

This returns the appropriate rows, thanks!

However, sometimes the rows will differ only by the field I want to sum. In
these cases, I want to... well... sum the fields together. However, when I
tell the query to group and sum on ED_OHandPTotal (for example), it adds
together all 14 rows of erronious data.

Any ideas?

Thanks!

Dustin

Try just DISTINCT not DISTINCTROW.

I am trying to write a query that returns a fairly simple set of information.
I have linked two tables together, I want to show data from both. I also
include a couple fields which will always be the same. Unfortunitely,
because (I guess) my join is not specific enough, I wind up with seven copies
of two different records, resulting the 14 records when I want 2.

I can "Group By", and that returns 2 recrods. However, then I want too sum
the values in two of the fields. If I select "Sum", the query returns the
sum of all 14 records, which obviously is a completely incorrect calculation.

I know I'm doing something wrong. I tried DISTINCTROW to select unique rows
only, but that doesn't change anything in the slightest. The code for the
SQL query is as follows:

SELECT DISTINCTROW tblPrimaveraDetail.PD_ActivityID AS ACT,
tblPrimaveraDetail.PD_ActivityDescription AS TITLE,
tblEstimateDetail.ED_OHandPTotal AS BC, '1' AS BQ, "OVERHEAD & PROFIT" AS RES
FROM tblEstimateDetail INNER JOIN tblPrimaveraDetail
ON tblEstimateDetail.ED_Primavera_ID = tblPrimaveraDetail.PD_ActivityID
WHERE
(((tblPrimaveraDetail.PD_UProj_ID)=[forms]![frmProjectMain]![txtUProj_ID]));

Ideas? Thanks!

Dustin

 

[Dustin Ventin]

The query looks pretty much like that:

SELECT DISTINCT tblPrimaveraDetail.PD_ActivityID AS ACT,
tblPrimaveraDetail.PD_ActivityDescription AS TITLE,
Sum(tblEstimateDetail.ED_MaterialTotal) AS BC,
'1' AS BQ,
"MATERIAL COST" AS RES

FROM tblEstimateDetail INNER JOIN tblPrimaveraDetail ON
tblEstimateDetail.ED_Primavera_ID = tblPrimaveraDetail.PD_ActivityID

GROUP BY tblPrimaveraDetail.PD_ActivityID,
tblPrimaveraDetail.PD_ActivityDescription,
'1',
"MATERIAL COST",
tblPrimaveraDetail.PD_UProj_ID
HAVING
(((tblPrimaveraDetail.PD_UProj_ID)=[forms]![frmProjectMain]![txtUProj_ID]));

(I have added the spaces to increase readablity.)

This adds together all the dulicate records returned because two fields in
one table match seven records in the other. All I want, though, is the
matching fields from the seven-record table to be used to fill in data for
the returned recrods from the two-record table, for a total of two records.
These two records should then be summed if everything but thier currency
value is the same, otherwise they should be left alone.

Thanks for your continued assistence.

Dustin

 

[Dustin Ventin]

Nevermind, I figured it out.

First, create a query that groups all the data I want from the
PrimaveraDetail table. Then, instead of linking my queries directly to
PrimaveraDetail, I link to the pre-grouped query. The sum works just fine
then.

Dustin