Dsum 4th Critieria (can be null)

L

lcook1974

Registered User.
Local time
Yesterday, 20:04
Joined
Dec 21, 2007
Messages
330
Hey everyone!!
I have "hopefully" a quick one. I have below code and need to add one more parameter to it however that parameter can be "null" if they don't want to choose the unit.

Code: 
Nz(DSum("[DonorCnt]", "qHD_SummaryR1", "[Month] = 1 AND [year] = " & Me.cmbyear & " AND [Hosp] = '" & Me.cmbHospital & "'"), 0)

How would I add that?

Field type: text
Name: me.cmbUnit
Can be null value

Larry
 
Where are you using this? in a form? If so

Code: 
If Not  IsNull(Me.Unit) Then
   Include
Else
   Exclude
Endif
 
Hi DCrake,
Thanks for the prompt reply and I shall try it out....Is there no way to put in the DSum() function? for some reason I thought there was...

I have a related question about my "approach" to this task. I'm attaching a sample DB so you can see what I'm working with on the "after update" in the combo box for the hospital.

Basically I'm using A LOT of Dsum() functions (you'll see...) to fill a data sheet for my coordinators. The data is based off a "replication" server and is linked to that view I created. The problem is when I run the form while connected to the "replication" server it takes about 6 minutes to run. But I noticed when I copied the data, query and form over to a stand alone DB (no link connections) it fired in about 30 seconds. The highlighted Blue rows are where the coordinator will enter data for that month.

my question(s)

1. should I create a function that will copy the table I use for reports INSTEAD of linking the table? (in order to optimize efficency)

2. What would have been a better approach to this task? (instead of writing ALL the Dsum() functions?

Larry
 

Attachments

1. Usually running a copy of a source table is a bad idea, unles you are going to be referencing the source a lot of times.... More or less like your original solution

2. Use a select statement to sum them all in one go...
Select Month, sum() from yourtable group by month order by month

That would run only 1 query and then in code loop to fill your text boxes
Instead of having then "intJan" I would have int01, int02, etc... so I can use numbers to identify my text boxes.
 
Hey namliam!!

Hmmmm....yeah when I run the query linked the time is much greater. They will be using it quite a bit...depending on how many hospitals they are assigned.

I did the sum on all the fields in my query and grouped by Year, Month and Hosp. (now they want Unit added)

How would I write that loop code? (Just push me, shove me or whatever...then I'll try it and re-post it if it doesn't work) :)

Why would you want to identify your text boxes like? would you use the '01' in a code or something? (sorry I don't understand why that would be better...) I want to be better at this...just trying to understand.
 
The reason for using 01 instead of Jan in code is that some months in some languages can be different Mar / Maa (for English/Dutch) for example or May/Mei and Oct/Okt. Different months for different languages and may not be exlusive to these examples.
This can really throw you for a loop if your not carefull with Language settings in your regional settings on different users that use your code/db.
01 is 01 will stay 01 and will always be 01 no matter what and everyone can relate month 01 to being Jan and 05 being May or Mei or ....

Then on top of that instead of Me.Field01Name you can use...
Me.Control("Field01Name") and in that substitute in the 01
Me.control("Field" & Variable & "name")

So by doing a query like I stated above you can load that into a recordset (aircode):
Code: 
dim rs = dao.recordset
Set rs = Currentdb.openrecorset("Select bla")
Do while not rs.eof
    me.Control("F" & format(rs!Month, "00") & "N") = rs!SummedValue
    rs.movenext
loop
rs.close
set rs = nothing
Also instead of doing only one sum in the query you can do multiple sums for the same hospital in the same query.
Select Month, sum() CountOfBrunett, sum() CountofBlond, Sum() CountOfRedHead from yourtable group by month order by month

On top of that you can make the query be what you want/need it to be (including the where clause and leaving out things that dont need to be used (this time) ) adding where clauses where/when needed, more or less like David showed you above... Do a search on "Search form" and my user addy...
course I could just link this:
http://www.access-programmers.co.uk/forums/showthread.php?t=198549&highlight=Search+form
And see where it gets you going, mind you this is goes towards building the SQL part not about solving your form filling thing.

By doing this and actually writing the SQL into a ODBC / pass through query you have the (source) DB do the work / heavy lifting which will make all of your PC and Network and DB admin a happy camper(s).

So make a pass through like you have already and stick the (finalized) sql in there.
currentdb.querydefs("YourQueryName").SQL = "Select bla bla from bla bla"
Then load the query into the recordset like above, loop the recordset and fill the form like there is no tomorrow so lightning fast.

Good luck on your project and post back any questions you have.
 
alrighty then...I'll give it a go and get back with you.

Thanks! i believe if I can solve this one then I'll be able to apply that to the other reports!!
 
Ok...I apparently suck at VBA.

I thought I understood what you were telling me to do but something isn't firing in my brain.

I have created another form HD_SummaryR2 with only the first row on it, because i want to make sure the VBA works before I spend the time creating all of text boxes. Renamed the text boxes as namliam suggested.

I put a button on there to "refresh" the data...change the query to only include 1 hospital but all the months. There is a list box that shows the query so I can see what number should be in the textboxes.

The code behind the button does something, cause the numbers moving around the "1" appears.

Could I get an example of how I should've written "my" line ?
your formant below...
Code: 
me.Control("F" & format(rs!Month, "00") & "N") = rs!SummedValue
 

Attachments

Well that depends on what part you are asking about...
rs!SummedValue
This is the value you want to populate with, in your case I guess rs!OnVent

Control("F" & format(rs!Month, "00") & "N")
Hmz, Control should be Controls
and then the "F" & format(rs!Month, "00") & "N" should be the full name of your control/textbox... ie. int01_TotalReferrals
format(rs!Month, "00") will return the 01 part the F and N should the contain the other 2 parts... something like:
"int" & format(rs!Month, "00") & "_TotalReferrals"

There are a few 'mistakes'/oversights in your code
1) Your using the source table without using a sum... while your form seems to function off the query ... Use the sum query or do the sum in the code as well.
2) You are trying to fill the listbox with SQL, however if you want to display this you have to
a) Put the sql in the ROWSOURCE property
b) Requery it, otherwize it wont update
 
OMG..if I could kiss you I would. I fixed it all and now it takes 14 seconds...no I don't think you heard me right. 14 seconds!!! (when linked to the SQL server)

Thank you, Thank you, thank you!!!
 
lcook1974 said:
OMG..if I could kiss you I would. I fixed it all and now it takes 14 seconds...no I don't think you heard me right. 14 seconds!!! (when linked to the SQL server)

Thank you, Thank you, thank you!!!
Click to expand...

14 secs... dont sound to great to me....

I am going to assume your Year and Month fields are actually 'normal' date fields in SQL server and possibly indexed as well....

Using Year = 2010 in that case will perform CONSIDERABLY worse than using something like:
YourDateField between 01-01-2010 and 31-12-2010

Dont know if the HOSP column is indexed or not? Adding one will probably inprove performance of your query, though if its part of a(nother) bigger system you need to make sure it doesnt negatively impact that.

Then have you ever used Parameters? If you use them that will help SQL Server not re-trace the query but simply use the same execution plan for each query, though gain is probably minimal it is a gain.
It works something like so:

Code: 
Declare @dt_start datetime;
Declare @dt_end   datetime;
set @dt_start = cast('2009-01-01 00:00:00' as datetime);
set @dt_end = cast('2009-12-31 23:59:59' as datetime);
Select .....
from ...
where YourDate between @dt_start and @dt_end;

Because SQL will recognize the fixed variables, it will pick the same execution plan instead of trying to re-figure-out something it already knows.... like it would when you send it this query:
Code: 
Select .....
from ...
where YourDate between cast('2009-01-01 00:00:00' as datetime)
                   and cast('2009-12-31 23:59:59' as datetime);
 
IN CAN GO FASTER!?!?! Teach me jedi master... :)

In the SQL mgmt studio I converted everything to a number except the Hosp (text) and Unit(text). I took the date part 'year' and 'month' out when I created the view. HD_summary is a linked 'view'.

I will modify the vw to only include the 'casedate' (where I get the year and month) and run it again.

Attached is screen shot of table fields.

I know I shouldn't use the reserve words...All that is being corrected. :)

Thanks! I'll keep you posted.
 

Attachments

  • HD_SummaryTable.jpg
    HD_SummaryTable.jpg
    97.4 KB · Views: 114
new table fields screen shot
 

Attachments

  • HD_SummaryTable.jpg
    HD_SummaryTable.jpg
    95.8 KB · Views: 97
And your query-ing the linked view in access ??? There is your 14 secs..., your forcing access to do the hard work instead of SQL server.

That is assuming your linked view is the same as the table in your uploaded db.
Create a new query in access, just in design view but dont add anything.
Now with the query open go into the menu and got Query > Query Specific > Pass Through
Then right click your title bar and setup which ODBC to use, just like you did for the linked table/view

DONT use your existing view but rather use the source table of that view directly and make the query in there
"Select bla bla from SOURCETABLE where bla bla"
I.e. basicaly copy the sql you load into the rs, but replace your view by the 'real' complete sum query.

Then see how quick/slow that is, also you probably shouldnt use this line:
Set Me.Recordset = rs
As that is just wrong...

If you are lord, master and supreme ruler of this table/SQL server you should just add the indexes on the HOSP and any *often* searched fields as your reports will certainly benifit from the faster index searches.
 
I have learned much from you master jedi... :)

4.9 seconds...

And I thought 14 seconds was AWESOME!

Thank you!! I'll add some bells and whistles and hit you if I have any more questions.
 
If possible you could upload your current code, perhaps I can have another look and/or perhaps others can learn from it

Another idea popped into my head just now, have you tried using a Crosstab query instead of this code to display your form?
 
Absolutely....Take a look and let me know how to improve it. I'm working on getting the "strWhere" clause correct so that I can search by Year & Hospital OR Hospital, Unit, Year. Unit would be optional.

The hardest thing after the kick in the right direction was writing the SQL code in VBA, the syntax was killing me! :)

Thanks for ALL the help!!!

Code: 
Private Sub cmdRefresh_Click()
Dim rs As DAO.Recordset
Dim db As Database
 
If IsNull(Me.cmbyear) Then
    MsgBox "you must choose a year", vbOKOnly, "Year required"
    Me.cmbyear.SetFocus
End If
 
 
 
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT Month([casedate])as cMonth, Sum(HD.Onvent) AS Onvent, HD.Hosp, Sum(HD.TotalDeath) as TotalDeath, Sum(HD.TE_Timely) as TE_Timely,  " & _
                          "Sum(HD.EyeDonorCnt) as EyeDonorCnt, Sum(HD.TissDonorCnt) as TissDonorCnt, Sum(HD.DonorCnt) as DonorCnt, Sum(HD.DCD) as DCD, Sum(HD.CNR) as CNR, " & _
                        "Sum(HD.Timely) as Timely, Sum(HD.Imminent) as Imminent, Sum(HD.Eligible) as Eligible, Sum(HD.OrgTx) as OrgTx, " & _
                        "Sum(HD.txHeart) as txHeart, Sum(HD.txLiver) as txLiver, Sum(HD.txPanc) as txpanc, Sum(HD.txIntestine) as txIntestine, Sum(HD.DonorOther_Neither) as DonorOther_Neither, " & _
                       "Sum(HD.txLfKidney) as txLfKidney, Sum(HD.txRtKidney) as txRtKidney, Sum(HD.txLfLung) as txLfLung, Sum(HD.txRtLung) as txRtLung " & _
                      "FROM dbo_HD_Summary HD " & _
                      "GROUP BY Month([casedate]), HD.Hosp " & _
                      "HAVING [HD.Hosp]= '" & Me.cmbHospital & "'")
 
 
Do While Not rs.EOF
Me.Controls("int" & Format(rs!cMonth, "00") & "_TotalReferrals") = Nz(rs!OnVent, 0)
Me.Controls("int" & Format(rs!cMonth, "00") & "_AllDeaths") = Nz(rs!Totaldeath, 0)
Me.Controls("int" & Format(rs!cMonth, "00") & "_TETimely") = Nz(rs!TE_Timely, 0)
Me.Controls("int" & Format(rs!cMonth, "00") & "_EyeDonors") = Nz(rs!EyeDonorCnt, 0)
Me.Controls("int" & Format(rs!cMonth, "00") & "_TissueDonors") = Nz(rs!TissDonorCnt, 0)
Me.Controls("int" & Format(rs!cMonth, "00") & "_OrganReferrals") = Nz(rs!OnVent, 0)
Me.Controls("int" & Format(rs!cMonth, "00") & "_OrganTimeliness") = Nz(rs!Timely)
Me.Controls("int" & Format(rs!cMonth, "00") & "_Imminent") = Nz(rs!Imminent, 0)
Me.Controls("int" & Format(rs!cMonth, "00") & "_Eligible") = Nz(rs!Eligible, 0)
Me.Controls("int" & Format(rs!cMonth, "00") & "_OTPD") = Nz(rs!OrgTx, 0)
Me.Controls("int" & Format(rs!cMonth, "00") & "_OrganDonors") = Nz(rs!DonorCnt, 0)
Me.Controls("int" & Format(rs!cMonth, "00") & "_DCD") = Nz(rs!DCD, 0)
Me.Controls("int" & Format(rs!cMonth, "00") & "_CNR") = Nz(rs!CNR, 0)
Me.Controls("int" & Format(rs!cMonth, "00") & "_Heart") = Nz(rs!txHeart, 0)
Me.Controls("int" & Format(rs!cMonth, "00") & "_Liver") = Nz(rs!txLiver, 0)
Me.Controls("int" & Format(rs!cMonth, "00") & "_Pancreas") = Nz(rs!txPanc, 0)
Me.Controls("int" & Format(rs!cMonth, "00") & "_Intestine") = Nz(rs!txIntestine, 0)
Me.Controls("int" & Format(rs!cMonth, "00") & "_Kidneys") = Nz(rs!txLfKidney, 0) + Nz(rs!txRtKidney, 0)
Me.Controls("int" & Format(rs!cMonth, "00") & "_Lungs") = Nz(rs!txLfLung, 0) + Nz(rs!txRtLung, 0)
Me.Controls("int" & Format(rs!cMonth, "00") & "_DonorNeither") = Nz(rs!DonorOther_Neither, 0)
 
rs.MoveNext
 
Loop
MsgBox "Refresh is complete...check that data", vbInformation, "REFRESH COMPLETE!!"
rs.Close
Set rs = Nothing
 
End Sub

How would I be able to display a cross tab query and still have those fields the coordinator needs to fill in?

I have the SQL cookbook and plan on writing more queries in the SQL server then linking them to the database like this one.

The next project (due by Dec 1...that's when we get inspected) is to do something similar but I think cross tab is the way to go.


THANK YOU!!! for all your help!! I learn more and more on this forum...it's great!

Larry
 
Just one little improvement

Code: 
If IsNull(Me.cmbyear) Then
    MsgBox "you must choose a year", vbOKOnly, "Year required"
    Me.cmbyear.SetFocus
    [COLOR="Red"][B]Exit Sub[/B][/COLOR]
End If
This will prevent the remaining code running until a chosen year has been made.

Also Dim the Db and Rs after the initial check because if you don't you are leaving them hanging in limbo in your memory cache.

Code: 
Dim rs As DAO.Recordset
Dim db As [COLOR="red"][B]DAO.[/B][/COLOR]Database
Keep code consistant
 
You must log in or register to reply here.

Similar threads

A
Solved Nz has started throwing up an error message
2
Replies
23
Views
1,019
The_Doc_Man
The_Doc_Man
N
Solved How to grab fields from Json string to be part of the invoice
Replies
14
Views
1,037
Edgar_
E
L
Solved Problem calculating correct age from birth to death
Replies
11
Views
1,238
GPGeorge
GPGeorge
Rodeo Clown
How to present a list of items to the user where some of those rows may be unavailable (i.e. -greyed out /disabled)
2
Replies
26
Views
1,373
MajP
MajP
C
msgbox that you can position where you want relative to the cursor
Replies
0
Views
515
CJ_London
C

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom