Need help with query results

D

Dena

I'm trying to filter records from a query. The query was created from 2
tables - both with the same userid field but one contains the first/last name
fields and the other contains the hotel code. I need all records in this
query to show if the user is associated to more than one Hotel Code.
Example...

UserID | FirstName | LastName |HotelCode
C0123 | Joe | Smith | ATLNP
C0123 | Joe | Smith | SBOJC
C0123 | Joe | Smith | SANMX
C0847 | Bill | Jones | LONKT
C0395 | Frank | Williams | MOWSS

Only Joe Smith's records should appear in the query results. What criteria
should be added? I'm struggling...
 
J

John Spencer

It all depends. Does the table with UserID and HotelCode prevent duplicates
on the combination of the two items?

If so, the following SQL should give you something to build on.

SELECT U.UserId, U.FirstName, U.LastName, UH.HotelCode
FROM Users as U INNER JOIN UserHotels as UH
ON U.UserID = UH.UserID
WHERE U.UserID IN
(SELECT UserID
FROM UserHotels
GROUP BY UserID
HAVING Count(HotelCode) > 1)

If you have duplicates, then you would need to use another query to get the
unique records for UserID and HotelCode

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
J

Jerry Whittle

It may take more than criteria; you may need a sub-query. To help us help
you, show us the SQL of your query. Open the query in design view. Next go
to View, SQL View and copy and past it here. Information on primary keys and
relationships would be a nice touch too.
 
D

Dena

Hi Jerry,

Thanks for the tips. The SQL code is:
SELECT User.UserID, User.JobTitle, User.UserAccess, User.UserType,
User.AccountStatus, User.LastLoginDate, User.FirstName, User.LastName,
User.UpdatedOn, User_Hotel.UserID, User_Hotel.HolidexCode,
User_Hotel.UpdatedOn
FROM [User] INNER JOIN User_Hotel ON User.UserID = User_Hotel.UserID;

The primary key is on the User ID field and that field also allows duplicates.
 
D

Dena

Hi John,

Thanks for the reply. This is the SQL view of my query:
SELECT User.UserID, User.JobTitle, User.UserAccess, User.UserType,
User.AccountStatus, User.LastLoginDate, User.FirstName, User.LastName,
User.UpdatedOn, User_Hotel.UserID, User_Hotel.HolidexCode,
User_Hotel.UpdatedOn
FROM [User] INNER JOIN User_Hotel ON User.UserID = User_Hotel.UserID;

The two tables used for this query both use the UserID field as the primary
field and allow duplicates.
--
Thanks,
Dena


John Spencer said:
It all depends. Does the table with UserID and HotelCode prevent duplicates
on the combination of the two items?

If so, the following SQL should give you something to build on.

SELECT U.UserId, U.FirstName, U.LastName, UH.HotelCode
FROM Users as U INNER JOIN UserHotels as UH
ON U.UserID = UH.UserID
WHERE U.UserID IN
(SELECT UserID
FROM UserHotels
GROUP BY UserID
HAVING Count(HotelCode) > 1)

If you have duplicates, then you would need to use another query to get the
unique records for UserID and HotelCode

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
Click to expand...
 
J

John Spencer

By definition, a primary key is unique. So if USERId is the primary key in
USER it is unique. I suspect that UserID in User_Hotel is a foreign key and
therefore does not have to be unique. My original question was to identify
if the combination of UserID and HolidexCode is unique within the User_Hotel
table.

Can you have two records with the same HolidexCode and UserID?

Try the following SQL and see if it gives you the desired results.

SELECT User.UserID, User.JobTitle, User.UserAccess, User.UserType,
User.AccountStatus, User.LastLoginDate, User.FirstName, User.LastName,
User.UpdatedOn
, User_Hotel.UserID
, User_Hotel.HolidexCode
, User_Hotel.UpdatedOn
FROM [User] INNER JOIN User_Hotel
ON User.UserID = User_Hotel.UserID
WHERE User.UserID in
(SELECT T2.UserID
FROM
(SELECT Distinct User_Hotel.UserID, User_Hotel.HolidexCode
FROM User_Hotel) as T2
GROUP BY T2.UserID
HAVING Count(T2.HolidexCode) > 1)



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Dena said:
Hi John,

Thanks for the reply. This is the SQL view of my query:
SELECT User.UserID, User.JobTitle, User.UserAccess, User.UserType,
User.AccountStatus, User.LastLoginDate, User.FirstName, User.LastName,
User.UpdatedOn, User_Hotel.UserID, User_Hotel.HolidexCode,
User_Hotel.UpdatedOn
FROM [User] INNER JOIN User_Hotel ON User.UserID = User_Hotel.UserID;

The two tables used for this query both use the UserID field as the
primary
field and allow duplicates.
Click to expand...
 
D

Dena

I checked the UserID field in the User table and you're right - there is no
primary key showing next to that field in the design view. The User_Hotel
table shows two primary keys next to both UserID and HolidexCode.

To answer your question about two records with the same user id and holidex
code, an example of results from the User_Hotel table is shown below:

UserID HolidexCode UpdatedOn
CN=006969/O=HOTEL ACTEX 9/25/2005 11:21:01 AM
CN=006970/O=HOTEL NTLHV 12/19/2006 10:39:18 PM
CN=006970/O=HOTEL SYDCB 12/19/2006 10:39:18 PM
CN=006970/O=HOTEL NTLWR 12/19/2006 10:39:18 PM
CN=006970/O=HOTEL TERAS 10/10/2005 3:55:08 AM
CN=008201/O=HOTEL AUSNW 2/6/2006 10:09:07 AM

The table displays multiple user id's but only lists each unique Holidex
code as in user id CN=006970/O=HOTEL.

I tried the SQL code but did not have luck - got a syntax error. What is
the 'T2' representing? Thank you so much for your help - I'm so green at
this :)
--
Thanks,
Dena


John Spencer said:
By definition, a primary key is unique. So if USERId is the primary key in
USER it is unique. I suspect that UserID in User_Hotel is a foreign key and
therefore does not have to be unique. My original question was to identify
if the combination of UserID and HolidexCode is unique within the User_Hotel
table.

Can you have two records with the same HolidexCode and UserID?

Try the following SQL and see if it gives you the desired results.

SELECT User.UserID, User.JobTitle, User.UserAccess, User.UserType,
User.AccountStatus, User.LastLoginDate, User.FirstName, User.LastName,
User.UpdatedOn
, User_Hotel.UserID
, User_Hotel.HolidexCode
, User_Hotel.UpdatedOn
FROM [User] INNER JOIN User_Hotel
ON User.UserID = User_Hotel.UserID
WHERE User.UserID in
(SELECT T2.UserID
FROM
(SELECT Distinct User_Hotel.UserID, User_Hotel.HolidexCode
FROM User_Hotel) as T2
GROUP BY T2.UserID
HAVING Count(T2.HolidexCode) > 1)



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Dena said:
Hi John,

Thanks for the reply. This is the SQL view of my query:
SELECT User.UserID, User.JobTitle, User.UserAccess, User.UserType,
User.AccountStatus, User.LastLoginDate, User.FirstName, User.LastName,
User.UpdatedOn, User_Hotel.UserID, User_Hotel.HolidexCode,
User_Hotel.UpdatedOn
FROM [User] INNER JOIN User_Hotel ON User.UserID = User_Hotel.UserID;

The two tables used for this query both use the UserID field as the
primary
field and allow duplicates.
--
Thanks,
Dena


John Spencer said:
It all depends. Does the table with UserID and HotelCode prevent
duplicates
on the combination of the two items?

If so, the following SQL should give you something to build on.

SELECT U.UserId, U.FirstName, U.LastName, UH.HotelCode
FROM Users as U INNER JOIN UserHotels as UH
ON U.UserID = UH.UserID
WHERE U.UserID IN
(SELECT UserID
FROM UserHotels
GROUP BY UserID
HAVING Count(HotelCode) > 1)

If you have duplicates, then you would need to use another query to get
the
unique records for UserID and HotelCode

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I'm trying to filter records from a query. The query was created from
2
tables - both with the same userid field but one contains the
first/last
name
fields and the other contains the hotel code. I need all records in
this
query to show if the user is associated to more than one Hotel Code.
Example...

UserID | FirstName | LastName |HotelCode
C0123 | Joe | Smith | ATLNP
C0123 | Joe | Smith | SBOJC
C0123 | Joe | Smith | SANMX
C0847 | Bill | Jones | LONKT
C0395 | Frank | Williams | MOWSS

Only Joe Smith's records should appear in the query results. What
criteria
should be added? I'm struggling...
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Now I am confused.


T2 just assigns a name to the subquery so we can properly reference the
subquery within the subquery in the where clause.

What is CN=006969/O=HOTEL? Is that the UserID?
Is "ACTEX" a HolidexCode?

I can't see the syntax error in the SQL I suggested. Since the USERID
and Holidex code are the primary key for the User_Hotel table, you
should be able to use this simpler version of the query.

Copy and paste that into the SQL view of a new query.

SELECT User.UserID, User.JobTitle, User.UserAccess, User.UserType,
User.AccountStatus, User.LastLoginDate, User.FirstName, User.LastName,
User.UpdatedOn
, User_Hotel.UserID
, User_Hotel.HolidexCode
, User_Hotel.UpdatedOn
FROM [User] INNER JOIN User_Hotel
ON User.UserID = User_Hotel.UserID
WHERE User.UserID in
(SELECT T2.UserID
FROM User_Hotel as T2
GROUP BY T2.UserID
HAVING Count(T2.HolidexCode) > 1)


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
D

Dena

Hi John,

Thanks so much - this works like a charm. To answer your question...

What is CN=006969/O=HOTEL? Is that the UserID? YES
Is "ACTEX" a HolidexCode?-- YES

I copied the SQL code into the existing query instead of a new query
yesterday which is why I couldn't get it to work. No problems now - thanks
again!!!

Thanks,
Dena


John Spencer said:
Now I am confused.


T2 just assigns a name to the subquery so we can properly reference the
subquery within the subquery in the where clause.

What is CN=006969/O=HOTEL? Is that the UserID?
Is "ACTEX" a HolidexCode?

I can't see the syntax error in the SQL I suggested. Since the USERID
and Holidex code are the primary key for the User_Hotel table, you
should be able to use this simpler version of the query.

Copy and paste that into the SQL view of a new query.

SELECT User.UserID, User.JobTitle, User.UserAccess, User.UserType,
User.AccountStatus, User.LastLoginDate, User.FirstName, User.LastName,
User.UpdatedOn
, User_Hotel.UserID
, User_Hotel.HolidexCode
, User_Hotel.UpdatedOn
FROM [User] INNER JOIN User_Hotel
ON User.UserID = User_Hotel.UserID
WHERE User.UserID in
(SELECT T2.UserID
FROM User_Hotel as T2
GROUP BY T2.UserID
HAVING Count(T2.HolidexCode) > 1)


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I checked the UserID field in the User table and you're right - there is no
primary key showing next to that field in the design view. The User_Hotel
table shows two primary keys next to both UserID and HolidexCode.

To answer your question about two records with the same user id and holidex
code, an example of results from the User_Hotel table is shown below:

UserID HolidexCode UpdatedOn
CN=006969/O=HOTEL ACTEX 9/25/2005 11:21:01 AM
CN=006970/O=HOTEL NTLHV 12/19/2006 10:39:18 PM
CN=006970/O=HOTEL SYDCB 12/19/2006 10:39:18 PM
CN=006970/O=HOTEL NTLWR 12/19/2006 10:39:18 PM
CN=006970/O=HOTEL TERAS 10/10/2005 3:55:08 AM
CN=008201/O=HOTEL AUSNW 2/6/2006 10:09:07 AM

The table displays multiple user id's but only lists each unique Holidex
code as in user id CN=006970/O=HOTEL.

I tried the SQL code but did not have luck - got a syntax error. What is
the 'T2' representing? Thank you so much for your help - I'm so green at
this :)
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem with query output 1
Restarting Record Count in a Query 2
Help with Query (or Macro??) 3
Assistance with calling UDF from a query 6
Query format problem 1
IF Function Help 2
Flattening Data 2
question about running a comparison query 2

Top