Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2007
    Posts
    16

    Unanswered: Concatenating Multiple Fields

    OK -

    I got the concatenate function for Excel from Mike The Bike's earlier post (http://www.dbforums.com/showthread.php?t=1624671) ... simple cut and paste, study the formula, and save it for future studying. Now I want to do the same thing in Access:

    The attached file is only a sample of my DB which has 60k records in it. You will notice that the "Address" values are broken up into 4 spearate fields:

    Street number
    Street Name
    Street Direction (North, South, etc.) --- some are blank
    "Type" of street (Ave, Street, etc.) --- some of these are blank as well

    There are a total of 17 tables in the DB. All have identical field names and (hopefully) all have unique records. There are 4 different fields that I needed to concatenate in order to get the street address to be in one field (felt pretty good figuring it out ). I did the following:

    Expr 1: [Situs House#] & " " & [Situs Street] & " " & [Situs Street Direction] & " " & [Situs Street Suffix]

    So, that worked. Now I have to join them all together into one table. It;s been so long that the "relationship" aspect of a relational DB is pretty fuzzy.

    How do I join all the tables together in order to have them all together.

    Or, do I not need to join them together --- can't I just use the data as it is in the DB and use queries to get the information I need?

    What I am looking to do with it is to parse the information so that I can:

    • See what records have been used in the past (last column)
    • Sort the records by certain "date built" ranges
    • Further sort by square footage
    Attached Files Attached Files
    Last edited by D-Dub; 11-22-07 at 18:24.
    I'm Old, I'm Grumpy, and I have no clue what I'm doing

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    sorts are easily done.. thats why the data is stored in SQL. you can either set a sort by using a query, setting the sort programaticaly in a form
    ...when you select a specified button set the sort order to what ever column

    eg
    me.orderby="mycolumnname"
    me.orderbyon = true

    perhaps even easier select a control on your form, and then select the A-Z or Z-A button

    you can apply filters selct the column you want to filter on, and then button that looks like as funnel with a flash
    so say you wanted to show records with a situs street of TUOLUMNE you'd find a record with TUOLUMNE in its situs street, then select the situs stret value and press the filter button... to remove a filter press the empty funnel button.

    to select data form different tables requires a clear unambiguous method of associating data in table 1 with table 2. usually that is doen on whats called a primary and foreign key
    a primary key is something that makes that record unique in that table, a foreign key is where that value is used in another table and is said to refer to the value in the other table.

    an example

    say you had a table identifying types of property
    eg
    1 flat
    3 apartment
    10 semi
    99 detached
    100 grandiose pile (suitable for drug dealers and captains of industry)

    and you had a table of properties
    if you included a value from the property type of table in your property table then you could identify and associate similar types of property. so if a customer wanted to look at say 5 bed detached houses, you could request the database only return properties of type detached.

    if you are just starting out on the db design trail can I suggest you have alook at this

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by healdem
    thats why the data is stored in SQL
    Huh ?
    George
    Home | Blog

  4. #4
    Join Date
    Sep 2006
    Posts
    9

    Use union query

    Since all of the tables have the same fields, you could use a union query to join all of the tables together and then use a filter in your report or form to select records from any of the tables that meet one or more criteria that you would specify in your form (or form used to call the report).

    For instance, if you had 1 table for condos, another for apartments, another for detached houses, you could easily produce a report of any unit that say had more than 4 bedrooms AND more than x square feet.

  5. #5
    Join Date
    Nov 2007
    Posts
    16
    Well, I spent the better part of the day reading and playing with it. I took the LONG way around, but I got it done.

    First I created a blank table with the structure only and appended the table with a "group" of the other tables. Then I did it two more times, making three "managable" tables, rather than trying to work with all the different ones.

    Used the trim and concatenate functions to make the data correct, and then started filtering the data to achieve the results I wanted. It was time consuming but I enjoyed playing with it. I'm sure it could have been done in a matter of minutes but, what the heck... I had fun.

    How does the union query work? What would the expresion look like?
    I'm Old, I'm Grumpy, and I have no clue what I'm doing

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by georgev
    Huh ?

    ...okay itsafaircopyerhonner. I though it easier to suggest that but some picky little !!!!!!! is always there to pull you up.

    what I really meant to say is that whats the data is always accessed using SQL

    i will go and stand by the naughty server for 100 hours repeating to myself I must not confuse posters by spouting gibberish

  7. #7
    Join Date
    Sep 2006
    Posts
    9

    Concatenate - Union Query

    Basically, a union query is used to access similar data from multiple tables so that a form or report can use the data from multiple tables as if the data was from 1 table. You write a separate query to return a result set from each table - from 5 tables requires 5 queries. Then you use the union query to combine the separate result sets into a single result set. Each of the 5 queries woud have to have the same number of fields with the same field names and the union query requires that you do it in SQL and would look something like the following:

    Select street, unit, fulladdress, oname, Lphone, Ophone, Email, alpha from qPhListO1

    Union Select street, unit, fulladdress, oname, Lphone, Ophone, Email, alpha from qPhListO2

    UNION Select street, unit, fulladdress, oname, Lphone, Ophone, Email, alpha from qPhListO3

    Union Select street, unit, fulladdress, oname, Lphone, Ophone, Email, alpha from qPhListR1

    Union Select street, unit, fulladdress, oname, Lphone, Ophone, Email, alpha from qPhListR2

    Union Select street, unit, fulladdress, oname, Lphone, Ophone, Email, alpha from qPhListR3

    UNION Select street, unit, fulladdress, oname, Lphone, Ophone, Email, alpha from qPhListC1
    ORDER BY street, unit;

    You could also use union queries to break apart 1 record into multiple records for purposes of generating a report or form. In the above case, there were up to 3 owner names in each record in the owner table and up to 3 renter names in each record in the renter table and I wanted to produce a phone book report in which each owner/renter name would appear separately.

  8. #8
    Join Date
    Nov 2007
    Posts
    16
    OK - So I'm just a little confused (Ignorance is not bliss. I understand some of it, but some is a bit fuzzy:

    First, you said that the UQ (Union Query) pulls the data to be used in a form or report... can I do a "Make Table" from the query as well?

    Second, I am assuming the SQL statement above all one UQ, and not multiple Union Queries (???):

    I noticed that the first part of the statement begins with "select", whereas the balance of it begins with "Union Select" ---- would like to understand the progression as well as how the results are achieved... I want to understand why the beginning starts with select, the following statements start with Union Select, and what trasnpires when the query is run.

    Did you first do independant queries on each of the tables, and then do a UQ based on those queries?
    I'm Old, I'm Grumpy, and I have no clue what I'm doing

  9. #9
    Join Date
    Sep 2006
    Posts
    9
    The syntax is correct. The first Select returns a result set as usual and the subsequent UNION SELECT statements add to the first result set. Each of the Select or Union Select statements in the example references separate queries or tables.

    Yes, you could use it to make a new table. Otherwise, if there was a reason to keep the data in separate tables, you could use it to 'work' as all of the data was in one table.

Posting Permissions

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