CODEWITHSUNDEEP
sqlexcelvbams-access
Snayff
Snayff

Reputation: 97

Compare Value Across Tables and Return Matches (SQL, VBA)

Morning all,

I think I am likely missing something obvious so I am hoping someone can nudge me in the right direction.

I am using Excel 2010 to query an Access 2010 Database using DAO. I have two tables I need to involve, [Absence] and [Staff]. The former includes details of absence and the staff's name, the latter includes more details on the staff, such as their team manager. I can currently query and return information about the Absence without any issues, however, I need to extend this to check that the staff's name in the Absence table is associated with the appropriate Team Manager in the Staff table. So, in plain English it should be "Give me all records from the Absence table between these two dates, if the staff are listed under this Team Manager in the Staff table".

Here is my code so far (extra fields stripped out for the example) which works fine:

Sub QueryDatabase()
Dim db As DAO.Database 'the database
Dim rex As DAO.Recordset 'the recordset in which to place the data

Set db = OpenDatabase(ActiveWorkbook.Path & "/DB.accdb", False, True, ";pwd=" & pw)
Set rex = db.OpenRecordset("SELECT [RecordDate], [StaffName], FROM [Absence] WHERE [RecordDate] BETWEEN #" & dFromDate & "# AND #" & dToDate & "# ;")

Any advice or guidance would be greatly appreciated!

Snayff

Upvotes: 0

Views: 840

Answers (1)

Paul Ogilvie
Paul Ogilvie

Reputation: 25276

  1. Always print the SQL query to a string. This allows you to inspect the query before it is being executed.

  2. are the dFromDate and dToDate already in the right format and did they print correctly to the SQL query (see previous point)?

To get your "Give me all records from the Absence table between these two dates, if the staff are listed under this Team Manager in the Staff table" you need a join, something like:

 SELECT Absence.name FROM Absence, Staff
 WHERE Absence.name = Staff.name
   AND Staff.mananger = manager_name;

Upvotes: 1

Related Questions