Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Wirral, Merseyside, Merseyside, England
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting data from multiple fields

    You can display the results of 2 or more fields together in a query using the following:

    NewFieldName:[Name Code]&[AddCode]
    or
    NewFieldName:[Name Code]&";"&[AddCode]

  2. #2
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Selecting data from multiple fields

    I have a database with a field for storing a main code and a field for storing additional codes i.e.

    Name| Code |AddCode
    Phil| ME7 |RO1;BR1


    I'm trying to make some kind of query/sql/vb way of selecting each record by the code AND the AddCode, i'd then bring in additional info based on each code so the resulting table would look like:

    Phil| ME7| RO1;BR1| ME7 info
    Phil| ME7| RO1;BR1| RO1 info
    Phil| ME7| RO1;BR1| BR1 info

    I can do this by making tables and appending to it, but I can't do this so it shows the data without making a table. It is possible I can use the table method but i'd prefer not to. Has anyone got any ideas how to overcome this problem or would it be a mammoth coding job?
    My VB/VBA knowledge is very very basic. I'm using Office 97 on WinNT.

    Any help would be much appreciated.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting data from multiple fields

    Thanks for the reply, but thats now quite what I meant. I didn't make a very good job of explaining it.

    I have a set of codes for job titles. These codes are used in the field [code] AND the field [AddCode]. The latter is for additional responsibilities. In my first post, the first example shows that I have the job code ME7 but additionals of RO1 & BR1. I want a query that will make me a table with duplicate names and a field with each individual code from both [code] and [addcode].

    Hope that clears any confusion up! Not the easiest thing to explain but i'm getting there.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Selecting data from multiple fields

    You have a problem in your table design. AddCode shouldn't be in the same record and it shouldn't contain multiple values. This is bad database design and will bite you down the road.

    You need another table that holds a record for each code and each additional code that relates to that code. That would allow you to have as many AddCodes as you need for each code and to access any or all of them separately without having to parse the value out of a multivalue field.
    Charlotte

  5. #5
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting data from multiple fields

    It's an old database - originally started 11 years ago (not by me) - and I think that "down the road" stage is where I am!

Posting Permissions

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