Combining multiple rows by creating new columns

[fyamoahaxracer]

Hi everyone, I have spent a few hours searching and cannot find an example that is similar enough for me to work with.

Is it possible to combine multiple rows by creating new columns? Searching the forum makes me believe it will take some VBA code.

What I have:
Number | person | email | phone | fax | Y/N | time
1 | bob | b.com | 123 | 123 | Y | 5:00
8 | bob | b.com | 888 | 888 | N |
4 | bob | b.com | 123 | 123 | Y | 5:00
1 | sue | s.com | 456 | 456 | N |
5 | sue | s.com | 456 | 456 | N |

What I'm looking for:
person | email | phone | fax | 1 | in_1 | 4 | in_4 | 5 | in_5 | 8 | in_8
bob | b.com | 888 | 888 | 5:00| Y | Y | 5:00 |NULL | N | N | Y
sue | s.com | 456 | 456 | N | Y |NULL | N | N | Y |NULL | N


It will take each different value in the number column and create a new column titles with the value.

It will update the phone and fax with the most recent value (The highest number in the number column will always be the most recent value.)

If the value is Yes, it will put the time instead of yes. If the value is no, it will put no.

If the person does not have a number that someone else has it will put no in the in_# column, else it will put yes.


This may take several queries to break the data apart better.
Any help would be greatly appreciated, I am new to access and have only created basic queries.

[Seth Schrock]

You would actually want to do this in a related table as records and not as new fields. See Database Normalization and Table Structures. By doing it this way, you can add unlimited number of values without having to code anything or change the design of anything.

[jforbes]

What you are asking for could get pretty complicated and would most likely result in some VBA. The reason it could get complicated is that it isn't a standard practice in a Database and is akin to de-normalization. There is a slim chance what you are looking for could be accomplished by a Crosstab Query, but I wouldn't get your hopes up.

So, before heading down a development path, it would probably be best to ask what you are attempting to accomplish with this? There may be a simpler solution; a solution more inline with the tools available in Access.

[fyamoahaxracer]

I will look into what both of you have suggested, thank you.
For now there doesn't seem to be an easy way of doing this.

I was able to figure out how to replace the Yes with the time so instead of having a Yes/No column and a time column the new column is No/Time where if it is a yes it will show the time instead of the work yes.

[Rabbit]

This is certainly doable. And we can help you do it. But before we can, you have to answer jforbe's question.

[fyamoahaxracer]

Thanks Rabbit, I read his question but completely skipped it in my response.
I have multiple rows per user, along with a number for each user. There can be multiple numbers per user. I am trying to combine multiple rows for each user into one row per user but create a column titled with that number and place a value whether or not the user has that number. Let me know if that makes sense, it sounds confusing trying to type it out.

[Rabbit]

That's what you want to do. The question is why. We understand what it is you want to accomplish, but if you tell us the why, you may find out that you don't need to do it that way at all.

[fyamoahaxracer]

I need to be able to view a list of all the people in order to figure out who is a Yes or No for each Number. It is just for reporting purposes so there is one list of all of the users and what numbers they are a yes or no for. (Hopefully this counts as a why.)

[Rabbit]

The next question is, does it have to be in that format? There's multiple ways to lay out that information and although I see the appeal of that layout, that's one of the more complicated methods to accomplish.

[fyamoahaxracer]

Yes it has to be in that format unfortunately.
I was able to create a crosstab query that does almost everything that I need.

person | email _| phone | fax | total __| total_Y __| 1 ___| 4 __| 5 | 8
bob ___| b.com | 888 __| 888 | 3 ____| ??_2_?? | 5:00 | 5:00 | 0 | N
sue ___| s.com | 456 __| 456 | 2 ____| ??_0_?? | N ___| 0 __| N | 0


A 0 means that the person does not have that number.
A N means they have that number but the value should be No
A time means they have the number and the value is a yes but I needed the time instead of yes.
I also have a total column that calculates the total numbers that each person has.

However, I need a column that calculates the total times each person has. I think this could use the Len() function to calculate the total of values that are longer than 1 character but I am having issues creating this column. And ideas?

[jforbes]

I would create a separate Query to calculate the totals, then create a third query to join the Crosstab Query you have with the New Totals Query on the Person. It will be easier to create and easier to maintain.

If you want to keep your query count down, create a Query based off the Crosstab Query, and add a column to select a Sum() from the base Table, kinda like:

SELECT 
  Crosstab.*
, (SELECT Sum(Time) FROM BaseTable WHERE BaseTable.Person=Crosstab.Person) AS TotalTime
FROM Crosstab

[Rabbit]

In addition to jforbes suggestions, you can use the IIf() function to change your values that are displayed, you can also combine it with a sum to get your total Y count.

[fyamoahaxracer]

Rabbit, can you elaborate a little more on your idea. I have looked at jforbes response but am trying to get everything into one query if possible. I have used IIf, Nz and Len before and understand how they work, but not exactly sure what you are suggesting.

I can get the Total values in both the Total and Total_Y columns and I can also get the Total_Y values in both columns but I cannot get each in their own column at this point. If I use a WHERE Len(...)>2 I can get the desired results for the Total_Y column but the results also appear in the Total column since it filters those out completely. Can I use the WHERE filter only for one column or SELECT value?

[Rabbit]

I have looked at jforbes response but am trying to get everything into one query if possible.

This (probably) won't be possible with everything you're trying to do.

I have used IIf, Nz and Len before and understand how they work, but not exactly sure what you are suggesting.

You said you want to display different values depending on the value of the field. You can do this using the IIf function.

IIf(field1 = 'some value', 'other value', field1)

Basically, what this does is, if field1 is equal to some value, return other value instead. Otherwise, for all other values of field1, return the original value.

Can I use the WHERE filter only for one column or SELECT value?

Nope.

[fyamoahaxracer]

Okay that is helping. I might have phrased one of my questions wrong.

If I have

# | 1 | 2 | 3 | 4 | 5
A | 0 | Y | N | Y | 0
B | N | N | 0 | 0 | 0

Can I create a both a column to sum the total Y and N in a row, and another column to sum just the Y in a row with only one query? Or will I have to go the route suggested above with multiple queries?

[Rabbit]

You can, but it's convoluted. It's easier to go with a separate query as jforbes suggested. And then combine them into one result set.

[fyamoahaxracer]

Okay thank you, that is the approach I am working on now.