Rename cell based on duplicate compound key

[luckycharms]

Hi Folks,

I'm trying to rename a cell when there are duplicates of mutliple columns. Here's an example

[FONT=courier new]Col_1   Col_2   Col_3   Note
A1      B1      C1      
A1      B2      C1      Not a Dup
A1      B1      C2      Not a Dup
A2      B2      C2      
A2      B2      C2      Duplicate
A3      B3      C3      
A3      B3      C3      Duplicate
A3      B3      C3      Duplicate
[/FONT]

I would like the query or code to update Col_3 (or add another column if that's easier) as below. Note that the order of the duplication is important.

Col_1   Col_2   Col_3   Note
A1      B1      C1      
A1      B2      C1      Not a Dup
A1      B1      C2      Not a Dup
A2      B2      C2      
A2      B2      C2Dup1  Duplicate
A3      B3      C3      
A3      B3      C3Dup1  Duplicate
A3      B3      C3Dup2  Duplicate

I've seen solutions that use row_number() and over() clauses, but those just work in sql server. Any thoughts for how to tackle this in Access?

Thanks!

 

[MStef]

Look at "DemoRenameCellA2000.mdb" (attachment, zip).
Look at Table1, queries, Module1, Form1 (VBA).
Open Form1 and click on the button.
Before take a copy of your table.
I think it is what you need.

 

[luckycharms]

Thanks so much for your help!

While it seemed to work for me, I was looking for a solution that wasn't so "Access-centric". I ended up scripting a solution that worked on an excel file.

 

[Jeanette Cunningham]

You wrote: "I was looking for a solution that wasn't so "Access-centric". I ended up scripting a solution that worked on an excel file."

However you posted to a forum that specialises in access and vba - next time choose a forum which specialises in the type of code you are looking for. The people who answer here are all volunteers and it is disappointing to spend voluntary time working on a solution only to find that the poster didn't want a solution using access.

 

[luckycharms]

Hi Jeanette,

Thanks for your response, and I totally understand your sentiment. I *greatly* appreciate the time, effort and expertise that volunteers like MStef graciously share!

Perhaps I mis-worded my response to MStef. Let my clarify what I meant when I said "something not so 'access-centric'". My database is in Access, but I don't use forms, buttons, etc. It just use it as a relational database, and do most everything in sql (though I manage relationships with the GUI). I do a bunch of stuff in VBA as well. While VBA makes my database somewhat less portable, it's mostly just used to set the data up. So, once set up, I can really port it to most any sql-based platform.

Perhaps I should learn how to use the forms and buttons and such. In any case, the great solution that MStef put together used forms and actions in a way that would make my DB even less portable, or at least that's what my perception was.

So, while I do truly appreciate MStef's help, this is also part of the process of development: you look for one solution, search and sniff around, and you then sometimes decide that going a different direction would be best. There have been countless times that I *have* used the solutions posted here and elsewhere in my work. I don't mean to abuse anyone's time or effort, and in fact, that's why I posted back in the first place: I want the history of this thing to be online for others to see if they walk down the same path in the future.

So, thanks to MStef and Jeanette for your help and comments, and I hope there wasn't too much disillusionment with my not using the solution posted here in the end.

Thanks.

 

[Pat Hartman]

I'm not sure why you think the use of forms makes the database less portable. You need Access to manage the tables. What's the difference if the database has forms so there is some framework and you don't have to work directly in tables?

Are you aware that an Access application is not limited to using Jet/ACE to store its data? Access can use data from any relational database that publishes an ODBC driver. That's pretty portable and scaleable where I come from.

 

[luckycharms]

Hi Pat,

Thanks for the reply. When I say "portable", I mean portable to different DB systems. An access form, for example, won't be portable to a MYSQL db, for example. Neither will VBA.

Interesting that Access is able to interact via ODBC - didn't know that, and it'd be interesting to look into. Regardless, such a solution still requires running on Windows, or some platform that supplies ODBC and runs Access, and then that also requires actually running Access externally to use the code you've written. How many Oracle DBAs would want to run Access to manage their Oracle data, for example?

You can agree with me or not - I don't feel the need to convince you. But thanks for the information about access connecting to other db's via odbc.

 

[Pat Hartman]

Most of the applications I build connect to SQL Server, Oracle, or DB2 databases. Very few actually connect to Jet/ACE. Access is an excellent development environment if you like flexibility, responsiveness, and don't feel the need to waste money on "real" development environments. I developed one application that a client sells with the option of ACE or SQL Server (actually all of my apps can switch. I just don't deliver them with that option exposed to the user). The client installs the BE database he prefers and when the FE opens the first time, it asks where it's BE is and that's it. Under the covers, the code deletes existing linked tables and recreates the links to the selected database. Try that with a different tool.