UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Join / Union Query Help, Access 2013    
 
   
tykra
post May 18 2017, 05:08 PM
Post#1



Posts: 51
Joined: 14-January 17



I have a time tracking table that stores several items to track projects.

The important info is that is stored in the table is

sDate = Start Date which is a date field
empID = the employ ID which is filled by a tempvar that is set on login
tHours = hours spent on project
tMinutes = minutes spent on project
tCompleted = y/n option

I have a query that pulls in rows that are equal to the empID to show items where the tCompleted = yes I also have an expression to total the time.

I have another query that pulls in rows that are equal to the empID to show items where the tCompleted = no I also have an expression to total the time. This query uses other expressions to assign tcHours to tHours and tcMinutes to tMinutes so that I can hopefully show them along side the numbers for those completed.

Both those work great as they show accurate information.

Here is where I am having troubles, I have a subform which I want to show total times which will be sorted by startdate to show totals for each week.

When I try to group by both empID AND startDate I get the nasty ambiguous outer joins alert.

How can I get a query to show me the date | hours (completed) | minutes (completed) | hours (not completed) | minutes (not completed) WHERE that info is assigned to the empID and occurred in the same week?

Go to the top of the page
 
Doug Steele
post May 18 2017, 07:03 PM
Post#2


UtterAccess VIP
Posts: 21,440
Joined: 8-January 07
From: St. Catharines, ON (Canada)


What's the SQL of the queries involved?

--------------------
Go to the top of the page
 
tykra
post May 18 2017, 08:39 PM
Post#3



Posts: 51
Joined: 14-January 17



Each displays the correct time for the correct user for the correct week. I figured it would have been as easy as


If I typed this out right the two queries are something like this is.

Shows times/task that are NOT recorded as Completed
CODE
SELECT [tDate]-Weekday([tDate],1) AS StartDate, Q_subformTimes.tCompleted AS tNotCompleted, Q_subformTimes.EmpID AS EmpID, Sum(Round((DateDiff("n",[tstartTime],[tendTime])/60),2)) AS totalTime, Fix([TotalTime]) AS tHours, ([totalTime]-[tHours])*60 AS tMinutes
FROM Q_subformTimes
GROUP BY [tDate]-Weekday([tDate],1), Q_subformTimes.tCompleted, Q_subformTimes.EmpID
HAVING (((Q_subformTimes.tCompleted)=2));


Shows time/task that ARE recorded as Completed
CODE
SELECT [tDate]-Weekday([tDate],1) AS StartDate, Q_subformTimes.tCompleted AS tCompleted, Q_subformTimes.EmpID AS EmpID, IIf([tCompleted]=1,Sum(Round((DateDiff("n",[tstartTime],[tendTime])/60),2)),0) AS tTimeCompleted, IIf([tCompleted]=1,Fix([tTimeCompleted]),"0") AS tCHours, IIf([tCompleted]=1,([tTimeCompleted]-[tCHours])*60,"0") AS tCMinutes
FROM Q_subformTimes
GROUP BY [tDate]-Weekday([tDate],1), Q_subformTimes.tCompleted, Q_subformTimes.EmpID
HAVING (((Q_subformTimes.tCompleted)=1));


I have a third query that I was using to try and join the 2 queries. It is currently a LEFT JOIN on EmpID - it produces the results in the screen capture. Since I am unable to group the not completed hours they are showing up in every row where the emp matches.

I tried a full join on EmpID and a Outer Join on the StartDate and that results in the ambiguous error. I also have tried to union the two queries, but that does not return all of the columns needed.


Attached File(s)
Attached File  results.png ( 11.38K )Number of downloads: 9
 
Go to the top of the page
 
MadPiet
post May 19 2017, 12:37 AM
Post#4



Posts: 2,225
Joined: 27-February 09



It seems that if you changed your query a little, this would be a LOT easier. In your query, you're UNIONING tCompleted and tNotCompleted, but there's no flag to distinguish one from another in your result. (Also, I'd convert all this stuff to minutes and do the hours/minutes later)... just INT() and MOD().

Then you'd have something like this:
CODE
SELECT [tDate]-Weekday([tDate],1) AS StartDate
    , "Incomplete" AS JobStatus
    , Q_subformTimes.tCompleted AS tNotCompleted
    , Q_subformTimes.EmpID AS EmpID
    , Sum(Round((DateDiff("n",[tstartTime],[tendTime])/60),2)) AS totalTime
    , Fix([TotalTime]) AS tHours
    , ([totalTime]-[tHours])*60 AS tMinutes
FROM Q_subformTimes
WHERE (((Q_subformTimes.tCompleted)=2))
UNION ALL
SELECT [tDate]-Weekday([tDate],1) AS StartDate
    , "Complete AS JobStatus
    , Q_subformTimes.tCompleted AS tCompleted
    , Q_subformTimes.EmpID AS EmpID
    , IIf([tCompleted]=1,Sum(Round((DateDiff("n",[tstartTime],[tendTime])/60),2)),0) AS tTimeCompleted
    , IIf([tCompleted]=1,Fix([tTimeCompleted]),"0") AS tCHours, IIf([tCompleted]=1,([tTimeCompleted]-[tCHours])*60,"0") AS tCMinutes
FROM Q_subformTimes
WHERE (((Q_subformTimes.tCompleted)=1))


and you'd wrap all that up in a totals query.
Go to the top of the page
 
tykra
post May 19 2017, 12:06 PM
Post#5



Posts: 51
Joined: 14-January 17



Tried this code, and it returns an error ... " your query does not include the specified expression '[tDate]-Weekday([tDate],1)' as part of an aggregate function. "

Go to the top of the page
 
Doug Steele
post May 19 2017, 12:56 PM
Post#6


UtterAccess VIP
Posts: 21,440
Joined: 8-January 07
From: St. Catharines, ON (Canada)


MadPiet forgot the GROUP BY clauses. I think this should work:

CODE
SELECT [tDate]-Weekday([tDate],1) AS StartDate
    , "Incomplete" AS JobStatus
    , Q_subformTimes.tCompleted AS tNotCompleted
    , Q_subformTimes.EmpID AS EmpID
    , Sum(Round((DateDiff("n",[tstartTime],[tendTime])/60),2)) AS totalTime
    , Fix([TotalTime]) AS tHours
    , ([totalTime]-[tHours])*60 AS tMinutes
FROM Q_subformTimes
WHERE (((Q_subformTimes.tCompleted)=2))
GROUP BY tDate, tCompleted, EmpID, tstartTime, tendTime
UNION ALL
SELECT [tDate]-Weekday([tDate],1) AS StartDate
    , "Complete" AS JobStatus
    , Q_subformTimes.tCompleted AS tCompleted
    , Q_subformTimes.EmpID AS EmpID
    , IIf([tCompleted]=1,Sum(Round((DateDiff("n",[tstartTime],[tendTime])/60),2)),0) AS tTimeCompleted
    , IIf([tCompleted]=1,Fix([tTimeCompleted]),"0") AS tCHours, IIf([tCompleted]=1,([tTimeCompleted]-[tCHours])*60,"0") AS tCMinutes
FROM Q_subformTimes
WHERE (((Q_subformTimes.tCompleted)=1))
GROUP BY tDate, tCompleted, EmpID, tstartTime, tendTime



--------------------
Go to the top of the page
 
tykra
post May 19 2017, 01:21 PM
Post#7



Posts: 51
Joined: 14-January 17



Not sure I want to order by start time and end time ... I want to group items that share the same date (StartDate) and the same user (empID).

Of course when I enter Start Date in the Group By statement it ask me to enter a value for the parameter. I thought the first item in the SELECT assigns a value to StartDate?


EDIT

The final view would look similar to the one I posted above.

Completed and NON completed times would display in the same row by start date.
This post has been edited by tykra: May 19 2017, 01:32 PM
Go to the top of the page
 
Doug Steele
post May 19 2017, 01:55 PM
Post#8


UtterAccess VIP
Posts: 21,440
Joined: 8-January 07
From: St. Catharines, ON (Canada)


CODE
SELECT [tDate]-Weekday([tDate],1) AS StartDate
    , "Incomplete" AS JobStatus
    , Q_subformTimes.tCompleted AS tNotCompleted
    , Q_subformTimes.EmpID AS EmpID
    , Sum(Round((DateDiff("n",[tstartTime],[tendTime])/60),2)) AS totalTime
    , Fix([TotalTime]) AS tHours
    , ([totalTime]-[tHours])*60 AS tMinutes
FROM Q_subformTimes
WHERE (((Q_subformTimes.tCompleted)=2))
GROUP BY tDate, tCompleted, EmpID, tstartTime, tendTime
UNION ALL
SELECT [tDate]-Weekday([tDate],1) AS StartDate
    , "Complete" AS JobStatus
    , Q_subformTimes.tCompleted AS tCompleted
    , Q_subformTimes.EmpID AS EmpID
    , IIf([tCompleted]=1,Sum(Round((DateDiff("n",[tstartTime],[tendTime])/60),2)),0) AS tTimeCompleted
    , IIf([tCompleted]=1,Fix([tTimeCompleted]),"0") AS tCHours, IIf([tCompleted]=1,([tTimeCompleted]-[tCHours])*60,"0") AS tCMinutes
FROM Q_subformTimes
WHERE (((Q_subformTimes.tCompleted)=1))
GROUP BY tDate, tCompleted, EmpID, tstartTime, tendTime
ORDER BY 1, 4

--------------------
Go to the top of the page
 
tykra
post May 19 2017, 02:22 PM
Post#9



Posts: 51
Joined: 14-January 17



Doug first I want to thank you for taking a look at this. That union query shows every single entry in a different row.

I want the final results to look like the image I attached a few post ago. Do I need to run another query using the union query to group by the start date?
Go to the top of the page
 
Doug Steele
post May 19 2017, 02:36 PM
Post#10


UtterAccess VIP
Posts: 21,440
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Let's step back a little bit to your earlier post where you showed the screenshot.

You said "I have a third query that I was using to try and join the 2 queries. It is currently a LEFT JOIN on EmpID - it produces the results in the screen capture. Since I am unable to group the not completed hours they are showing up in every row where the emp matches." That's the SQL I wanted to see... Also, what do you mean that you are "unable to group the not completed hours"?


--------------------
Go to the top of the page
 
tykra
post May 19 2017, 03:16 PM
Post#11



Posts: 51
Joined: 14-January 17



What I meant by unable to group the hours correctly .....

The subformTimes query shows one row with 3 hours of "not completed" time which occurred in the week starting on 5/13/2017; however the result of the query Q_taskTimes which uses a LEFT join shows those 3 hours in every single StartDate for that user as seen in the capture instead of placing those 3 hours only in the correct group.

The third query pulls in the two queries I had posted. Q_taskbyWeek and Q_taskbyWeekNotcompleted with a Left Join on EmpID. I wanted to also group by start date to show those 3 hours in a column, I now understood that I couldnt union completed and not completed and that it will likely take more than 2 queries to do what I want.

That third query looks something likes this ...

CODE
SELECT Q_taskbyWeek.StartDate, Q_taskbyWeek.EmpID, Format([tHours],"0") AS tCHours, Format([tMinutes],"0") AS tCMinutes, Format([tCHours],"0") AS tHours, Format([tCMinutes],"0") AS tMinutes, [tHours]+[tCHours] AS totalHours, [tMinutes]+[tCMinutes] AS totalMinutes
FROM Q_taskbyWeek LEFT JOIN Q_taskbyWeekNotcompleted ON Q_taskbyWeekNotcompleted.EmpID = Q_taskbyWeek.EmpID
GROUP BY Q_taskbyWeek.StartDate, Q_taskbyWeek.EmpID, Format([tHours],"0"), Format([tMinutes],"0"), Format([tCHours],"0"), Format([tCMinutes],"0"), [tHours]+[tCHours], [tMinutes]+[tCMinutes];


Thanks again for your help.

Go to the top of the page
 
Doug Steele
post May 19 2017, 03:37 PM
Post#12


UtterAccess VIP
Posts: 21,440
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Try changing

CODE
FROM Q_taskbyWeek LEFT JOIN Q_taskbyWeekNotcompleted ON Q_taskbyWeekNotcompleted.EmpID = Q_taskbyWeek.EmpID

to

CODE
FROM Q_taskbyWeek LEFT JOIN Q_taskbyWeekNotcompleted ON Q_taskbyWeekNotcompleted.EmpID = Q_taskbyWeek.EmpID AND Q_taskbyWeekNotcompleted.StartDate = Q_taskbyWeek.StartDate


However, that still probably won't give you want you want: it won't find Not Completed tasks for the employee unless the employee also has Completed tasks for the same StartDate.

I don't understand, though, why you've got GROUP BY in that query. GROUP BY is only used when you've got aggregate functions such as Sum or Count: when you don't, it does nothing.

--------------------
Go to the top of the page
 
tykra
post May 19 2017, 04:31 PM
Post#13



Posts: 51
Joined: 14-January 17



THAT appears to work! I am going to have to test it a bit further, but when I ran the query it showed the 3 hours in the correct week.

Also removed all of that Group By and the results are the same, now to remember why they were there, maybe was a bad copy and paste and a night of gin and tonic. hmmmm

THANKS again for your guidance - you are a true MVP!


Go to the top of the page
 
tykra
post May 19 2017, 04:33 PM
Post#14



Posts: 51
Joined: 14-January 17



If you get bored have a gander at my time/date question regarding validation to look for duplicate ranges. wink.gif

Have yourself a great night and a better tomorrow!
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th November 2017 - 04:12 PM