Results 1 to 6 of 6
  1. #1
    Lata is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2011
    Posts
    12

    Merging values from 2 fields into one field

    Hi,



    I have got a table which contains 2 fields LOW VALUE and HIGH VALUE.
    For the LOW VALUE field, it contains value something like B1 and the HIGH VALUE field contains B9...which means it covers all the values in between B1 and B9.
    I want to write a query to join this table with another table where it will consider all the values between B1 and B9.

    Can someone please help me how to proceed on this one?

    Many Thanks.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,183
    You want what's often called a non-equi join. Create your query with joins between the fields, then go into SQL view and change the = in the join to >= and <= as appropriate. From then on the query will have to be manipulated in SQL view, as the design grid can't represent a non-equi join.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Lata is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2011
    Posts
    12
    what you suggested makes real sense but what my requirement at the moment is:

    I have a postcode table.
    The fields in it are customer ID, Low value, high value.

    There are values for customerid field, low value field and very rarely there is a value in high value field. when i try to do this nonequi join, it is taking only those records which have got range between low and high and ignoring rest all records. If i dont apply any join(non equi) condition, it is just considering the low value though there is a range for high value...i am trying to comeout with a solution where in it covers all the records that contain only low value and also the records which have got range and all the records have to be covered that are within that range when I do a join with another different table on some common key.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,183
    You didn't mention that the high value was usually empty. What's it supposed to do when there's no high value? Use the low for both? You might be able to use the Nz() function to replace the high with the low when it's Null.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Lata is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2011
    Posts
    12
    Thankyou very much...its working with the function you mentioned

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,183
    Happy to help.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. merging fields, link, text boxes
    By compooper in forum Programming
    Replies: 1
    Last Post: 06-23-2011, 03:30 PM
  2. Use a lookup field to influence values in another field
    By nathanrt in forum Database Design
    Replies: 3
    Last Post: 01-13-2011, 03:40 PM
  3. merging two fields
    By brandon in forum Access
    Replies: 5
    Last Post: 06-09-2010, 08:17 AM
  4. Sum values in concatenated fields
    By jdrubins in forum Reports
    Replies: 4
    Last Post: 09-01-2009, 07:20 AM
  5. Merging values in Access
    By jains in forum Forms
    Replies: 0
    Last Post: 06-08-2009, 09:31 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums