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]

Re: Query question...

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

hth


"john" <john@test.comw rote in message
news:3vSdnSsi8Z X7svrYnZ2dnUVZ8 tadnZ2d@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]

Re: Query question...

"john" <john@test.comw rote in
news:3vSdnSsi8Z X7svrYnZ2dnUVZ8 tadnZ2d@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]

Re: Query question...

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" <rquintal@sPAmp atico.caschreef in bericht
news:Xns9885C1C F65EDBBQuintal@ 66.150.105.47.. .

"john" <john@test.comw rote in
news:3vSdnSsi8Z X7svrYnZ2dnUVZ8 tadnZ2d@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]

Re: Query question...

"john" <john@test.comw rote in
news:3vSdnSsi8Z X7svrYnZ2dnUVZ8 tadnZ2d@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.I DUser) AS Count1
FROM UserApp LEFT JOIN AppProfile ON UserApp.IDApp = AppProfile.IDAp p
GROUP BY UserApp.IDUser
ORDER BY UserApp.IDUser]. sq1
INNER JOIN
[SELECT UserApp.IDUser, COUNT(UserApp.I DUser) AS Count2
FROM UserApp LEFT JOIN AppProfile ON UserApp.IDApp = AppProfile.IDAp p
WHERE AppProfile.IDAp p 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_mdacroadma p.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]

Re: Query question...

"Lyle Fairfield" <lylefairfield@ aim.comschreef in bericht
news:Xns9886433 F07D9Alylefairf ieldaimcom@216. 221.81.119...

"john" <john@test.comw rote in
news:3vSdnSsi8Z X7svrYnZ2dnUVZ8 tadnZ2d@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.I DUser) AS Count1
FROM UserApp LEFT JOIN AppProfile ON UserApp.IDApp = AppProfile.IDAp p
GROUP BY UserApp.IDUser
ORDER BY UserApp.IDUser]. sq1
INNER JOIN
[SELECT UserApp.IDUser, COUNT(UserApp.I DUser) AS Count2
FROM UserApp LEFT JOIN AppProfile ON UserApp.IDApp = AppProfile.IDAp p
WHERE AppProfile.IDAp p 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]

Re: Query question...

"john" <john@test.comw rote in
news:a5ednVMbXI fdSPrYnZ2dnUVZ8 t2dnZ2d@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" <rquintal@sPAmp atico.caschreef in bericht
news:Xns9885C1C F65EDBBQuintal@ 66.150.105.47.. .

>"john" <john@test.comw rote in
>news:3vSdnSsi8 ZX7svrYnZ2dnUVZ 8tadnZ2d@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]

Re: Query question...

"Bob Quintal" <rquintal@sPAmp atico.caschreef in bericht
news:Xns98865C8 0965BEBQuintal@ 66.150.105.47.. .

"john" <john@test.comw rote in
news:a5ednVMbXI fdSPrYnZ2dnUVZ8 t2dnZ2d@casema. nl:
>

>Thanks Bob,
>Unfortunatel y 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]

Re: Query question...

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]

Re: Query question...

"Lyle Fairfield" <lylefairfield@ aim.comschreef in bericht
news:1164465327 .122952.161450@ f16g2000cwb.goo glegroups.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@FortuneJames.com]

Re: Query question...

john wrote:

"Lyle Fairfield" <lylefairfield@ aim.comschreef in bericht
news:1164465327 .122952.161450@ f16g2000cwb.goo glegroups.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.IDUs er)) 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.I DApp WHERE tblAppProfile.I DApp Is Null)
AND A.IDUser = tblUserApp.IDUs er) = 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.IDAp p =
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
CDMAPoster@Fort uneJames.com

[john]

Re: Query question...

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

<CDMAPoster@For tuneJames.comsc hreef in bericht
news:1164683791 .973049.54120@4 5g2000cws.googl egroups.com...

john wrote:

>"Lyle Fairfield" <lylefairfield@ aim.comschreef in bericht
>news:116446532 7.122952.161450 @f16g2000cwb.go oglegroups.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.IDUs er)) 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.I DApp WHERE tblAppProfile.I DApp Is Null)
AND A.IDUser = tblUserApp.IDUs er) = 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.IDAp p =
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
CDMAPoster@Fort uneJames.com
>

[CDMAPoster@FortuneJames.com]

Re: Query question...

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
CDMAPoster@Fort uneJames.com

[john]

Re: Query question...

<CDMAPoster@For tuneJames.comsc hreef in bericht
news:1164777370 .791880.275740@ 16g2000cwy.goog legroups.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