Using Sub Queries?

[djclntn]

Although to some it might not matter while others could find me as a bit of a nuisance. I’ll keep betting on a dead horse on I’m sure that most people use Access & come to this Forum on a business sense/to make money, whereas I use Access as an enjoyable hobby & a learning experience. The learning experience comes from ALL of you guys whom I truly admire your knowledge & sincerely appreciates EVERYONE’S help/input!



My new “adventure” is using sub queries within queries for 1) cosmetic appearance & 2) reducing the amount of queries in a dbs. So, let’s use an Employees Sums query which contains these following flds: EmployeeID, Yr, Mnth, CountOfDays, SumOfHrs, & SumOfErngs.

Now, in one query I have just the Year (2011 & 2012) & CountOfMnths, then embedded within this query I have a query which has the Yr, Mnth, CountOfEmpoyees, CountOfWkdays, SumOfHrs, & SumOfErngs per each month per year – the 2-linked fields are, “Yr.” Then embedded within this query has the month, EmplyeesID, CountOfEmpoyees, CountOfWkdays, SumOfHrs, & SumOfErngs – the 2-linked by “Month.” So, I have the ”Year” which expands to the “Mnths” which then expands to the “EmployeesID” etc. Great!
-Yr
- Mnth
-EmployeeID, etc

In another query/sub query I have the “EmployeesID” first & the CountsOfMonths. Embedded with this query I hare the “Yr” “Mnth,” CountOfWkdays, SumOfHrs, & SumOfErngs. The thing is, I cannot get per each EmployeeID just to expand to the “Yr” & then expand to the “MnthYr” ect. I’ve spent 2-days racking my brains out trying everything I could think of but nothing is working.
What I have is:
-EmployeeID & CountOfMnths
-EmployeeID, Yr, Mnth, CountOfWkdays, SumOfHrs, & SumOfErngs.

Whereas what I wish to have a query-sub query be,
- EmployeeID & CountOfMnths
- Yr
-EmployeeID, Mnth, CountOfWkdays, SumOfHrs, & SumOfErngs.

For the life of me I cannot set up this query/sub qury & maybe I can’t do so?

[June7]

There is usually a way to get the desired result. If not by queries, then by code, or by a combination of the two.

Need data to analyse. Want to provide project?

[djclntn]

June,

I need you to find & pay particular attention to the query: 'Employees MnthYr Summary*' how or if I can get the year into a sub query?

Look at the Query, 'MnthYr Employees Summary*' * you'll see how I have the year, then the mnths under that year, & then the Employees under the mnths

[June7]

Not understanding yet. The year is in a subquery - Employees MnthYr Summary1. Employees MnthYr Summary* is based on the other query. Just haven't included the Yr field. If it is included it must be as Group By and the records will be grouped by employee and year. If you want to nest these queries in one sql, the result would be a very long statement.

[djclntn]

Thanks I'll just leave it as is.