Query: And/Or Criteria Vs. Totals

M

Manny74

Board Regular
Joined
May 6, 2016
Messages
124
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hello All,

So I am trying to determine how many orders my provider entered by noon, also how many discharge orders entered by noon.

It seems Access has two common criterias I can use, And is one, and Or is another.

I want a query that gives me both the numbers of orders and discharges orders entered before noon...It seems the query I created gives me one or another.

My query has <#12:00PM# for Order by Noon...but when I enter <#12:0PM# for D/C by Noon (the Or row, I only get the total for Order by Noon (see below, 21)....

Ideally I'd like to see totals for both (Order by Noon 21 and D/C by Noon 22 - see below)

When I place<#12:00PM# for Order by Noon and D/C by Noon, on the same row, I only get records that have both Order by Noon and D/C by Noon....

How can I get both totals on my query?



[TABLE="width: 353"]
<colgroup><col width="85" style="width: 64pt; mso-width-source: userset; mso-width-alt: 3108;"> <col width="86" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3145;"> <col width="127" style="width: 95pt; mso-width-source: userset; mso-width-alt: 4644;"> <col width="108" style="width: 81pt; mso-width-source: userset; mso-width-alt: 3949;"> <col width="64" style="width: 48pt;"> <tbody>[TR]
[TD="width: 85, bgcolor: transparent"]MD[/TD]
[TD="width: 86, bgcolor: transparent"]VISIT TYPE[/TD]
[TD="width: 127, bgcolor: white"]ORDER BY NOON[/TD]
[TD="width: 108, bgcolor: white"]D/C BY NOON[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DOE MD, JIM[/TD]
[TD="bgcolor: transparent"]IP[/TD]
[TD="bgcolor: white"]22:06[/TD]
[TD="bgcolor: white"]0:10[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DOE MD, JIM[/TD]
[TD="bgcolor: transparent"]IP[/TD]
[TD="bgcolor: white"]12:36[/TD]
[TD="bgcolor: white"]2:22[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DOE MD, JIM[/TD]
[TD="bgcolor: transparent"]IP[/TD]
[TD="bgcolor: white"]16:17[/TD]
[TD="bgcolor: white"]6:35[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DOE MD, JIM[/TD]
[TD="bgcolor: transparent"]IP[/TD]
[TD="bgcolor: white"]11:21[/TD]
[TD="bgcolor: white"]10:10[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DOE MD, JIM[/TD]
[TD="bgcolor: transparent"]IP[/TD]
[TD="bgcolor: white"]10:58[/TD]
[TD="bgcolor: white"]10:20[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DOE MD, JIM[/TD]
[TD="bgcolor: transparent"]IP[/TD]
[TD="bgcolor: white"]6:44[/TD]
[TD="bgcolor: white"]10:37[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DOE MD, JIM[/TD]
[TD="bgcolor: transparent"]IP[/TD]
[TD="bgcolor: white"]8:29[/TD]
[TD="bgcolor: white"]10:50[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DOE MD, JIM[/TD]
[TD="bgcolor: transparent"]IP[/TD]
[TD="bgcolor: white"]8:02[/TD]
[TD="bgcolor: white"]11:10[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DOE MD, JIM[/TD]
[TD="bgcolor: transparent"]IP[/TD]
[TD="bgcolor: white"]10:05[/TD]
[TD="bgcolor: white"]11:14[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DOE MD, JIM[/TD]
[TD="bgcolor: transparent"]IP[/TD]
[TD="bgcolor: white"]8:54[/TD]
[TD="bgcolor: white"]11:26[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DOE MD, JIM[/TD]
[TD="bgcolor: transparent"]IP[/TD]
[TD="bgcolor: white"]8:29[/TD]
[TD="bgcolor: white"]11:27[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DOE MD, JIM[/TD]
[TD="bgcolor: transparent"]IP[/TD]
[TD="bgcolor: white"]6:50[/TD]
[TD="bgcolor: white"]11:28[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DOE MD, JIM[/TD]
[TD="bgcolor: transparent"]IP[/TD]
[TD="bgcolor: white"]7:17[/TD]
[TD="bgcolor: white"]11:30[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DOE MD, JIM[/TD]
[TD="bgcolor: transparent"]IP[/TD]
[TD="bgcolor: white"]9:25[/TD]
[TD="bgcolor: white"]11:36[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DOE MD, JIM[/TD]
[TD="bgcolor: transparent"]IP[/TD]
[TD="bgcolor: white"]6:47[/TD]
[TD="bgcolor: white"]11:41[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DOE MD, JIM[/TD]
[TD="bgcolor: transparent"]IP[/TD]
[TD="bgcolor: white"]10:30[/TD]
[TD="bgcolor: white"]11:50[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DOE MD, JIM[/TD]
[TD="bgcolor: transparent"]IP[/TD]
[TD="bgcolor: white"]12:24[/TD]
[TD="bgcolor: white"]11:52[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DOE MD, JIM[/TD]
[TD="bgcolor: transparent"]IP[/TD]
[TD="bgcolor: white"]10:29[/TD]
[TD="bgcolor: white"]11:55[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DOE MD, JIM[/TD]
[TD="bgcolor: transparent"]IP[/TD]
[TD="bgcolor: white"]8:21[/TD]
[TD="bgcolor: white"]11:58[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DOE MD, JIM[/TD]
[TD="bgcolor: transparent"]IP[/TD]
[TD="bgcolor: white"]10:40[/TD]
[TD="bgcolor: white"]11:58[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DOE MD, JIM[/TD]
[TD="bgcolor: transparent"]IP[/TD]
[TD="bgcolor: white"]11:29[/TD]
[TD="bgcolor: white"]11:59[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DOE MD, JIM[/TD]
[TD="bgcolor: transparent"]IP[/TD]
[TD="bgcolor: white"]12:58[/TD]
[TD="bgcolor: white"]11:59[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DOE MD, JIM[/TD]
[TD="bgcolor: transparent"]IP[/TD]
[TD="bgcolor: white"]9:21[/TD]
[TD="bgcolor: white"]12:05[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DOE MD, JIM[/TD]
[TD="bgcolor: transparent"]IP[/TD]
[TD="bgcolor: white"]10:11[/TD]
[TD="bgcolor: white"]12:05[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DOE MD, JIM[/TD]
[TD="bgcolor: transparent"]IP[/TD]
[TD="bgcolor: white"]10:11[/TD]
[TD="bgcolor: white"]12:18[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DOE MD, JIM[/TD]
[TD="bgcolor: transparent"]IP[/TD]
[TD="bgcolor: white"]9:25[/TD]
[TD="bgcolor: white"]12:30[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DOE MD, JIM[/TD]
[TD="bgcolor: transparent"]IP[/TD]
[TD="bgcolor: white"]12:26[/TD]
[TD="bgcolor: white"]12:30
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DOE MD, JIM[/TD]
[TD="bgcolor: transparent"]IP[/TD]
[TD="bgcolor: white"]14:15[/TD]
[TD="bgcolor: white"]12:30[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: white"]21
[/TD]
[TD="bgcolor: white"]22
[/TD]
[TD="bgcolor: transparent"]43
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Sort by date Sort by votes
Hi as a rule in cases like this (where criteria is possibly at odds), you should write two queries for this. If you want to you can "combine" them into a single report after that.
 
Upvote 0
I agree. I would build a query to add the category I want to classify the record in (for example 'morning' for before noon) then run a second query totaling by category.
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
Access table set start of day and end of day to my schedule
Replies
2
Views
633
Tcurtis
T
K
  • Question Question
Auto Sort (Descinding) based on a column header using VBA
Replies
1
Views
253
Fluff
Fluff
S
  • Solved
Customized running total in Excel power query
2
Replies
11
Views
446
JGordon11
JGordon11
T
  • Question Question
Formula to sum multiple sequences/ranges of values within an array?
Replies
2
Views
250
Fluff
Fluff
A
  • Solved
Sum hours by date range by employee ID
Replies
3
Views
267
alessiaexcels
A

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top