473,326 Members | 2,196 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Missing Data in Query

Hello,

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

Expand|Select|Wrap|Line Numbers
  1. 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]
  2. 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!
Mar 20 '12 #1
9 2797
Rabbit
12,516 Expert Mod 8TB
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.
Mar 21 '12 #2
NeoPa
32,556 Expert Mod 16PB
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.
Mar 21 '12 #3
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.
Mar 21 '12 #4
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?
Mar 21 '12 #5
Rabbit
12,516 Expert Mod 8TB
Access doesn't have a FULL OUTER JOIN. Use a LEFT JOIN or a RIGHT JOIN.
Mar 21 '12 #6
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:
Expand|Select|Wrap|Line Numbers
  1. 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]
  2. 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?
Mar 21 '12 #7
Rabbit
12,516 Expert Mod 8TB
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
Mar 21 '12 #8
NeoPa
32,556 Expert Mod 16PB
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.
Mar 22 '12 #9
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
Apr 20 '12 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: 'bonehead | last post by:
Greetings, I'd like to figure out some syntax for retrieving the data from a table when I don't know all the of field names. What I do know are, the name of the table, the names of the primary...
0
by: alexz | last post by:
valuA = (request.form("toadd")) If valuA = "" then SQL = "UPDATE CourseReg SET attended='Active' WHERE ID IN("&request.form("toadd")&")" Set RS = MyConn.Execute(SQL) End If MyConn.Close Set...
3
by: Justin Emlay | last post by:
I'm hopping someone can help me out on a payroll project I need to implement. To start we are dealing with payroll periods. So we are dealing with an exact 10 days (Monday - Friday, 2 weeks). ...
4
by: Wally | last post by:
I have a record set (rs) that contains 25 barcodes values that I set to true in a cookie. (see code section below) If I read the cookie from within the same page that created it, I see all 25...
1
by: KC | last post by:
Hello, I am using Access 2002. WinXP, Template from MS called Orders Mgmt DB. I have tweaked this DB to work for our small co. It has worked pretty well up until I made the mistake of deleting...
0
by: Chris | last post by:
I finally got my report to be recongized, that login issue is terrible. Using VS.NET 2003, created report inside of it. Now when I attempt to display the report is blank (missing data, though I can...
3
by: danceli | last post by:
After loading the BCP files that are created during the trigger/ reporting events I've noticed that the data in the table is missing records. I've also noticed that the missing records (records in...
2
by: danceli | last post by:
I have made trigger on table 'FER' that would be fired if data is inserted, updated to the table. And also, I made batch file using bcp to extract the newly updated / inserted records. But I got...
1
by: anubis2k7 | last post by:
Hi, I am having a problem with dynamically sorting/grouping data in my report at runtime. My problem is that when the report is run using sorting/grouping I am missing data. Specifically, my...
15
by: Mr.Tom.Willems | last post by:
Hello people, I am ussing an MS access database to enter and manage data from lab tests. until now i was the only one handeling the data so i had no need for a controle on how missing data was...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.