Query Table1 to only return results based on 'criteria' within a 2ndtable

[mralmackay]

Hi,

Is it possible to do the following? I've got a table, called 'Cases'
and a table called 'Users'

Within these tables are numerous fields, however what I want to be
able to do is the following:

Where the 'Created By' field (in table Cases) is equal to the 'User-
ID' field (in table Users) and 'UserGroup' (in table users) =
'Helpdesk' show the following fields from the Cases table:
- CaseID
- Created By
- Details
- Resolution

These tables aren't currently linked (they're actually 'Linked' tables
to our source system - Remedy) through ODBC connections, however I can
set-up a relationship if need be to do this query?

Thanks in advance for your help with this as I'm lost as to what to
do!

Cheers, Al.

 

[John W. Vinson]

Hi,

Is it possible to do the following? I've got a table, called 'Cases'
and a table called 'Users'

Within these tables are numerous fields, however what I want to be
able to do is the following:

Where the 'Created By' field (in table Cases) is equal to the 'User-
ID' field (in table Users) and 'UserGroup' (in table users) =
'Helpdesk' show the following fields from the Cases table:
- CaseID
- Created By
- Details
- Resolution

These tables aren't currently linked (they're actually 'Linked' tables
to our source system - Remedy) through ODBC connections, however I can
set-up a relationship if need be to do this query?

Thanks in advance for your help with this as I'm lost as to what to
do!

Cheers, Al.

This is easier than you might think.

Create a new Query. Add both tables to the query grid.

Drag the [UserID] field from Users to the [Created By] field in Cases.
Select the fields you want to see, plus the UserGroup field; on the Criteria
line under UserGroup put

"Helpdesk"

You may want to uncheck the Show checkbox in this column.

You don't need an established relationship to see data, but you might if you
want the query to be updateable.

 

[mralmackay]

Thanks John.

I thought this should be simple, but I get the following error
message:
ODBC -- call failed
Driver][ISAM]ISAM error (#10004)

Below is my SQL from Access (slightly changed what I'm trying to get
from my original request but even with the original request I got the
same error):
SELECT Count([Case-ID]) AS Expr1
FROM [P2P-Request] INNER JOIN GroupUserJoin ON [P2P-Request].[Created-
By] = GroupUserJoin.[Full-Name]
WHERE (((GroupUserJoin.[Group-Name])="P2P Help Desk Team"));

Any thoughts? Thanks for your help, Al.

Hi,

Is it possible to do the following?  I've got a table, called 'Cases'
and a table called 'Users'

Within these tables are numerous fields, however what I want to be
able to do is the following:

Where the 'Created By' field (in table Cases) is equal to the 'User-
ID' field (in table Users) and 'UserGroup' (in table users) =
'Helpdesk' show the following fields from the Cases table:
- CaseID
- Created By
- Details
- Resolution

These tables aren't currently linked (they're actually 'Linked' tables
to our source system - Remedy) through ODBC connections, however I can
set-up a relationship if need be to do this query?

Thanks in advance for your help with this as I'm lost as to what to
do!

Cheers, Al.

This is easier than you might think.

Create a new Query. Add both tables to the query grid.

Drag the [UserID] field from Users to the [Created By] field in Cases.
Select the fields you want to see, plus the UserGroup field; on the Criteria
line under UserGroup put

"Helpdesk"

You may want to uncheck the Show checkbox in this column.

You don't need an established relationship to see data, but you might if you
want the query to be updateable.

 

[John W. Vinson]

Thanks John.

I thought this should be simple, but I get the following error
message:
ODBC -- call failed
Driver][ISAM]ISAM error (#10004)

That would appear to be an ODBC error on your join - that is, external to
Access proper. Can you view and edit data in the linked table?

 

[mralmackay]

Hi John,

I can view the table, it's a linked table to our Case Logging System
(Remedy), but can't edit.

Appreciate any thoughts that you may have. If I do a 'normal' query,
for example limit down the results on x field it works fine - it's
only when trying to do this across these linked tables.

Thanks, Al.

Thanks John.

I thought this should be simple, but I get the following error
message:
ODBC -- call failed
Driver][ISAM]ISAM error (#10004)

That would appear to be an ODBC error on your join - that is, external to
Access proper. Can you view and edit data in the linked table?