Results 1 to 9 of 9
  1. #1
    aftfm is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2014
    Posts
    22

    query from two tables to one field

    How can query from two tables to one field



    For example, I have two tables the same as their domain names

    I want sole between the two tables

    Fields / name, number, breed

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    oneFld: Tbl.name & "/" & tbl.number & tbl.breed

  3. #3
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    Ranman,

    Why would you want to have that much information in one field in a single table? Everything I have been reading on normalization says not to do that. I am just wanting to know for better clarification.

    Thank you for your time.
    Walker

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @NightWalker,

    The syntax ranman256 used is for a column in a query, not an actual table.

    Lets say you have two fields in a table - "LName" and "FName" and you want to use their name in a report. You would concatenate the first name and last name in the query , not add a new field in the table.

    So in design view of a query you would add this to a column
    Code:
    FullName: FName & " " & LName
    In SQL view it would look like
    Code:
    SELECT SomeField, AnotherField, FName & " " & LName as FullName

    In ranman256's answer, he used the query design view syntax. However, there is a problem because the OP has a field name of "Name" and a field name of "Number", both of which are reserved words that shouldn't be used as object names.

    What will be returned is the name of the table (Tbl.name). Don't know what the name refers to: Owner name, dog name, event name,....????
    And probably I would add a space between "Number" and breed:
    Code:
    oneFld: tablename.[name] & "/" & table2name.[number] & " - " & table2name.breed
    The brackets are needed to tell Access that they are field names, not keywords.
    Replace "tablename" and "table2name" with the proper table names.

  5. #5
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    Steve,

    can you look at this thread https://www.accessforums.net/showthread.php?t=59909

    I am having trouble getting data back out in my qry

  6. #6
    aftfm is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2014
    Posts
    22
    I want this and this pregnancy under certain
    Not with some


    Click image for larger version. 

Name:	Exam.png 
Views:	16 
Size:	24.1 KB 
ID:	24710

  7. #7
    aftfm is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2014
    Posts
    22
    I thank everybody

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Is this solved???

  9. #9
    aftfm is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2014
    Posts
    22
    Are you required is not possible Emperical

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

Similar Threads

  1. Replies: 6
    Last Post: 02-11-2014, 05:20 AM
  2. Update One Field from Query on Two Tables
    By user9 in forum Queries
    Replies: 1
    Last Post: 01-13-2014, 08:55 AM
  3. Replies: 7
    Last Post: 03-08-2013, 03:58 PM
  4. Replies: 3
    Last Post: 07-31-2012, 06:05 AM
  5. Split Query Field into Sub Queries/Tables
    By maggioant in forum Queries
    Replies: 0
    Last Post: 10-15-2009, 05:23 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