Summation query

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

Guest

I'm trying to fill a combo box list with a query in its Row Source. The
query is looking at some fields in a table but I'm mainly interested in Cycle
& Status. The status can be -1, 0, or 1. I want to fill the list with
cycles whose status is not -1, and I do not want duplicate cycles listed. I
put the following in the row source but it gives me duplicate cycles.

Me.cboCyclePM.RowSource = _
"SELECT tblPMHistory.Cycle, tblPMHistory.Dept,
tblPMHistory.CycleStatus " & _
"FROM tblPMHistory " & _
"GROUP BY tblPMHistory.Cycle, tblPMHistory.Dept,
tblPMHistory.CycleStatus " & _
"HAVING (((tblPMHistory.Dept) = [Forms]![frmEquipE]![cboDeptPM]) And
((tblPMHistory.CycleStatus) <> -1)) " & _
"ORDER BY tblPMHistory.Cycle;"
 
Del said:
I'm trying to fill a combo box list with a query in its Row Source. The
query is looking at some fields in a table but I'm mainly interested in Cycle
& Status. The status can be -1, 0, or 1. I want to fill the list with
cycles whose status is not -1, and I do not want duplicate cycles listed. I
put the following in the row source but it gives me duplicate cycles.

Me.cboCyclePM.RowSource = _
"SELECT tblPMHistory.Cycle, tblPMHistory.Dept,
tblPMHistory.CycleStatus " & _
"FROM tblPMHistory " & _
"GROUP BY tblPMHistory.Cycle, tblPMHistory.Dept,
tblPMHistory.CycleStatus " & _
"HAVING (((tblPMHistory.Dept) = [Forms]![frmEquipE]![cboDeptPM]) And
((tblPMHistory.CycleStatus) <> -1)) " & _
"ORDER BY tblPMHistory.Cycle;"
Click to expand...


I'm not sure that close or not, but yhe HAVING clause is
inappropriate. I suggest that you try using:

Me.cboCyclePM.RowSource = _
"SELECT DISTINCT Cycle, Dept, CycleStatus " & _
"FROM tblPMHistory " & _
"WHERE Dept = " & Me!cboDeptPM & _
" And CycleStatus) <> -1 " & _
"ORDER BY Cycle;"

You may get the same cycle in different rows, but they
should be for different status values.

Note that the above syntax assumes that the Dept field is a
number type.
 
Back
Top