Recordsets & Insert into

Posted on 1997-10-21
Last Modified: 2012-08-13
I need some advice.  I am using a recordset to select each row in a query and then performing many date comparison calculations on the date columns in the record.  The final calc I end up with is a total # of days for the row.  I then compare this value to another value which determines whether this record should appear on my report.  What is the best way to capture this value for the record?  I had created an empty column in my underlying table that the query is based on and then updated this value with the vtotaldays (total #of days).  Then I used the query as the recordsource for my report.  This seems inefficient to me.  I also tried to use a DoCmd.RunSQL "Insert into..." command to insert the records I want into another table.  However, I am having trouble inserting the variables from my calculations into the new table.  Any suggestions and/or advice is welcome.  Thanks.
Question by:kearley
  • 7
  • 7

Expert Comment

ID: 1957658
Hugh...let's see about clearifying before trying to help...

1.  you have many records that have a date field
2.  you want to find out how many different dates are in this     recordset
3.  if you have created an empty "column" or field in the
    underlying table, is this the same value as the count of
4.  What is the underlying table, the new table you're trying to
    populate with the filtered data for your report?

If you answer these and supply the calculation that you're using I'll probably be able to supply you with the sufficient code and/or simply a sufficient query to use for your report

Author Comment

ID: 1957659
1.  I have a table (Log) which contains Log#, DRecDate, CompDate, TAT, etc.  This table is used to store correspondence which is logged in on the date received and supposed to be completed within a certain turnaround time (TAT).  I have another table (Pending) which contains PendingId, Log#, PendIn (date), PendOut (date).  This table contains pending dates for the logged item.  The logged item can be logged in and out of "pending" several times, thus the relationship from table Log is 1:N.
2.  In my module, I am creating a recordset which is based on a query combining the above 2 tables so I need to get all of the Pending In and Out dates for each Log#.  I need to create a report of only those Logged items which will be over the standards if not completed by a certain date.  Thus, I am only looking at those records where there is no completed date.  I am prompting for the date in a dialog box, then using that date for my "TOTALDAYS" calculation. I need to find the difference between the Date Received and the first Pending In date (if one) and the difference between each PendOut/In dates (if any) and the difference between the prompted date and the last PendOut date(if one).If there isn't a PendingOut date then I need to use the last Pending In date (if one) or the Date Received.  If the total of all of the date differences is greater than the turnaround time, then I need to print this record on the report.
3.  I am trying to find a way to calc the TotalDays and then store that particular record where the TotalDays > TAT so it can be printed on my report.  I was trying to insert the record I want into another table but was having trouble with the INSERT INTO command.
4.  The underlying table for my query that I'm using in my recordset is both the Log table and the Pending table.  I was using the new table (with the newly inserted records) as the recordsource for my report.

Here is my code:
strQuery = "SELECT * FROM qryThursdayRpt;"
Set db = CurrentDb()
Set rs = db.OpenRecordset(strQuery, dbOpenDynaset)

    With rs
        If Not .EOF Then
            Do Until .EOF
                vTeamCd = ![TeamCd]
                vLogNum = ![Log#]
                vContract = ![contract#]
                vUnit = ![Unit]
                vFile = ![File]
                vClient = ![ClientName]
                vItem = ![ItemDesc]
                vTeamName = ![TEAMNAME]
                vTAT = ![TAT]
                vPendIn = ![PendIn]
                vPendOut = ![PendOut]
                vDrecDate = ![DRECDATE]
                vDiffRecdThurs = WorkDays(vDrecDate, vThursdate)
                    If Not IsNull(vPendOut) And Not IsNull(vPendIn) Then
                        vDiffPInPOut = vDiffPInPOut + WorkDays(vPendIn, vPendOut)
                    ElseIf IsNull(vPendOut) And Not IsNull(vPendIn) Then
                        vDiffPOutThurs = WorkDays(vPendOut, vThursdate)
                    End If
                    vTotalDays = vDiffRecdThurs - vDiffPInPOut - vDiffPOutThurs
        End If
        DoCmd.RunSQL "Insert Into tblThursday ([TeamCd], [Log#], [Contract#],[Unit],[File],[ClientName],[ItemDesc],[DrecDate],[PendOut],[TeamName],[InCompDays])_"
            VALUES ('" & vTeamcd & "','" & vLogNum & "', '" & vContract & "', '" & vUnit & "', '" & vFile & '",'" & vClient & "', '" & vItem & "', #" & vDrecDate & "#, #" & vPendOut & "#,'" & vTeamName & "', " & vTotalDays &")"
End With

Let me know if you need more info.


Expert Comment

ID: 1957660
my first suggestion after reading all the way through it, is that you can cut down some of the coding and possibly increase some speed by cutting out all the fields but the key field in your insert into...then you can create a query that grabs all the info out of the appropriate table based on the available key fields in your 'temp' table "tblThursday"

There may be a problem using this tactic if you're trying to compare from multiple tables, but then all you'd have to do is create multiple temp tables "tblThursdayPending" & "tblThursdayOpen" (or something to that affect", and if the tables are identical (or your comparison queries), then you can run a union query to combine the resuls of both tmp tables, and run the report from the union query.

as for the filtering...I'm still a bit confused about that since there's a lot of criteria that you're trying to explain.  I could probably capture your meaning if I had the time to sit down and map it all out....but if you could do this for me it would help tremendously...try mapping your needs according to 'objects', for example

from Table1, I need all data, that meets....
joining that info with Table2 that meets....
the resluts of both combining into this report....

or something close to that I think we could probably get away without using a temp table at all, because a query can actually accept user inputs - as both a 'parameter' and some vba code works withing a query as will, for example Iif([TotalDays] > TAT, "true condition, false condition)

Hope I haven't lost you, I'll be back on-line later tonight to check for any new posts

Author Comment

ID: 1957661
First off, thanks so mucn for your help.
I'll try to simplify my filtering needs.  From Table1, I need to get the rows, if any, from Table2.  Table2 contains In/Out dates for Table1.  I need to perform TOTAL DAYS that it took for an item from Table1 to get completed, thus performing calculations based on Date Received, Date Completed, and Pending In/Out dates.  This particular report needs to only indicate those from Table1 that are NOT completed where the TOTAL DAYS > TAT.  Each item in Table1 has a particular TAT.  
Hope that helps.  I tried doing all of my date difference calculations in a query and I received the "query to complex, try getting rid of some expressions..." message.  That's when I created the module.

Expert Comment

ID: 1957662
I don't know why this wasn't posted the first time I tried, but here it goes again...

let's see if I can simply this even want

records in Tabl1 that
    are not closed (where does this info get stored?)
    where recvd date & TAT (turn-around-time) is after todays
and you want the report to show
  the above records
  any records in table2 that are tied into table1

is that right

Author Comment

ID: 1957663
Here's what I want:  records in Table1 that are not closed, so I need to just grab those that do not have a completed date.  Each of these records has a TAT (in # of days), most times it's 5.  I then need to calculate how many days between the date of the report (I'm prompting for this via dlg form), and the date received (Date Received is in table1 also).  But! there could also be records in Table2, but not always.  Table2 contains a history of when the record went in and out of "Pending" (If an item needed to be researched or handed off to someone else, etc. they don't want this "pending" time to be counted against them for meeting TAT).  After calcing the actual days it took to complete an item, I need to compare this to 5 days (the TAT). If it is greater than 5 (the TAT), I need the record to print on the report.
Hope this helps.

Expert Comment

ID: 1957664
select * from table1 where (([completedDate] = is null))

and join this query with the records from table2 that you are wanting....if you let me know how they're linked together I'll expand on the above SQL

the date of your report should be either date() or if you are running this report through a 'print report form' then simply put a text box control on it, applying a date format to it, and have the default value be date(), this will allow the user to change the date of the report if they wanted to see something last week

then on the report you can have some unbound textbox controls that you manually fill in like putting in the properties
recordsource|=[CompletedDate]-([recvdDate]+[TAT])    |
this would give you the amount of days past the alloted time of completion, and you don't have to do anything but add a control to your report, and give it the above property...

as for adding your records from table2, you can create a new report and add it to the existing one as a sub-report, this way if you have several records or 1 record, they will show up, or if you have none, the main form will display the info you want, but have no sub-form for that particular record

again, let me know how the 2 tables are linked I'll help you with the sub-form
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.


Author Comment

ID: 1957665
I think I've made this overly confusing.  I need the values in table2 to calculate the turnaround time and then compare it to that TAT in Table1.  Table1's PK is Log# and is linked to Table2 by the Log#. Table2 has PendingId, Log#, PendInDate, PendOutDate.  I need to find the records in table2 if any, calculate the difference between each PendIn and pendOut date and then add them all up, then take this sum and subtract it from the difference of DateReceived and DateCompleted (or Report date if not completed) and THEN compare this result to the TAT in table1.  I think i figured it out.  My report works.  I have another question about reports -- a different one where I have been struggling with subreports.  Should I post a new question?  Thanks so much for your help.

Expert Comment

ID: 1957666
sub-reports is a good idea, since you might not have any records in table2 that relate to table1 and there is a direct's a great idea to use...what's the question with it??

Author Comment

ID: 1957667
I have a lot of different calculations that I need to make such as 1) Total # of items received between two dates, 2) Total # of items completed between two dates, 3) Total # of internal items received between two dates.  Each of these has to be totalled for each Team Code.  I created totals queries to get the results I need but the problem is when there are NONE for a particular team code, I do not get a 0, I get an empty set.  So what I did was create 3 subreports based each query.  I placed the subreports on my main report and then I had to create an additional unbound control for each subreport in which the control source was =Iif( = 0,0,!control).  That is the only way I can get a 0 to print if an empty set is returned.  My main report consists of a total of 10 subreports.  I would have to create 10 additional unbound controls to get each to print 0's.  This is a ton of work for just one report!  Is there any other way to get a 0 to print if the query returns nothing?

Accepted Solution

chapie earned 50 total points
ID: 1957668
if you link the totals query to the Table1 data, then theoretically you should only get the records from table1 that have totals, if however you right-click on the link (in the query) you should see 'join properties' then you can select 3 options, one of which is correct for fixing your problem
  1    only shows matching records from both tables (default)
  2    show all records in table1, and only those in table2 that
  3    show all records in table2, and only those from table1
       that match

you would have to change it to option2 in this case, then you would get all the records necessary in your report whether or not they have totals from table2, then you would only need 1 sub-report (for the details in table2 that you might want to show)

Author Comment

ID: 1957669
Unfortunately I had already tried what you suggested.  I chose the 2nd join option and still get only get a subset of the entries in my result set.  Table1 has all Team Codes.  My totals query returns only those team codes that have entries in table2 regardless of how many team codes are in table1 even though I chose option2.  I am most times using Crosstab queries to tie to table1.  Even when I get a team code that does not have an entry that one of the other teamcodes has, I get a blank entry, not a 0.

Expert Comment

ID: 1957670
you would actually want to base your total query on a new query designed with the option we're discussing, then you should get totals that equal zeros.

I know it's a pain and seems like a waste to base 1 query on another but when dealing with totals, and crosstabs sometimes that's what you have to do

Author Comment

ID: 1957671
Cool.  You're right, it is a pain, but it works.  Thanks so much!

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now