Will anyone help mr re-create this query...again?

[djclntn]

Due to the fact that I made changes in my database I lost some queries; one that someone here helped me create (SQL) & was a vital Query to me.



Objective: I want a query which shows the date (TDATE), the SumOfHRS From my [Employees/Payroll] Query -&-SumOfHRS from my [Daily Task] Table. Here are the SQL for both queries:

Daily Task Query:
SELECT [Daily Tasks].DailyTaskID, [Daily Tasks].TDATE AS QURTLY, [Daily Tasks].TDATE AS MNTH, [Daily Tasks].TDATE AS WKDY, [Daily Tasks].TDATE AS TDATE, [Daily Tasks].ProjectID, [Daily Tasks].APPLCTNS, [Daily Tasks].[File Name], [Daily Tasks].[NOTES/TASKS], [Daily Tasks].HRS, [Daily Tasks].CMPLTD
FROM [Daily Tasks];

Employees/Payroll Query:
SELECT [Employees/Payroll].Employee_ID, [Employees/Payroll].TDATE AS QRTLY, [Employees/Payroll].TDATE AS MNTH, [Employees/Payroll].TDATE AS WKDY, [Employees/Payroll].TDATE AS TDATE, [Employees/Payroll].HRS, [Employees/Payroll].[D\E], [Employees/Payroll].DNW
FROM [Employees/Payroll]
WHERE ((([Employees/Payroll].Employee_ID) Is Not Null));

*Note someone here helped me with this previously, /but due to me making changes I messed it up.

Any help would truly be appreciated!

[June7]

Expressions to extract date parts from date:

Month([TDATE]) As Mnth

Weekday([TDATE]) As Wkday

Year([TDATE]) As TdateYear

Quarter will be much more complicated.
IIf([MNTH] LIKE "[1,2,3]",1, IIf([MNTH] LIKE "[4,5,6]",2, IIf([MNTH] LIKE "[7,8,9]",3, IIf([MNTH] LIKE "[10,11,12]",4, Null)))) As QURTLY

[orange]

Or to find quarter

Datepart("q",yourDate)

For the first query

SELECT [Daily Tasks].DailyTaskID, [Daily Tasks].TDATE AS QURTLY, [Daily Tasks].TDATE AS MNTH, [Daily Tasks].TDATE AS WKDY, [Daily Tasks].TDATE AS TDATE, [Daily Tasks].ProjectID, [Daily Tasks].APPLCTNS, [Daily Tasks].[File Name], [Daily Tasks].[NOTES/TASKS], [Daily Tasks].HRS, [Daily Tasks].CMPLTD
FROM [Daily Tasks];

I think this should work, but untested:

SELECT DailyTaskID
, Datepart("q",TDATE) AS QURTLY
, Month(TDATE) AS MNTH
, Weekday(TDATE) AS WKDY
, TDATE AS TDATE
, ProjectID
, APPLCTNS
, [File Name]
, [NOTES/TASKS]
, HRS
, CMPLTD
FROM [Daily Tasks];

And for the second, but untested:

SELECT Employee_ID
, Datepart("q",TDATE) AS QRTLY
, Month(TDATE) AS MNTH
, Weekday(TDATE) AS WKDY
, TDATE AS TDATE
, HRS
, [D\E]
, DNW
FROM [Employees/Payroll]
WHERE (((Employee_ID) Is Not Null));


Also,
when you only have 1 table in your query, you do not need to identify your field names using the Table.fldname.
Fldname alone is fine, since there is no ambiguity as to which field you mean.

and you should avoid spaces and special characters in field and object names.

Good luck.

[June7]

Darn, I keep forgetting about DatePart function. However, if year is not calendar year (such as July-June fiscal), don't think will work.

[orange]

Previous info/assistance on a query for djcIntn
https://www.accessforums.net/databas...mat-19256.html

Some Fiscal Year info is here if needed
http://support.microsoft.com/kb/210249

[djclntn]

Guys, first & foremost I want to thank you (s) for ALL of your assistance/input. For reasons which I won’t bore you with I’ve reverted back to my original databases & doing things as I have been mainly because I find myself having more flexibility in creating my queries etc.

I do need another question answered:

If I have a date field (TDATE) in a query & I want a column next to the date field (TDATE) showing me the number of days past the (TDATE) to the current date – is there an expression I can use to get these results?
Eg.
ArtistiID
[LastPlayed]: TDATE (Min)
[NubmerDaysPast]: TDATE ….?

[djclntn]

ArrtistID -- Creed
LastPlayed -- 11/23/2011
NumnberDaysPast -- 3 (current date: 11/26/2011)

[June7]

You can simply subtract two dates to get elapsed period. Calculation will default to days unit.
Date() - [LastPlayed]

If you need another date unit (years, months, minutes), need to use DateAdd function or further calculate the days value (multiply by 24 to get hours, etc). Refer to Access Help or Google for info on DateAdd.

[djclntn]

It worked. Much thanks!

[djclntn]

Problem,

I tried using this same expression “Date() –[Field’s Name] “ in my Employees LastOfDW Query

Query:
- EmployeeID
- TDATE (Max)
- DLW: Date()-[TDATE]

SQL:
SELECT [Employees/Payroll].Employee_ID, Max([Employees/Payroll].TDATE) AS MaxOfTDATE, Date()-[TDATE] AS DLW
FROM [Employees/Payroll]
GROUP BY [Employees/Payroll].Employee_ID, Date()-[TDATE]
HAVING ((([Employees/Payroll].Employee_ID) Is Not Null))
ORDER BY Max([Employees/Payroll].TDATE) DESC;

In return I’m getting ALL of the Employees LastDateWork & the #-of-days.
Say I have 30-Employees, before I added this expression I had 30-records displaying the Employees ID &
The Last date (Max) they worked, Great! Then, when I added a third column to this query with the above expression (wanting to see how many days elapsed since the current date compared/diff from the Employee LastDayWorked) I got not just the 30-Employees-LastDayWorked-#OfDays (elapsed), but instead I got kicked-back ALL 700+ records from day-1. I didn’t want that!

I’m whining now, why is Access so damn hard! Just when you think you understanding something & you try to apply an expression somewhere else which ALL of the DATA Types looks identical it doesn’t work!

[June7]

This is not an Access issue. The issue is with the SQL statement, which is working properly. Problem is you are doing this calculation in an aggregate query. The combination of EmployeeID and the calculated difference causes every record to be unique so grouping on these two fields returns every record. Need to do an aggregate calc on the field, such as Avg or Max or Min. I think what you really want is to calc the difference of MaxOfTDATE and current Date.

[djclntn]

Given your above suggestion I tried several expressions in wanting to is to calc the difference of MaxOfTDATE and current Date, though I’m having no luck.

This is what I tried:
LastDayWorked: [MaxOfTDATE]-DATE()

I looked on MS Help & Goggle DateDiff Expressions but found nothing useful.

[orange]

Please show the entire query sql that you are having trouble with.

Also, did you read June7's note re the aggregate query?

[June7]

What do you mean by 'no luck'? The SQL errors, wrong results, no results? Show the SQL statement. Why do you call the calculated field LastDayWorked. The difference will not return Last Day, it returns number of days elapsed.

[djclntn]

Excuse my delayed response, I'm just working on too many projects at once

I believe that when I tried it again I change the aggregate (?) to "expression" & it worked! Probably that was what you were trying to tell dense-head here.

Thanks June & Orange for your help & PATIENCE with me.