A pretty easy one for the experts

[Dave]

I know this looks long, but it's not and I'm sure this is
an easy question for some one that knows how.

I'm trying to do a query incorporating three different
tables. Essentially they are all linked by the same
Project Name. One table has details about the proejct,
one table has status updates about the project and one
table has tests done for the project.

I was able to successfully create a query that would
allow me only to display the most recent status message
for that particular project by creating this query:
SELECT [Project Name], Max([Update Status Date]) AS
MaxDaate
FROM [Project Status]
GROUP BY [Project Name];
and then run another query whereby a joined the program
name with the program ID, which gave me the most recent
status update assocatiated with that particular project.

However, now I want to add the test information for each
project.

The problem is unless the project has a test associated
with it, it won't display any info.

Is ther a way to tell it that if has no information in
the test information to still display the project and
status information?

Thanks,
Dave

 

[Kevin Sprinkel]

By default, the query does an INNER JOIN which requires
records to be in both tables. To always return records
from one table, plus values from the second table if they
exist, use a LEFT JOIN.

In your second select statement,

SELECT <fieldlist> FROM tabletoreturnalways LEFT JOIN
tabletoreturnifanyrecordsexist ...

HTH
Kevin Sprinkel

 

[Tom Ellison]

Dear Dave:

There's a facility that does this, called a LEFT (or RIGHT) JOIN.

If you're used to working in the design grid, you won't see it this
way. You can still do it, though. There will be a line connecting to
the Status table describing how the tables are related. Right click
on this line. There are 3 buttons there, along with a description of
how the JOIN works. Read carefully - it should be self explanitory.

You may want to do this for the details table also, in case there is a
status but no details, or even a project with no status and no
details.

If you want to see any of the columns from the status table other than
the project (the common column to all tables) and the status date, you
will need some additional logic to get his working.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Bill]

Sounds like you need to use an outer join.

This will give you all records in Project even if no records exist in test
for the project.
Add whatever columns you need to the select statement.
SELECT Project.[Project Name], Tests.[Test Name]
FROM Project LEFT OUTER JOIN Tests
ON Project.ProjectID = Tests.TestID;

Hope that helps.

Bill

 

[David]

I just wanted to thank you all for posting. The solution
worked amazingly. My boss is so happy with my finished
product and I would have never figured it out otherwise.

This message board is such a great help and service.

Thanks again,
David

-----Original Message-----
Dear Dave:

There's a facility that does this, called a LEFT (or RIGHT) JOIN.

If you're used to working in the design grid, you won't see it this
way. You can still do it, though. There will be a line connecting to
the Status table describing how the tables are related. Right click
on this line. There are 3 buttons there, along with a description of
how the JOIN works. Read carefully - it should be self explanitory.

You may want to do this for the details table also, in case there is a
status but no details, or even a project with no status and no
details.

If you want to see any of the columns from the status table other than
the project (the common column to all tables) and the status date, you
will need some additional logic to get his working.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

I know this looks long, but it's not and I'm sure this is
an easy question for some one that knows how.

I'm trying to do a query incorporating three different
tables. Essentially they are all linked by the same
Project Name. One table has details about the proejct,
one table has status updates about the project and one
table has tests done for the project.

I was able to successfully create a query that would
allow me only to display the most recent status message
for that particular project by creating this query:
SELECT [Project Name], Max([Update Status Date]) AS
MaxDaate
FROM [Project Status]
GROUP BY [Project Name];
and then run another query whereby a joined the program
name with the program ID, which gave me the most recent
status update assocatiated with that particular project.

However, now I want to add the test information for each
project.

The problem is unless the project has a test associated
with it, it won't display any info.

Is ther a way to tell it that if has no information in
the test information to still display the project and
status information?

Thanks,
Dave

.