Split Query Field into Sub Queries/Tables

[maggioant]

I have an excel file that contains the fields: [Event ID], [Event Name], [Required Attendees], [Actual Attendees]

All of the names of the required and actual attendees are listed in one cell in the event record. The names are listed in this format: Doe, John H; Dow, Jane M;

I need to figure out a way to create a report of absent attendees for each event. I also need to create another report of people who were not required to attend an event, but attended anyway.

Here is an image of what I am trying to attempt:

I would imagine that this needs some combination of a split function and some type of join query? I'm relatively new to Access with no VBA experience, so this is a little beyond me.

 

[namliam]

If you have an excel file, how about using a VLookup or Countif function to check for non-attendends?

Perhaps even a simple filter will do depending on the exact setup... (no screeny attached)

 

[yavahn]

I would have used a V-lookup as well, then apply a filter to th column, and specify, where is not null (blanks)

 

[maggioant]

The problem is that the Access database is being used to generate many other reports on a daily basis, therefore, I'm trying to keep all the reporting in Access as not to complicate things.

What I really need is a way to distribute the data contained in one cell into rows of a new table. Is there a way to take a field with comma delineated names and create a table that contains: [Event ID], [Name] columns with the data in rows?

 

[Anchoress]

Being the Word maven and Access n00b I am, if I understand you correctly, what I'd do is use Word to normalise the data with find and replace or TextToTable, then paste, import or upload the data into Access.

Is that what you mean? You want to turn the one record with all its "many" data in one cell into something that can be pivoted out into a one-to-many record set?

 

[maggioant]

That is what I'm trying to do, but it needs to happen within Access.

The source data file for this DB is downloaded daily, so it's not practical to perform any kinds of edits outside of Access.

 

[Anchoress]

That is what I'm trying to do, but it needs to happen within Access.

The source data file for this DB is downloaded daily, so it's not practical to perform any kinds of edits outside of Access.

Right, k. Good luck!

 

[maggioant]

Thanks. At this point it's looking like it might not be possible.

Have a good weekend.

 

[Anchoress]

Thanks. At this point it's looking like it might not be possible.

Have a good weekend.

Oh I'm sure it's possible! Access will do a back-flip and hand you a cappuccino if you know how to ask properly. Splitting data and bunging it into separate fields or records is something Access can do half-asleep with one hand tied behind its back - I just don't know how to do it lol. Most of the Access geniuses on this board are in the UK, so this isn't the best time to get their help. If you can hang tight to the end of the weekend, you should get some great replies at least by midnight Sunday night, if not earlier.

Have you checked the sample code section? You might want to look and see if there's anything covering splitting data into separate records.

Also, how much do you know about Append Queries? Normalising a bunch of new records and adding them to a table is one of the main things Append queries were made for. Might be worth reading up on. I'll look and see if I can find anything on the board that would be useful.

 

[Anchoress]

THIS splits data into multiple fields - not exactly what you wanted, but still...
LOTS of tips on normalisation...
THIS looks like exactly what you're trying to do...

And while I'm kicking myself for not being more on the ball, have you already investigated the functionality of the import function? Because it will normalise a flat file DB into a relational DB using any criteria you like, including punctuation separators.

 

[namliam]

Thanks. At this point it's looking like it might not be possible.

Have a good weekend.

Just quickly confirming that it IS possible (offcourse, though the cappucino might be a real challenge )

Looks like you have some links to read, come back if you get stuck

 

[maggioant]

As expected, I'm a little lost here. I read through the samples, but couldn't figure out how to adapt any of them to parse through a cell of comma delineated values. It also seems like the Mid$, Left$ and Right$ functions won't work in this case, as they require you to specify a number of characters.

In my example, both the length of the Attendees' names and the number of Attendees is variable within each record.

Also, I checked out the import feature but it doesn't seem to help in this case... at least in Access 2007.

I think I'll be okay once I'm able to get the Attendees cells (required and attended) into tables. This "data parsing" is really tough to figure out!

 

[namliam]

Do you have a sample of the file you need to process?

Sounds like a simple import...

As for the Mid/Left/Right, yes they require you to give a position/number of characters but you can use Instr or InstrRev to find character positions

 

[maggioant]

I've created a copy of the file and loaded it with test names. Thanks for your help. I never would have imagined that parsing these names could be so difficult!

 

[namliam]

Normalizing data like this is a NIGHTMARE...

Step 1)
Import this file into an import table

Step 2)
Process the table,
Try having a look at:
http://www.access-programmers.co.uk/forums/showthread.php?t=175633
or
http://www.access-programmers.co.uk/forums/showthread.php?t=130858

And see if you can addapt those to suite your exact needs...