Field from Multiple tables

  • Thread starter Thread starter Charlotte Hinger
  • Start date Start date
C

Charlotte Hinger

I'm designing a research database, and want a topic form
with a field that will access titles from various tables
such as books, articles, interviews. How do I do this? I
know how to do it from a single table, but not from
various tables?

Also, what area would I need to know more about to have
figured out the answer by myself?

Thanks Charlotte
 
Thanks, sounds like you understand what I want. Do the
tables all have to be related? Where is the on-line help
you are referring to located. Do you mean on the main
microsoft site?

Charlotte
 
OK. Here's where I got to (Sequence) Objects, Queries,
New, New Query Dialog box, Design View, Close Show Table,
Query, SQL Specific, Union. Then a blank box appears, but
there is no place on the menu from which to draw SQL
server statements, nor can I access any fields. Now what?
Thanks
Charlotte
 
OK. Here's where I got to (Sequence) Objects, Queries,
New, New Query Dialog box, Design View, Close Show Table,
Query, SQL Specific, Union. Then a blank box appears, but
there is no place on the menu from which to draw SQL
server statements, nor can I access any fields. Now what?
Click to expand...

Well... you need to know some SQL. (It's not SQL/Server by the way -
SQL was a language standard before Microsoft ever heard of it, and
both Access and SQL/Server are products which use their own version of
that standard).

The simplest way would be to create a query in the query grid using
one of the tables that you want to include in your UNION query. (Since
I don't know your table structure I can't advise). Use the View menu
option or the query design tool in the toolbar to switch to SQL view;
you'll see something vaguely like

SELECT this, that, theother FROM mytable...

Use copy and paste to make multiple copies of this separated by the
keyword UNION (to exclude duplicate records) or, more likely for this
case, UNION ALL (to allow duplicate records - it'll run faster if
there are none to be excluded). You'll end up with something like

SELECT this, that, theother FROM mytable
UNION ALL
SELECT this, that, theother FROM anothertable
UNION ALL
SELECT this, that, theother FROM yetadifferenttable

The fieldnames don't need to be the same, but there must be the same
number of fields, and they must be of matching datatypes, in each
SELECT clause.
 
John, I keep getting a sytax error. Do these tables have
to be related in some way first? Am I supposed to try to
build the SQL statement in one column on the grid? What I
did was open up the column which was Books as the table,
then alter the SQL statement under the field title.
Perhaps I should have used the other column for other
tables?
Thanks.
 
John, I keep getting a sytax error.
Click to expand...

Perhaps you could post the SQL that you're using. Clearly something is
wrong, but I can't tell what you're doing wrong if you don't tell me
what you're doing!
Do these tables have to be related in some way first?
Click to expand...

No. The purpose of a UNION query is to string together similar tables
into one big "table" - so you are NOT joining them.
Am I supposed to try to
build the SQL statement in one column on the grid?
Click to expand...

No. You're using the grid as a tool to create a Query, to save
yourself the trouble of typing in the entire thing in SQL. Just create
a Query on the Books table containing the fields that you want to
search. The query will presumably (again... I CANNOT SEE YOUR
DATABASE, and my telepathy is on the blink today!) contain several
fields. You would then switch views from Query Grid view to SQL view,
because only in SQL view can you build the UNION query.

Since you now have a "template", the Books query, you can make as many
copies of the template as you need, with the UNION ALL operator
between them, by copying and pasting the template with appropriate
modifications.
 
OK Whew, Thanks. I'm going to print out all your
information and see if I can find someone who can look
over my shoulder (small town, it will involve a drive) and
point out to me where I'm going wrong based on what you
are telling me. I would happily email you my database
(which is small and experimental so I won't mess up my
real stuff) if attactments don't worry you and you can
stand the thought of spending more time on this. You've
been terrific, already! Thank you.Charlotte Hinger
 
Back
Top