Query Help

[Kerensky18]

Hello;

I'm designing a database to track radio's.. The problem is I want to show
last names from a related table. But when I generate the report or the query
I get all the lastnames that I have stored in my table..

Here is the query I'm using

SELECT [Radio/staff].[Date-Out], [Radio/staff].[Date-In],
[Radio/staff].[Radio Number], [Radio/staff].[Staff Number], Staff.[Last Name]
FROM Staff LEFT JOIN [Radio/staff] ON Staff.[Badge Number] =
[Radio/staff].[Staff Number]
GROUP BY [Radio/staff].[Date-Out], [Radio/staff].[Date-In],
[Radio/staff].[Radio Number], [Radio/staff].[Staff Number], Staff.[Last Name]
ORDER BY [Radio/staff].[Date-Out] DESC;


When I do this I get a report that list the proper radios signed out to the
right people but following that I get alot of blank entries that just list
all of the last names in the staff table.

Any help would be appreciated on this.

Thankyou.

 

[John Spencer]

Try changing the JOIN from a LEFT JOIN to an INNER JOIN. Right now the
query is saying give me ALL records from the Staff table and if there are
any records in Radio/Staff table then show that data along with the
information from the Staff table. This will give you all the records from
the two tables where there is a matching record in both tables

If what you want is ALL records from Radio/Staff (whether they have an
associated Staff record or not) then change the join to a RIGHT JOIN. Which
should give you all the records in Radio/Staff table and if there is an
associated Staff record, the record returned will include the staff name.

Also, since you are not doing any aggregation, there is not really a good
reason to GROUP all these records. Using the DISTINCT keyword in the SELECT
clause will return the same results.

SELECT DISTINCT [Radio/staff].[Date-Out]
, [Radio/staff].[Date-In]
, [Radio/staff].[Radio Number]
, [Radio/staff].[Staff Number]
, Staff.[Last Name]
FROM Staff INNER JOIN [Radio/staff]
ON Staff.[Badge Number] = [Radio/staff].[Staff Number]
ORDER BY [Radio/staff].[Date-Out] DESC;
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Kerensky18]

Thank you for your help I was unaware of the different types of joins.

Try changing the JOIN from a LEFT JOIN to an INNER JOIN. Right now the
query is saying give me ALL records from the Staff table and if there are
any records in Radio/Staff table then show that data along with the
information from the Staff table. This will give you all the records from
the two tables where there is a matching record in both tables

If what you want is ALL records from Radio/Staff (whether they have an
associated Staff record or not) then change the join to a RIGHT JOIN. Which
should give you all the records in Radio/Staff table and if there is an
associated Staff record, the record returned will include the staff name.

Also, since you are not doing any aggregation, there is not really a good
reason to GROUP all these records. Using the DISTINCT keyword in the SELECT
clause will return the same results.

SELECT DISTINCT [Radio/staff].[Date-Out]
, [Radio/staff].[Date-In]
, [Radio/staff].[Radio Number]
, [Radio/staff].[Staff Number]
, Staff.[Last Name]
FROM Staff INNER JOIN [Radio/staff]
ON Staff.[Badge Number] = [Radio/staff].[Staff Number]
ORDER BY [Radio/staff].[Date-Out] DESC;
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hello;

I'm designing a database to track radio's.. The problem is I want to show
last names from a related table. But when I generate the report or the
query
I get all the lastnames that I have stored in my table..

Here is the query I'm using

SELECT [Radio/staff].[Date-Out], [Radio/staff].[Date-In],
[Radio/staff].[Radio Number], [Radio/staff].[Staff Number], Staff.[Last
Name]
FROM Staff LEFT JOIN [Radio/staff] ON Staff.[Badge Number] =
[Radio/staff].[Staff Number]
GROUP BY [Radio/staff].[Date-Out], [Radio/staff].[Date-In],
[Radio/staff].[Radio Number], [Radio/staff].[Staff Number], Staff.[Last
Name]
ORDER BY [Radio/staff].[Date-Out] DESC;


When I do this I get a report that list the proper radios signed out to
the
right people but following that I get alot of blank entries that just list
all of the last names in the staff table.

Any help would be appreciated on this.

Thankyou.