Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dcores
Starting Member
3 Posts |
 Posted - 2004-05-14 : 09:43:19
|
| Hello everybody!!I'v been brainstorming on the following:I' have a table where phonenumbers are stored in.(acces DB and usage datapages)something like this:numberid | userid | description | phonenumber---------------------------------------------1 | 2 | Mobile | 01232 | 2 | Home | 32503 | 3 | Home | 65204 | 4 | Mobile | 5860And this is the brainstorm thing:If i make a query, standard select * from this table, it shows the records like this... ok no prob! but what i want is to combine the ouput. meaning that all the phonenumbers of an userID come in the same row, something like this:numberid | userid | description | phonenumber---------------------------------------------1 | 2 | Mobile | 0123,3250<-(both phonenumbers of 3 | 3 | Home | 6520 user with ID = 2) 4 | 4 | Mobile | 5860Thirst, i hope I made my self clear:D and second of all, is this possible...?I had an idea to resolve this with the query I use. Is it possible to put in your SELECT, something like thisSELECT phonenumer.Mobile, Phonenumber.Home ?I tried this in acces but when i want to run the query, i get a pop-up that i have to fill in an value for these two...Thanks in advanceand if you don't know what I'm taling about, feel free to ask:Dleter! |
|
|
drymchaser
Aged Yak Warrior
552 Posts |
Posted - 2004-05-14 : 11:16:49
|
| You could:Create a Cross-Tab query with userid as row headings, (numberid & description) as column heading, and MAX(phonenumber) as value.Then create a select query with the cross-tab as the "table" and you can build your string. |
 |
|
|
dcores
Starting Member
3 Posts |
Posted - 2004-05-14 : 11:59:04
|
| Thanks!that's indeed an Idea. Just when i read your message, i was doin the following...I made a second table equal to the one i'm using...I have a problem with that now:when I insert a new record in table 1, it isn't inserted in table 2.A made a relationshop 1-1.What do you have to do, in order to get the record inserted from table 1 into table 2thanks!D. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-05-14 : 13:36:30
|
| your query makes no sense. you are displaying "Mobile" as the description of the phone numbers, but then you are printing two phone numbers next to it, and one of them is the home # but there's no way to tell which is which.Why do you need to format the data this way? For a printout? Have you tried designing a report in Access yet? You shouldn't be too converned with formatting your data in a query to make it look nice; get the data right and then do the formatting in a report.- Jeff |
|
|
|
|
|
|
|
|
Two rows combined...