expression in query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Description Defects
Bent 130
Split 130
Defective 29
Holes) 11
Pullback 9
Damaged 2
Slug Mark 2
General 1
General 1

I have a table where for various Description types I get sometimes for the
Defects filed the same value. When I get the tie in the Defects field , I
need to show one of the two values less 1.
For instance, I show “130†twice, I need to show 130 for Bent, and for
Split should show 129.5
I‘d like to use a formula in one of my queries to reflect the change.

Can anyone help?

Ra
 
Try these queries ---
Ra_Count ---
SELECT Ra.Defects, Count(Ra.Defects) AS CountOfDefects
FROM Ra
GROUP BY Ra.Defects
HAVING (((Count(Ra.Defects))>1));

Ra_Count_Offset ---
SELECT First(Ra.Description) AS FirstOfDescription, [Ra].[Defects]-0.5 AS
Corrected_Defects
FROM Ra INNER JOIN Ra_Count ON Ra.Defects = Ra_Count.Defects
WHERE (((Ra_Count.CountOfDefects)=2))
GROUP BY [Ra].[Defects]-0.5;

SELECT Ra.Description,
IIf([FirstOfDescription]=[Description],[Corrected_Defects],[Defects]) AS
Adjusted_Defects
FROM Ra LEFT JOIN Ra_Count_Offset ON Ra.Description =
Ra_Count_Offset.FirstOfDescription;

There is a problem when you have matching descriptions - General.
Description Adjusted_Defects
Bent 130
Split 129.5
Defective 29
Holes) 11
Pullback 9
Damaged 2
Slug Mark 1.5
General 0.5
General 0.5
 
Thank you so much. It is working !!!

Any suggestions on matching Descriptions?...

Ra

KARL DEWEY said:
Try these queries ---
Ra_Count ---
SELECT Ra.Defects, Count(Ra.Defects) AS CountOfDefects
FROM Ra
GROUP BY Ra.Defects
HAVING (((Count(Ra.Defects))>1));

Ra_Count_Offset ---
SELECT First(Ra.Description) AS FirstOfDescription, [Ra].[Defects]-0.5 AS
Corrected_Defects
FROM Ra INNER JOIN Ra_Count ON Ra.Defects = Ra_Count.Defects
WHERE (((Ra_Count.CountOfDefects)=2))
GROUP BY [Ra].[Defects]-0.5;

SELECT Ra.Description,
IIf([FirstOfDescription]=[Description],[Corrected_Defects],[Defects]) AS
Adjusted_Defects
FROM Ra LEFT JOIN Ra_Count_Offset ON Ra.Description =
Ra_Count_Offset.FirstOfDescription;

There is a problem when you have matching descriptions - General.
Description Adjusted_Defects
Bent 130
Split 129.5
Defective 29
Holes) 11
Pullback 9
Damaged 2
Slug Mark 1.5
General 0.5
General 0.5
--
KARL DEWEY
Build a little - Test a little


Ra said:
Description Defects
Bent 130
Split 130
Defective 29
Holes) 11
Pullback 9
Damaged 2
Slug Mark 2
General 1
General 1

I have a table where for various Description types I get sometimes for the
Defects filed the same value. When I get the tie in the Defects field , I
need to show one of the two values less 1.
For instance, I show “130†twice, I need to show 130 for Bent, and for
Split should show 129.5
I‘d like to use a formula in one of my queries to reflect the change.

Can anyone help?

Ra
Click to expand...
Click to expand...
 
Any suggestions on matching Descriptions?...
I do not understand the question.
--
KARL DEWEY
Build a little - Test a little


Ra said:
Thank you so much. It is working !!!

Any suggestions on matching Descriptions?...

Ra

KARL DEWEY said:
Try these queries ---
Ra_Count ---
SELECT Ra.Defects, Count(Ra.Defects) AS CountOfDefects
FROM Ra
GROUP BY Ra.Defects
HAVING (((Count(Ra.Defects))>1));

Ra_Count_Offset ---
SELECT First(Ra.Description) AS FirstOfDescription, [Ra].[Defects]-0.5 AS
Corrected_Defects
FROM Ra INNER JOIN Ra_Count ON Ra.Defects = Ra_Count.Defects
WHERE (((Ra_Count.CountOfDefects)=2))
GROUP BY [Ra].[Defects]-0.5;

SELECT Ra.Description,
IIf([FirstOfDescription]=[Description],[Corrected_Defects],[Defects]) AS
Adjusted_Defects
FROM Ra LEFT JOIN Ra_Count_Offset ON Ra.Description =
Ra_Count_Offset.FirstOfDescription;

There is a problem when you have matching descriptions - General.
Description Adjusted_Defects
Bent 130
Split 129.5
Defective 29
Holes) 11
Pullback 9
Damaged 2
Slug Mark 1.5
General 0.5
General 0.5
--
KARL DEWEY
Build a little - Test a little


Ra said:
Description Defects
Bent 130
Split 130
Defective 29
Holes) 11
Pullback 9
Damaged 2
Slug Mark 2
General 1
General 1

I have a table where for various Description types I get sometimes for the
Defects filed the same value. When I get the tie in the Defects field , I
need to show one of the two values less 1.
For instance, I show “130†twice, I need to show 130 for Bent, and for
Split should show 129.5
I‘d like to use a formula in one of my queries to reflect the change.

Can anyone help?

Ra
Click to expand...
Click to expand...
Click to expand...
 
Back
Top