2 criteria for query

[NeedExcelHelp07]

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

 

[Stefan Hoffmann]

hi,

hmm, Excel or Access?

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 <--

 

[NeedExcelHelp07]

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!

hi,

hmm, Excel or Access?

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 <--
.

 

[KARL DEWEY]

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.

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!

hi,

hmm, Excel or Access?

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 <--
.

 

[NeedExcelHelp07]

How would I create a conflict table?

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.

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!

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 <--
.

 

[KARL DEWEY]

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.

How would I create a conflict table?

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.

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!

:

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 <--
.