By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,578 Members | 825 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,578 IT Pros & Developers. It's quick & easy.

Data from multiple tables for report - how to do it?

P: n/a
I can create queries and reports based on info from one table. How do
I create one using information from multiple tables. What do I need
to make sure the information from one table will be correctly assigned
to specific person from another table?

Thanks.
Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
What's the structure of the tables in question? You should be able to create a UNION query to append otgether the output from several tables and then build your report on that...

--
Message posted via http://www.accessmonster.com
Nov 13 '05 #2

P: n/a
On Tue, 14 Dec 2004 00:37:33 GMT, "David Seeto via AccessMonster.com"
<fo***@AccessMonster.com> wrote:
What's the structure of the tables in question? You should be able to create a UNION query to append otgether the output from several tables and then build your report on that...

First, what is a union query?? Second of all, I have no idea how to
write any code for Access. All I know about is relationships.

Databases consist of multiple student tables. Primary table is
tbl_student_info and consists of field for last name, first name, and
the key is their SS number.

All other tables are linked using the key/SSN. For instance,
tbl_AA/AS has a key identifier, with second table labeled StudentSSN
with a one-to-many relationship going from the key ID in the main
table to the StudentSSN in all other tables.

I want to pull info from any table with the StudentSSN relationship
into a query. For example, in a query to glean the status of the
students AA/AS, I need to
1) query tbl_student_info and tbl_AA/AS.
2) I want to generate a report showing the information in the query
combining information from both tables.

Is this even possible? I think if I can understand HOW to report info
from multiple sources, I will solve a major issue. I have never
written any code in my life for Access so I need to rely on queries
and reports to generate information. I have no problem editing and
moving around items to create the desired results. However, if this
is dependent on programming language, I am a very dead duck. I have
never taken a class in Access and reading it in a book makes no sense
in any of the books I have looked at. I am stupid, dumb, iggerint
about programming. If it MUST have programming, then I cannot do it.

Does this help at all?

Thanks!
Nov 13 '05 #3

P: n/a
Your description does not indicate you need a UNION query, just a Query
joining the tables you describe. You create it in the Access Query
Builder... add the Tables, join them by click-and-drag from the key field to
the foreign key field in the related Table, right click and set Join
Properties, then drag down the Fields you want to the grid. Use that Query
as the RecordSource of the Report. No code needed -- at least to accomplish
what you've described up to now.

I have a colleague who developed a simple and straightforward database
application (but with very extensive capability) that tracked food ordering
and delivery for a major metropolitan school district for over ten years --
not a line of code in the whole thing, except code automatically generated
by the control wizards. So, "programming" is not a necessity to do some
impressive work with Access.

A good book that usually makes sense even to the rankest of beginners is
"Microsoft Access <versionnumber> Step by Step" from Microsoft Press. You
really do need to get past the "I am unable to understand it" phase; I am
certain you can grasp it.

Larry Linson
Microsoft Access MVP

<Sami> wrote in message news:ol********************************@4ax.com...
On Tue, 14 Dec 2004 00:37:33 GMT, "David Seeto via AccessMonster.com"
<fo***@AccessMonster.com> wrote:
What's the structure of the tables in question? You should be able to
create a UNION query to append otgether the output from several tables and
then build your report on that... First, what is a union query?? Second of all, I have no idea how to
write any code for Access. All I know about is relationships.

Databases consist of multiple student tables. Primary table is
tbl_student_info and consists of field for last name, first name, and
the key is their SS number.

All other tables are linked using the key/SSN. For instance,
tbl_AA/AS has a key identifier, with second table labeled StudentSSN
with a one-to-many relationship going from the key ID in the main
table to the StudentSSN in all other tables.

I want to pull info from any table with the StudentSSN relationship
into a query. For example, in a query to glean the status of the
students AA/AS, I need to
1) query tbl_student_info and tbl_AA/AS.
2) I want to generate a report showing the information in the query
combining information from both tables.

Is this even possible? I think if I can understand HOW to report info
from multiple sources, I will solve a major issue. I have never
written any code in my life for Access so I need to rely on queries
and reports to generate information. I have no problem editing and
moving around items to create the desired results. However, if this
is dependent on programming language, I am a very dead duck. I have
never taken a class in Access and reading it in a book makes no sense
in any of the books I have looked at. I am stupid, dumb, iggerint
about programming. If it MUST have programming, then I cannot do it.

Does this help at all?

Thanks!

Nov 13 '05 #4

P: n/a
The question I have for you is where is the Access Query Builder. I
am familiar with Design View, Wizard, and New. Is this what you mean?
And which one? I have tried to create a query in the Wizard, using
info from two tables, but I always get a message that essentially
tells me this is not possible. Thanks!

On Tue, 14 Dec 2004 05:30:24 GMT, "Larry Linson"
<bo*****@localhost.not> wrote:
Your description does not indicate you need a UNION query, just a Query
joining the tables you describe. You create it in the Access Query
Builder... add the Tables, join them by click-and-drag from the key field to
the foreign key field in the related Table, right click and set Join
Properties, then drag down the Fields you want to the grid. Use that Query
as the RecordSource of the Report. No code needed -- at least to accomplish
what you've described up to now.

I have a colleague who developed a simple and straightforward database
application (but with very extensive capability) that tracked food ordering
and delivery for a major metropolitan school district for over ten years --
not a line of code in the whole thing, except code automatically generated
by the control wizards. So, "programming" is not a necessity to do some
impressive work with Access.

A good book that usually makes sense even to the rankest of beginners is
"Microsoft Access <versionnumber> Step by Step" from Microsoft Press. You
really do need to get past the "I am unable to understand it" phase; I am
certain you can grasp it.

Larry Linson
Microsoft Access MVP

<Sami> wrote in message news:ol********************************@4ax.com...
On Tue, 14 Dec 2004 00:37:33 GMT, "David Seeto via AccessMonster.com"
<fo***@AccessMonster.com> wrote:
>What's the structure of the tables in question? You should be able to

create a UNION query to append otgether the output from several tables and
then build your report on that...
First, what is a union query?? Second of all, I have no idea how to
write any code for Access. All I know about is relationships.

Databases consist of multiple student tables. Primary table is
tbl_student_info and consists of field for last name, first name, and
the key is their SS number.

All other tables are linked using the key/SSN. For instance,
tbl_AA/AS has a key identifier, with second table labeled StudentSSN
with a one-to-many relationship going from the key ID in the main
table to the StudentSSN in all other tables.

I want to pull info from any table with the StudentSSN relationship
into a query. For example, in a query to glean the status of the
students AA/AS, I need to
1) query tbl_student_info and tbl_AA/AS.
2) I want to generate a report showing the information in the query
combining information from both tables.

Is this even possible? I think if I can understand HOW to report info
from multiple sources, I will solve a major issue. I have never
written any code in my life for Access so I need to rely on queries
and reports to generate information. I have no problem editing and
moving around items to create the desired results. However, if this
is dependent on programming language, I am a very dead duck. I have
never taken a class in Access and reading it in a book makes no sense
in any of the books I have looked at. I am stupid, dumb, iggerint
about programming. If it MUST have programming, then I cannot do it.

Does this help at all?

Thanks!


Nov 13 '05 #5

P: n/a
Your database window should have a series of object types on the left hand side - Tables, Queries, Forms, Reports, etc. The buttons across the top - Open, Design, New - relate to the objects chosen on the left.

So if you click on "Queries" and then click on "New", it should present the New Query window with the options of how you can go about creating a new query - Design View, Simple Query Wizard, Crosstab Query Wizard, etc (I'm using Access 2000). Personally, I find the Query Wizard more confusing than just entering the Design View, and Larry's description relates to that.

Anyway, choosing Design View, Access then asks you what tables you want to include in your Query: click on the table names and then on the Add button, and the tables - with their fields - will appear in the top half of Query Design screen (which is what I think Larry is referring to when he says "Access Query Builder"). Click on Close - if you want to add another table later, you can always click on the "Show Table" button on the toolbar later on.

So you should now have two tables in the top section and nothing in the bottom section. The bottom series of columns is for what you actually want to appear as output for this query - you can populate it manually, or you can just double click on the fields you want from the tables in the top section.

The key part of this is how you join the two tables together. If you have already defined a relationship between these two tables in the Access database, then they might appear automatically - otherwise, you are going to have to define one yourself clicking on and dragging the field in one table over to the matching field in the other table. In your case, you should drag the key field and then the SSN field from one table to another.

Let us know if that makes sense... once you get that far, you should be able to work out a lot of the rest by yourself...

--
Message posted via http://www.accessmonster.com
Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.