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.comw rote 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.comw rote 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******@sPAmp atico.caschreef in bericht
news:Xn******** **************@ 66.150.105.47.. .

"john" <jo**@test.comw rote 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.comw rote 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.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]

"Lyle Fairfield" <ly***********@ aim.comschreef in bericht
news:Xn******** *************** **********@216. 221.81.119...

"john" <jo**@test.comw rote 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.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]

"john" <jo**@test.comw rote 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******@sPAmp atico.caschreef in bericht
news:Xn******** **************@ 66.150.105.47.. .

>"john" <jo**@test.comw rote 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******@sPAmp atico.caschreef in bericht
news:Xn******** **************@ 66.150.105.47.. .

"john" <jo**@test.comw rote in
news:a5******** *************** *******@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]

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