Re Post - Problem With Same-table Join

Y

yunno_who

I apologise for the re-post, but still cannot get this to work, even
with David's response - does anyone have any other ideas?

Thanks.




Hi there


I don't seem to want to do anything that's very different to the host
of examples of joing a table to itself that I've found in this
newsgroup, but obviously I'm missing something.


I have a table that contains employee records for every month of the
year. I'm simply trying to find out the IDs of those employees who
left, for example, between May and June.


I have managed to get it working as separate queries, but need to get
it into 1 query to work via VBA.


This is as far as I've got:


SELECT
(SELECT Aggregated_2006_YTD_Salary_Subset_Table.Employee_ID
FROM Aggregated_2006_YTD_Salary_Subset_Table
WHERE (((Month([Update_Date]))=5))) AS Month_5
FROM (SELECT Aggregated_2006_YTD_Salary_Subset_Table.Employee_ID
FROM Aggregated_2006_YTD_Salary_Subset_Table
WHERE (((Month([Update_Date]))=6))) AS Month_6
RIGHT JOIN Month_5 ON Month_6.Employee_ID = Month_5.Employee_ID
WHERE (((Month_6.Employee_ID) Is Null));


I would much appreciate any help you can give.


Reply »


From: David Cox - view profile
Date: Sun, Aug 27 2006 5:44 pm
Email: "David Cox" <[email protected]>
Groups: microsoft.public.access.queries
Not yet ratedRating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse | Find messages by this author


In a hurry:-

I would simply do a group query to get maximum date for each employee
and
use criteria between ....







- Hide quoted text -
- Show quoted text -
Hi there

I don't seem to want to do anything that's very different to the host
of examples of joing a table to itself that I've found in this
newsgroup, but obviously I'm missing something.

I have a table that contains employee records for every month of the
year. I'm simply trying to find out the IDs of those employees who
left, for example, between May and June.

I have managed to get it working as separate queries, but need to get
it into 1 query to work via VBA.

This is as far as I've got:

SELECT
(SELECT Aggregated_2006_YTD_Salary_Subset_Table.Employee_ID
FROM Aggregated_2006_YTD_Salary_Subset_Table
WHERE (((Month([Update_Date]))=5))) AS Month_5
FROM (SELECT Aggregated_2006_YTD_Salary_Subset_Table.Employee_ID
FROM Aggregated_2006_YTD_Salary_Subset_Table
WHERE (((Month([Update_Date]))=6))) AS Month_6
RIGHT JOIN Month_5 ON Month_6.Employee_ID = Month_5.Employee_ID
WHERE (((Month_6.Employee_ID) Is Null));

I would much appreciate any help you can give.
Click to expand...



Reply » Rate this post: Text for clearing space


From: (e-mail address removed) - view profile
Date: Sun, Aug 27 2006 11:50 pm
Email: (e-mail address removed)
Groups: microsoft.public.access.queries
Not yet ratedRating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse | Find messages by this author


I'm sorry - your answer is a little too cryptic for my brain - could
you elucidate a little further please?
 
K

KARL DEWEY

TRy this ---
SELECT Aggregated_2006_YTD_Salary_Subset_Table.Employee_ID
FROM Aggregated_2006_YTD_Salary_Subset_Table
WHERE [Update_Date] Between #5/1/2006# And #6/30/2006#;


[email protected] said:
I apologise for the re-post, but still cannot get this to work, even
with David's response - does anyone have any other ideas?

Thanks.




Hi there


I don't seem to want to do anything that's very different to the host
of examples of joing a table to itself that I've found in this
newsgroup, but obviously I'm missing something.


I have a table that contains employee records for every month of the
year. I'm simply trying to find out the IDs of those employees who
left, for example, between May and June.


I have managed to get it working as separate queries, but need to get
it into 1 query to work via VBA.


This is as far as I've got:


SELECT
(SELECT Aggregated_2006_YTD_Salary_Subset_Table.Employee_ID
FROM Aggregated_2006_YTD_Salary_Subset_Table
WHERE (((Month([Update_Date]))=5))) AS Month_5
FROM (SELECT Aggregated_2006_YTD_Salary_Subset_Table.Employee_ID
FROM Aggregated_2006_YTD_Salary_Subset_Table
WHERE (((Month([Update_Date]))=6))) AS Month_6
RIGHT JOIN Month_5 ON Month_6.Employee_ID = Month_5.Employee_ID
WHERE (((Month_6.Employee_ID) Is Null));


I would much appreciate any help you can give.


Reply »


From: David Cox - view profile
Date: Sun, Aug 27 2006 5:44 pm
Email: "David Cox" <[email protected]>
Groups: microsoft.public.access.queries
Not yet ratedRating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse | Find messages by this author


In a hurry:-

I would simply do a group query to get maximum date for each employee
and
use criteria between ....







- Hide quoted text -
- Show quoted text -
Hi there

I don't seem to want to do anything that's very different to the host
of examples of joing a table to itself that I've found in this
newsgroup, but obviously I'm missing something.

I have a table that contains employee records for every month of the
year. I'm simply trying to find out the IDs of those employees who
left, for example, between May and June.

I have managed to get it working as separate queries, but need to get
it into 1 query to work via VBA.

This is as far as I've got:

SELECT
(SELECT Aggregated_2006_YTD_Salary_Subset_Table.Employee_ID
FROM Aggregated_2006_YTD_Salary_Subset_Table
WHERE (((Month([Update_Date]))=5))) AS Month_5
FROM (SELECT Aggregated_2006_YTD_Salary_Subset_Table.Employee_ID
FROM Aggregated_2006_YTD_Salary_Subset_Table
WHERE (((Month([Update_Date]))=6))) AS Month_6
RIGHT JOIN Month_5 ON Month_6.Employee_ID = Month_5.Employee_ID
WHERE (((Month_6.Employee_ID) Is Null));

I would much appreciate any help you can give.
Click to expand...



Reply » Rate this post: Text for clearing space


From: (e-mail address removed) - view profile
Date: Sun, Aug 27 2006 11:50 pm
Email: (e-mail address removed)
Groups: microsoft.public.access.queries
Not yet ratedRating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse | Find messages by this author


I'm sorry - your answer is a little too cryptic for my brain - could
you elucidate a little further please?
Click to expand...
 
Y

yunno_who

Thanks for taking the time to respond Karl.

Your suggestion will, I think, only give me those employees who were
present in both May and June - I need to know the IDs of those
employees who were present in May, but not in June, i.e. who left
between May and June.

I understand how to do this between two tables, or two queries, but
it's when the data for all months is in the same table that I have the
problem.

Thanks for your help.

Neil
 
D

David Cox

Q_maxdate:-

SELECT Table1.name1, Max(Table1.date1) AS maxdate
FROM Table1
GROUP BY Table1.name1
ORDER BY Table1.name1;

Q_leavers:

SELECT Q_maxdate.name1, Q_maxdate.maxdate
FROM Q_maxdate
WHERE (((Q_maxdate.maxdate) Between #3/1/2000# And #4/30/2000#));

I apologise for the re-post, but still cannot get this to work, even
with David's response - does anyone have any other ideas?

Thanks.




Hi there


I don't seem to want to do anything that's very different to the host
of examples of joing a table to itself that I've found in this
newsgroup, but obviously I'm missing something.


I have a table that contains employee records for every month of the
year. I'm simply trying to find out the IDs of those employees who
left, for example, between May and June.


I have managed to get it working as separate queries, but need to get
it into 1 query to work via VBA.


This is as far as I've got:


SELECT
(SELECT Aggregated_2006_YTD_Salary_Subset_Table.Employee_ID
FROM Aggregated_2006_YTD_Salary_Subset_Table
WHERE (((Month([Update_Date]))=5))) AS Month_5
FROM (SELECT Aggregated_2006_YTD_Salary_Subset_Table.Employee_ID
FROM Aggregated_2006_YTD_Salary_Subset_Table
WHERE (((Month([Update_Date]))=6))) AS Month_6
RIGHT JOIN Month_5 ON Month_6.Employee_ID = Month_5.Employee_ID
WHERE (((Month_6.Employee_ID) Is Null));


I would much appreciate any help you can give.


Reply »


From: David Cox - view profile
Date: Sun, Aug 27 2006 5:44 pm
Email: "David Cox" <[email protected]>
Groups: microsoft.public.access.queries
Not yet ratedRating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse | Find messages by this author


In a hurry:-

I would simply do a group query to get maximum date for each employee
and
use criteria between ....







- Hide quoted text -
- Show quoted text -
Hi there

I don't seem to want to do anything that's very different to the host
of examples of joing a table to itself that I've found in this
newsgroup, but obviously I'm missing something.

I have a table that contains employee records for every month of the
year. I'm simply trying to find out the IDs of those employees who
left, for example, between May and June.

I have managed to get it working as separate queries, but need to get
it into 1 query to work via VBA.

This is as far as I've got:

SELECT
(SELECT Aggregated_2006_YTD_Salary_Subset_Table.Employee_ID
FROM Aggregated_2006_YTD_Salary_Subset_Table
WHERE (((Month([Update_Date]))=5))) AS Month_5
FROM (SELECT Aggregated_2006_YTD_Salary_Subset_Table.Employee_ID
FROM Aggregated_2006_YTD_Salary_Subset_Table
WHERE (((Month([Update_Date]))=6))) AS Month_6
RIGHT JOIN Month_5 ON Month_6.Employee_ID = Month_5.Employee_ID
WHERE (((Month_6.Employee_ID) Is Null));

I would much appreciate any help you can give.
Click to expand...



Reply » Rate this post: Text for clearing space


From: (e-mail address removed) - view profile
Date: Sun, Aug 27 2006 11:50 pm
Email: (e-mail address removed)
Groups: microsoft.public.access.queries
Not yet ratedRating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse | Find messages by this author


I'm sorry - your answer is a little too cryptic for my brain - could
you elucidate a little further please?
 
J

John Spencer

Try the following

SELECT Month_5.Employee_ID
FROM
(SELECT Employee_ID
FROM Aggregated_2006_YTD_Salary_Subset_Table as A
WHERE Month([Update_Date])=5) AS FirstMonth
LEFT JOIN
(SELECT Employee_ID
FROM Aggregated_2006_YTD_Salary_Subset_Table as B
WHERE Month([Update_Date])=6) AS SecondMonth
ON FirstMonth.Employee_ID = SecondMonth.Employee_ID
WHERE SecondMonth.Employee_ID Is Null

That should return a list of all employee ID's that were present in the
FirstMonth and are not present in SecondMonth.

I apologise for the re-post, but still cannot get this to work, even
with David's response - does anyone have any other ideas?

Thanks.




Hi there


I don't seem to want to do anything that's very different to the host
of examples of joing a table to itself that I've found in this
newsgroup, but obviously I'm missing something.


I have a table that contains employee records for every month of the
year. I'm simply trying to find out the IDs of those employees who
left, for example, between May and June.


I have managed to get it working as separate queries, but need to get
it into 1 query to work via VBA.


This is as far as I've got:


SELECT
(SELECT Aggregated_2006_YTD_Salary_Subset_Table.Employee_ID
FROM Aggregated_2006_YTD_Salary_Subset_Table
WHERE (((Month([Update_Date]))=5))) AS Month_5
FROM (SELECT Aggregated_2006_YTD_Salary_Subset_Table.Employee_ID
FROM Aggregated_2006_YTD_Salary_Subset_Table
WHERE (((Month([Update_Date]))=6))) AS Month_6
RIGHT JOIN Month_5 ON Month_6.Employee_ID = Month_5.Employee_ID
WHERE (((Month_6.Employee_ID) Is Null));


I would much appreciate any help you can give.


Reply »


From: David Cox - view profile
Date: Sun, Aug 27 2006 5:44 pm
Email: "David Cox" <[email protected]>
Groups: microsoft.public.access.queries
Not yet ratedRating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse | Find messages by this author


In a hurry:-

I would simply do a group query to get maximum date for each employee
and
use criteria between ....







- Hide quoted text -
- Show quoted text -
Hi there

I don't seem to want to do anything that's very different to the host
of examples of joing a table to itself that I've found in this
newsgroup, but obviously I'm missing something.

I have a table that contains employee records for every month of the
year. I'm simply trying to find out the IDs of those employees who
left, for example, between May and June.

I have managed to get it working as separate queries, but need to get
it into 1 query to work via VBA.

This is as far as I've got:

SELECT
(SELECT Aggregated_2006_YTD_Salary_Subset_Table.Employee_ID
FROM Aggregated_2006_YTD_Salary_Subset_Table
WHERE (((Month([Update_Date]))=5))) AS Month_5
FROM (SELECT Aggregated_2006_YTD_Salary_Subset_Table.Employee_ID
FROM Aggregated_2006_YTD_Salary_Subset_Table
WHERE (((Month([Update_Date]))=6))) AS Month_6
RIGHT JOIN Month_5 ON Month_6.Employee_ID = Month_5.Employee_ID
WHERE (((Month_6.Employee_ID) Is Null));

I would much appreciate any help you can give.
Click to expand...



Reply » Rate this post: Text for clearing space


From: (e-mail address removed) - view profile
Date: Sun, Aug 27 2006 11:50 pm
Email: (e-mail address removed)
Groups: microsoft.public.access.queries
Not yet ratedRating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse | Find messages by this author


I'm sorry - your answer is a little too cryptic for my brain - could
you elucidate a little further please?
 
Y

yunno_who

Hi John - you're my hero - many thanks indeed, and so straight-forward
in the end (I won't say simple).

For the sake of those other lowly mortals, such as myself, who may come
this way and slavishly follow the letter of your post, there is one
small correction:

SELECT FirstMonth.Employee_ID ////corrected line
FROM
(SELECT Employee_ID
FROM Aggregated_2006_YTD_Salary_Subset_Table as A
WHERE Month([Update_Date])=5) AS FirstMonth
LEFT JOIN
(SELECT Employee_ID
FROM Aggregated_2006_YTD_Salary_Subset_Table as B
WHERE Month([Update_Date])=6) AS SecondMonth
ON FirstMonth.Employee_ID = SecondMonth.Employee_ID
WHERE SecondMonth.Employee_ID Is Null

Many thanks again John.
 
J

John Spencer

Sorry about the typo. I started out using Month_5 and Month_6 and then
decided to make it more generic with FirstMonth and Secondmonth. Missed
that correction, but happily you figured out my error.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Cannot get same-table join to work 2
Occurances happening at the same time query 1
A problem occurred running while microsoft access was communicating with OLE server or ActiveX Control. 0
Update with subquery[complex] 8
Id number not found 1
Left Join? 9
DCount with a Context 2
Add new records into source table on Form 8

Top