Querying 2 Tables

[Emma]

I have two tables related to each other through a 1 to many relationship. How
do I write a query for a report. The first table (one) contains the client
information and has a unique ID called ID. The second table (many) holds
destinations and has a unique ID called ID and a Client ID which is the first
tables ID. I want to show that the client is going to one destination. I have
3 parameters Client First Name, Client Last Name and Destination Name.
However when I run the query nothing shows up can someone explain to me how
to get the Client Information and Destination to both show up so I can make a
report?

 

[Dirk Goldgar]

I have two tables related to each other through a 1 to many relationship.
How
do I write a query for a report. The first table (one) contains the client
information and has a unique ID called ID. The second table (many) holds
destinations and has a unique ID called ID and a Client ID which is the
first
tables ID. I want to show that the client is going to one destination. I
have
3 parameters Client First Name, Client Last Name and Destination Name.
However when I run the query nothing shows up can someone explain to me
how
to get the Client Information and Destination to both show up so I can
make a
report?

Using the query designer, add both tables to the table pane. If the
designer automatically draws a join line between the ID field in the Clients
table and the ID field in the Destinations table, click that line and delete
it -- it was a bad guess. Then drag the ID field from the Clients table and
drop it on the [Client ID] field in the Destinations table. That should
creat a join line between those two tables, linking the Clients.ID with
Destinations.[Client ID].

Now drag the necessary fields from each table to the query's field grid.
You'll want the name fields from the Clients table and the ID, [Client ID],
and [Destination Name] fields from the Destinations table. If you open this
query in datasheet view, you should see all the destinations to which
clients are going, and the info about the clients who are going to each.

I'm not sure what you have in mind for applying the parameters. It seems to
me you would want to either specify a client name and see all the
destinations that client is going to, or specify a destination name and see
all the clients going to that destination. In either case, you might want
to select a specific client or destination, or else type in a partial name
and get all the matching records. But before you even get into that, you
need to make sure your query, as constructed above, returns all the data you
want when it is unfiltered.

 

[Emma]

I did use an outer and inner query and got rid of the problem of being asked
the first and last names twice! Cheers thanks for the advice!

I have two tables related to each other through a 1 to many relationship.
How
do I write a query for a report. The first table (one) contains the client
information and has a unique ID called ID. The second table (many) holds
destinations and has a unique ID called ID and a Client ID which is the
first
tables ID. I want to show that the client is going to one destination. I
have
3 parameters Client First Name, Client Last Name and Destination Name.
However when I run the query nothing shows up can someone explain to me
how
to get the Client Information and Destination to both show up so I can
make a
report?

Using the query designer, add both tables to the table pane. If the
designer automatically draws a join line between the ID field in the Clients
table and the ID field in the Destinations table, click that line and delete
it -- it was a bad guess. Then drag the ID field from the Clients table and
drop it on the [Client ID] field in the Destinations table. That should
creat a join line between those two tables, linking the Clients.ID with
Destinations.[Client ID].

Now drag the necessary fields from each table to the query's field grid.
You'll want the name fields from the Clients table and the ID, [Client ID],
and [Destination Name] fields from the Destinations table. If you open this
query in datasheet view, you should see all the destinations to which
clients are going, and the info about the clients who are going to each.

I'm not sure what you have in mind for applying the parameters. It seems to
me you would want to either specify a client name and see all the
destinations that client is going to, or specify a destination name and see
all the clients going to that destination. In either case, you might want
to select a specific client or destination, or else type in a partial name
and get all the matching records. But before you even get into that, you
need to make sure your query, as constructed above, returns all the data you
want when it is unfiltered.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Emma]

Hi Dirk thanks for the advice, I've figured out how to narrow it down to one
client and one destination. I have first name and last names as parameters.
Then I have the following SQL In (SELECT ID FROM Destination WHERE Identifier
= [Guess]). It seems to be working great.

Just one little annoyance is that if I put this as a subreport (because
there's too many fields to have as a report) I'm being asked for the first
and last names in the main report then again in the sub report, is there
anyway to have the parameters only appear once? Because the outer report
askes for the client's first and last names as well? I guess what I'm asking
is should I make this query based on the outer query or just leave it alone?

I have two tables related to each other through a 1 to many relationship.
How
do I write a query for a report. The first table (one) contains the client
information and has a unique ID called ID. The second table (many) holds
destinations and has a unique ID called ID and a Client ID which is the
first
tables ID. I want to show that the client is going to one destination. I
have
3 parameters Client First Name, Client Last Name and Destination Name.
However when I run the query nothing shows up can someone explain to me
how
to get the Client Information and Destination to both show up so I can
make a
report?

Using the query designer, add both tables to the table pane. If the
designer automatically draws a join line between the ID field in the Clients
table and the ID field in the Destinations table, click that line and delete
it -- it was a bad guess. Then drag the ID field from the Clients table and
drop it on the [Client ID] field in the Destinations table. That should
creat a join line between those two tables, linking the Clients.ID with
Destinations.[Client ID].

Now drag the necessary fields from each table to the query's field grid.
You'll want the name fields from the Clients table and the ID, [Client ID],
and [Destination Name] fields from the Destinations table. If you open this
query in datasheet view, you should see all the destinations to which
clients are going, and the info about the clients who are going to each.

I'm not sure what you have in mind for applying the parameters. It seems to
me you would want to either specify a client name and see all the
destinations that client is going to, or specify a destination name and see
all the clients going to that destination. In either case, you might want
to select a specific client or destination, or else type in a partial name
and get all the matching records. But before you even get into that, you
need to make sure your query, as constructed above, returns all the data you
want when it is unfiltered.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Hi Dirk thanks for the advice, I've figured out how to narrow it down to
one
client and one destination. I have first name and last names as
parameters.
Then I have the following SQL In (SELECT ID FROM Destination WHERE
Identifier
= [Guess]). It seems to be working great.

Just one little annoyance is that if I put this as a subreport (because
there's too many fields to have as a report) I'm being asked for the first
and last names in the main report then again in the sub report, is there
anyway to have the parameters only appear once? Because the outer report
askes for the client's first and last names as well? I guess what I'm
asking
is should I make this query based on the outer query or just leave it
alone?

It's hard to answer, because I'm not at all sure how you have this set up.
What do you mean by "there's too many fields to have as a report"? What
exactly do you want to have on your report, and how do you want it to be
organized? If there are only the two tables involved, I'd be surprised if
you need to use a subreport. Are you familiar with the Sorting & Grouping
feature of Access reports?