Multiple Tables on one report

[Guest]

I need to have a report pull data from two tables at the same time. I'm
trying to figure this out but I need some help walking through the process.
Here's how I have things currently laid out.

Table1 is where the customer returns are kept. Users can add/modify records
from Form1. On Form1 there is a command button that opens form2 where users
can create a corrective action (Table2) that is tied to the record that is
opened on Form1. I'm using the OpenArgs function to pass data from one form
to the other. What I need to do now is create a report that has some data
from Table1 and some data from Table2. How would I go about this? A union
query?

 

[Jeff Boyce]

First, what is "common" between the two fields? Unless you want to have
every record from Table1 in every possible combination with every record
from Table 2, you need to have a way to join related records.

Or are you saying the records that go into Table1 are in NO WAY related to
the records that go into Table2? (I don't think so, just checking).

What is the relationship between the tables? That is, can a single record
in Table1 have ONLY one corresponding record in Table2, potentially MANY
"child" records in Table2, or may (but not must) have any "child" records in
Table2?

For the purposes of creating a report, the number and type of Forms is
irrelevant. Tables store data, forms display data (and allow for data
entry/edit), and reports "print out" data.

Cover the above details before proceeding to generating a query...

(by the way, a UNION query might be appropriate if you had "similar" records
in more than one table, but it sounds like you have "related" records)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

I have 1 controls that I pass data from Form1 to Form2 using the OpenArgs
function. That field is what links the two records together. That is how I
know which records are tied together. The data that is passed from Form1 to
Form2 is stored in Table2. There can only be one corresponding record in
Table2. When the open Form2 via Form1 I have it filtered to open the specific
record that was created based on Form1. If the users open Form2 via Form1 and
there isn't a corresponding record then they will show a blank recordon
Form2. I don't have any relationships set up between the two tables. I don't
think I need them either. Hope this makes things clearer for you.

SS

 

[Jeff Boyce]

It may only be a matter of semantics...

Forms don't have data, tables do.

So you're saying that one record in Table1 (?CustomerReturns) can have only
one record in Table2 (?CorrectiveAction).

If there is always one and only one record in Table2 for each record in
Table1, why do you have Table2?

It seems like it would be enough to have your Table1, and add one additional
field for "Corrective Action Taken". The source for this new field could be
based on a small lookup table of CorrectiveActions.

This way, if you decide to add new corrective actions, you can just add them
to the lookup table and have them "appear" in the combobox you'd use on your
form.

This approach would save you the work of passing arguments and navigating
between two forms.

Or am I still confused? (I believe I understand WHAT you are doing, my
comments are suggesting that you might not need to do those things to
accomplish your "WHY".)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Yes that is correct that only one record from Table1 can have only one record
in Table2. The reason for the 2 tables is because Table2 can also have
records created that are not tied to any records in Table1. Users can create
an internal corrective action without having a customer return attached to
it. And plus there are many other fields in Table2 but I only need 1 control
to tie the two tables together. So there are 2 ways to create a corrective
action, one is from the table1(customer returns) and the other is just from
an internal issue. There are two ways to access Form2 to do this. One is from
my main menu and the other is from a command button on Form1. I hope this
explains in better detail the reasoning behind the 2 tables.

 

[Jeff Boyce]

Back to the top...

You asked about creating a report that pulls data from two tables.

Create a query and add both tables.

Join the two tables on their common field.

Select the fields you'll want in your report from either/both tables.

Save the query.

Create a report, and base it on the query.

Regards

Jeff Boyce
Microsoft Office/Access MVP