Add sequential number to a column that has duplicate names

I am currently working on a database project that involves creating an excel by extracting certain fields from a table in Access by using DAO.recordset.  After doing the process of extracting information and assigning the correct names to some of the records in those fields I have duplicates that will need an sequence number that will identify them depending on some of the criteria from other columns.  If I could get some guidance on how to accomplish this.  right now, this process produces a CSV file, then it imports this CSV in the database, and then exports the file as XLS.  I am hoping that this sequence can be done when the csv is imported into the database and assign the sequence numbers on the names that need it, and then continue to export the xls.  If it's vba I am ok with it and is probably better for me if it's done with vba.

example of desired results
tanktank2010Asked:
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.

Danny ChildIT ManagerCommented:
Bit hard to identify the end goal without some examples, but if you're after creating a naming convention to define equipment by its specifications, then yes, Excel could build this.

If so, each name should have something unique, like the serial number, as part of it

I DONT think you should use room locations as part of the name though, unless each device will NEVER move - otherwise you'll just be renaming your database with every office changearound.

Our office uses a single code to specify device - D=Desktop, L=Laptop, S=Server, etc.  We then use a 2 digit code for the country that it's in (we never move things to different countries...), and then a 5 digit serial number, unique to each device.  You could expand this scheme to include CPU type, etc.  We have reels of the stickers that we just put on whatever device needs them.

But, the core question is, what do you want the naming convention to tell you?  Would you want to identify which PCs are powerful enough to run the most demanding apps, for instance?  Would a code to identify all Haswell chipsets be sufficient?  Identifying each particular CPU model could be very time-consuming to maintain over the next 10 years...
0
tanktank2010Author Commented:
Hello Dan,

The example I used was just for how I would need that "object name" to come out in reference to the sequence.  I can't really give the exact outcome and true data results because of the information that it deals with.  Using that example was the closes I could get as far as the results that I need.  But In order to get that sequence number will depend on the ID, "floor", "room", "building" (fields enclosed with " " are made up fields for the example).  The object name has of course different names that don't really deal with computer equipment, I just used the names for an example.  I hope I can really get some kind of guidance with that example I provided.  In my case the "floor, room, building" are fields that will identify that object name and that will not change (if this helps in anything).

I really appreciate your help.
0
Rey Obrero (Capricorn1)Commented:
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Danny ChildIT ManagerCommented:
you could build a code just by concatenating the cells  you have there, but it's not clear to me why the ID numbers (RS-001, etc) are repeated for 2 different Object Types.

In what way would a simple structure like
=A2&"-"&B2&"-O"&C2&"-F"&D2&"-R"&E2&"-B"&F2
not work for you - thus giving
RS-001-CPU1-O1B-F1-R101-B1A

Or, is your query more to do with generating the red numbering in your sample table?  if so, what defines the logic here - why does it drop from 3 to 1 on row 7, for instance?
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
scsymeCommented:
Assuming that your example table occurs in range A1:F17 of a worksheet, then you need use column G to create the suffix. The formula to put into cell G2 and then copy down is
=IF(E2<>E1,1,IF(AND(A2=A1,D2=D1,E2=E1,F2=F1),G1,IF(A2<>A1,G1+1,99)))

Open in new window


The object name in cell B2 would then be given by the formula
="CPU"&G2

Open in new window


This is based on several assumptions pulled from your responses above. Some of them don't feel right, like the fact that the same ID is assigned to object type 1A and 1B in each room (but maybe a laptop and charger get the same ID?)
0
scsymeCommented:
Just reading through this again and the requirement really needs to be made a bit clearer. It seems, in your example, that for each room (on a particular floor in a particular building) if there is more than one unique ID then  the object name needs to take on a suffix. If there is only one unique id, as is the case in the last four rows, then you do not want/need a suffix.

It also seems that you want the solution in MS Access - but all the respondents so far (me included) seems to have picked up this question from the Excel topic. Are you wedded to a solution in MS Access?
0
tanktank2010Author Commented:
Hello Dan,

Yes, I will need to generate the red numbering on my sample table.  I am adding another image to where it may break it down a little more.  The sequencing stops depending on the "floor", "room", "building".  So say if there was a RS-009 CPU on second floor, then that would be CPU alone, and not a CPU2 because it falls under a different group.  
So, even though, they are in the same building, they are in different rooms or floors and will need the sequencing number in that ObjectName depending on those columns.  If the same group of objects are in building 1B (as an example), then it should probably look the same in there with the difference being in the Building number.  That's why i left room empty on RS-008 to show that that column may be empty, but it will need sequencing if needed.  The building field will never bee empty, "floor", and "room" columns could be empty and still have an objectName that may need to increment the number if there is a duplicate name.

I know you may ask why two IDs RS-00#s.  Think of it of two objects types, that are stuck together for same CPU.  Something else to keep in mind is that there could be one RS-00#, two or three RS-00#s, but not more than three.

I hope this helps clarify a little more.
example.JPG
0
tanktank2010Author Commented:
Scsyme,

It would be ideal if it is in Access.  I was hoping it could be done when the the csv is imported in the database, and those sequence numbers could be applied before it is exported to .xls.  I used the formula you provided me and it works, but in has the 1 on the ones that should not have a number because is the only one.  and it continues to add sequence number on some where it should stop, and pick back up with new sequence number because it falls in a different section.  But your logic on the formula is really close to what I need, could this be done in MS Access?
0
Rey Obrero (Capricorn1)Commented:
@tanktank2010,

have you seen the post at http:#a40640747 ?
0
tanktank2010Author Commented:
Rey,

Yes, I am looking at it, and trying to apply it in my side, but I am getting 1s across.  Still trying to apply the correct fields to the query to see if it works.
0
Rey Obrero (Capricorn1)Commented:
one very important thing about this process is to have a unique record id.
0
tanktank2010Author Commented:
Rey,

will it affect that the fact that the Object ID here may show 1, 2 or 3 times?   That object ID is the main Id that identifies such object name.
0
Rey Obrero (Capricorn1)Commented:
if the Object ID shows 1,2 times in the field of the table, you  cannot use it as the Unique RECORD ID.

an example of Unique record id is an autonumber, since it is not repeated.
0
tanktank2010Author Commented:
Rey,

is not working 100%, since that query looks on one field only  
********************  This is the query from the sample you advised to look at
SELECT X.*
FROM (SELECT DisplayName, Competitor, HS, (select count(*)     From t01HalfSupport as X2     where x2.DisplayName =x1.DisplayName       and (x2.HS > x1.HS           Or  (x2.HS = x1.HS               and x2.ID < x1.ID)                )     ) +1 AS Rank FROM t01HalfSupport AS x1)  AS X
WHERE (((X.[rank])<=10))
ORDER BY X.DisplayName, X.rank;
*************************************

On my scenario, It will have different columns to read from, in order to restart the sequence number. In my scenario I have 6 columns, that would take place of the HS from the query example.
0
Danny ChildIT ManagerCommented:
Looks like an Access-based solution is preferred, and I'll let the Clever People deal with that.

You may want to rethink whether your decision about a "single" item NOT gettin a number suffix is sustainable.  Having to renumber an OLD entry just when  you add a new (the "second") item seems fraught with trouble to me.
0
tanktank2010Author Commented:
Thanks Dan.. Either way I will take any approach that will help on that.  Either vba in Access or Excel, or formulas.  If I come up with a solution I can always post it here.  I'm trying the options that Scsyme and Rey shared, both work to a certain extent.   But I do appreciate your help and quick response.
0
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.