Missing Data in Query

[Chelle8263]

Hello,

I am missing data in my payroll query and I am not sure why. Here is my SQL statement:

SELECT Payroll.[Emp Name], Payroll.[Piece Price ID], Payroll.Date, ([Lunch Out]-[Lunch In])*24 AS Lunch, (([End Time]-[StartTime])*24)-[Lunch] AS TotalHours, [TotalHours]*[Hourly Rate] AS [Day Amount], [Projected Piece Total].[Projected Total], [Projected Total]-[Day Amount] AS [Piece Price Balance]
FROM (Employees INNER JOIN Payroll ON Employees.[Employee ID]=Payroll.[Emp Name]) INNER JOIN [Projected Piece Total] ON (Payroll.[Piece Price ID]=[Projected Piece Total].[Piece Price ID]) AND (Employees.[Employee ID]=[Projected Piece Total].[Employee ID]);

I tried to change my "join types" in my relationships and that didn't seem to add the missing dates of the records I am missing for the employees. Please help. Thank you!

[Rabbit]

Please use code tags when posting code.

We can't actually help without knowing the specific data that's missing. But in general, you're probably missing data because it doesn't exist in one of the joined tables. To resolve that, you should use an outer join instead of an inner join.

[NeoPa]

Asking why your posted code doesn't match your intentions, and the results differ from what you expect, makes little sense if we have no indication either of your intentions or your expected results. I feel almost foolish having to point that out.

[Chelle8263]

Thank you for your resposes. I apologize for the confusion. You are right Rabbit. I am missing data in the Piece Price ID from my payroll table, which seems to be causing the missing data in my payroll query. I am trying to play with the joins in the SQL statement and typing in FULL OUTER JOIN for both joins on my SQL and the message, "syntax error in joining operation" pops up. How should my SQL look to join the fields where the Piecework ID is missing in the payroll table? Or what syntax should I be using instead? Hope this makes better sense.

[Chelle8263]

I also tried opening the Join Propoerties dialog box and clicking Option 2/Option 3 from the query design view, but this message appears: "The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in your SQL statement." Do I have to create the seperate query or can I create a short cut by typing in a different syntax for the joining properties in the SQL statement?

[Rabbit]

Access doesn't have a FULL OUTER JOIN. Use a LEFT JOIN or a RIGHT JOIN.

[Chelle8263]

I tried putting in combinations of LEFT JOIN- RIGHT JOIN, LEFT JOIN- INNER JOIN, INNER JOIN- RIGHT JOIN into my SQL statement.
For instance, using the first combination referenced, LEFT JOIN-RIGHT JOIN, this is what my SQL statement looks like:

SELECT Payroll.[Emp Name], Payroll.[Piece Price ID], Payroll.Date, ([Lunch Out]-[Lunch In])*24 AS Lunch, (([End Time]-[StartTime])*24)-[Lunch] AS TotalHours, [TotalHours]*[Hourly Rate] AS [Day Amount], [Projected Piece Total].[Projected Total], [Projected Total]-[Day Amount] AS [Piece Price Balance]
FROM (Employees LEFT JOIN Payroll ON Employees.[Employee ID]=Payroll.[Emp Name]) RIGHT JOIN [Projected Piece Total] ON (Employees.[Employee ID]=[Projected Piece Total].[Employee ID]) AND (Payroll.[Piece Price ID]=[Projected Piece Total].[Piece Price ID]);

Each time I try these different combinations, this message pops up, "Join expression not supported." How should my SQL statement look in order to be supported? Do I need to adjust something else in my SQL statement?

[Rabbit]

That looks fine assuming that

• Employees.[Employee ID]=Payroll.[Emp Name]
• The data types in the joins match.
• You actually want a left join into a right join, which is unlikely

[NeoPa]

Chelle,

Consider the joins as indicating direction. Start with a table, or other record source, which you can consider to be at the top of a flat-topped hill. From there, you can have joins to other record sources at the same level (INNER JOIN) or at a lower to the left (LEFT JOIN) or to the right (RIGHT JOIN). Once you have embarked on your trip down the hill though, on either side, you are already on the slope, so further connections may only be made in the same direction. You cannot INNER JOIN to or from any record source which already includes any of a LEFT JOIN or a RIGHT JOIN, nor can you RIGHT JOIN to one including a LEFT JOIN nor vice-versa.

I hope this helps you to get your head around this issue.

[Chelle8263]

Thank you for your help. I found that I needed to make an inital query to build the outer join first and then build my final query from the initial query. This article really helped me understand how to build my query with ambiguous joins. http://office.microsoft.com/en-us/ac...aspx?section=1