Build query from other queries with same/similar info just 1 diff # over multiple months

[P3anut]

Views: Size: ">Attachment 46550


This is an example similar to what I am working with. I am trying to build a query from multiple queries where it will list a person only once going down and the months across the top.
Any help is appreciated and if it's not possible please let me know.

[Gicu]

See updated file for example of a crosstab query. You might be interested to have a look at my free utility that allows you to create an Excel pivot table based on your Access query:http://forestbyte.com/ms-access-util...able-designer/

Cheers,

[P3anut]

Attachment 46568
I did what you showed me but now I'm getting this error. I can't share much due to working with peoples personal info but any help would be appreciated.

[Gicu]

You should add those fields in the source query (Master Query) instead of the crosstab.

[P3anut]

Attachment 46570
It is in my source query, and I want the total to show for each person for each month. I don't know what I am doing wrong. I can explain what the fields mean if you need me to clarify.
Thank you for all the help you have given so far.

[Gicu]

In post #3 you were summing them in the crosstab, now you are trying to do it in totals query. MasterQuery (you should try to avoid spaces in the field and objects names as it will make it easier for you by avoiding to enclose them in square brackets) should be a simple Select query, do not try to aggregate in it; create your calculated field (Total:NCOAttain+NATAttain) then choose it in the crosstab wizard and select Sum.

Cheers,

[P3anut]

Attachment 46574
So something like this? And if so this is the error I get if it's not Total: SUM([NCOAttain]+[NATAttain]). Any idea how to fix this?

[Gicu]

No, you would need to add Sum() on the first row (Total:Sum([NCOAttain]+[NATAttain]), but I think there is no need for the query to be a totals query at this point. Click the sigma sign on the ribbon to get rid of the Total row on the query design window. The whole point of the crosstab query is that it will do the aggregation for you (group by on the rows and columns and sum\count\avg, etc. in the data grid in the middle) so you don't need to "prep" the data for it.

If you can't get it going consider creating a sample db with just a few dummy records with no personal\sensitive data in it and upload it here (you might need to zip it first).

Cheers,
Vlad

[P3anut]

Sorry for the late reply. I did what you said and now I keep running into more similar issues. I made a copy of the database I'm working with but it's fed from another one that I also copied but I can't attach. It is compressed and zipped but it won't upload even though it is smaller than all the limits listed on the attachment upload page. I don't know if you can work with what's here or not without the other database but I don't know what to do make it upload. I can't delete anymore info from it because then it won't work the way it's supposed to. I really do appreciate all your help.Sample DB 2.zip

[ssanfu]

"Rate", "First" and "Last" are reserved words in Access and shouldn't be used for object names. See What not to use in names
"Rate" is not very descriptive. "Rate" of what?? I am guessing Recruitment rate?
Same goes for "First" and "Last". I would use "FName" and "LName"


Some Naming suggestions:
Use only letters and numbers (exception is the underscore) for object names.
Do not use spaces, punctuation or special characters in object names.
Do not begin an object name with a number.
Do not use Look up FIELDS, multi-Value fields or Calculated fields in tables.


[Talent Scouts] has a space in it. Better would be "Talent_Scout". Best would be "TalentScout".
[NRS-ID] has a hyphen in it. Better would be "NRS_ID".
[1stAtt] name begins with a number. Better would be "FirstAtt"


What data type is EPODATA.Status? If is is a number, the WHERE clause should be

WHERE (((EPODATA.Status)= 1 Or (EPODATA.Status)="R") AND ((EPODATA.Active)=True))

In my designs, tables names are plural and field names are singular. ( Ex: tblStations.Station)


What is "MonthIndex"? What data type is it - text or a number?
Instead of all of the saved queries, you could have a form (lets call it "frmCriteria", with a text box (for now) named "tbMonthIndex". Then, in the query (qryFYBtMonthIndex), you could reference the text box and execute the query for any FY month index.


I added a query "qryFYBtMonthIndex" and a form "frmCriteria" for you to test (since I don't have the BE)

[P3anut]

The DB I wasn't able to send to you is where I pull all my info from for all these queries. It was also designed about 15+ years ago so all the object names and such was not my choice. There's just so much info that I am afraid to change it as I will either mess the whole thing up or just do it severely wrong.

To answer your questions:
Rate in this sense is actually referring to the current employee job title, is the best way to describe it.
EPODATA.Status can be either a number or a letter and I have no idea what it's true purpose is for other than an abbreviated form of the current job the person is doing.
MonthIndex is the way the designer decided to organize a lot of our data by since we work on a monthly basis in a fiscal year time frame. We have multiple date versions so this was what I'm guessing was their way to make it easier to filter/organize/etc. by months. It is a number, ex: 1022 (Oct FY22), 0921 (Sep FY21). Access is told to see it as a short text though.

I tried the query and form you created on my database but I can't get the form to work. It opens VBA and shows this:
When I go to close it VBA tells me that I will stop the debugger.
Also, they both are good ideas just not exactly what I was trying to get to. My goal is to have something similar to this excel sheet:
I'm currently creating this excel sheet by hand since I can't figure out a way to get access to make something similar.
Please feel free to ask if you need me to explain anything and I am also willing to take screenshots of the VBA programming of the original DB if need be.

[Gicu]

Your back-end contains sensitive information so it is very important to remove all that before trying to upload it here. Make a copy of that file and use my free utility here to review and empty all the tables that contain private\sensitive information:http://forestbyte.com/ms-access-util...-data-cleaner/
Once it is clean populate the two tables used in the master query (EPODATA and RecruiterStatsbyMonth) with some dummy records enough to be able to get some meaningful cross-tab results.

Then do a Compact and Repair and zip the file. You should be able to upload it now.

Or at least create a new Access file and import those two tables, remove the sensitive data, zip and upload.

Cheers,

[ssanfu]

My bad.
Somehow the query name was removed.

Try this

Private Sub btnOpenQuery_Click()
    DoCmd.OpenQuery "qryFYBtMonthIndex"
End Sub

[P3anut]

This was the best I could do without it taking me forever. Let me know if this is what you were asking for.
Sample DB 1 Basic.zip

Also fixed what you said @ssanfu and it works fine now.

[Gicu]

Hi P3anut ,

Please review the updated file. I have imported the tables from the one you uploaded yesterday in the original front-end you posted. The crosstab now works OK and I also added a few objects (2 tables, 2 queries and 2 forms) to support exporting the data to Excel as a pivot table which should help you with the bigger report. You can modify the Excel file to add a master sheet like you shown, make that reference the pivot table for the appropriate fields (using the GetPivotData()) and save it somewhere. Then on the pivot design form choose to update the Excel file instead of creating a new one everytime.

Cheers,
Vlad