UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Sorting On A Field In A Report, Access 2013    
 
   
rwu
post Nov 6 2017, 05:59 PM
Post#1



Posts: 43
Joined: 11-May 12



I have a report that I want to group on "Phase" (a number) and sort by "Work Code" (also a number).

When I run the query, I can manipulate it to look like how I want it ordered by sorting the "Work Code" field from smallest to largest and then doing the same with the "Phase" field. However, when this query gets converted to a report and is formatted to group and sort, it groups all the items in the phase together, but then "Work Code" has an odd order, e.g.,

7001

31232

33421

33425

500015

31248

55201

31230

500016

500018

55220

Any suggestions on how to get it in true ascending order? The group, sort and total is:

Group on Phase from smallest to largest

Group on Work Code from smallest to largest

Sort by Work Code from smallest to largest (This seems redundant since the grouping is already from smallest to largest, but I wanted the header and footer for Work Code. I've also tried it with and without this sort with the same result.)

I have also tried converted the numbers to text with a "0" in front of any number that was not 6 digits and still obtained the same problem.

Thanks for any suggestions.














Go to the top of the page
 
theDBguy
post Nov 6 2017, 06:06 PM
Post#2


Access Wiki and Forums Moderator
Posts: 71,220
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Just a guess but try sorting the grouped field first before grouping it. For example:

Sort Phase
Sort WorkCode
Group Phase

Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
rwu
post Nov 6 2017, 06:11 PM
Post#3



Posts: 43
Joined: 11-May 12



Just tried your suggestion and wound up with essentially the same result except that everything is grouped separately, i.e., every item is a separate group. But the order on the work code is still goofy.
Go to the top of the page
 
theDBguy
post Nov 6 2017, 06:17 PM
Post#4


Access Wiki and Forums Moderator
Posts: 71,220
Joined: 19-June 07
From: SunnySandyEggo


Sorry about that. Can you post a sample copy for us to play with?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
rwu
post Nov 6 2017, 06:43 PM
Post#5



Posts: 43
Joined: 11-May 12



Sure, please see the attached. The report is the only one in there. Use 10/21/17 for both of the date prompts and you should see the results mentioned in my earlier post.

Thanks.
Attached File(s)
Attached File  HSI_Timecard.zip ( 346.14K )Number of downloads: 6
 
Go to the top of the page
 
projecttoday
post Nov 6 2017, 11:11 PM
Post#6


UtterAccess VIP
Posts: 8,679
Joined: 10-February 04
From: South Charleston, WV


It doesn't make sense to have a group and a sort on the work code with a group on OT in between.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
rwu
post Nov 7 2017, 04:03 PM
Post#7



Posts: 43
Joined: 11-May 12



I don't remember why that was there - maybe the user wanted to have OT separated out. Even if it is removed, I think the other problem still exists....
Go to the top of the page
 
theDBguy
post Nov 7 2017, 04:33 PM
Post#8


Access Wiki and Forums Moderator
Posts: 71,220
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Sorry for the delay... Is this what you wanted?
Attached File(s)
Attached File  HSI_Timecard.zip ( 349.86K )Number of downloads: 4
 

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
rwu
post Nov 7 2017, 04:43 PM
Post#9



Posts: 43
Joined: 11-May 12



No worries about delay. Yes, it is. Looks like you took the sort out and did it through the groupings? Could you explain what was going on for my future reference?

Thank you!
Go to the top of the page
 
theDBguy
post Nov 7 2017, 04:46 PM
Post#10


Access Wiki and Forums Moderator
Posts: 71,220
Joined: 19-June 07
From: SunnySandyEggo


Hi,

I took the sort out because it was redundant. However, the underlying problem was your use of a lookup field at the table level.

So, what I did was remove the reference to the lookup field and added the related table in the query and updated the control source for the combobox, which I then converted into a textbox.

Hope it makes sense...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
rwu
post Nov 7 2017, 04:56 PM
Post#11



Posts: 43
Joined: 11-May 12



I see what you did. And yes, it does make sense. Thanks for finding the bug. You are always helpful!
Go to the top of the page
 
theDBguy
post Nov 7 2017, 11:07 PM
Post#12


Access Wiki and Forums Moderator
Posts: 71,220
Joined: 19-June 07
From: SunnySandyEggo


Hi,

You're welcome. Robert and I were happy to assist. Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
rwu
post Nov 22 2017, 03:28 PM
Post#13



Posts: 43
Joined: 11-May 12



My user has been back into this database after the fixes and it has a new problem. I suspect it is related to the removal of the reference to the lookup field, but I'm not sure how. She used to be able to copy/paste the dates in the multiple records without filling every thing else in for efficiency (many entries with the same date), i.e., she does a number of records with just the same date only. Now it won't let her go past the 1st added record without filling everything in. It comes up with the message "The Microsoft Access database engine cannot find a record in the table "Work Codes" with key matching field(s) 'Work Code'. Any suggestions?
Go to the top of the page
 
rwu
post Nov 22 2017, 03:34 PM
Post#14



Posts: 43
Joined: 11-May 12



Wait - I'm going to pick back through what you sent back and see if something got overlooked...I'll be back if I can't figure it out.
Go to the top of the page
 
rwu
post Nov 22 2017, 05:33 PM
Post#15



Posts: 43
Joined: 11-May 12



theDBguy, I went back to what you sent back to me with the fix to the report (which works fine). I tried what I described as far as copy/pasting into the date worked field and got the same message about not finding the Work Codes. This didn't exist before....
Go to the top of the page
 
theDBguy
post Nov 22 2017, 06:21 PM
Post#16


Access Wiki and Forums Moderator
Posts: 71,220
Joined: 19-June 07
From: SunnySandyEggo


Hi,

If you’re saying when you download the original file you uploaded in post #5 and it doesn’t have the problem you’re describing right now, then I am thinking it must have something to do with the lookup field in the table.

Sent from phone...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
rwu
post Nov 22 2017, 06:23 PM
Post#17



Posts: 43
Joined: 11-May 12



That's correct. She didn't have this problem in the original one, but it exists now. Not sure how the lookup (or taking it away) goofed it up. It exists in the file you modified and sent back.
Go to the top of the page
 
theDBguy
post Nov 22 2017, 09:35 PM
Post#18


Access Wiki and Forums Moderator
Posts: 71,220
Joined: 19-June 07
From: SunnySandyEggo


Hmm, I thought I took it out because it typically causes problems. I am not in front of a computer right now but try applying my solution to the original file and see if it causes the current issue.

Sent from phone...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
theDBguy
post Nov 23 2017, 07:37 PM
Post#19


Access Wiki and Forums Moderator
Posts: 71,220
Joined: 19-June 07
From: SunnySandyEggo


Hi,

I downloaded the database to try it out. Can you please give me the steps to duplicate the problem? Thanks.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
rwu
post Nov 27 2017, 04:02 PM
Post#20



Posts: 43
Joined: 11-May 12



Back after the holiday.

If you open up the Work Hours List (form) or the Work Hours table and go to new record. Type a date like 11/4/17 into Date Worked. Then copy it and try to paste it into subsequent records without filling anything else in on the 1st new record. You should get the error code I previously mentioned. The workaround this is to fill in the entire record before going on to the next one, but the user likes to fill the dates in on numerous records before filling in the rest, since it can be the same for 5, 10, etc. records.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    12th December 2017 - 10:31 PM