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.

Hi

Welcome to the forum

Correct syntax for a heterogeneous query:
Get it working for your first table and then create the union .

HTH

pootle flump ur codings are working excelent.

Thank you, it works beaut! I can clearly see it was the syntax.

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

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.