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
--
Ben said:
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,
IIf([Uttryck1]=[Uttryck2],"Don't","Show") AS Uttryck3, (SELECT
Count(*)
FROM
[Test List] q WHERE q.[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));
(in case you would wonder:
[Forms]![SelectProjectforListtocall]![Kombinationsruta0]
this is an external factor to eliminate all calls that are not
related
to
this project. It will only keep the ID's of doctors that have
been
called
for
the chosen project and the complete list of doctors. now I
want
to
remove
the
doubles in the same query by counting the recurring ID's)
--
Novice
:
You would enter the select statement into the SQL view of a
query.
Make
sure
you use your table and field names.
--
Duane Hookom
MS Access MVP
--
And where do I put this ? Sorry for my incompetence
SELECT [Test List].*,
(SELECT Count(*)
FROM [Test List] q
WHERE q.[ID] = [Test List].[ID] ) as TheCount
FROM [Test List];
--
Novice
:
Try
SELECT [Test List].*,
(SELECT Count(*)
FROM [Test List] q
WHERE q.[ID] = [Test List].[ID] ) as TheCount
FROM [Test List];
BTW: I don't care for field/column names that are
properties,
functions,
or
sql aggregates (Name, Count)
--
Duane Hookom
MS Access MVP
--
ID Name Project Result COUNT
17 James Test25 Done 2
19 George
2
25 Ben Test25 Cancelled 1
17 James
2
19 George Test25 Done 2
21 Louis
1
07 Ellen
1
ID - Name - Project - Result are taken from tables and
exist.
Count
is
what
I look for. It gives a number for the amount of times an
ID
(name)
appears
in
the query.
I am going to display them in a continuous form where I
will
filter
them
with VBA on click. I just need to get this count so I
can
remove
all
the
ones
that say more than 1 after. To display all names that
haven't
been
called
yet
for the specific project.
--
