MS Access SQL Query in VBA

[h2o4001]

Hi!

I have never programmed anything in VBA before, so I don't have a clue what I'm doing... Anyway, here is what I'm trying to do. I need to find records in a table based on a date that is entered into a form and also the type (which is a dropdown box w/ the possible choices) which is on the same form. In other words, there are two variables which need to be passed from the form to the VBA code and then used in the SQL Query that is programmed in the VBA code. I want this to run when the user cliks on a button.

Here is what I'm trying to work with... There is more, but I figured that if I can get this small part to work then I will have a greater chance figuring out the other parts... Thanks for any help!



Private Sub Run_Carryover_Click()

Dim TotalReceiptsSQL As String

TempVars.Add "DateVar", "[Forms]![Weekly Carryover]![EndDate]"
TempVars.Add "TypeVar", "[Forms]![Weekly Carryover]![ConcernType]"

TotalReceiptsSQL = "SELECT MAX(Receipts.[Date Received]) AS [Date], MAX(Receipts.[Reporting Type]) AS Type, Sum(Receipts.[Total Receipts]) AS [Sum Of Total Receipts] FROM Receipts WHERE (((Receipts.[Date Received]) Between #"

TotalReceiptsSQL = TotalReceiptsSQL & DateVar

TotalReceiptsSQL = TotalReceiptsSQL & "#) AND ((Receipts.[Reporting Type])= '"

TotalReceiptsSQL = TotalReceiptsSQL & TypeVar

TotalReceiptsSQL = TotalReceiptsSQL & "'));"


DoCmd.RunSQL TotalReceiptsSQL

End Sub


When I clike the button to run the code, I get the following error message "A RunSQL action requires an argument consisting of an SQL statement."

[OBP]

Can I ask why it has to be a VBA SQL code rather than a simple Access Query?

[h2o4001]

I guess it doesn't have to be, but I wanted to try it that way...

Here is what I'm doing...

I have three tables. Concerns, Receipts, Carryover

The concerns table is where the info on the concern is entered and where it is also indicated as being closed (Columns: Type, Date Closed, Closed (yes/no checkbox).

Receipts is how many concerns we get (Columns: Type, Date Received, Total Number).

The Carryover table is where I want to store the data from the other two tables.

Basically what I need to do is take from receipts the sum of all the receipts of a given type received on a given date. Then I need to subtract from that the number of closed concerns based on the closed type and the same date used in the receipts table. This will give me the carryover for the day (the amount of concerns that we have yet to finish for the day). Then the next day I need: Carryover (from previous day the concerns that were not closed) + Receipts (received today) - Closed (closed that day) to determine that days carryover. Which I want to store in the carryover table.

So it's Carryover = Previou_Carryover + Receipts - Closed

I would let access figure this out everytime (perform the calculations on the fly, but I'm worried that if there are a lot of records spread out over a large amount of time it will slow it down considerably since it will have to search each record to determine the type and when it was closed...).

Make sense?

[h2o4001]

Oh, the VBA that I posted doesn't do all that I mentioned in the previous post... That was just a starting point for me so I could try to figure it out...

[h2o4001]

Oh, one other thing... If at all possible I would like it to perform that calculation from the date that is entered (most likely it will be today's date) back 30 days so that way if any closed dates or receipts are updated later that will be taken into consideration... Which means that the Carryover Table will have to be updated and then have the newest date inserted into it...

I'm probably making this way too complicated... Live and learn though!

Thanks for any help!

[OBP]

I think that you might just as well do it on the fly as each time you run your update query to put the data in to the carryover table it will run through all the records anyway and then have to update the table n top of that and then present the data.
If you could post a zipped copy of your database it would help a lot.
Which version of Access are you working with?
I can only handle up to Access 2002.

[h2o4001]

OBP,

Thank you so much for all your help. I'm working w/ 2007, but I have it in 2002-2003 file format. I think you should be good to go with opening and using it.

Ok, little more detail on what I'm attempting with this...

Tables:

ARS_Concerns_Internet - used to track the conerns and supporting info.

Employees - Common table to store all the employee info

Receits - stores the amount of concerns we get in (receipts), type, and date

Weekly_Carryover (going to rename to daily_Carryover) - Stores the date and the number of receipts that are carried over. Type needs to be incuded since the carryover will be based on the type of receipt (each receipt type will have it's own carryover)
{Formula to determine carryover: Carryover = receipts + Carryover - Closed
EX....
Date Receipts Type Closed Carryover
9/1/07 100 Internet 10 90
9/2/07 100 Internet 10 180
9/3/07 100 Internet 10 270

I wanted to store the carryover value for each date in the daily_carryover (currently weekly_carryover) form so that way when I run the query to figure out the number of receipts and closed concerns, I would only have to go back only so far (to check for any updates to previous dates numbers) in stead of going all the way back to the begining. I would like it to recaculate the receipt number and closed concerns back for 30 days each time the query is run. 9 months (just as an example) from now, I don't want to have to go back all the way to the beginnig to get the carryover number. I wanted it to be able to look back to the day before the 30 days, pick up the carryover and then continue on calculating that value... (Make sense??)

I've been thinking about it, and I guess this would work to (same format as example above):

9/1/2007 100 Internet 10 90
9/2/2007 100 Internet 10 90
9/3/2007 100 Internet 10 90
Total: 300 Internet 30 270

Do you think that would be better to store in a table? I know that you shouldn't store calculated values, but having it check every concern/receipt everytime will slow it down...

Queries:
Most have corresponding forms with them.

Receipt/Closed_Total_Query - Gets the total receipts for the day plus the total number closed for the day based on type and begin and end dates specified.

Carryover_Count_Query - my attempt to get the total carrover for the date range by adding the columns generated by the Receipt/Closed_Total_Query


At this point, I'm open to any suggestions that you might have...

[OBP]

How many records do you think you are likely to get?
Queries will run 10,000 in a second or two.
I am still not quite clear on the output required, taking your example
Date Receipts Type Closed Carryover
9/1/07 100 Internet 10 90
9/2/07 100 Internet 10 180
9/3/07 100 Internet 10 270
you appear to have the Carryover accumulating, but won't they be actioned during the carry over period?
You also seem to have a disconnection between your data.
The Main table holds open & closed but the receipts are in the receipts table, what connects them?
You have no relationships between tables.

[h2o4001]

OBP,

Lets put it this way....

We get a receipt (in other words we receive a concern someone has). Each concern in considered a receipt. So the total number of concerns we receive in a day = the total number of receipts. The type of receipt is how the concern was submitted (ie. Internet, CIC-call center...).

The ARS_Concerns_Internet is where the internet team will log the concerns (the extra information about the receipt). You can relate the two tables by the type of receipt/concern.

As for the carryover... We receive receipts everyday and work the receipts as fast as we can. However, sometimes we can't get through all of them, so we "carryover" some to the next day from the previous week.

I need to be able to show management how many receipts we got in, the number we were able to close that day, and the number that we didn't get to... Most likely we will not be carrying over as many as I indicated in the example.

I would leave the calculations to be done on the fly (figuring out how many of each type are closed in a day, how many receipts we got, the carryover) but there are 23 people who will be logging this stuff and there isn't an end date in sight (I do know that eventually we will have to archive some of it because Access isn't built to handle millions of records...).

So to make up for this, I was thinking that I can create a simple table to store the number of receipts what we get in, the number that we close and the number we have left over for the day (plus the number of receipts we had from the previous day). So yes, the Carryover will be accumulating, since we don't get through all the receipts in at once... Moste likely we will carry some, but hopefully not a lot (unlike the example I gave where it looks like we are carrying over a lot).

I eventually need a form that will show the current weeks receipts, number closed, and the carryover (for each day - with carry over being cumulative from the carryover from the previous date). So we get in 100 receipts on Monday, we close 80 of them, the carryover for Monday will be 20. On Tuesday, we get in another 100, and we close another 80, which leaves 20 for Tuesday, but we also have the other 20 receipts we didn't get to on Monday, so the final total Carryover for Tuesday will be 40. On Wednesday we get in another 100, close 80, carry 20 for Wed plus the previous 40 so the final Carryover for Wed is 60. Make sense?

When the database is running the queries to determine all of this, I need it to go back at least 30 days and recalculate all that information so that way if someone updates a number some where it's accounted for (either close a receipt or add to the number of receipts received). This database will be running for a while so I don't want it to have to go back to the very begining every time to figure this out...

If this is still unclear to you, let me know and I will try to explain it in another way... I don't know if you have Excel or not, but I'm attaching a spreadsheet showing how the Carryover is calculated...

Thank you so much for you help!!!!

[h2o4001]

Ok, so I just realized that the Weekly_Carryover and Receipts Tables were really dulicating data... Not the best design...

How about if we combine the Weekly_Carryover and the Receipts Tables into one where it will have the Date Received, ReceiptsTotal, Type, NumberClosed, and Carryover

Then build a query that will enable the user to update this table with the number of closed receipts (based on type and closed date which will be, eventually tied to a form for them to select the type from a listing of types and they enter the closed date)? The query though needs to go back for 30 days prior to the end date selected to recalculate the number closed so that way if someone forgot to update something it will get re-figured in automatically (does that make sense?). It also needs to update the Carryover portion of the table with the cumulative sum for each date (based on the end date selected and 30 days prior).

Example...
Date.........Type.......ReceiptsTotal..NumberClosed...............Carryover
9/09/2007..Internet..100.................50................................50
9/10/2007..Internet..100................20................................130
9/11/2007..Internet..100...........(not filled in until query run)....130
9/12/2007..Internet..100..........(Not filled in until query run)....130

The query that needs to be run will check for the type of receipt, closed date, and closed?=yes. Which when that query is run, we might see that on 9/11/2007 we closed 75 Internet concerns/receipts. Then this info will need to be added to the Receipt table row 9/11/2007 (closed date = receipt date) where the type matches the type closed.

After updating the table, it would look like this (if the [end date] used was 9/11):
Date.........Type.......ReceiptsTotal..NumberClosed...............Carryover
9/09/2007..Internet..100.................50................................50
9/10/2007..Internet..100................20................................130
9/11/2007..Internet..100.................75................................155
9/12/2007..Internet..100..........(Not filled in until query run)....155

I know that eventually we will probably have to archive some of these to keep the database running smoothly. If we do that, I was concerned that it would affect the carryover calculation since we would be removing the record of the receipt and concern (in ARS_Concerns_Internet Table). By storing the Carryover in the Receipt Table, I figured that would be a way around that...

If the calculations are perfomed on the fly, and carryover is not stored in the receipt table, then Carryover would be calculated as (see below example of table): 205 right? Or am I not looking at this right
Date.........Type.......ReceiptsTotal..NumberClosed...............
*9/09/2007..Internet..100..............50.......***Archived record***
9/10/2007..Internet..100................20.............................
9/11/2007..Internet..100.................75.............................
9/12/2007..Internet..100..........(Not filled in until query run)..

Apparently I didn't think this through enough when I started in on this... Live and learn!

[OBP]

Now we are getting somewhere, I still think that you do not need a table, you could just restrict the record check to say the last 3 months, that would run quickly enough.
I would have thought that any carried over receipts would be worked on before new receipts, otherwise they will never catch up.
I shouldn't think that you will have archive data more than once per year, how many calls do you think will be handled per day?
I have been very busy today on 3 other databases but I will do some work on this tomorrow (I am in the UK so it is evening here).
Do you want me to create ta main Menu or add the required changes to any forms or anything?

[h2o4001]

OBP...

Ok, I know that this has to be confusing...

I just wanted to say thank you for all your time and effort!

[h2o4001]

You are right, we will never be "caught up"... We constantly get in more every day. Sometimes we can't close a receipt because we need to go to someone else for an answer (which takes time). While we are waiting, we go ahead and keep working on the new receipts and any old ones we might have. We actually close more than I indicated in the examples... I was just using those numbers to show how the carryover will accumulate.

We usually get in about 180 to 230 per day (sometimes more, sometimes less)... There is also 4 other teams though with the same amount of receipts and people, who eventually will be added into this database so that number will go way up!

I would love to restrict it to just 3 months but the carryover is cumulative and I need to make sure that it keeps the right numbers (even if we archive some data)... I figured having it count every closed receipt every time from the begining to the end date selected would get very intensive since eventually there will be about 100 of us using this database with 900-1150 receipts being received daily...

As for adding/changing anything, if you want to, go ahead! I would appreciate any help with this. Or if you just want to give me the ideas and have me implement them (to the best of my limited ability) then I can do that. Sometimes I think I learn the best by seeing it... It's up to you since it's your time.

[h2o4001]

Oh, one more thing... In theory, we could close more receipts than we get in. What I'm getting at is that say on 9/13 we closed 125 receipts, they could be any of the receipts that we had been carrying over from the previous days and some of the current or any combination there of... When we close the receipt (the closed date) that is when we can count it and include it in the NumberClosed column in this example. It would be counted on whatever day it was closed (no matter what date it was actually received). The day the receipts are actually received by us must always be the receipt date. So if we we get in 100 receipts on 9/9, then the total amount of receipts needs to be counted on that date. Whenever we close them is when they get counted in the NumberClosed on the closed date. So we could get a receipt in on 9/9 (gets recorded under receipts for that date) and we might close it on 9/14 (gets recorded under the NumberClosed for that date).

Date.........Type.......ReceiptsTotal..NumberClosed...............Carryover
9/09/2007..Internet..100.................50................................50
9/10/2007..Internet..100.................75................................75
9/11/2007..Internet..100................95................................80
9/13/2007..Internet..100.................125..............................55
9/14/2007..Internet...20.................75.................................0
9/15/2007..Internet..100.................70................................30
9/16/2007..Internet..100..........(Not filled in until query run).....230


Darn, I made a mistake in my previous examples (I will edit them to correct this)... I had a couple rows with CIC for the Type... This is only supposed to be for one type of receipt. Each type of receipt will have it's own carryover...

If all the types of receipts are stored in one table then carryover would look like this:

Date.........Type.......ReceiptsTotal..NumberClosed...............Carryover
9/10/2007..Internet..100.................50................................50
9/10/2007..CIC........100................20................................80
9/11/2007..CIC........100.................75................................105
9/11/2007..Internet..100................95................................55
9/12/2007..CIC........100.................135..............................70
9/12/2007..Internet..20.................75..................................0
9/13/2007..CIC........100.................70................................100
9/13/2007..Internet..100..........(Not filled in until query run).....100

Does that make sense? It would have to group the Receipts based on type, then add the receipts received of the same type - closed receipts of the same type + receipts from previous date of the same type. So the grouping would like this:

Date.........Type.......ReceiptsTotal..NumberClosed...............Carryover
9/10/2007..Internet..100.................50................................50
9/11/2007..Internet..100................95................................55
9/12/2007..Internet..20.................75..................................0
9/13/2007..Internet..100..........(Not filled in until query run).....100

Date.........Type.......ReceiptsTotal..NumberClosed...............Carryover
9/10/2007..CIC........100................20................................80
9/11/2007..CIC........100.................75................................105
9/12/2007..CIC........100.................135..............................70
9/13/2007..CIC........100.................70................................100

[OBP]

OK here it is, for now I have just created a Crosstab Query, a form and a Report based on the crosstab query.
The Form can't show the Incrementing Carry over (only the total), but the Report can.
I can create an append query to put the Crosstab results in to a Table if you wish.
I have created about 37000 smulated records to show how quickly the Query runs.
But it is now to big to Post on here, can you PM me your eamil address?

[h2o4001]

I found a query that will give the running sum total for the carryover... I can get it to work with a table (a basic table that is...), but I'm unsure how to get it to work with the results of a query...

I'm attaching the database that I made to show the example.

[OBP]

I have emailed you a copy of the database, it currently has the records for 100 calls per day for 365 days, and you can see how fast the query is.
You have highlighted a problem with one of your previous posts.
If you create a table of carry-overs you will have to update with those open ones that get closed ad infinitum.
That means running the whole set of records to do that.
Would a query from that displays only Open carry-overs at start up be a good idea?