StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You've got this line of code: Format (a.Date_Time, 'yyyy-mm-dd') as adate,</p> <p>And you're expecting to get a date for a person who has no dates associated with them in table a. The only way that's going to work is if you create a new query (or SELECT statement) that selects the unique dates from table a, and NOT JOIN that back to table p.</p> <p>Try running this, and then see if you can build the rest of your query off it. I'm a little swamped at work, but you should be able to take it over the finish line.</p> <pre><code>SELECT QueryDate.ID, QueryDate.NIP, QueryDate.nama, QueryDate.adate, IIf((Min(TA_Record_Info.Date_Time)<>Max(TA_Record_Info.Date_Time)),Format(Min(TA_Record_Info.Date_Time),"hh:mm:ss"),IIf(Format(Min(TA_Record_Info.Date_Time),"hh:mm:ss")<"12:00:00",Format(Min(TA_Record_Info.Date_Time),"hh:mm:ss"),'-')) AS InTime, IIf((Max(TA_Record_Info.Date_Time)<>Min(TA_Record_Info.Date_Time)),Format(Max(TA_Record_Info.Date_Time),"hh:mm:ss"),IIf(Format(Max(TA_Record_Info.Date_Time),"hh:mm:ss")>"12:00:00",Format(Max(TA_Record_Info.Date_Time),"hh:mm:ss"),'-')) AS OutTime FROM QueryDate LEFT JOIN TA_Record_Info ON (QueryDate.adate=DateValue(TA_Record_Info.Date_Time)) AND (QueryDate.ID=TA_Record_Info.Per_Code) GROUP BY QueryDate.ID, QueryDate.NIP, QueryDate.nama, QueryDate.adate; </code></pre> <p>I have a separate query called QueryDate which consists of:</p> <pre><code>SELECT HR_Personnel.ID, HR_Personnel.NIP, HR_Personnel.Name as nama, QDt.UniqueDate as adate FROM HR_Personnel, (SELECT DateValue([Date_Time]) AS UniqueDate FROM TA_Record_Info GROUP BY DateValue([Date_Time])) AS QDt; </code></pre> <p>You can probably combine the two if you try hard enough.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload