Eliminating repetitions in a field

[BobC]

I am trying to modify a query(qryNewReten)whose source is a single table
(TblNewReten). The table contains a numeric field(HA)that correctly
contains repetitious numeric values. I want to use the 'values' from the
HA field to serve as the source to a combo box; but I would rather not
have the repetitions. I assume I should create another field in the
query which would list the non-repetitious values of the HA field? I do
not know how to do that. (I hope I explained this correctly?)

 

[KARL DEWEY]

Use this as the source ---
SELECT [TblNewReten].[HA] FROM [TblNewReten] GROUP BY [TblNewReten].[HA];

 

[John W. Vinson]

I am trying to modify a query(qryNewReten)whose source is a single table
(TblNewReten). The table contains a numeric field(HA)that correctly
contains repetitious numeric values. I want to use the 'values' from the
HA field to serve as the source to a combo box; but I would rather not
have the repetitions. I assume I should create another field in the
query which would list the non-repetitious values of the HA field? I do
not know how to do that. (I hope I explained this correctly?)

If all you want from qryNewReten is the value of HA, you can use the "Unique
Values" property of the query. The SQL would be

SELECT DISTINCT HA FROM tblNewReten ORDER BY HA;

If the query returns other fields, you need to decide what you want to do with
them! If each value of the field HA is in a record with *different* values for
some other field or fields, which record do you want to use?

 

[BobC]

Sorry, but I sometimes use the wrong words! I am not certain that I
understanding what you are asking ... ? so I will try to give more
details as at what I want to do ...

I have a form (frmNewReten) that I am using to locate and update 8 of
the 10 fields in tblNewReten via qryNewReten.

The remaining two numeric (integer) fields (HA and POLYR) are used to
'uniquely' locate a desired record that I want to update. Neither of
these two indexed fields are allowed to be themselves updated.

On frmNewReten, these two fields (HA and POLYR) are currently displayed
using combo boxes. (note: My original question only asked about one of
the fields (HA) in an effort to keep things simple; but, the same issue
of redundancy also applies to field POLYR. I want to emphasize that
given a particular HA and a particular POLYR, they will uniquely define
a single record ... the one to be updated.)

All works fine with frmNewReten, except that in using the combo boxes,
they each display redundant numbers. Therein lies the issue of wanting
not have to scroll past 15 replications of one number to get to the next
choice.

 

[John W. Vinson]

Sorry, but I sometimes use the wrong words! I am not certain that I
understanding what you are asking ... ? so I will try to give more
details as at what I want to do ...

I have a form (frmNewReten) that I am using to locate and update 8 of
the 10 fields in tblNewReten via qryNewReten.

I don't know the recordsource of the form. I don't know the SQL of
qryNewReten. Give me a hand here? What's the Primary Key of the table?

The remaining two numeric (integer) fields (HA and POLYR) are used to
'uniquely' locate a desired record that I want to update. Neither of
these two indexed fields are allowed to be themselves updated.

On frmNewReten, these two fields (HA and POLYR) are currently displayed
using combo boxes. (note: My original question only asked about one of
the fields (HA) in an effort to keep things simple; but, the same issue
of redundancy also applies to field POLYR. I want to emphasize that
given a particular HA and a particular POLYR, they will uniquely define
a single record ... the one to be updated.)

All works fine with frmNewReten, except that in using the combo boxes,
they each display redundant numbers. Therein lies the issue of wanting
not have to scroll past 15 replications of one number to get to the next
choice.

Don't base the combo on tblNewReten.

Instead create a Query, selecting only the one field. Set that query's Unique
Values property to Yes.

The combo will now show only one instance of each value of HA.

It will also be useless for finding a record, since one value of HA
corresponds to multiple records, unless I'm misunderstanding! Would you select
a value of HA in one combo, and a value of POLYR in the other? Will they
jointly uniquely identify a record?

 

[BobC]

qryNewReten is the recordsource of frmNewReten.

Yes ... On frmNewReten, I must select a value of HA in one combo, and a
value of POLYR in the other combo in order to jointly, uniquely identify
the specific record.

Simplified ...
HA contains: 1 1 1 2 2 2 3 3 3 ...
POLYR contains: 2001 2002 2003 2001 2002 2003 2001 2002 2003 ...

Right now, I am using HA as my source for one of the combo boxes; I get
111222333 ...
I really want 1 2 3 ...
Likewise, for the POLYR combo box ... I want 2001 2002 2003 ...

I was thinking that I should be able to add two fields to qryNewReten
that were based off of fields HA and POLYR that would be subsets of HA
and POLYR and which had no repetitions. I would then use these new
fields as my source to the two combo boxes on frmNewReten ?????????

 

[John Spencer]

As John Vinson said earlier you need to base the comboboxes on a distinct
query.

You can build two queries to do that and instead of using the TABLE as the
source for the comboboxes, use the queries.

Query One
-- Select the table with HA and PolYr
-- Add the HA field
-- DOUBLE click in a blank area of the upper section of the design area
-- Set the Unique Values property to yes
-- Save this as qHA

Repeat the above with the PolYr field and save the query as qPolYr

Now open your form in design view and select the HA combobox
In the property sheet, set the RowSource to qHA and the Row Source type to
Table/Query

Repeat for the PolYr combobox.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[BobC]

Thank you!
That part now works.
Unfortunately I may be headed down the wrong path.
HA an POLYR jointly 'technically' define a unique record; however it
does not seem to get me in a position to edit that specific record;
which was original intent for the form and the two comboboxes.
My goal is to create a form in which I can easily edit the table.

 

[John W. Vinson]

Thank you!
That part now works.
Unfortunately I may be headed down the wrong path.
HA an POLYR jointly 'technically' define a unique record; however it
does not seem to get me in a position to edit that specific record;
which was original intent for the form and the two comboboxes.
My goal is to create a form in which I can easily edit the table.

I'd suggest using the two unbound combos on the form (perhaps on the form
header to make it more apparent that these are for navigation not for data
editing. Let's call them cboHA and cboPOLYR.

Put a command button on the form, labeled Find Record or the like, and name it
cmdFind.

Find its Click event; select the ... icon by it, and invoke the code builder.
Put in code like

Private Sub cmdFind_Click()
Dim rs As DAO.Recordset
Dim iAns As Integer
' get the form's record source as a recordset
Set rs = Me.RecordsetClone
' find the record for the chosen HA and POLYR
rs.FindFirst "[HA] = '" & Me.cboHA & "' AND [POLYR] = '" & Me!cboPOLYR & "'"
If rs.NoMatch Then
' no such record, offer the user a new record
iAns = MsgBox("No record found! Create a new one?", vbYesNo)
If iAns = vbYes Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRecord
Me!HA = Me!cboHA
Me!POLYR = Me!cboPOLYR
End If
Else
' go to the record that was found
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End Sub

 

[BobC]

THANK YOU!!!!!!!!!!!!!!!

Thank you!
That part now works.
Unfortunately I may be headed down the wrong path.
HA an POLYR jointly 'technically' define a unique record; however it
does not seem to get me in a position to edit that specific record;
which was original intent for the form and the two comboboxes.
My goal is to create a form in which I can easily edit the table.

I'd suggest using the two unbound combos on the form (perhaps on the form
header to make it more apparent that these are for navigation not for data
editing. Let's call them cboHA and cboPOLYR.

Put a command button on the form, labeled Find Record or the like, and name it
cmdFind.

Find its Click event; select the ... icon by it, and invoke the code builder.
Put in code like

Private Sub cmdFind_Click()
Dim rs As DAO.Recordset
Dim iAns As Integer
' get the form's record source as a recordset
Set rs = Me.RecordsetClone
' find the record for the chosen HA and POLYR
rs.FindFirst "[HA] = '" & Me.cboHA & "' AND [POLYR] = '" & Me!cboPOLYR & "'"
If rs.NoMatch Then
' no such record, offer the user a new record
iAns = MsgBox("No record found! Create a new one?", vbYesNo)
If iAns = vbYes Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRecord
Me!HA = Me!cboHA
Me!POLYR = Me!cboPOLYR
End If
Else
' go to the record that was found
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End Sub

 

[BobC]

Would the code change if my fields contain integers?

Thank you!
That part now works.
Unfortunately I may be headed down the wrong path.
HA an POLYR jointly 'technically' define a unique record; however it
does not seem to get me in a position to edit that specific record;
which was original intent for the form and the two comboboxes.
My goal is to create a form in which I can easily edit the table.

I'd suggest using the two unbound combos on the form (perhaps on the form
header to make it more apparent that these are for navigation not for data
editing. Let's call them cboHA and cboPOLYR.

Put a command button on the form, labeled Find Record or the like, and name it
cmdFind.

Find its Click event; select the ... icon by it, and invoke the code builder.
Put in code like

Private Sub cmdFind_Click()
Dim rs As DAO.Recordset
Dim iAns As Integer
' get the form's record source as a recordset
Set rs = Me.RecordsetClone
' find the record for the chosen HA and POLYR
rs.FindFirst "[HA] = '" & Me.cboHA & "' AND [POLYR] = '" & Me!cboPOLYR & "'"
If rs.NoMatch Then
' no such record, offer the user a new record
iAns = MsgBox("No record found! Create a new one?", vbYesNo)
If iAns = vbYes Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRecord
Me!HA = Me!cboHA
Me!POLYR = Me!cboPOLYR
End If
Else
' go to the record that was found
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End Sub

 

[John W. Vinson]

Would the code change if my fields contain integers?

Yes; remove the quote delimiters:

rs.FindFirst "[HA] = " & Me.cboHA & " AND [POLYR] = " & Me!cboPOLYR

 

[BobC]

Thanks ... works great!!!!!!!!!!!!!!!!!

Would the code change if my fields contain integers?

Yes; remove the quote delimiters:

rs.FindFirst "[HA] = " & Me.cboHA & " AND [POLYR] = " & Me!cboPOLYR