Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2006
    Posts
    4

    Unanswered: Union Query with DAO 3.51

    Hi

    I'm trying to create a union query from 2 databases of similar structure. Both databases contain same tables and one is the production database the other an archive. And connecting to databases using DAO 3.51.

    I have made an attempt however fails when run, if anyone can correct the syntax it would be appreciated.


    sSql = "SELECT [dbFamilyName] & ', ' & [dbFirstName] AS dbPatient, [dbDob], [dbPatientId] " & _
    "FROM C:\Shared\Data\meds.[Patient] " & _

    "UNION SELECT [dbFamilyName] & ', ' & [dbFirstName] AS dbPatient, [dbDob], [dbPatientId] " & _
    "FROM C:\Shared\meds-archive.[Patient] " & _

    "ORDER BY [dbFamilyName], [dbFirstName];"


    Now problem is gives a syntax error at FROM clause.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Welcome to the forum

    Correct syntax for a heterogeneous query:
    Code:
    SELECT *
    FROM Table1 IN 'C:\Remotedb.mdb'
    Get it working for your first table and then create the union .

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2006
    Posts
    4
    Thank you, it works beaut! I can clearly see it was the syntax.

  4. #4
    Join Date
    Sep 2006
    Posts
    4
    Hi

    Would it be possible to perforn an INNER JOIN using similar syntax?

    SELECT Appointment.dbappointno, Appointment.dbdate, Appointment.dbtime, Appointment.dbstatus, Practitioner.dbpracno, Practitioner.dbsurname & ', ' & Practitioner.dbgivenname AS dbpractitioner
    FROM Practitioner, Appointment
    WHERE (dbpatno = 2769)
    UNION SELECT Appointment.dbappointno, Appointment.dbdate, Appointment.dbtime, Appointment.dbstatus, Practitioner.dbpracno, Practitioner.dbsurname & ', ' & Practitioner.dbgivenname AS dbpractitioner
    FROM Practitioner IN 'C:\Shared\med-archive.mdb', Appointment IN 'C:\Shared\med-archive.mdb'
    WHERE (dbpatno = 2769)
    ORDER BY dbdate DESC, dbtime DESC

  5. #5
    Join Date
    Sep 2006
    Posts
    4
    Thank you, I have found the solution.


    SELECT Appointment.dbappointno, Appointment.dbdate, Appointment.dbtime, Appointment.dbstatus, Practitioner.dbpracno, Practitioner.dbsurname & ', ' & Practitioner.dbgivenname AS dbpractitioner
    FROM Appointment INNER JOIN Practitioner ON Practitioner.dbpracno = Appointment.dbpracno IN 'C:\Shared\med.mdb'
    WHERE (dbpatno = 2769)
    UNION SELECT Appointment.dbappointno, Appointment.dbdate, Appointment.dbtime, Appointment.dbstatus, Practitioner.dbpracno, Practitioner.dbsurname & ', ' & Practitioner.dbgivenname AS dbpractitioner
    FROM Appointment INNER JOIN Practitioner ON Practitioner.dbpracno = Appointment.dbpracno IN 'C:\Shared\med-archive.mdb'
    WHERE (dbpatno = 2769)
    ORDER BY dbdate DESC, dbtime DESC

    It works like a treat.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •