473,288 Members | 2,350 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,288 software developers and data experts.

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

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
5 38596
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: Rigga | last post by:
Hi, I am new to mysql and need help on how to join tables. I have a database which contains 4 tables, the main table contains information by date order and the other 3 contain data also in date...
2
by: IanCraft | last post by:
I am new to Access 2002 and have a questions regarding using a form for data entry. I have 15 items I need to keep track of that are listed individually on a form as follows: Item#1Name ...
2
by: Sami | last post by:
Could someone explain clearly how to go about doing this? I have tried setting up the structure on numerous occasions, but it never seems to work. Could someone please help me out? Thanks!
8
by: Jason L James | last post by:
Hi all, does anyone know if I can create a dataview from multiple datatables. My dataset is constructed from four separate tables and then the relationships are added that link the tables...
8
by: beretta819 | last post by:
Ok, so I apologize in advance for the wordiness of what follows... (I am not looking for someone to make this for me, but to point me in the right direction for the steps I need to take.) I was...
4
by: dreaken667 | last post by:
I have a MySQL database containing 16 tables of data. Each table has a different number of columns and there are few common field names accross tables. I do have one master table with which I connect...
6
by: Laure | last post by:
Hi all, I'm a C++ programmer trying to help out a friend with a VBA app someone else wrote for him years ago. Don't know a lot about VBA. There is a report in this app that has a particular...
2
by: samvb | last post by:
Hello All, I have a database with multiple tables. I need to be able to generate a report based on demand. That is, user to select which info needs to include in the report? SAM
4
by: slenish | last post by:
Hello all, Im having a problem getting a report to work with multiple tables. First let me give a little detail in to how im doing this. I am using a form to pull the report. I have a report...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.