Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2011
    Posts
    5

    Unanswered: UNION query with a lookup field from 4 tables

    Hello,

    I am building an Access DB for work on my own and I haven't had any classes on MS ACCESS 2007 or SQL. I have managed to learn a lot on YouTube, but this specific situation I have not found yet. Please help, and thank you.

    The tables are A.TABLE, B.TABLE, C.TABLE, and L.TABLE. Tables A, B and C have a "location" field that looks up information from the Location table named L.TABLE. A.TABLE, B.TABLE and C.TABLE each have Admin, Serial, and Location (that will give a dropdown list of possible locations) fields. The fields I want are in the result are Admin, Serial, and Location (that will give a dropdown list of possible locations).

    To help clarify, I have three different types of equipment, one type per table in tables A, B, and C. The location field in these three tables draws from the L table which only has an ID field and location field. Possible locations where the equipment is currently at is the only info in this table.

    My questions are the following:

    1) How do I create a UNION query that pulls information from A.TABLE, B.TABLE, C.TABLE, with a "location" field having the dropdown list of possible locations from the L.TABLE?

    2) How do I UNION from tables with differing amounts of fields, for example, Table A has 5 fields, Table B has 5 fields, and Table C has 10 fields? But I need all the fields from all the tables? I keep getting an error saying the amount of fields doesn’t match.
    Any help on this is very much appreciated. I am using MS ACCESS 2007, and I have no training in SQL either, so please be gentle. 
    Thanks in advance.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by mulinixa View Post
    To help clarify, I have three different types of equipment, one type per table in tables A, B, and C.
    There should be only one table, possibly with a column (field) identifying the type of equipment. With 3 tables for storing data of the same kind, your database is not normalized and this will cause problems, sooner or later. See: Database Design :: Normalization Basics - Techniques

    Quote Originally Posted by mulinixa View Post
    1) How do I create a UNION query that pulls information from A.TABLE, B.TABLE, C.TABLE, with a "location" field having the dropdown list of possible locations from the L.TABLE?
    A query does not have a "dropdown list". Queries are short pieces of code written in a language called SQL (Structured Query Language) designed for managing data in relational database management systems (RDBMS). What you an do is to create Relationships and joins that will bind the contents of two or more tables, based on a common value in a specific column in each table. See: Creating Database Relationships in Access

    By the way, you should never name any object of a database with a name containing a dot (.) which has a special meaning in the SQL language. In general you should never use special characters, nor reserved words (there are many), for the name of an object (table, column, query, etc...) in an Access database. This too will cause problems and errors.

    Also, and for various reasons that would be too long to explain here (most of them concern security and data integrity), you should never allow a user to have access to the data directly, or if you prefer, to open a table or a query. Forms and Reports should be systematically used. See for instance: Data Input Via Forms

    Quote Originally Posted by mulinixa View Post
    2) How do I UNION from tables with differing amounts of fields, for example, Table A has 5 fields, Table B has 5 fields, and Table C has 10 fields?
    You can't. By definition: Each SQL statement within the UNION query must have the same number of fields in the result sets with similar data types. See for instance: Union queries and UNION (Transact-SQL)
    Have a nice day!

Tags for this Thread

Posting Permissions

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