Okay, I finally gave in again, I ha

[zippy66]

Okay, I finally gave in again, I have been checking forums to see if any light can be shed on my problem but no joy. I want to pull data together from three different tables, eg. course date and hours, shift date and hours, leave date and hours, to do this I have used unions to join the three relevant tables together with an inner join to the staff table to fetch the details of the contracted hours per week for each staff. Basically I want to provide the status of hours for staff on a weekly basis, eg debit/credit each week. I can get the correct details if I use a Union query first, eg.

SELECT DISTINCTROW shift_entry.staff_id AS [Nurse/Carer], shift_entry.shift_date AS [Date], shift_entry.total_hours_worked AS [Total Hrs], staff.contracted_hours AS [ContractedHrs]
FROM staff INNER JOIN shift_entry ON staff.staff_id = shift_entry.staff_id
UNION
SELECT DISTINCTROW course_registration.staff_id, course_registration.course_date, course_registration.total_course_time, staff.contracted_hours
FROM staff INNER JOIN course_registration ON staff.staff_id = course_registration.staff_id
UNION
SELECT DISTINCTROW leave_entry.staff_id, leave_entry.leave_date, leave_entry.leave_hours, staff.contracted_hours
FROM staff INNER JOIN leave_entry ON staff.staff_id = leave_entry.staff_id;

and then run another query to get the relevant data eg

SELECT [Total Hours Query].[Nurse/Carer], Sum([Total Hours Query].[Total Hrs]) AS [SumOfTotal Hrs], [Total Hours Query].ContractedHrs, ((Min([Total Hours Query].Date)-Max([Total Hours Query].Date))*(ContractedHrs/7)) AS MaxOfDate
FROM [Total Hours Query]
GROUP BY [Total Hours Query].[Nurse/Carer], [Total Hours Query].ContractedHrs;

The data, by the way will be retrieved by running reports with between....and to dates.

I wanted to nest the Union query with the second query and use the GROUP BY clause at the end but I am getting an error in the FROM clause. Faulty sql is below:

SELECT [Nurse/Carer], Sum([Total Hrs]) AS [SumOfTotal Hrs], ContractedHrs, ((Min(Date)-Max(Date))*(ContractedHrs/7)) AS [MaxOfDate]
FROM (SELECT DISTINCTROW shift_entry.staff_id AS [Nurse/Carer], shift_entry.shift_date AS [Date], shift_entry.total_hours_worked AS [Total Hrs], staff.contracted_hours AS [ContractedHrs]
FROM staff INNER JOIN shift_entry ON staff.staff_id = shift_entry.staff_id
UNION
SELECT DISTINCTROW course_registration.staff_id, course_registration.course_date, course_registration.total_course_time, staff.contracted_hours
FROM staff INNER JOIN course_registration ON staff.staff_id = course_registration.staff_id
UNION
SELECT DISTINCTROW leave_entry.staff_id, leave_entry.leave_date, leave_entry.leave_hours, staff.contracted_hours
FROM staff INNER JOIN leave_entry ON staff.staff_id = leave_entry.staff_id)
GROUP BY [Nurse/Carer], ContractedHrs;

I am probably even going about this totally the wrong way and there may even be a better way to go about this, but Access is new to me and I am still learning. (by my mistakes!).
Any help would be great,

thanks, Zippy

 

[Nowell]

Hi,

You appear to have a not needed bracket here:


FROM staff INNER JOIN leave_entry ON staff.staff_id = leave_entry.staff_id)

This is on your last FROM statement

 

[zippy66]

Hi Nowell,

Many thanks for the reply, the problem still persists. The last bracket is actually the closing bracket for the nested SELECT DISTINCTROW statements and relevant UNION statement. Although, the problem may be that there is something I have missed or is in the wrong place, or it is just not possible at all to nest Unions within another statement. Each individual Union statment will run with a Group By clause, but this is not the result I am after, however, it may be that the Unions cannot be grouped altogether. Baffles me!

Thanks again
Zippy

 

[r937]

zippy, remove the DISTINCTROW keywords, i don't think you need them

change UNION to UNION all to avoid the sort, which is unnecessary

then in your large query, add "as X" after the closing parenthesis containing your union query

this gives an alias name to the derived table produced by the parenthesized union query

rudy

http://r937.com/

SQL Consulting

 

[zippy66]

Thanks for the reply Rudy,

This still doesn't work I am afraid. The same error is returned.

Cheers,
Zippy