Needing Sub Query Help

[djclntn]

I need help with creating a Sub Query



Here are ALL of the fields:
- EmployeesID
- Yrs (2012)
- MnthYr (11/12)
- Hrs
- Erngs ($)

I have query with the sums of ALL of the above fields by ‘MnthYr.’ Now, I am able to create a sub query as such: YrlyMnthly Employees Summary_SQ
- Query: Yr
- SubQuery: MnthYr, SumofHrs, & SumofErngs (that’s for each MnthYr per yr)
- SubQuery: EmployeeID, CountOfWkdys, SumOfHrs, & SumofErngs (that’s for each MnthYr per yr)

This query/sub query serves me well. However, I have another sub query Employees YrlyMnthly Summary_SQ, which displays (in order)

- Query: EmployeeID, CountofMnths, CountOfWkdys, SumofHrs, & SumofErngs (that’s for each MnthYr per yr)
- SubQuery: Yr, MnthYr, SumofHrs, & SumofErngs (that’s for each MnthYr per EmplyID)

*Here’s the issue which I cannot solve & have been at it for many days. In the last subquery Employees YrlyMnthly Summary_SQ how can I (break down) make a subquery for the ‘Yr’ & link it back to the Employees YrlyMnthly Summary_SQ? So, under each 'EmploeeID' would just have the 'Yr' & then under "Yr' would show the rest of the fields/CountofMnths, CountOfWkdys, SumofHrs, & SumofErngs (that’s for each MnthYr per yr)

Like in my YrlyMnthly Employees Summary_SQ query which is first SubQuery into 'Yr' I want the same thing for the Employees YrlyMnthly Summary_SQ subquery to first drop down into ‘Yr” & then into ‘MnthYr’ but nothing I’ve tried works. If this is not too confusing, can I ask for some suggestions?

[June7]

My brain is now tied in knots! Provide db for analysis.

Possibly you need to build a report using Grouping & Sorting with aggregate calcs functionality. This will allow display of detail records and summary data.

[djclntn]

June,

I'm going to try to send you a portion of a bigger dbs. Just the "Employees" Tables, Queries, Reports, but my Tables are linked to aother dbs. If you can't work with this dbs let mme know & I'll try something else.

Here's what I would like for you to look at, open the query, 'Employees MnthYr Summary_SQ' When you drop down each name/Employee it has the 'Yr' 'MnthYr' etc. How can I subquery the 'Yr' field where when I dropdown on the 'Employee' it'll just have the 'Yr' & then when I dropdown the 'Yr" it'll have 'MnthYr' & the rest of the fields?

[June7]

Can't test queries without data. If you want to provide limited backend db, I will work on.

[djclntn]

June,

I don't unserstand I was able to open the db & had access to the queries. I'm able to save it in mdb format. Don't you only have Access 07 or do you have 2010? I don't know what you mean by, "limited backend db?" Let me resend you the db & see if you can "access" it. Pardon the pun

[June7]

The tables are all links. This means the tables are really in another file - the backend - and what I see in this file - the frontend - are just links to the backend tables. This is a split design. Without the backend I do not have data and queries won't work. Rest the cursor over any table and you will see the link path to the backend. Linked tables are apparent by the little arrow on left side of table icon.

By 'limited' I mean make a copy of the backend, remove confidential data. Be sure to run Compact & Repair and zip.

[djclntn]

June,

Once again I want to thank you for your patience & time with me! Here's what I did which ought to work. I don't know what the correct lingo is to decribed what I did, but I un-linked all of the Tables by Copying them & then pasting them as 'Structure and Data (Local Table). I'm sorry to say that I don't understand/know how to 'make a copy of the 'backend.' What does 'backend' mean?'

[June7]

Frontend and Backend refer to the components of a split database. Review http://office.microsoft.com/en-us/ac...010342026.aspx

Make a copy the same way you make a copy of of any file - Copy/Paste.

I will look at your db tonight.

[June7]

Employees YrlyMnthly Summary_SQ already has a link that opens related records. You want another link off of that dataset? Which query has the 'MnthYr per yr' data that you want in this setup? This a first for me. Never encountered anyone trying to configure queries like this. Could be you are relying too much on queries for viewing summary data. As I said in earlier post, possibly you need to build report using Grouping & Sorting with aggregate calcs functionality. This will allow display of detail records and summary data. You are already familiar with this functionality so why not utilizing for this aggregation? Also, subform/subreport can be very powerful tool for presentation of related data.

Suggest you build dynamic criteria in queries, forms, reports. Instead of building (by copy/paste) a new object each month, year or whatever, set filter criteria as needed. Several ways to accomplish.

[djclntn]

June,

If you still have my Employee db, open up the Report named, Employees YrMnth Summary. You'll first see the "Employee,' then the 'Yr,' & then the 'MnthYr.' 2-Questions: 1) How can I filter the Employees so theat if I want to see the data from JckMrtn I don't have to scroll all the down. Is there any way I can expand/collospe the 'Yr' so that I only see the results per year per Employee, but can/howI can drop down the 'Yr' & see the results for 'Mnth/Yr? Is my best bet is to create another Report with the employees & just the "yr' results?

You say, "build dynamic criteria in queries, forms, reports" can you elabote some more or give me some ideas?

[June7]

Many ways to filter a report or form recordset. Some require more code than others. You can pass filter criteria to the opening form or report or you can filter after opening. With report open in Report view, right click on EmployeeID field to get the shortcut menu, select filter method.

Here is one method for dynamic queries http://datapigtechnologies.com/flash...tomfilter.html