Not in past history

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

Guest

I posted this problem several days ago, and tried the suggestions but still
can't get it to work. Here was the original posting:
I have two access 2003 tables. One is Client and contains name, index#, and
age... The other is HistoryOfWhoClientDated and contains index# of client and
index# of someone they dated. I want to list every client and give a list of
at least 4 people they have NOT dated before ( from HistoryOfWhoClientDated)
and within a specified age group ( i am actually going to have compare 6 more
fields - but if i can figure this part, i can figure the rest). I know i can
use topvalue somehow but i don't know how to go through every client, match 4
that weren't dated before (as recorded in the history table). Any thoughts
on how to accomplish this?

Of the two suggestins, I got closest to working was this query, but because
i am not knowledgable in sql i am having a problem.
SELECT Client.ID AS dater, DatedHistory.id AS datee
FROM Client AS client, DatedHistory AS datedhistory
WHERE (Client.ID <>[datedhistory].[id])
And Client.ID Not In (
select datedhistory.id
from datedhistory
where(datedhistory.datedid=datedhistory.id)
)
ORDER BY Client.ID;

Can you help? It is still producing those that hav ealready been dated.

thanks!
 
I don't have an answer to your issue (except to use an UNMATCHED query) but
I do have a question...

Why are you storing "age"? Age changes. You should store birthdate in your
table and then calculate age on your queries, reports, and forms.

The preferred calculation is....

=DateDiff("yyyy",[Birthdate],Date())+(Format([Birthdate],"mmdd")>Format(Date
(),"mmdd"))
 
You can start this out by creating a query that maps each client against
every other client:

Select T1.ClientID, T2.ClientId as PotentialDate
FROM tblClient T1, tblClientT2
WHERE T1.ClientID <> T2.ClientID
AND T2.Gender = T1.PreferredGender
AND T1.Gender = T2.PreferredGender

Name this Query 1.

Then, use Query1 in combination with HistoryOfWhoClientDated as follows:

SELECT Q.ClientID, Q.PotentialDate
FROM Query1
LEFT JOIN HistoryOfWhoClientDated H
ON Q.ClientID = H.ID
AND Q.PotentialDate = H.Datee
WHERE H.ClientID IS NULL

This (call it Query2) will get you every combination of people that meet
gender preferences and have not already dated. To get the top four
potential dates for a particular Client, try the following. Assuming that
ClientIDs are assigned sequentially, this would get you the four people that
have been clients the longest, so you might want to consider inverting the
sort order.

SELECT TOP 4 ClientID, PotentialDate
FROM Query2
WHERE ClientID = [Which Client]
ORDER BY PotentialDate

HTH
Dale
 
Back
Top