Question update field based on date range

[semry]

I have 2 tables like this

TableA
FromDate
ToDate
BatchType
TotalBatches

TableB
Date
BatchType
NumBatches

Is their a way to update the TotalBatches in TableA with the sum of NumBatches from tableB that have the same BatchType and falls between the FromDate and ToDate of TableA?

 

[AccessMSSQL]

Yes. Write a function in VBA first and then call the function from a query.

Public Function GetTotalBatches(BatchType as integer, FromDate as date, ToDate as Date)
 
dim strSQL as string
strSQL = "SELECT Sum(NumBatches) as SumOfBatches FROM TableB WHERE Date "
strsql = strsql & " between #" & fromdate & "# and #" & ToDate & "#"
strsql = strsql & " AND BatchType = " & batchtype
 
dim rs as dao.recordset
set rs = currentdb.openrecordset(strsql,dbopendynaset, dbopendynamic)
if not rs.eof then
   GetTotalBatches = rs("SumOfBatches")
end if
rs.close
set rs = nothing
end Function

Here is the query:
UPDATE TableA SET TotalBatches = GetTotalBatches(BatchType, FromDate, ToDate)

 

[semry]

I couldn't get it work. I keep getting an data type mismatch error calling it from a query

 

[jdraw]

Date is a reserved word in Access.
You shouldn't store calculated values in Tables. If you can calculate your field in a query, the use the query to do the calculation when required.

For Between see
http://www.techonthenet.com/sql/between.php

 

[vbaInet]

Like jdraw said, calculated values should not be saved. It's only in exceptional cases that it's not deemed as bad design.

* Create a SELECT query and add the two tables in the query
* Join by the BatchType fields in both tables

http://office.microsoft.com/en-us/access-help/sum-data-by-using-a-query-HA010096310.aspx

But with regards the BETWEEN what criteria will you use? I ask this because you have two date fields.

 

[semry]

Tried re-naming all my Dates, nothing. Also my first choice was to display my results in a query but I can't figure out how to calculate it.

 

[vbaInet]

Tried re-naming all my Dates, nothing. Also my first choice was to display my results in a query but I can't figure out how to calculate it.

See the link provided.

 

[semry]

I've tried the links again and attached is what I get

 

[vbaInet]

First of all remove the criteria and see the result, then put the criteria under the appropriate field. You have your criteria all wrong.

And you didn't answer this question. -->

But with regards the BETWEEN what criteria will you use? I ask this because you have two date fields.

 

[semry]

I'm trying to sum the number of batches by batch type in table B that fall between the From and To dates of Table A

 

[vbaInet]

Please refer to the proper table names. I can see the tables in your screenshot and referring to table A or B is meaningless to me or anyone reading this thread.

Is there a Date field in BatchProd? You have a From and To date in ProductivityRpt so if there's no date field in BatchProd I don't understand what you're trying to connect there.

 

[semry]

ProductyRpt
FromDt - Date field
ToDt - Date Field
BatchType - Text


BatchProd
IndexDt - Date
BatchType - text
NumBatches - Numeric

I need to sum NumBatches for each batchType that falls between FrmDt and ToDt.

 

[vbaInet]

I think you mean, you need a sum of NumBatches for each BatchType where the IndexDt falls between FrmDt and ToDt. Am I right?

 

[semry]

Now I think your F***ing with me
Thanks for not helping

 

[vbaInet]

Watch your language. If we were playing with people on here do you think this forum will be as successful as it is. To keep to protocol, you've been reported.

Now back to your question, you're just not explaining yourself properly and that's the reason why you've not had any other person reply to your thread. As much as it is frustrating for you that you haven't yet had an answer it is also frustrating for us when the OP is not properly explained for us to help.

Are you giving the query a date and you want it to look for records where the date you inputted falls between the From and To dates?

 

[semry]

Ok I will try and be clear. My tables/Queries are attached

ProductyRpt
FromDt - Date field
ToDt - Date Field
BatchType - Text


BatchProd
IndexDt - Date (Yes, this is a date)
BatchType - text
NumBatches - Numeric

BatchProd is acutally a simple query of tables Productivity and BatchCtrl joined at BatchNam.

ProductivityRpt is a Excell report I get once a month so please don't give me a hard time about the spaces in the field names, It's not mine.

Each FromDt - ToDt covers one week of the month.

I need to sum the [BatchProd]![NumBatches] for each [BatchProd]![BatchType] that have a [BatchProd]![IndexDt] that falls between the [ProductivityRpt]![FromDt] and [ProductivityRpt]![ToDt]

I hope the results would look like this
FromDt...ToDt......BatchType..........SumOfNumBatches
1/1/12....1/8/12...1_INPT_SINGLE... 204

 

[semry]

I forgot the attachment

 

[vbaInet]

ProductivityRpt is a Excell report I get once a month so please don't give me a hard time about the spaces in the field names, It's not mine.

I wasn't even going to anyway. Too early in the day for that

Now let's compare these three quotes:

I need to sum the [BatchProd]![NumBatches] for each [BatchProd]![BatchType] that have a [BatchProd]![IndexDt] that falls between the [ProductivityRpt]![FromDt] and [ProductivityRpt]![ToDt]

I think you mean, you need a sum of NumBatches for each BatchType where the IndexDt falls between FrmDt and ToDt. Am I right?

I need to sum NumBatches for each batchType that falls between FrmDt and ToDt.

Can you see that 1 and 2 are the same whereas 3 doesn't mention anything about IndexDt. That field was the crucial part of the puzzle which I was trying to clarify. This is why we need posters to be explicit as possible in their explanations. I hope you understand.

Have a look at qrySumPerBatchType. Note that grouped fields have to come first and it should come in the order that I've set it. If you were to place it in the order you showed in your second to last post, you will not get the desired results, very important. If you want it in a different order you can create a separate query based on the query above and arrange it as you wish.

 

[semry]

I've tried that before and it does not work. The sum of AMB between 1/1/12 and 1/8/12 is 208 not 1034.

Also I need the sum of BatchTypes not batch names.

When I remove BatchNames from the query I get what I need as far as BatchTypes for each Date period but the sum of BatchTypes for 1_Single_Patient during the date period 1/1/12 to 1/8/12 should be 288 not 3168.

 

[AccessMSSQL]

Semry:
The reason you probably get a datatype error is because your batchtype field is probably a text field. My code assumed it was an integer field.

Here's modified version:

Public Function GetTotalBatches(BatchType as integer, FromDate as date, ToDate as Date)
 
dim strSQL as string
strSQL = "SELECT Sum(NumBatches) as SumOfBatches FROM TableB WHERE Date "
strsql = strsql & " between #" & fromdate & "# and #" & ToDate & "#"
strsql = strsql & " AND BatchType = '" & batchtype & "'"
 
dim rs as dao.recordset
set rs = currentdb.openrecordset(strsql,dbopendynaset, dbopendynamic)
if not rs.eof then
   GetTotalBatches = rs("SumOfBatches")
end if
rs.close
set rs = nothing
end Function

Try that. All I did was surround the batchtype comparison with single quotes.