2 criteria for query

How would I write a query where I want to find a conflict of user profiles
sorted by id's? I have a table listing user id's, names, and profiles. I want
to find users with conflicts. For example if a user has profile 2001 or 2002,
then they can not have profiles 2012, 2014, 2015.

Thanks

hi,

hmm, Excel or Access?

On 10.12.2009 18:11, NeedExcelHelp07 wrote:
How would I write a query where I want to find a conflict of user profiles
sorted by id's? I have a table listing user id's, names, and profiles. I want
to find users with conflicts. For example if a user has profile 2001 or 2002,
then they can not have profiles 2012, 2014, 2015.
Where is the conflict?

May a user only have zero, one or two profiles or is this number a year
and the user may not have profiles in the future?

The basic approach is to group by the user, and filter by HAVING
Count(*) 1, e.g.

SELECT [UserId], Count(*)
FROM [yourTable]
WHERE [Profile] Year(Now)
GROUP BY [UserID]
HAVING Count(*) 1


mfG
-- stefan --

Thanks for the help.
I think Access because of the large number of data.
It is a four digit number assigned for access to a system.
A user can have between 1-30 different profiles.

Thanks!

"Stefan Hoffmann" wrote:

hi,

hmm, Excel or Access?

On 10.12.2009 18:11, NeedExcelHelp07 wrote:
How would I write a query where I want to find a conflict of user profiles
sorted by id's? I have a table listing user id's, names, and profiles. I want
to find users with conflicts. For example if a user has profile 2001 or 2002,
then they can not have profiles 2012, 2014, 2015.
Where is the conflict?

May a user only have zero, one or two profiles or is this number a year
and the user may not have profiles in the future?

The basic approach is to group by the user, and filter by HAVING
Count(*) 1, e.g.

SELECT [UserId], Count(*)
FROM [yourTable]
WHERE [Profile] Year(Now)
GROUP BY [UserID]
HAVING Count(*) 1


mfG
-- stefan --
.

I think you will need to build a 'Conflict' or 'Conpatible' table, whichever
is easiest for you.
Assigned Conflict
2001 2012
2001 2033
2001 2014
2001 2015
2002 2012
2002 2033
2002 2014
2002 2015

--
Build a little, test a little.


"NeedExcelHelp07" wrote:

Thanks for the help.
I think Access because of the large number of data.
It is a four digit number assigned for access to a system.
A user can have between 1-30 different profiles.

Thanks!

"Stefan Hoffmann" wrote:

hi,

hmm, Excel or Access?

On 10.12.2009 18:11, NeedExcelHelp07 wrote:
How would I write a query where I want to find a conflict of user profiles
sorted by id's? I have a table listing user id's, names, and profiles. I want
to find users with conflicts. For example if a user has profile 2001 or 2002,
then they can not have profiles 2012, 2014, 2015.
Where is the conflict?

May a user only have zero, one or two profiles or is this number a year
and the user may not have profiles in the future?

The basic approach is to group by the user, and filter by HAVING
Count(*) 1, e.g.

SELECT [UserId], Count(*)
FROM [yourTable]
WHERE [Profile] Year(Now)
GROUP BY [UserID]
HAVING Count(*) 1


mfG
-- stefan --
.

How would I create a conflict table?

"KARL DEWEY" wrote:

I think you will need to build a 'Conflict' or 'Conpatible' table, whichever
is easiest for you.
Assigned Conflict
2001 2012
2001 2033
2001 2014
2001 2015
2002 2012
2002 2033
2002 2014
2002 2015

--
Build a little, test a little.


"NeedExcelHelp07" wrote:

Thanks for the help.
I think Access because of the large number of data.
It is a four digit number assigned for access to a system.
A user can have between 1-30 different profiles.

Thanks!

"Stefan Hoffmann" wrote:

hi,

hmm, Excel or Access?

On 10.12.2009 18:11, NeedExcelHelp07 wrote:
How would I write a query where I want to find a conflict of user profiles
sorted by id's? I have a table listing user id's, names, and profiles. I want
to find users with conflicts. For example if a user has profile 2001 or 2002,
then they can not have profiles 2012, 2014, 2015.
Where is the conflict?

May a user only have zero, one or two profiles or is this number a year
and the user may not have profiles in the future?

The basic approach is to group by the user, and filter by HAVING
Count(*) 1, e.g.

SELECT [UserId], Count(*)
FROM [yourTable]
WHERE [Profile] Year(Now)
GROUP BY [UserID]
HAVING Count(*) 1


mfG
-- stefan --
.

My last post include an example of such table.
I can not tell you how to apply in a query without knowing your table
structure, table name, fields with datatype, and sample data.

--
Build a little, test a little.


"NeedExcelHelp07" wrote:

How would I create a conflict table?

"KARL DEWEY" wrote:

I think you will need to build a 'Conflict' or 'Conpatible' table, whichever
is easiest for you.
Assigned Conflict
2001 2012
2001 2033
2001 2014
2001 2015
2002 2012
2002 2033
2002 2014
2002 2015

--
Build a little, test a little.


"NeedExcelHelp07" wrote:

Thanks for the help.
I think Access because of the large number of data.
It is a four digit number assigned for access to a system.
A user can have between 1-30 different profiles.

Thanks!

"Stefan Hoffmann" wrote:

hi,

hmm, Excel or Access?

On 10.12.2009 18:11, NeedExcelHelp07 wrote:
How would I write a query where I want to find a conflict of user profiles
sorted by id's? I have a table listing user id's, names, and profiles. I want
to find users with conflicts. For example if a user has profile 2001 or 2002,
then they can not have profiles 2012, 2014, 2015.
Where is the conflict?

May a user only have zero, one or two profiles or is this number a year
and the user may not have profiles in the future?

The basic approach is to group by the user, and filter by HAVING
Count(*) 1, e.g.

SELECT [UserId], Count(*)
FROM [yourTable]
WHERE [Profile] Year(Now)
GROUP BY [UserID]
HAVING Count(*) 1


mfG
-- stefan --
.