Combining data into individual records

[ipabrewer]

This is my first post. I hope y'all can help me! It's for my comic book database.



I can't find example code of what I want to do because I don't know how to describe what I need. All I can do is show an example set of data of what I have and an example of the output.

Here goes!

Sample original data:
Title Issue Number
Action Comics 679
Action Comics 684
Action Comics 686
Action Comics 687
Action Comics 687
Action Comics 688
Action Comics 689
Action Comics 690
Action Comics 691
Action Comics 692
Action Comics 709
Action Comics 757
Action Comics 796
Wolverine 1
Wolverine 2
Wolverine 3
Wolverine 5

Sample output data:
Owned Books
Action Comics 687-692,709,757,796
Wolverine 1-3,5

Any help with making that happen would be awesome. Cheers!

[ItsMe]

That's an easy one to answer. Lottsa VBA and a few custom functions.

[ipabrewer]

Dude, seriously? Ah man.

[ItsMe]

Yeah, I am not seeing a way around it. I do not know everything. There may be a built in function that could help. But, you are still going to need to work with strings and parse out the numbers. While you are there doing that, you might as well use a simple +1 as logic to determine the flow of statements (like an IIf() or an if else then statement).

[ipabrewer]

Yeah, I am not seeing a way around it. I do not know everything. There may be a built in function that could help. But, you are still going to need to work with strings and parse out the numbers. While you are there doing that, you might as well use a simple +1 as logic to determine the flow of statements (like an IIf() or an if else then statement).

That's more or less what the programmer at work told me too. I can probably work it out via VBA eventually, but it's like learning a brand new language.

Do you think if I post this on a VBA forum I might get some help or is this like asking for free coding?

[ItsMe]

It can be like asking for free coding. However, there are many friendly helpful people here on AccessForums.Net. If someone recognizes you are trying, you will likely get to where you want to be.

I am not sure of your background. You may find it helpful to view the VBA screencast series I linked to in the Tutorial Forum. I would start with the Introduction to the IDE and follow through from there. Having said that, starting from the beginning will likely be beneficial. Be sure to copy the user and pass from Post #1 into notepad for easo of logging onto the landing page.
https://www.accessforums.net/showthr...671#post277671

[rpeare]

One thing to add, what you're asking for is not that hard to do with code, but your naming convention on your fields *must* be consistent. i.e. the issue number must always be last, there must always be a space between the issue number and the name of the comic, the name of the comic must always be the same. The problem with free form text fields to do this type of manipulation is this:

the amazing spiderman 1
the amazing spider man 2
amazing spiderman 3
amazing spider man 4
amazing spider-man 5

If these are all part of the same title any misspelling will cause it to be identified as NOT part of the group when you do your processing. You would be better off storing the TITLES in a separate table and, when storing your data store the title (title ID from the title table) and the issue number in separate fields, that would save you a lot of aggravation doing this type of operation. I can't tell if your data is actually structured this way or not based on your original post, the direction you go really depends on your data structure, parsing a string is more difficult than dealing with discrete values in a table.

[ItsMe]

Yeah, parsing and normalizing would go a long way. It is not likely the only and last thing you will ever want to do is what you described in post #1.