Using SQL to select pairs of records from Access Database

1
votes

I am trying to extract records from an access database using Excel VBA with using an SQL statement. Using Excel VBA with an SQL SELECT statement I can extract records using the WHERE clause. Now I am trying to select PAIRS of records from my database but I am not sure how I can achieve this.

Access Table: (NB: there are other columns in the table)

     Date    DTE    Name    Sc
    41668   AAA425  AdamJ   98
    41669   ABC500  JohnB   85
    41689   ADE450  AdamJ   56
    41789   AFF350  AdamJ   74

I want to select pairs based on Name

E.g.

Date  DTE     Name  Sc  Date   DTE     Name   Sc
41668 AAA425  AdamJ 98  41689  ADE450  AdamJ  56
41668 AAA425  AdamJ 98  41789  AFF350  AdamJ  74
41689 ADE450  AdamJ 56  41789  AFF350  AdamJ  74

Then I want to filter/select based on the first DTE

Date   DTE     Name   Sc  Date   DTE     Name Sc
41668  AAA425  AdamJ  98  41689  ADE450  AdamJ 56
41668  AAA425  AdamJ  98  41789  AFF350  AdamJ 74

Can anyone point me in the right direction to how I can achieve this?

2
sqlexcelvbams-access
What's the primary key for the Access Table? Which field(s) make the record unique? – Emacs User
Your first sample output seems to indicate that you want every possible combination of every possible pair. I would recommend loading the recordset into variant arrays and/or dictionaries and perform the processing there before outputting to the worksheet. MS SQL might have a chance at this with various forms of partition tables but I just don't see this as viable directly from MS Access. – user4039065
Basically I want to run a regression on sc (Test scores) for each test (DTE) one at a time. DTE = test, Name = student name, Sc= Test score. I realise there will be literally thousands (millions) of pairs. I would like to limit the matches to a certain date period (i.e. before a date) and/or where the date difference between the pairs is < 90. Each record is has a unique index number. I can use studentID instead of student name if that makes it easier. – anrichards22

2 Answers

0
votes

I don't fully understand what (or more why) you want to do but you can do it with something like this:

Select your main driving record:

select max(date),
       name
  from NameDates (or whatever you called your table)
 group by name;

So that will give you the latest record for each person.

Now join that select to another select from the same table:

with mainName as
(select max(date) as main_date,
        name as main_name
   from NameDates (or whatever you called your table)
  group by name
)
select main_date,
       main_name,
       date,
       name,
       sc
  from mainName,
       NamesDates
 where main_name = name
   and main_date != date;

Check it working here:

http://sqlfiddle.com/#!4/321b3/8

0
votes

Yes this can be done is ACCESS using ACCESS SQl like the following:

Assuming your data is kept in a table NamesDates.

select mainName.main_date,
       mainName.main_name,
       NamesDates.date,
       NamesDates.name,
       NamesDates.sc, 
       NamesDates.StudentID
  from NamesDates
       LEFT JOIN 
       (select max(date) as main_date,
               StudentID AS main_StudentID
          from NameDates 
          group by StudentID
       ) AS mainName,
       ON mainName.main_StudentID = NamesDates.StudentID
 where  mainName.main_date != NamesDates.date;

Your request to match records based on the name is un-usual as names are not often unique. Normally and unique identifier for the person would be used to match up the records to created pairs.

PS. I suspect you will need to use "min(date)" not "max(date)"

Also, it sounds like you need to add a WHERE clause on both the main and subqueries to limit the dates to the 90 day period you mention.

Harvey