Query question...

[john]

I have table User-App and table App Profile
User-App App Profile
IDuser IDApp IDApp
1 34 34
1 45 45
2 34
2 45
2 90
3 34

I can't seem to figure out the following:
I need to find out which user fits the App profile. With fitting the profile
I mean that the user uses 1 to every App (but not more) of those that are in
the Profile table. In this case only user 1 and 3 fit the profile.

Can someone point me in the right direction?
Thanks!
john

[tina]

have you tried an Inner Join between the two tables in the query, linked
between the IDApp fields?

hth
"john" <jo**@test.comwrote in message
news:3v******************************@casema.nl...

I have table User-App and table App Profile
User-App App Profile
IDuser IDApp IDApp
1 34 34
1 45 45
2 34
2 45
2 90
3 34

I can't seem to figure out the following:
I need to find out which user fits the App profile. With fitting the

profile

I mean that the user uses 1 to every App (but not more) of those that are

in

the Profile table. In this case only user 1 and 3 fit the profile.

Can someone point me in the right direction?
Thanks!
john

[Bob Quintal]

"john" <jo**@test.comwrote in
news:3v******************************@casema.nl:

I have table User-App and table App Profile
User-App App Profile
IDuser IDApp IDApp
1 34 34
1 45 45
2 34
2 45
2 90
3 34

I can't seem to figure out the following:
I need to find out which user fits the App profile. With
fitting the profile I mean that the user uses 1 to every App
(but not more) of those that are in the Profile table. In this
case only user 1 and 3 fit the profile.

Can someone point me in the right direction?
Thanks!
john

paste this into the sql view of the query builder, it should
work.

SELECT IDuser,
count(User-App.IDApp) as x
FROM User-App
INNER JOIN [App Profile]
ON User-App.IDApp = [App Profile].IDApp
HAVING count(User-App.IDApp) = dCount("IdApp","[App Profile]")
GROUP BY IDUser;

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[john]

Thanks Bob,
Unfortunately I keep getting an error on:

HAVING count(User-App.IDApp) = dCount("IdApp","[App Profile]")

I tried every possible quoting and also tried replacing , in ; but I keep
getting a syntax error.
Any idea what's causing this?
john

"Bob Quintal" <rq******@sPAmpatico.caschreef in bericht
news:Xn**********************@66.150.105.47...

"john" <jo**@test.comwrote in
news:3v******************************@casema.nl:

>I have table User-App and table App Profile
User-App App Profile
IDuser IDApp IDApp
1 34 34
1 45 45
2 34
2 45
2 90
3 34

I can't seem to figure out the following:
I need to find out which user fits the App profile. With
fitting the profile I mean that the user uses 1 to every App
(but not more) of those that are in the Profile table. In this
case only user 1 and 3 fit the profile.

Can someone point me in the right direction?
Thanks!
john

paste this into the sql view of the query builder, it should
work.

SELECT IDuser,
count(User-App.IDApp) as x
FROM User-App
INNER JOIN [App Profile]
ON User-App.IDApp = [App Profile].IDApp
HAVING count(User-App.IDApp) = dCount("IdApp","[App Profile]")
GROUP BY IDUser;

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Lyle Fairfield]

"john" <jo**@test.comwrote in
news:3v******************************@casema.nl:

I have table User-App and table App Profile
User-App App Profile
IDuser IDApp IDApp
1 34 34
1 45 45
2 34
2 45
2 90
3 34

I can't seem to figure out the following:
I need to find out which user fits the App profile. With fitting the
profile I mean that the user uses 1 to every App (but not more) of
those that are in the Profile table. In this case only user 1 and 3
fit the profile.

Can someone point me in the right direction?
Thanks!
john

Maybe ...

SELECT sq1.IDUser FROM
[SELECT UserApp.IDUser, COUNT(UserApp.IDUser) AS Count1
FROM UserApp LEFT JOIN AppProfile ON UserApp.IDApp = AppProfile.IDApp
GROUP BY UserApp.IDUser
ORDER BY UserApp.IDUser]. sq1
INNER JOIN
[SELECT UserApp.IDUser, COUNT(UserApp.IDUser) AS Count2
FROM UserApp LEFT JOIN AppProfile ON UserApp.IDApp = AppProfile.IDApp
WHERE AppProfile.IDApp IS NOT NULL
GROUP BY UserApp.IDUser
ORDER BY UserApp.IDUser]. sq2
ON
sq1.IDUser=sq2.IDUser
AND
sq1.Count1=sq2.Count2

--
Lyle Fairfield

from http://msdn.microsoft.com/library/de...l=/library/en-
us/dnmdac/html/data_mdacroadmap.asp

Obsolete Data Access Technologies
Obsolete technologies are technologies that have not been enhanced or
updated in several product releases and that will be excluded from future
product releases. Do not use these technologies when you write new
applications. When you modify existing applications that are written
using these technologies, consider migrating those applications to
ADO.NET.
The following components are considered obsolete:
....
Data Access Objects (DAO): DAO provides access to JET (Access) databases.
This API can be used from Microsoft Visual Basic®, Microsoft Visual C++®,
and scripting languages. It was included with Microsoft Office 2000 and
Office XP. DAO 3.6 is the final version of this technology. It will not
be available on the 64-bit Windows operating system.
.....

[john]

"Lyle Fairfield" <ly***********@aim.comschreef in bericht
news:Xn*********************************@216.221.8 1.119...

"john" <jo**@test.comwrote in
news:3v******************************@casema.nl:

>I have table User-App and table App Profile
User-App App Profile
IDuser IDApp IDApp
1 34 34
1 45 45
2 34
2 45
2 90
3 34

I can't seem to figure out the following:
I need to find out which user fits the App profile. With fitting the
profile I mean that the user uses 1 to every App (but not more) of
those that are in the Profile table. In this case only user 1 and 3
fit the profile.

Can someone point me in the right direction?
Thanks!
john

Maybe ...

SELECT sq1.IDUser FROM
[SELECT UserApp.IDUser, COUNT(UserApp.IDUser) AS Count1
FROM UserApp LEFT JOIN AppProfile ON UserApp.IDApp = AppProfile.IDApp
GROUP BY UserApp.IDUser
ORDER BY UserApp.IDUser]. sq1
INNER JOIN
[SELECT UserApp.IDUser, COUNT(UserApp.IDUser) AS Count2
FROM UserApp LEFT JOIN AppProfile ON UserApp.IDApp = AppProfile.IDApp
WHERE AppProfile.IDApp IS NOT NULL
GROUP BY UserApp.IDUser
ORDER BY UserApp.IDUser]. sq2
ON
sq1.IDUser=sq2.IDUser
AND
sq1.Count1=sq2.Count2

Lyle, this is amazing! It works! Thanks a lot!
Am I right that one can never build this query with the standarad access
query builder? Because when I view this query in the query builder I don't
even see the source table names.
john

[Bob Quintal]

"john" <jo**@test.comwrote in
news:a5******************************@casema.nl:

Thanks Bob,
Unfortunately I keep getting an error on:

>HAVING count(User-App.IDApp) = dCount("IdApp","[App
Profile]")

I tried every possible quoting and also tried replacing , in ;
but I keep getting a syntax error.
Any idea what's causing this?
john

no, but remove it from the SQL view. Go to desing view.
add the field User-App.IDApp to the grid. put
dCount("IdApp","[App Profile]") as criteria for the field.
change the Group By to WHERE.

try it again.

Bob

>
"Bob Quintal" <rq******@sPAmpatico.caschreef in bericht
news:Xn**********************@66.150.105.47...

>"john" <jo**@test.comwrote in
news:3v******************************@casema.nl :

>>I have table User-App and table App Profile
User-App App Profile
IDuser IDApp IDApp
1 34 34
1 45 45
2 34
2 45
2 90
3 34

I can't seem to figure out the following:
I need to find out which user fits the App profile. With
fitting the profile I mean that the user uses 1 to every App
(but not more) of those that are in the Profile table. In
this case only user 1 and 3 fit the profile.

Can someone point me in the right direction?
Thanks!
john

paste this into the sql view of the query builder, it should
work.

SELECT IDuser,
count(User-App.IDApp) as x
FROM User-App
INNER JOIN [App Profile]
ON User-App.IDApp = [App Profile].IDApp
HAVING count(User-App.IDApp) = dCount("IdApp","[App
Profile]") GROUP BY IDUser;

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[john]

"Bob Quintal" <rq******@sPAmpatico.caschreef in bericht
news:Xn**********************@66.150.105.47...

"john" <jo**@test.comwrote in
news:a5******************************@casema.nl:

>Thanks Bob,
Unfortunately I keep getting an error on:

>>HAVING count(User-App.IDApp) = dCount("IdApp","[App
Profile]")

I tried every possible quoting and also tried replacing , in ;
but I keep getting a syntax error.
Any idea what's causing this?
john

no, but remove it from the SQL view. Go to desing view.
add the field User-App.IDApp to the grid. put
dCount("IdApp","[App Profile]") as criteria for the field.
change the Group By to WHERE.

try it again.

Thanks Bob,
Just did so. At first there was a syntax error again but when I changed the
, to ; the query ran. However with no resulting records. I will look into
this later as I have a deadline to catch.
john

[Lyle Fairfield]

john wrote:

Am I right that one can never build this query with the standarad access
query builder? Because when I view this query in the query builder I don't
even see the source table names.

There is an SQL view in the query builder (view pull-down - upper
left). That is where I constructed the query. Design view is limited in
that it does not seem to show or allow sub-queries as SQL strings, nor
non-equi joins. This can lead users to think that JET SQL is much less
powerful than it actually is.

On the other hand JET SQL has syntax rules for parentheses around JOINS
that I have not mastered, nor am I likely to as I now use SQL-Server
and the parentheses are not needed there. So when I am composing JOINS
in JET, I often fire up the query builder, do the JOINS with Table
Names and then switch to SQL view and modify the SQL to replace the
Table Names with subquery strings.
And the query builder is a great place to test things.

The query I posted seems very long and complicated to me. I hope
someone will post something simpler.

[john]

"Lyle Fairfield" <ly***********@aim.comschreef in bericht
news:11**********************@f16g2000cwb.googlegr oups.com...

john wrote:

>Am I right that one can never build this query with the standarad access
query builder? Because when I view this query in the query builder I
don't
even see the source table names.

There is an SQL view in the query builder (view pull-down - upper
left). That is where I constructed the query. Design view is limited in
that it does not seem to show or allow sub-queries as SQL strings, nor
non-equi joins. This can lead users to think that JET SQL is much less
powerful than it actually is.

On the other hand JET SQL has syntax rules for parentheses around JOINS
that I have not mastered, nor am I likely to as I now use SQL-Server
and the parentheses are not needed there. So when I am composing JOINS
in JET, I often fire up the query builder, do the JOINS with Table
Names and then switch to SQL view and modify the SQL to replace the
Table Names with subquery strings.
And the query builder is a great place to test things.

Thank for explanation.

The query I posted seems very long and complicated to me. I hope
someone will post something simpler.

Anyway, the query saved me tons of work today...
john

[CDMAPoster]

john wrote:

"Lyle Fairfield" <ly***********@aim.comschreef in bericht
news:11**********************@f16g2000cwb.googlegr oups.com...

The query I posted seems very long and complicated to me. I hope
someone will post something simpler.

Anyway, the query saved me tons of work today...
john

I used the following table and field names:

tblUserApp
IDUser IDApp
1 34
1 45
2 34
2 45
2 90
3 34

tblUser
1 U1
2 U2
3 U3

tblApp
IDApp AppName
34 App1
45 App2
90 App3

tblAppProfile
APID IDApp
1 34
2 45

The specific user has to be using at least one software from
tblAppProfile but not if any software in tblUserApp for that user is
from tblApp, yet not in tblAppProfile:

SELECT DISTINCT IDUser FROM tblUserApp
WHERE (SELECT Count(A.IDApp) FROM tblAppProfile AS A WHERE A.IDApp IN
(SELECT B.IDApp FROM tblUserApp AS B WHERE B.IDUser =
tblUserApp.IDUser)) 0
AND (SELECT Count(A.IDApp) FROM tblUserApp AS A WHERE A.IDApp =
(SELECT tblApp.IDApp FROM tblApp
LEFT JOIN tblAppProfile ON
tblApp.IDApp = tblAppProfile.IDApp WHERE tblAppProfile.IDApp Is Null)
AND A.IDUser = tblUserApp.IDUser) = 0;

For your table structure I couldn't come up with anything simpler than
Lyle's solution.

What if a Y/N field called Profile is added to tblApp, and
tblAppProfile is eliminated?:

tblApp
IDApp AppName Profile
34 App1 -1
45 App2 -1
90 App3 0

The specific user has to be using at least one software from tblApp
with Profile = -1but not if any software in tblApp for that user has
Profile = 0.

A join with Abs(Sum(Profile)) = Count(Profile) might simplify things.

Maybe something like:

SELECT IDUser FROM tblUserApp INNER JOIN tblApp ON tblUserApp.IDApp =
tblApp.IDApp GROUP BY IDUser HAVING Abs(Sum(Profile)) = Count(Profile);

Any IDApp that doesn't show up in tblAppProfile should prevent that
IDUser from showing up. Note: I didn't test this much. It seemed to
work for your example even when different combinations of Profile were
checked.

James A. Fortune
CD********@FortuneJames.com

[john]

Thanks for the insight James, that gives me a new way to look at it. Lyle's
solution worked very well to put the right profiles together.

I now have 50 profiles and, from the apps a user needs, I need to find the
most suitable profile for him (with all of his apps and with the least
redundant icons). So the tblAppProfile now consists of 50 Profiles and all
apps involved per profile.

I'm thinking about using queries which create:
1. TableA with nr of apps per user.
2. TableB with nr of apps per user per profile.
3. A joined (tableA-TableB) table (tableC) with a field that subtracts the
nr of apps fields (table B field minus table A field) where the outcome is 0
or higher. This means the user fits that profile.
4. a percentage calculation of how many apps of a profile a user uses.

john

<CD********@FortuneJames.comschreef in bericht
news:11*********************@45g2000cws.googlegrou ps.com...

john wrote:

>"Lyle Fairfield" <ly***********@aim.comschreef in bericht
news:11**********************@f16g2000cwb.googleg roups.com...

The query I posted seems very long and complicated to me. I hope
someone will post something simpler.

Anyway, the query saved me tons of work today...
john

I used the following table and field names:

tblUserApp
IDUser IDApp
1 34
1 45
2 34
2 45
2 90
3 34

tblUser
1 U1
2 U2
3 U3

tblApp
IDApp AppName
34 App1
45 App2
90 App3

tblAppProfile
APID IDApp
1 34
2 45

The specific user has to be using at least one software from
tblAppProfile but not if any software in tblUserApp for that user is
from tblApp, yet not in tblAppProfile:

SELECT DISTINCT IDUser FROM tblUserApp
WHERE (SELECT Count(A.IDApp) FROM tblAppProfile AS A WHERE A.IDApp IN
(SELECT B.IDApp FROM tblUserApp AS B WHERE B.IDUser =
tblUserApp.IDUser)) 0
AND (SELECT Count(A.IDApp) FROM tblUserApp AS A WHERE A.IDApp =
(SELECT tblApp.IDApp FROM tblApp
LEFT JOIN tblAppProfile ON
tblApp.IDApp = tblAppProfile.IDApp WHERE tblAppProfile.IDApp Is Null)
AND A.IDUser = tblUserApp.IDUser) = 0;

For your table structure I couldn't come up with anything simpler than
Lyle's solution.

What if a Y/N field called Profile is added to tblApp, and
tblAppProfile is eliminated?:

tblApp
IDApp AppName Profile
34 App1 -1
45 App2 -1
90 App3 0

The specific user has to be using at least one software from tblApp
with Profile = -1but not if any software in tblApp for that user has
Profile = 0.

A join with Abs(Sum(Profile)) = Count(Profile) might simplify things.

Maybe something like:

SELECT IDUser FROM tblUserApp INNER JOIN tblApp ON tblUserApp.IDApp =
tblApp.IDApp GROUP BY IDUser HAVING Abs(Sum(Profile)) = Count(Profile);

Any IDApp that doesn't show up in tblAppProfile should prevent that
IDUser from showing up. Note: I didn't test this much. It seemed to
work for your example even when different combinations of Profile were
checked.

James A. Fortune
CD********@FortuneJames.com

[CDMAPoster]

john wrote:

Thanks for the insight James, that gives me a new way to look at it. Lyle's
solution worked very well to put the right profiles together.

I now have 50 profiles and, from the apps a user needs, I need to find the
most suitable profile for him (with all of his apps and with the least
redundant icons). So the tblAppProfile now consists of 50 Profiles and all
apps involved per profile.

I'm thinking about using queries which create:
1. TableA with nr of apps per user.
2. TableB with nr of apps per user per profile.
3. A joined (tableA-TableB) table (tableC) with a field that subtracts the
nr of apps fields (table B field minus table A field) where the outcome is 0
or higher. This means the user fits that profile.
4. a percentage calculation of how many apps of a profile a user uses.

john

Your plan looks complicated, but so is your problem. It seems you want
to find out which of the 50 or so profiles are the best for a given
user. In Mathematics there is a concept called a functional. A
functional is a special kind of function that takes a function as an
input and returns a number as an output. The number can be used as a
score to determine which functions are the most appropriate for a given
purpose. So you're looking for a way to assign a number to each
profile for a specific user. That suggests putting all the profiles
into a single table if you don't want to run a query for each profile
table then append the result to an output table. Your "functional"
will take the table with all the profiles as input and return a score
for each profile within for a specific user. All the information about
functionals is for implementing Lyle's idea.

To use my idea instead for multiple profiles would require something
like: set the values for the Profile field in code for each profile,
calculate a score, then move on to the next one until the best profile
is found for that user. That's not as clean as straight SQL. Maybe
stitching together the results from individual profiles will be easier
unless you can get Lyle to abstract his solution to account for
multiple profiles. Having more than one profile shoots down my
simplification.

James A. Fortune
CD********@FortuneJames.com

[john]

<CD********@FortuneJames.comschreef in bericht
news:11**********************@16g2000cwy.googlegro ups.com...

john wrote:

>Thanks for the insight James, that gives me a new way to look at it.
Lyle's
solution worked very well to put the right profiles together.

I now have 50 profiles and, from the apps a user needs, I need to find
the
most suitable profile for him (with all of his apps and with the least
redundant icons). So the tblAppProfile now consists of 50 Profiles and
all
apps involved per profile.

I'm thinking about using queries which create:
1. TableA with nr of apps per user.
2. TableB with nr of apps per user per profile.
3. A joined (tableA-TableB) table (tableC) with a field that subtracts
the
nr of apps fields (table B field minus table A field) where the outcome
is 0
or higher. This means the user fits that profile.
4. a percentage calculation of how many apps of a profile a user uses.

john

Your plan looks complicated, but so is your problem. It seems you want
to find out which of the 50 or so profiles are the best for a given
user. In Mathematics there is a concept called a functional. A
functional is a special kind of function that takes a function as an
input and returns a number as an output. The number can be used as a
score to determine which functions are the most appropriate for a given
purpose. So you're looking for a way to assign a number to each
profile for a specific user. That suggests putting all the profiles
into a single table if you don't want to run a query for each profile
table then append the result to an output table. Your "functional"
will take the table with all the profiles as input and return a score
for each profile within for a specific user. All the information about
functionals is for implementing Lyle's idea.

To use my idea instead for multiple profiles would require something
like: set the values for the Profile field in code for each profile,
calculate a score, then move on to the next one until the best profile
is found for that user. That's not as clean as straight SQL. Maybe
stitching together the results from individual profiles will be easier
unless you can get Lyle to abstract his solution to account for
multiple profiles. Having more than one profile shoots down my
simplification.

Thanks.
john