Convert Rows to a Column...

[imtheodore]

I am working on a scheduling application. The data is being passed to me in the follwoing format:

Day Room1 Room2 Room3 Room4 Room5 Room6 Etc...
1/1/07 Jon Bill Ray Julie Tom Paul
1/2/07 Bill Ray Jon Cindy Kathy Tom
etc...

How can I display these values (querying by day) in a list box for selection? ie: 1/1/07

Jon
Bill
Ray
Julie
Tom
Paul


Thanks,
Dave

 

[DJkarl]

I am working on a scheduling application. The data is being passed to me in the follwoing format:

Day Room1 Room2 Room3 Room4 Room5 Room6 Etc...
1/1/07 Jon Bill Ray Julie Tom Paul
1/2/07 Bill Ray Jon Cindy Kathy Tom
etc...

How can I display these values (querying by day) in a list box for selection? ie: 1/1/07

Jon
Bill
Ray
Julie
Tom
Paul


Thanks,
Dave

You should normalize the data set you're dealing with, rather than trying to come up with some elaborate solution to handle improperly stored data. It looks like this data was being kept in a spreadsheet or report type format, if you have the ability to change the data format of the import or linked data then do so, if that's not an option then you may want to change it in a local copy before working with it, it will make your life MUCH easier down the road.

This:

1/1/07   Jon         Bill           Ray        Julie         Tom        Paul

Should be more like this:

1/1/07   Jon         
1/1/07   Bill           
1/1/07   Ray        
1/1/07   Julie         
1/1/07   Tom        
1/1/07   Paul

As to HOW you would go about normalizing the data that's the trick, I would do it manually if this were a one time thing, or write a custom function to handle the transposing of data if this were an ongoing process and I couldn't get the datasource in a good useable format.

 

[imtheodore]

The problem is that I'm getting the data from another system, daily. There is no way to do it manually or receive it in a different manner. I'm not sure what else I can do.
I need to query the data each day to provide a list of employees for that particular day.

 

[imtheodore]

To normalize the data, you would need to import the spreadsheet and then run a series of append queries - one for each room.

If you are adept at Excel VBA, you may be able to write automation code to convert rows to columns in Excel and then import the pivoted data.

The data is linked from a SQL db.
I run a daily query to get my (unnormalized) data.
How would I use append queries? I'm not sure what you are thinking?

 

[imtheodore]

OK, I figured out how to append the rows, I put all of the appends in a macro. Is there any way to suppress the message "You are about to append one row..." so I do not have to click ok 34 times?

 

[Rabbie]

Go to Tools --> Options --> Edit/find and untick the items in the confirm box.

HTH