Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2006
    Posts
    4

    Unanswered: Find string in multiple fields

    I'm working with an existing dataset from excel and need to change the setup of alot of the data in order to get what i need. One table has attendance data but it is broken up by session number---so id, show1, show2, etc...with 1's and 0's. I would rather have multiple records per person and the dates, so, id, show date. Is there an easy way to manipulate the data to do this?

    Otherwise, i need to write code to search through all of the [show#] fields..there are about 15, to look for a string of text.

    Anyone have any good suggestion on how to either one of these things? Thanks!

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    I think i might be able to help you, but I'm having trouble understanding exactly what it is you're looking for. Could you restate your problem?
    Me.Geek = True

  3. #3
    Join Date
    Aug 2006
    Posts
    4

    problem

    Well, ultimately i want to be able to tell how many sessions they were eligible for...based on what already exists: 1=attended,0=did not attend, W=withdrew. So i think it would be easiest to change the data setup from [person], [show1],[show2]...etc. TO [person], [show]---this would have the show dates (that would come from a lookup table). Is that any clearer?

  4. #4
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Well you've identified one of the pitfalls of the data structure you are faced with. The best solution as you have also identified is to get the data into a structure like:

    Person___ShowNumber____Attendance


    You can use a query to view your data in this structure. Suppose your table is called ShowsAcross then the following query should do the trick:

    SELECT Person, "Show1" AS Show, Show1
    FROM ShowsAcross
    UNION
    SELECT Person, "Show2" AS Show, Show2
    FROM ShowsAcross
    UNION
    SELECT Person, "Show3" AS Show, Show3
    FROM ShowsAcross
    etc...

    You can then use the above query as the source for all your reports.

    You can alter the above query to a Make Table query and then it would make a table in the new structure.

    As for Excel I don't really know how you would go about restructuring in Excel other than manually copy/paste.

    hth
    Chris

  5. #5
    Join Date
    Aug 2006
    Posts
    4

    show #

    Thanks, this is helpful. I'm kind of lost on how to add in a field that shows which show # it is. Otherwise i just have all 0's and 1's, but am not sure which show it goes with. I tried to write in SQL a right() on [show1] AS show3, but that didn't work.

  6. #6
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Sorry, I don't think I got the query quite right. Anyway, I've attached a working example.

    Take a look at the query ShowAttendance. This shows your data re-shaped. The query AttendanceWithDates just adds dates based on a Shows table.

    hth
    Chris
    Attached Files Attached Files

  7. #7
    Join Date
    Aug 2006
    Posts
    4

    thank you

    Thank you....i got it to work!

Posting Permissions

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