query problem

  • Thread starter Thread starter matt hallam
  • Start date Start date
M

matt hallam

I have two tables and I want to compare the name fields within them:

table: allusers
Name - in the format LastName FirstName
Email

table: leavers:
Last Name
LeaverDate

I want to write a query such that I can get Name, Email & LeaverDate in
table, but I can't work out the necessary query to "link" the tables. I've
been tryinbg stuff like:

SELECT allusers.Name, leavers.LeaverDate FROM allusers, leavers WHERE Name
= Any (SELECT leavers.[Last Name] FROM leavers);

but doesn't like it. Is it possible to do this sort of query? Or will I
needs nested recordsets?

cheers in advance
Matthew
 
Does not look like a very good data structure. What if you have more than
one "smith"?

Why not just add the termination date to the "allusers" table?

I can't see how your data structure would be useful since the leavers can't
be easily linked to the allusers. I assume when you pull reports, forms,
and queries, that there is no way for you to elliminate terminated
employees. Typically, you would have some method to allow only active
people to be included in reports. The way I always do that is by including
a termination date in my table, or an 'inactive' checkbox.
 
sorry, should have said that tables are from two data sources that I'm
trying to match


Rick B said:
Does not look like a very good data structure. What if you have more than
one "smith"?

Why not just add the termination date to the "allusers" table?

I can't see how your data structure would be useful since the leavers can't
be easily linked to the allusers. I assume when you pull reports, forms,
and queries, that there is no way for you to elliminate terminated
employees. Typically, you would have some method to allow only active
people to be included in reports. The way I always do that is by including
a termination date in my table, or an 'inactive' checkbox.

--
Rick B



matt hallam said:
I have two tables and I want to compare the name fields within them:

table: allusers
Name - in the format LastName FirstName
Email

table: leavers:
Last Name
LeaverDate

I want to write a query such that I can get Name, Email & LeaverDate in
table, but I can't work out the necessary query to "link" the tables. I've
been tryinbg stuff like:

SELECT allusers.Name, leavers.LeaverDate FROM allusers, leavers WHERE Name
= Any (SELECT leavers.[Last Name] FROM leavers);

but doesn't like it. Is it possible to do this sort of query? Or will I
needs nested recordsets?

cheers in advance
Matthew
Click to expand...
Click to expand...
 
SELECT allusers.Name, leavers.LeaverDate
FROM allusers INNER JOIN leavers
ON allusers.Name = leavers.[Last Name]
 
I have two tables and I want to compare the name fields within
them:

table: allusers
Name - in the format LastName FirstName
Email

table: leavers:
Last Name
LeaverDate

I want to write a query such that I can get Name, Email &
LeaverDate in table, but I can't work out the necessary query
to "link" the tables. I've been tryinbg stuff like:

SELECT allusers.Name, leavers.LeaverDate FROM allusers,
leavers WHERE Name = Any (SELECT leavers.[Last Name] FROM
leavers);

but doesn't like it. Is it possible to do this sort of query?
Or will I needs nested recordsets?

cheers in advance
Matthew
Click to expand...

You'll need to break up the fullname field into a lastname part,
using a userdefined function or some messy sql

SELECT allusers.Name,
left(allusers.Name,
iif(instr(" ",allusers.Name)>0,
instr(" ",allusers.Name)-1,
len(allusers.name)) as lname,
leavers.leaverdate
FROM allusers, leavers
WHERE left(allusers.Name,
iif(instr(" ",allusers.Name)>0,
instr(" ",allusers.Name)-1,
len(allusers.name)) = leavers.[last Name]
 
cheers, works a treat

Matthew

Bob Quintal said:
I have two tables and I want to compare the name fields within
them:

table: allusers
Name - in the format LastName FirstName
Email

table: leavers:
Last Name
LeaverDate

I want to write a query such that I can get Name, Email &
LeaverDate in table, but I can't work out the necessary query
to "link" the tables. I've been tryinbg stuff like:

SELECT allusers.Name, leavers.LeaverDate FROM allusers,
leavers WHERE Name = Any (SELECT leavers.[Last Name] FROM
leavers);

but doesn't like it. Is it possible to do this sort of query?
Or will I needs nested recordsets?

cheers in advance
Matthew
Click to expand...

You'll need to break up the fullname field into a lastname part,
using a userdefined function or some messy sql

SELECT allusers.Name,
left(allusers.Name,
iif(instr(" ",allusers.Name)>0,
instr(" ",allusers.Name)-1,
len(allusers.name)) as lname,
leavers.leaverdate
FROM allusers, leavers
WHERE left(allusers.Name,
iif(instr(" ",allusers.Name)>0,
instr(" ",allusers.Name)-1,
len(allusers.name)) = leavers.[last Name]
Click to expand...
 
Back
Top