Problem with query - I am sure this should be easy 1

[redav]

Hi

I am trying to do a query which is pretty straightforward but I'm having real problems getting the correct results.

Just to quickly surmise the problem I have an 'employees' table with a unique ID, a 'reports' table with a unique ID and also a lookup table which links the two together.

It looks like this

'employees'
ID Name
1 Smith
2 Jones

'reports'
ID ReportName
1 Starting Certificate
2 Leaving Certificate

'lk_employees_reports'
ID fk_employees_id fk_reports_id DateProduced
1 1 2 01SEP2004
2 1 1 02SEP2004
3 2 2 03SEP2004

Each time a report is printed a record is inserted into 'lk_employees_reports'.

I need to select all employees from the 'employee' table and include the DateProduced field if there is an entry for the specific reportID.

So if the selected ReportID is 1 it shows all employees and includes the DateProduced fields for employee 1 in this example.

I have tried loads of different joins and things and cannot get all employees back (just once) and include the DateProduced when appropriate.

Any help much appreciated.

Thanks

 

[RickSpr]

It seems like you just want an outer join.

SELECT [Name], [DateProduced]
FROM [employees] LEFT JOIN [lk_employees_reports]
ON [employees].[ID] = [lk_employees_reports].[fk_employees_id]

If that's not what you need, tell us what's missing, or what's present that you didn't want.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[redav]

Thanks very much for the reply.

Apologies, I missed one important point in my original post.

I want to return all employees every time and only include the DateProduced if there is one for the selected report.
So in the example above if the reportID is 1 then both employees are to be returned but only employeeID 1 has a DateProduced.

I have tried including WHERE lk_employees_reports.lk_reports_id Is Null OR lk_employees_reports.lk_reports_id = [SELECTED_REPORT_ID]

but if the employee has had another report produced previously then they are excluded with this particular clause.

Without this clause I get the employees returned multiple times, once for each report they have had produced.

Hope this makes the problem clearer.

Cheers

 

[lespaul]

So in the example above if the reportID is 1 then both employees are to be returned but only employeeID 1 has a DateProduced.

In the example above, all the records have a DateProduced?

Why don't you give us another example and which records you want returned?

Leslie

 

[RickSpr]

I want to return all employees every time and only include the DateProduced if there is one for the selected report.

That's exactly what an outer join does. In fact, if you bring up the Join Properties dialog (by double-clicking the join line in the query diagram), the wording for the second option is very much like what you said.

Did you even try my suggestion? If so, what was wrong with the results? If not....

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[redav]

Thanks for the replies

I have already tried an outer join but it doesn't work. I think it should be an outer join but there must be more to get the desired results.

The problem is as I explained above, I have just copied the query you supplied into Access and as I said each employee is returned multiple times(once for each time they have had a report produced). I need to only return each employee once but when I include the WHERE clause above I do not get all employees back (if the employee has had another report produced apart from reportID 1 then they are not included in the results, I can see this is because ReportID is neither null or 1).

lespaul. If you look at the example only EmployeeID 1 has had ReportID 1 produced already so if the query is for reportID 1 then both employees should be returned, including the DateProduced for ReportID 1 if applicable. Only EmployeeID 1 has a DateProduced for this ReportID.

I can understand the confusion because it does initally seem very straighforward but if you set up the tables and stick the query in you will see that there is a bit more to it.

I am sure that this has a simple solution, it is just a case of knowing what that is

Many thanks for the help, it is much appreciated.

 

[RickSpr]

You want the employee ID and, if they have ever had the report produced, you also want the date. But if they have had the report produced more than once, you don't want a row for each date, because you want each employee just once. So in this situation, which date do you want?

Try this query. I think it will return what you want, using the latest DateProduced. You can change MAX to MIN for the earliest date, or to FIRST or LAST if you don't care which date is returned.
SELECT [Name], MAX([DateProduced]) AS LastDateProduced
FROM employees LEFT JOIN lk_employees_reports ON employees.ID = fk_employees_id
WHERE fk_reports_id=[SELECTED_REPORT_ID]
GROUP BY [Name]
UNION
SELECT [Name], Null
FROM employees
WHERE NOT EXISTS
(SELECT 1 FROM lk_employees_reports
WHERE fk_employees_id = employees.ID
AND fk_reports_id = [SELECTED_REPORT_ID]);

By the way, you do realize any query like this will not be updatable, don't you?

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[redav]

Thanks again for the help.

I have just copied your query into Access and I get the error:-

'Join expression not supported'

Multiple entries for the same employee and reports will not be a problem because if a record already exists for the specified report the DateProduced is updated rather than a new record being inserted.

Please could you clarify what you mean when you say this query will not be updateable.

Cheers

 

[lespaul]

Please could you clarify what you mean when you say this query will not be updateable.

If you take the results from this query and display them in a form, you will not be able to change any of the information displayed, it is a READ ONLY query result.

Leslie

 

[RickSpr]

I thought it was very strange that you get the 'Join expression not supported' message, because I knew I had tested it. It turns out that I had made what I thought was an inconsequential change to my copy, after I pasted it here. Apparently it wasn't inconsequential after all, but I don't see why.

The problem is being caused by the first FROM clause. The field fk_employees_id must, for some reason, be qualified with the table name:
lk_employees_reports.fk_employees_id

"The query will not be updateable" means you won't be able to update the records in the query datasheet, nor on a form that uses the query as a record source. Until this last post, you haven't said anything about needing to update/add records via this query.

If you need to update records, we've got a problem. I don't think there's any way to get this particular recordset with an updateable query. The problem is that you want to test criteria against lk_employees_reports, when there may not be any record in that table for the employee. That automatically disqualifies any employees with no records in the lookup table. If you use the UNION query to add those employees' records back in, the recordset is not updateable.

I hesitate to suggest it, but I can see one possible solution. You could add records to lk_employees_reports so that every combination of employee ID and report ID is represented. Set the DateProduced to Null for employees who have never actually produced the report. Your query would then be easy:
SELECT [Name], [DateProduced]
FROM [employees] LEFT JOIN [lk_employees_reports]
ON [employees].[ID] = [lk_employees_reports].[fk_employees_id]
WHERE [lk_employees_reports].[fk_reports_id]=[SELECTED_REPORT_ID]

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[redav]

Hi

Thanks very much for the help. The query works great.

I don't need to update using this query as each time a report or reports are printed the relevant record in lk_employees_reports is updated or if it does not exists for the printed report a record is inserted.

Just one more minor adjustment though if I may.

I have added another field to lk_employees_reports which is 'YearEnd'. This is because the reports will need to be produced for certain employees each year and I need a record of which reports have been produced for which employees and for which year. This hold at the moment either 2004 or 2005.

If a DateProduced exists for the specified year then it is to be included otherwise then the DateProduced should be null. Again all employees need to be returned.

I have put a WHERE clause in the first SELECT for the year but it excluded other records so all employees where not returned.

Where should this WHERE clause go?

Thanks again for the help, I have been messing round with this on and off for over a week now and this has got me moving with it again.

Cheers

 

[RickSpr]

I'm afraid I can't make out what you want. Do you just want an additional criterion for selecting matching rows in lk_employees_reports? You said "I have put a WHERE clause in the first SELECT for the year..." but the first select already had a WHERE clause. Are you saying you added "AND [lk_employees_reports].[YearEnd]=[SELECTED_YEAR]" to the WHERE clause? That's what I would have suggested, but you said it "excluded other records." That doesn't tell me what you want in the result set.

An example would help me understand, but we need more sample data than what you started this thread with; it doesn't provide enough possibilities. Add more data and show me the result set you want.

Another way would be for you to describe the results you want procedurally, for example:
1. Make a list of all names in the 'employees' table.
2. For each employee name, look in 'lk_employees_reports' for a row with fk_reports_id = 1 and YearEnd = 2005. If a row is found, copy the DateProduced to the list next to the employee name.
etc.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[redav]

Thanks for the reply.

Sorry, yes I did put add the AND onto the WHERE as you stated above.

Here is the amended table:-

lk_employees_reports
ID fk_employees_id fk_reports_id YearEnd DateProduced
1 1 1 2004 14/09/2004
2 1 2 2004 14/09/2004
3 2 1 2005 14/09/2004

So as before I want all employees to be returned and the DateProduced if the employee has had the specific report produced for the specified year.

So basically, as well as the reportID I now want to specify a year.

If the 'employees' and 'reports' tables remain as before and we use the new lookup table above for an example, if I specify reportID 1 and YearEnd 2005 then both employees should be returned but only employeeID 2 has a DateProduced (only employeeID 2 has had reportID 1 produced for the Year 2005, the DateProduced for any other years are not to be returned)

The example you gave of a procedural description is actually exactly what I need.

I hope I have explained this a little better this time. If not then please let me know.

Cheers

 

[RickSpr]

It seems to me that adding "AND [lk_employees_reports].[YearEnd]=[SELECTED_YEAR]" to the WHERE clause should have given you that, but you said it "excluded other records". What records did it exclude that you wanted it to include?

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[redav]

Sorry, my mistake, the year stuff works great now.

Thanks to you and lespaul for your help with this. I now have it working just right.