473,836 Members | 1,586 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Compare data between two recordsets / lookup recordset value in another recordset

18 New Member
As the title suggets, I have created to recordsets (1: current year, 2: previous year)
I simply want to be able to do the following:
1-by-1 lookup an id/value in last year and pull the information back displaying it with Debug.Print.

from there I think i know how to write this data to an array.
Jun 27 '13 #1
17 7925
rizo98
18 New Member
the code I currently have is as follows:

Expand|Select|Wrap|Line Numbers
  1. Function arraytest2()
  2.  Dim db As DAO.Database
  3.  Dim rst As DAO.Recordset
  4.  Dim rst1 As DAO.Recordset
  5.  Dim rst1filtered As DAO.Recordset
  6.  
  7.  Dim Rowcount As Long
  8.  Dim FieldCount As Integer
  9.  Dim i As Integer
  10.  Dim j As Integer
  11.  Dim arrCount() As Variant
  12.  
  13.  Set db = CurrentDb
  14.  
  15.  
  16.  
  17.  Set rst = db.OpenRecordset("qry_Market Share", dbOpenDynaset)
  18.  Set rst1 = db.OpenRecordset("qry_Market Share1", dbOpenDynaset)
  19.  Set rst1filtered = db.OpenRecordset("qry_Market Share1", dbOpenDynaset)
  20.  
  21.  
  22. Rowcount = rst.RecordCount
  23. FieldCount = rst.Fields.count
  24.  
  25. 'set the dimensions for the array
  26. ReDim arrCount(0 To Rowcount, 0 To 8)
  27.  
  28.  
  29. rst.MoveLast
  30. rst.MoveFirst
  31.  
  32.  
  33. Do Until rst.EOF
  34.  
  35. rst1filtered.Filter = "Firmid = " & rst.Fields("Firmid").Value & ""
  36. Set rst1filtered = rst1.OpenRecordset
  37.  
  38.  
  39.  
  40.         'write to the array
  41.         arrCount(i, 0) = rst.Fields("FirmID")
  42.         arrCount(i, 1) = rst.Fields("FirmName")
  43.         arrCount(i, 2) = rst.Fields("Perc")
  44.         arrCount(i, 3) = rst.Fields("Turnover")
  45.         arrCount(i, 4) = rst.Fields("Total")
  46.         arrCount(i, 5) = rst1filtered.Fields("FirmID").Value
  47.         arrCount(i, 6) = rst1filtered.Fields("FirmName").Value
  48.         arrCount(i, 7) = rst1filtered.Fields("perc").Value
  49.  
  50.  
  51.             rst.MoveNext
  52.         'see the array in the Immediate window
  53.         Debug.Print rst.Fields("FirmID"), rst.Fields("FirmName"), rst.Fields("Perc"), rst1.Fields("FirmID"), rst1.Fields("FirmName"), rst1.Fields("perc")
  54.          'Debug.Print i, arrCount(i, 0), arrCount(i, 1), arrCount(i, 2), arrCount(i, 3), arrCount(i, 4), arrCount(i, 5), arrCount(i, 6), arrCount(i, 7)
  55.  
  56.         i = i + 1
  57. Loop



End Function
Jun 27 '13 #2
ADezii
8,834 Recognized Expert Expert
Unless I am misinterpreting your request, create a Recordset directly from the Filter and display the results, as in:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim rst As DAO.Recordset
  3. Dim strSQL As String
  4. Dim intNumOfFields As Integer
  5. Dim intFldCtr As Integer
  6.  
  7. 'Fictitious Value to search for
  8. Const conVALUE_TO_SEARCH As Long = 1
  9.  
  10. strSQL = "SELECT * FROM tblPreviousYear WHERE [ID] = " & conVALUE_TO_SEARCH
  11.  
  12. Set MyDB = CurrentDb
  13. Set rst = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
  14.  
  15. rst.MoveLast: rst.MoveFirst     'For accurate Record Count
  16.  
  17. intNumOfFields = rst.Fields.Count
  18.  
  19. With rst
  20.   Do Until .EOF       'Return anything?
  21.     For intFldCtr = 0 To intNumOfFields - 1
  22.       Debug.Print .Fields(intFldCtr) & " | " & .Fields(intFldCtr).Value
  23.     Next
  24.       Debug.Print "--------------------------------------------------"      'Separator Bar
  25.         .MoveNext
  26.   Loop
  27. End With
  28.  
  29. rst.Close
  30. Set rst = Nothing
Jun 27 '13 #3
rizo98
18 New Member
Thanks for the reply. I want to search another recordset or array though. let me give you an example. I have a recordset called Athlete2013 and another recordset called Athlete 2012.
Looking at the below recordsets, I want to take Mo Farah from Recordset_Athle te2013 and search for him in Recordset_Athle te2012. Having found him, I want to to display his position. So as a final output I will have something like below. I hope this is clear. I want to be able to do this for each record in Recordset_Athle te2013.

Expand|Select|Wrap|Line Numbers
  1. Athlete    2013_position    2012_Position
  2. Mo Farah    1    2
  3.  
  4.  
  5.  
  6.  
  7. Recordset_Athlete2013
  8. Athlete    2013_position
  9. Mo Farah    1
  10.  
  11. Recordset_Athlete2012
  12. Athlete    2012_Position
  13. Mo Farah    2
Jun 27 '13 #4
Rabbit
12,516 Recognized Expert Moderator MVP
If that's the result you wnat, there's no need for any VBA code. Just join the two recordsets on the Athlete.
Jun 27 '13 #5
rizo98
18 New Member
But if I just join the recordsets on the athletes then will that not jumble up the results? E.g. Mo Farah was the winner in 2013 and runner up in 2012. If I joined the recordsets would I not have Mo Farah's 2013 score and someone else's (2012 winner) score next to it?
ASo I would have as below. But Mo was in 2nd place in 2012 and not 1st.
Athlete 2013_position 2012_Position
Mo Farah 1 1


Also the recordsets do not have the same number of records.
Jun 27 '13 #6
Rabbit
12,516 Recognized Expert Moderator MVP
Why would it jumble? As long as you join on the key fields, in this case Athlete, then the records will match up. If the recordsets don't have the same number of records, you can just use an outer join, be it left, right, or full outer.
Jun 27 '13 #7
zmbd
5,501 Recognized Expert Moderator Expert
also sounds like the database isn't normalzied...> Database Normalization and Table Structures.
Jun 27 '13 #8
ADezii
8,834 Recognized Expert Expert
@zmbd:
I feel as though you are correct about the Data not being Normalized.

I think the question here is Does each Athlete have a Unique Identifier that is consistent in each Table?
Jun 27 '13 #9
rizo98
18 New Member
I can do this by creating a query but i need to do it using vba. So i need to know the best method of going about it. By using recordsets, arrays or another method. The database is normalised. I have gathered the data for 2012 and 2013 using queries. I can create another query and do a right join on 2012 and get all corresponding records for 2012. However as i said i need to do this in vba. I have assigned 2013 query to a recordset called rst and 2012 query to another recordset called rst1 as shown in my code above. Now i need to a method that simply does the following.
For each record in recordset in rs lookup firmID in rst1. If exists then print the line using debug.print. If it doesnt exist them go to next record in recordset
Jun 27 '13 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

2
1905
by: J.D. Buehls | last post by:
I am displaying some values on a report. I am opening the .asp page with a Response.ContentType = "application/msword" and these particular values appear in a table. One column shows percentages such as "5", "95", "50", or "<5" There is no "%" character stored in the database just the number (and the "<" sign if there is one). Problem is NONE of the "<5" values appear in my report table. Does this have something to do with "<" being...
4
52871
by: Maur | last post by:
Hi all, I have 2 tables say t_OLD and t_NEW. The new has corrections for audit purposes. They are identical in all respects (i.e. new is a copy of old and then changes are made to t_new) I would like a quick way to cycle through all the the fields in each table and compare the values to see if there are
2
7931
by: ano1optimist | last post by:
I have a form with a search button. I'm using command parameters to pass search criteria to a stored procedure. Here is my code: Stored procedure: CREATE PROCEDURE . @strCriteria varchar(200) AS execute (@strCriteria) GO
0
2024
by: Jason | last post by:
I have a primary form which is used to enter/edit data in a table named Test_Results. On this primary form there is a subform which displays site addresses. This subform is linked to the primary form by field named TestID. The subform is used just for displaying site address data, data which is stored in another table named Total_Site_Address. In the Total_Site_Address table there are numerous fields that form the site addresses...
3
8843
by: Brian Foree | last post by:
I am developing an ASP.NET application that uses Access 2000 as its backend, and have just started getting the following error on 2 ASP.NET pages that had been working until late last week (and I don't think I made any changes to either page other than changing the user control that creates the header). Server Error in '/myApp' Application. ---------------------------------------------------------------------------- ----
3
1256
by: GeryLuz | last post by:
Hi! I can't find how to build an SQL statement inside an ASP page using another open recordset as its where clause. My code is the following: Set rs0 = cn.Execute ("Select Id_Fam from Familia where Descripcion = '"&perfil&"'") sql = "Select Id_Usuario from Usu_Fam where Id_Fam = " & rs0("Id_Fam") set rs2 = cn.Execute(sql)
10
3045
kamill
by: kamill | last post by:
Dear All, I need to send some data from my site to another site, but i do not want to hit that site. Is there any option. Currently i am using form to doing it but after submitting it, i do not want to transfer control to another site. I needed immediate help. Thanking you. Regards Kamill
2
1767
dnb
by: dnb | last post by:
how can we fill data in same mshflexgrid using two recordset? Some from one & some from another recordset? please give reply
1
1405
by: Chandan Kr Sah | last post by:
How to use like command in code behind page to compare with recordset value ? As I want to do comparision like below Dim rs As DAO.Recordset If rs1!.Value Not Like & '*Prep*'" Then '...do calculations End If
1
2863
by: lipton | last post by:
how can i compare a mysql table feild value with selected redio button value with php
0
9668
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10843
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10589
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10254
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7790
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6978
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5825
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4015
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3112
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.