Simulate operator without

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi. I have a database (a table) with 2 fields : the name of a member and the
name of a viewed movie. I try to obtain the list of movies that a member have
not seen yet. In my table, I have added un false member that have viewed all
the movies. To identify the membre for the query, I use a public variable. I
didn't figured out how to do that and I'm looking for that one since a long
time. Thank very much for your help.
 
I have a table video with the field video_name. I have a table member with
the field member_name. I have an intermediate table borrows with 2 fields for
primary key : borrows_member_name and borrows_video_name. I work with a
project and the SQL server (MSDE). I have succeed to make it work by using 2
views (one that uses the other one) but since I want to be able to modify the
first view according to the member (with a public variable), I can't hard
code the first view to make my second one working. I have to make it works
with only one view. I have tried to overlap 2 views but it didn't work. Thank
for your help.
 
What exactly are you trying to achieve? (you don't really ask a question
anywhere in your two posts...)

If what you're trying to get is a list of what videos a given member has not
rented, try:

SELECT video_name
FROM video
WHERE video_name NOT IN
(SELECT borrows_video_name
FROM borrows
WHERE borrows_member_name = [Name of Member?])

That'll prompt you for a member name, but you could always replace that with
a reference to the member_name on your form:

SELECT video_name
FROM video
WHERE video_name NOT IN
(SELECT borrows_video_name
FROM borrows
WHERE borrows_member_name = Forms!NameOfForm!NameOfControl)
 
Hi Douglas,
Yes, I'm trying to get a list of what videos a given member has not rented.
I'm giving a try of your solution right now. Let You know if it works.
Thank

Douglas J. Steele said:
What exactly are you trying to achieve? (you don't really ask a question
anywhere in your two posts...)

If what you're trying to get is a list of what videos a given member has not
rented, try:

SELECT video_name
FROM video
WHERE video_name NOT IN
(SELECT borrows_video_name
FROM borrows
WHERE borrows_member_name = [Name of Member?])

That'll prompt you for a member name, but you could always replace that with
a reference to the member_name on your form:

SELECT video_name
FROM video
WHERE video_name NOT IN
(SELECT borrows_video_name
FROM borrows
WHERE borrows_member_name = Forms!NameOfForm!NameOfControl)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Moff said:
I have a table video with the field video_name. I have a table member with
the field member_name. I have an intermediate table borrows with 2 fields
for
primary key : borrows_member_name and borrows_video_name. I work with a
project and the SQL server (MSDE). I have succeed to make it work by using
2
views (one that uses the other one) but since I want to be able to modify
the
first view according to the member (with a public variable), I can't hard
code the first view to make my second one working. I have to make it works
with only one view. I have tried to overlap 2 views but it didn't work.
Thank
for your help.
Click to expand...
Click to expand...
 
Hi Douglas,
It works very well. And how easy was the solution :-)
Thank

Douglas J. Steele said:
What exactly are you trying to achieve? (you don't really ask a question
anywhere in your two posts...)

If what you're trying to get is a list of what videos a given member has not
rented, try:

SELECT video_name
FROM video
WHERE video_name NOT IN
(SELECT borrows_video_name
FROM borrows
WHERE borrows_member_name = [Name of Member?])

That'll prompt you for a member name, but you could always replace that with
a reference to the member_name on your form:

SELECT video_name
FROM video
WHERE video_name NOT IN
(SELECT borrows_video_name
FROM borrows
WHERE borrows_member_name = Forms!NameOfForm!NameOfControl)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Moff said:
I have a table video with the field video_name. I have a table member with
the field member_name. I have an intermediate table borrows with 2 fields
for
primary key : borrows_member_name and borrows_video_name. I work with a
project and the SQL server (MSDE). I have succeed to make it work by using
2
views (one that uses the other one) but since I want to be able to modify
the
first view according to the member (with a public variable), I can't hard
code the first view to make my second one working. I have to make it works
with only one view. I have tried to overlap 2 views but it didn't work.
Thank
for your help.
Click to expand...
Click to expand...
 
Hi Douglas,
It works very well. And how easy was the solution :-)
Thank
Click to expand...
This seems to work too.
Edit name to suit you.

SELECT a.member_name,
a.video_name
FROM (SELECT Members.member_name,
Videos.video_name
FROM Members,
Videos) AS a
LEFT JOIN VideoMemberLoans AS b
ON (a.video_name = b.video_name)
AND (a.member_name = b.member_name)
WHERE (((b.video_name) IS NULL)
AND ((b.member_name) IS NULL))
ORDER BY a.member_name,
a.video_name;
 
If what you're trying to get is a list of what videos a given
member has not rented, try:

SELECT video_name
FROM video
WHERE video_name NOT IN
(SELECT borrows_video_name
FROM borrows
WHERE borrows_member_name = [Name of Member?])

That'll prompt you for a member name, but you could always replace
that with a reference to the member_name on your form:

SELECT video_name
FROM video
WHERE video_name NOT IN
(SELECT borrows_video_name
FROM borrows
WHERE borrows_member_name = Forms!NameOfForm!NameOfControl)
Click to expand...

If you do that, be sure to define the form reference as a parameter,
because if you don't, the results can be unpredictable in A2K3 (at
least -- it may be a problem in other Jet 4 versions of Access, but
that's where I encountered it).
 
David W. Fenton said:
If what you're trying to get is a list of what videos a given
member has not rented, try:

SELECT video_name
FROM video
WHERE video_name NOT IN
(SELECT borrows_video_name
FROM borrows
WHERE borrows_member_name = [Name of Member?])

That'll prompt you for a member name, but you could always replace
that with a reference to the member_name on your form:

SELECT video_name
FROM video
WHERE video_name NOT IN
(SELECT borrows_video_name
FROM borrows
WHERE borrows_member_name = Forms!NameOfForm!NameOfControl)
Click to expand...

If you do that, be sure to define the form reference as a parameter,
because if you don't, the results can be unpredictable in A2K3 (at
least -- it may be a problem in other Jet 4 versions of Access, but
that's where I encountered it).
Click to expand...

Good point, although not particularly relevant in this case since the
query's going against SQL Server.
 
Back
Top