report that includes multi tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need help. I have been using a single table in queries, filters, and
reports. No problem. I now want to join several tables to generate one
alphabetized list. I tried joining just two tables in the query to see if
I'm doing this right and the output is only giving me data from one table not
both. I can't figure out what I'm doing wrong. The diagram shows joined in
the desin view.

This is my ultimate desire: I have 6 tables by year 1999-2005. The file #
is auto generated. The tables include client info (name, address, phone,
account type, county code #, and misc) The imorportant key is the county
code #. I want to produce a final report that is alphabetized by client last
name and includes: file #, name, acct. type, and county code #.

Can anyone help me please. Thanks
 
Did you add the fields from the joined table to the lower half of your
query?

Just adding the table is only half of it. You have to then select the
fields from that second table that you want.
 
For starters you really shouldn't have a separate table for each year. Your
application would be simpler if everything were in one table and you used
criteria to select what you wanted.

In any event, when you want to merge multiple lists, you need to UNION them
which is a different operation from JOIN. Union basically tacks one list on
to the end of another. It's like adding the Stratford white pages to the end
of the Bridgeport white pages. You couldn't join the two lists because
different people live in each town.
 
Thanks for replying.

There is separate tables for the years because the autonumber is used to
create a new file #. The column before the new file # is the year ie 05 for
2005. When the report is printed it prints 05 - *file #*. In order to use
the auto number and start back at 1 when the next year started a new table
was created. I want to combine the tables in the query so I can sort in my
report by last name and not have to go through each report by year, which is
what they have been doing here for several years. I know there is an easier
way using what they already have.

The next question I have is how to do a UNION instead of a JOIN. When I was
reading through the instructions and asking questions the UNION hadn't come
up so if you could please instruct me I would greatly appreciate it.
 
No I did not read in the instructions on how to do that. I added more
information to Pat Hartman's reply. So if you can advise me on how to do
what you are suggesting I am willing to learn and see which works the easiest.

Thanks
 
Did not read your structure. I assumed you were talking about normalized
relational tables (cutomertable, ransactiontable, partinfortable, etc.)

Becuase you are not using a normalized design, you will not be able to do
your taks as I mentioned. You will have to use Union queries and such.

I'd suggest puttin all your records in one table and including a field for
the year or date.
 
Making a key have meaning is what is causing your problem. The year
something happened is an attribute in its own right. Having a number series
restart each year is the other part of the problem. You would be better off
just letting the autonumber increment and keeping the year as a separate
column.

Union queries are described in help if you care to look. The basic rules
are that the queries need exactly the same number of columns, they must be in
the same order, and they must be the same data type. Because of these
restrictions, it is generally easier to union queries than tables since with
queries, you can reorder the columns easily. The columns do not have to have
the same names. The basic syntax is:
Select * from tblA
Union Select * from tblB
Union Select * from tblC;

The Union query is not supported by the QBE so you won't have any help with
building it. You need to build it in SQL view. In order to save myself
typing, I create the select queries with QBE and then just paste their SQL
into the union query or in some cases, just select from the saved query
rather than a table.
 
Back
Top