G
Guest
Kind of. Practically it is like this.
When a new project is created, all doctors that qualify to a criteria are
supposed to be called. Now, I try to create a list of doctors that qualify to
this criteria and then when they have been called, I want them off the
list... so that they don't get called twice.
So, in short
if doctor A was called for Project A, he should NOT be in the list
If doctor B is called for project C, he should be in the list
If doctor C was never called, he should be in the list
--
Novice
When a new project is created, all doctors that qualify to a criteria are
supposed to be called. Now, I try to create a list of doctors that qualify to
this criteria and then when they have been called, I want them off the
list... so that they don't get called twice.
So, in short
if doctor A was called for Project A, he should NOT be in the list
If doctor B is called for project C, he should be in the list
If doctor C was never called, he should be in the list
--
Novice
Duane Hookom said:I understood your goal :"Create a list of doctors that haven't been called
yet and this For each Project" to mean that you wanted a list of all
project/doctor combinations that were not in the Called table.
--
Duane Hookom
MS Access MVP
--
Ben said:First of all... if you are fed up with me/it, let me know and I will find
other solutions to solve this problem.
I tried it and it gave me a list of enormously many rows. And it seems to
take away exactly those that i need. As I said before, I succeeded in
creating something like I need but it is just too slow and my goal is to
speed it up. Here's what I had.
3queries.
Number1
SELECT [First name], [Last name], [Male/Female],
[Language ID], specialisme, Phonenumber,
[First name] & " " & [Last name] & " " &
Forms!SelectProjectforListtocall!Kombinationsruta0 AS Uttryck1,
ID, [Country ID], Action, Actionby, Actionforproject
FROM [Läkare List]
Number2
SELECT C.Practician AS Uttryck2, C.[Project ID], L.[First name], L.[Last
name], [L].[First name] & " " & [L].[Last name] & " " & [c].[Project ID]
AS
Uttryck1, C.Called
FROM Called AS C INNER JOIN [Läkare List] AS L ON C.Practician = L.ID
WHERE (((C.Called)=True));
Number 3
SELECT [For list to call1].[First name], [For list to call1].[Last name],
[For list to
call1].[Male/Female], [For list to call1].[Language ID], [For list to
call1].specialisme, [For list to
call1].Phonenumber, [For list to call1].Uttryck1, [For list to
call2].Uttryck1, IIf([For list to
call1.Uttryck1]=[For list to call2].[Uttryck1],"No","Yes")
AS Uttryck2,
[For list to call1].ID, Language.Language, Speciality.Speciality, [For
list
to call1].[Country ID],
[For list to call1].Action, [For list to call1].Actionby, [For list to
call1].Actionforproject
FROM
(([For list to call1]
LEFT JOIN [For list to call2]
ON [For list to call1].Uttryck1 = [For list to call2].Uttryck1)
LEFT JOIN [Language]
ON [For list to call1].[Language ID] = Language.[Language ID])
LEFT JOIN Speciality
ON [For list to call1].specialisme = Speciality.SpecialityID
WHERE ((([For list to call1].specialisme)=
[Forms]![SelectProjectforListtocall]![Kombinationsruta13])
AND (([For list to call1].[Country
ID])=[Forms]![SelectProjectforListtocall]![Kombinationsruta28])
AND ((IIf([For list to call1].[Uttryck1]=[For list to
call2].[Uttryck1],"No","Yes"))="Yes"))
OR ((([For list to call1].specialisme)=
[Forms]![SelectProjectforListtocall]![Kombinationsruta13])
AND ((IIf([For list to call1].[Uttryck1]=[For list to
call2].[Uttryck1],"No","Yes"))="Yes")
AND (([Forms]![SelectProjectforListtocall]![Kombinationsruta28]) Is Null))
OR ((([For list to
call1].specialisme)=[Forms]![SelectProjectforListtocall]![Kombinationsruta13])
AND (([For list to call1].[Country
ID])=[Forms]![SelectProjectforListtocall]![Kombinationsruta28])
AND ((IIf([For list to call1].[Uttryck1]=[For list to
call2].[Uttryck1],"No","Yes"))="Yes"))
OR ((([For list to
call1].specialisme)=[Forms]![SelectProjectforListtocall]![Kombinationsruta13])
AND ((IIf([For list to call1].[Uttryck1]=[For list to
call2].[Uttryck1],"No","Yes"))="Yes")
AND (([Forms]![SelectProjectforListtocall]![Kombinationsruta28]) Is Null))
OR ((([For list to call1].[Country
ID])=[Forms]![SelectProjectforListtocall]![Kombinationsruta28])
AND ((IIf([For list to call1.Uttryck1]=[For list to call2.Uttryck1],
"No","Yes"))="Yes")
AND (([Forms]![SelectProjectforListtocall]![Kombinationsruta13]) Is Null))
OR (((IIf([For list to call1].[Uttryck1]=[For list to
call2].[Uttryck1],"No","Yes"))="Yes")
AND (([Forms]![SelectProjectforListtocall]![Kombinationsruta28]) Is Null)
AND (([Forms]![SelectProjectforListtocall]![Kombinationsruta13]) Is
Null));
Still based on doctors (läkare list or L), Project and Called
--
Novice
Duane Hookom said:If you want a list of Doctors not associated with each project,
-create a cartesian query of all doctors and projects
(qcarDoctorProjects):
SELECT Project.[Project ID], Project.[Project Name],
Doctors.ID, Doctors.[First Name], Doctors.[Last Name]
FROM Project, Doctors;
-create a LEFT JOIN query based on qcarDoctorProjects and the Called
table:
SELECT qcarDoctorProjects.[Project ID], qcarDoctorProjects.[Project
Name],
qcarDoctorProjects.ID, qcarDoctorProjects.[First Name],
qcarDoctorProjects.[Last Name]
FROM qcarDoctorProjects LEFT JOIN
Called ON (qcarDoctorProjects.ID = Called.ID) AND
(qcarDoctorProjects.[Project ID] = Called.[Project ID])
WHERE (((Called.[Call ID]) Is Null));
--
Duane Hookom
MS Access MVP
--
Thanks a million!
Well, For some unexplained reason the result appeared... without new
changes.
But unfortunately, after testing it on my test data... it is not giving
the
expected result nor speed increase...
Any other suggestions to achieve my goals.
Table Doctors (Läkare List)
ID
First Name
Last Name
Table Project
Project ID
Project Name
Table Called
Call ID
ID (Relation to Table Läkare List)
Project ID (Relation to Table Project)
Goal: Create a list of doctors that haven't been called yet and this
For
each Project
--
Novice
:
Open the debug/immediate window (press Ctrl+G) and enter:
? DCount("*","[Test List]")
Replace the 99 with a legitimate ID value in the following:
? DCount("*","[Test List]","[ID] = 99")
--
Duane Hookom
MS Access MVP
--
I tried... no more circular reference but now just #Error as a
result.
I took out all unnecessary stuff now to make it less messy.
SELECT L.ID, Called.Practician, Called.[Project ID],
DCount("*","[Test
List]","[ID] = " & L.[ID]) AS TheCount
FROM [Läkare List] AS L LEFT JOIN Called ON L.ID =
Called.Practician;
Yes, ID is numeric and is also an autonumber... could that be a
reason
that
this doesn't work ?
Any other ways to exclude double appearing Records from a query
without
creating a new query?
PS, I have something that works but it takes about 8minutes to load
because
it loads in 3 different queries towards 1 result...
--
Novice
:
DCount() is horribly slow but you can try the following assuming
the
ID
field is numeric.
SELECT L.ID, L.[Country ID], L.Region,
L.[Dokter info ID], L.[Name Practice/Hospital],
L.[First name], L.[Last name], L.Title, L.[Male/Female],
L.[Language ID], L.specialisme, L.[Country number],
L.Phonenumber, L.Faxnumber, Called.Practician, Called.[Project ID],
[Practician] &
[Forms]![SelectProjectforListtocall]![Kombinationsruta0]
AS
Uttryck1,
[ID] & [Forms]![SelectProjectforListtocall]![Kombinationsruta0] AS
Uttryck2,
IIf([Praction]=[ID],"Don't","Show") AS Uttryck3,
DCount("*","[Test List]","[ID] = " & L.[ID] ) AS TheCount
FROM [lÄKARE LIST] AS L LEFT JOIN Called
ON L.ID = Called.Practician
WHERE Called.[Project
ID]=[Forms]![SelectProjectforListtocall]![Kombinationsruta0]
Or Called.[Project ID] Is Null;
--
Duane Hookom
MS Access MVP
--
Thanks for being so patient with me.
Well, that doesn't seem to be it... Sorry, I should have written
the
full
error message. It says " Circular reference caused by 'Test
List' "
Isn't there something like dcount that can do like this...
Count(all records in Column [ID], from query [Test List], where
the
[ID]
from current record is equal)
all in query [test list]
--
Novice
:
I think the issue is because you create a column named Uttryck2
and
then
use
it in another expression. I avoid this at all cost.
--
Duane Hookom
MS Access MVP
--
I applied it to my existing query sql but I must be doing
something
wrong
(circular referens error message shown).
Here is my SQL for Query [Test List]
SELECT L.ID, L.[Country ID], L.Region, L.[Dokter info ID],
L.[Name
Practice/Hospital], L.[First name], L.[Last name], L.Title,
L.[Male/Female],
L.[Language ID], L.specialisme, L.[Country number],
L.Phonenumber,
L.Faxnumber, Called.Practician, Called.[Project ID],
[Practician]
&
[Forms]![SelectProjectforListtocall]![Kombinationsruta0] AS
Uttryck1,
[ID]
&
[Forms]![SelectProjectforListtocall]![Kombinationsruta0] AS
Uttryck2,