Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2008
    Posts
    7

    Unanswered: Merging two columns into one and therefore doubling rows

    So, I feel that I've missed something here but here goes...

    If i have a table with two columns, how can I (using a query) pull those together to appear to be the same field... ?
    Code:
    col1	col2
    1	2
    3	4
    
    to be
    
    colX	colY
    1	fromCol1
    2	fromCol2
    3	fromCol1
    4	fromCol2

    A fuller explanation of what I'm doing.... (it's not imperative that you read this bit!)


    I have a system that monitors kids coming in and out of a nursery. The childrens "shifts" have a "start time" and "end time" already stored in data, and every other bit of the system likes these to be separate fields.

    However I need to calculate how many staff are required at any one time. So my plan is to combine these start/end times into one field as above with the second column telling me if a child is going in/out. therefore using these piece of data i can iterate through the data and for each start/end point calculate how many children there will be.

    Any other suggestions to solve this would be welcome. No doubt something will come to me if I just stop thinking about it.!

    ta
    Adrian

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you can do it with a single query, using your existing table

    it needs a start/end time slice, though, to measure the number of kids

    what would you like to use, every 15 minutes, half hour, or something?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2008
    Posts
    7

    Thanks

    Hi, thanks for your suggestion.

    That was my initial idea for doing this, but I was thinking rather than doing unnecessary calculations based on every 30 minute period, I should do that calculations based solely on when the number of children change throughout the day.

    I think I may well use this 30 minute interval method unless I have a brainwave in my sleep tonight.

    thanks again :-)

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, holler if you would like to see the query

    or go ahead and write your application code if you must ...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •