Page 2 of 2 FirstFirst 12
Results 16 to 29 of 29
  1. #16
    P3anut is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    15

    Vlad,
    I tried to run what you had on the sample database and I got an error.Attachment 46720 Attachment 46721
    I figured I'd give it a try on my actual one but I got the same error.
    It does manage to save an excel file but it appears like this:Attachment 46722
    I close it and reopen it and it looks like this:Click image for larger version. 

Name:	Access Help 11.JPG 
Views:	14 
Size:	74.5 KB 
ID:	46723
    This is close to what I want but I don't know if this is supposed to be the final result what with the error.

  2. #17
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @P3anut
    Don't know how you are trying to attach files/images, but all are invalid.

  3. #18
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Sorry, as Steve said no idea what error you are getting, what is the error number and description?

    Attached is an Excel file that I produced using your sample data.

    Cheers,
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #19
    P3anut is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    15
    Hopefully this fixed it and these can be seen.
    I'll look at the new DB.
    1+2:Click image for larger version. 

Name:	Access Help 6.JPG 
Views:	15 
Size:	21.8 KB 
ID:	46727Click image for larger version. 

Name:	Access Help 7.JPG 
Views:	15 
Size:	34.8 KB 
ID:	46728
    3:Click image for larger version. 

Name:	Access Help 10.jpg 
Views:	15 
Size:	31.5 KB 
ID:	46729
    4:Click image for larger version. 

Name:	Access Help 11.JPG 
Views:	14 
Size:	74.5 KB 
ID:	46730

  5. #20
    P3anut is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    15
    So if the excel sheet is what I'm supposed to get from the running the form you sent before, it's not happening for me.
    It's also not showing all of what I would like it to show.
    I'm starting to think that it's not possible what I'm looking for.......
    Attached is an example with hopefully more of an explanation of what I'm looking for.

    Would Like.zip

    Thank you both for all your help.
    P3anut

  6. #21
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I would need to see a screen shot of the pivot table design form that causes that error. I had a look at your wish list and while it can be probably done, it is not an easy report to create as it seems to combine current month data with previous months totals. You would need to explain better what fields exactly are being summed for each of those columns and the grouping order. It can probably be done in Excel by using automation and combining two separate queries, one for the current month and another (crosstab like) for the previous months (of the current fiscal year only???).

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #22
    P3anut is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    15
    I did not change anything about the tables, queries, and forms you made on the Sample DB. All I did was copy and paste. When I go to run the form I select Master Query, which I made sure looks just like the one from the Sample DB, and this is what pops up:Click image for larger version. 

Name:	Access Help 13.JPG 
Views:	12 
Size:	79.7 KB 
ID:	46732
    I leave it on create and click excel pivot table and then this pops up:Click image for larger version. 

Name:	Access Help 14.JPG 
Views:	12 
Size:	18.3 KB 
ID:	46733Click image for larger version. 

Name:	Access Help 15.jpg 
Views:	12 
Size:	47.0 KB 
ID:	46734
    I click debug and this is what it shows me:Click image for larger version. 

Name:	Access Help 16.jpg 
Views:	12 
Size:	73.1 KB 
ID:	46735
    Do you need more info than this?

    As for the wish list layout, the colums are current month #s(Current Month Gross), IMATT+NATInAtt(In Month Att), 1stAtt+NAT1stAtt(1st OM ATT), and OutAtt+NAT2ndAtt(>/= 2nd OM), and current month #s(NET). I did manage to create a crosstab query that shows what I want but for some reason it lists the people more than once:Click image for larger version. 

Name:	Access Help 17.JPG 
Views:	13 
Size:	61.4 KB 
ID:	46736
    I'll attach what it looks like in another post, I've reached max attachments. Let me know if you can see these photos. I'm pretty sure I've fixed the issue.

  8. #23
    P3anut is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    15
    Click image for larger version. 

Name:	Access Help 18.1.jpg 
Views:	12 
Size:	129.8 KB 
ID:	46737
    The drawn brackets show which names are duplicated.

    I'm starting to think that the reason it's duplicating the names is because that month was when it meets the criteria of the 3 new columns I added.

  9. #24
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    In the first screen shot where you show the form the metric for the last three fields that you added is missing (should be Sum like the two above).
    Would you be able to upload a new sample with the changes you made to the Master Query so I could see why you get duplicates?

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  10. #25
    P3anut is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    15
    Did what you said and I'm still getting an error: Click image for larger version. 

Name:	Access Help 19.JPG 
Views:	8 
Size:	94.2 KB 
ID:	46741
    When I hit debug it still takes me to the same line of code in VBA as before:Click image for larger version. 

Name:	Access Help 16.jpg 
Views:	8 
Size:	73.1 KB 
ID:	46742

    And here's a sample DB showing what I changed, it's labeled as Master Query 1: Sample DB 2.1.zip

  11. #26
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi,
    I am running the pivot based on the updated Master Query 1 and it seems to work with no errors and no duplicates.
    Click image for larger version. 

Name:	Screenshot 2021-11-22 084507.jpg 
Views:	9 
Size:	121.6 KB 
ID:	46744
    I assume you are getting the error when you run it in your actual db; you will need to use the debug window or to hover the mouse over the various variables on that line to see which one produces the error. Make sure the field names match the form.


    You are getting duplicates in your crosstab query because you added the three new calculated fields to the row headings forcing the query to group by them. They are actually data fields; you would have to create one crosstab query for each (they would need to replace the "Total" field in the crosstab and be summed like that one).

    Cheers,Vlad
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  12. #27
    P3anut is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    15
    Is there anyway to combine all the data field into one line for each person in access?

  13. #28
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Not really, to see them all on one line similar to your Excel mock-up you would need to use a temporary local table to act as a placeholder then run one append query to populate the first three fields (person, station and DIV) then start running Update queries based on the individual crosstabs joined to the local table via the first three fields to populate the sums for the four data fields (you could also add an update query for the current month as you had it in the Excel file), If you can upload a new Excel or Word mockup showing me exactly what is the source (matching the fields in Master Query or Master Query 1) is for each of those fields and the logic for the color scheme (conditional formatting) I could try to make a form that would put all of that together (it would probably help if you could add a few more records in the sample tables to have more than 2 persons).

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  14. #29
    P3anut is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    15
    This just sounds like it's going to get complicated so I really appreciate all the help (and teaching) but I think I'm just going to have to tell my supervisor that it's not possible. I can make what you have given me so far work.
    P3anut

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 12-20-2017, 09:45 PM
  2. Replies: 1
    Last Post: 09-27-2017, 12:11 AM
  3. Replies: 6
    Last Post: 09-10-2017, 08:13 PM
  4. Grouping info for the last twelve months
    By herbc0704 in forum Queries
    Replies: 2
    Last Post: 09-14-2011, 10:30 AM
  5. Replies: 1
    Last Post: 07-07-2009, 01:00 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums