Set one Record to Yes

I hav a table that has multiple transactions to a customer and I like to to have one of the fields changed to "Y"..

Example

Permit Number        PermitT          Process Status         Permit Issued Date      Primary Issued
                                   Type Code                                                                              To Flag	
00-00000015	ERPR	     Permit Complete	     04/27/2000	           Y
00-00000015	ERPR	     Permit Complete	     04/27/2000	           Y
00-00000015	GRDG	     Permit Issued	     03/28/2000	           Y
00-00000015	GRDG	     Permit Issued	     03/28/2000	           Y
00-00000015	PLMB	     Permit Issued	     04/27/2000	           Y
00-00000015	PLMB	     Permit Issued	     04/27/2000	           Y



I like assistance to change all the "Primary Issued To Flag" to "N" except one it needs to stay as "Y",

the number of records are different per customer, a customer could have one or many as you see in the example above..

Need assitance please...
TonyEFAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
How do you determine which of the records you've shown above should be marked as False?
0
TonyEFAuthor Commented:
it does not matter as long as one of them is left with "Y" and all others with "N"
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
So this would work:

Currentproject.connection.execute "UPDATE [YourTable] SET [Primary Issued To Flag]=False WHERE YourIDField <> (SELECT Top 1 YourIDField FROM YourTable)"
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

TonyEFAuthor Commented:
I tried to run an update query but did not work..

I also tried the following in the query:

UPDATE tbl_Permit_Info_Final SET tbl_Permit_Info_Final.[Primary Issued To Flag] = "N"
WHERE (((tbl_Permit_Info_Final.[Permit Number])<>(SELECT Top 1 [Permit Number] FROM tbl_Permit_Info_Final)));

Did not work, only the first records on the table did not change all the others changed to "N"
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
What "did not work"?

You indicated you wanted to change all the records in a table to False except for one random record. The code does that, and you seem to indicate this is so.

If you have other requirements, you must clearly state them.
0
TonyEFAuthor Commented:
I must be doing something wrong. Can you send me an access database example on how you are using the code that you given me???

the criteria is to change all records but one to N and only leave one to Y for each of the customers account.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
for each of the customers account.
That's where the issue might be. The code i gave you changes the value to "False" for all accounts, not by customer.

I'll have to review and see what I can do.
0
Jim P.Commented:
Here is code to do what you want to do. But some design notes:

Spaces in field names are frowned upon.
Why do you have duplicate records? Study up on Database normalization

Public Function Q28242266()

Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim SQL As String

Dim IDVal As String

'Set all the records to false
SQL = "UPDATE tbl_Permit_Info_Final " & _
    "SET tbl_Permit_Info_Final.[Primary Issued To Flag] = False "

DoCmd.SetWarnings False
DoCmd.RunSQL SQL
DoCmd.SetWarnings True

'Go through row by row and then set the first record _
 to true.
SQL = "SELECT [Permit Number], [Primary Issued To Flag]  " & _
    "FROM tbl_Permit_Info_Final " & _
    "ORDER BY [Permit Number] "

Set DB = CurrentDb()                'Use the current database
Set RS = DB.OpenRecordset(SQL)      'actually open the recordset


IDVal = "blank"

If RS.EOF = False Then RS.MoveFirst

Do Until RS.EOF
    If RS![Permit Number] <> IDVal Then          'Reset if a new group id
        With RS
            .Edit
            ![Primary Issued To Flag] = True
            .Update
        End With
        IDVal = RS![Permit Number]
    End If
    RS.MoveNext
Loop

Set RS = Nothing        'Close out the recordset and free the memory space.
Set DB = Nothing

End Function

Open in new window

0
Vadim RappCommented:
TonyEF, you said you need to leave one record with "Y" per customer; but how is Customer represented in the data you posted in your question? how to tell which record belongs to which customer?
0
hnasrCommented:
One way is to use a recordset of the table records sorted by Permit Number.
This example for table b(f1, f2, f3)
f1 f2 f3
1   1  Y
1   2  Y
1   3  Y
2   1  Y
2   1  Y

    Dim rs2 As Recordset
    Set rs2 = CurrentDb.OpenRecordset("Select f1, f2, f3 from b order by f1")
    Dim f1 As Integer
    Dim c As Integer
    rs2.MoveFirst
    rs2.Edit
    rs2("f3") = "Y"
    rs2.Update
    c = 0
    For i = 1 To rs2.RecordCount
        rs2.Edit
        If f1 = rs2("f1") Then
            rs2("f3") = "N"
        Else
            f1 = rs2("f1")
            rs2("f3") = "Y"
        End If
        rs2.Update
        rs2.MoveNext
    Next

Open in new window

Resulr:
1   1  Y
1   2  N
1   3  N
2   1  Y
2   1  N
Hope that helps!
0
Nick67Commented:
There are many different ways to accomplish what you seek, BUT

HOW do you want to accomplish this?  From a button click on a form?  Through a query? or some other method?

HOW are you differentiating between customers that you want this to be done for, and those that you do not want it done for?  On a form?  Through a parameter query?

it does not matter as long as one of them is left with "Y" and all others with "N"
That sounds quite dubious, from a DB developer's point of view.

WHY would you want to do things that way?  What does the field 'Primary issued to flag' signify in the logic of your app?

We can certainly provide the ability to change data -- but understanding what exact data you want to change and why will be important to helping you change the right data
0
Vadim RappCommented:
It's hilarious that whole crowd of experts are sending solutions of the problem where the keyword is "per customer", while "customer" has never been defined.
0
Nick67Commented:
As an Answerer, it is always hard to know the skill level of the Questioner.
And...every Answerer has indicated that the Question begs many others

I am normally the first guy to request a sample, or provide one
Can you send me an access database example on how you are using the code that you given me???

But there isn't enough to go on with here yet
:)
0
TonyEFAuthor Commented:
I want to get this done on a query and if you can send me a database with an example I would appreciate it...

to answer the question of why is there duplicate, these are not duplicate records, the issue is that the data is being taken from one system to another and the new system requires to have one of the records to have a Y in that particular field and the other system it did not...
0
hnasrCommented:
Suppose you want to instruct a child to do the job, what instructions your are going to tell him in order to insert Y or N?. Apply these instructions to your data.
0
Jim P.Commented:
I want to get this done on a query and if you can send me a database with an example I would appreciate it... TonyEF

There is no really effective way to do it. We have not a clue of the data you are importing. We have no knowledge of the real data. We have no real knowledge of the rest of the structure. We have no clue as why your data is not normalized. We have no clue of your final intent.

We can not read your mind, see your data, know why you are doing this. My mind reading license expired a few years ago. So please help us try to help you.
0
Nick67Commented:
@TonyEF

Have you heard of SWAG? (scientific wild-ass guess) :)

I have created a sample that makes sense to me.
Select a customer
Select a main permit
Sub permits show in the listbox
Click the button to change data.

It won't be exactly what you need, but the mechanics behind the button can be adapted.
What you want to do cannot be done in a single query as you want to change all the filtered records to NO and then a single record of that same set to YES.

Have a look
flags.mdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TonyEFAuthor Commented:
I've requested that this question be deleted for the following reason:

There was no answer to this question and some of the responders were rude and condescending.. This kind of behavior should not be tolerated in a site like this especially when a customer is paying for the service....

0
Vadim RappCommented:
Some of the responders have understandably become impatient (I'd say) after their multiple requests for additional clarification did not result in any meaningful response.

Such as, I asked to clarify what is customer, which was the central figure in your question, and which you did not define. You did not answer it.

You work here with the experts who don't receive a dime of compensation, and who volunteer their free time to help you, while often being top pros in their fields. They spend their time hoping to create some quality solution to nontrivial problem; when they see that it appears instead just wasting time, this reaction becomes not surprising.
0
Nick67Commented:
I am sorry that you perceived responses as rude--deletion does not solve your problem.  And it wastes the time I spent creating a working sample
0
Nick67Commented:
I hope the sample helped, and that you solved the problem
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.