CODEWITHSUNDEEP
sqlms-access
user820304
user820304

Reputation:

Showing results from multiple tables

I'm working on a legacy Access CRM, for which I have been tasked to add some extra functionality to last us until we can switch to a proper solution.

I have a series of tables representing a one-to-many connection between a Case and various types of Case Notes. These case notes all represent various types of infomation specific to a given workflow (ie an email going into our database needs to have specific fields, which are different than a call), and therefore each different kind of Case Note is represented by a different table. So my table looks something like

Where there are some shared fields between Case Notes, such as Email. One desired feature is to filter a list of email addresses into a ComboBox, based on previous notes tied to a case along with defaults (email addresses used commonly within our day-to-day operations).

Currently, I am using VBA to open a query over each table, then I iterate over the RecordSet capturing unique values. However I would like to pull these from the same query, if possible.

I have been reading on the various type of JOINs, and I do not believe they do what I need.

Can I combine certain fields from different tables to create a list of unique values?

Upvotes: 1

Views: 51

Answers (2)

onedaywhen
onedaywhen

Reputation: 57023

I would like to pull these from the same query, if possible.

Using SQL DLL while in ANSI-92 Query Mode (or do the same manually using the Access GUI tools):

CREATE VIEW CaseEmailAddresses ( case_id, email_address )
AS
SELECT [Case ID], [To Email] FROM Case_Emails
UNION
SELECT [Case ID], [Caller Email] FROM Case_Calls;

Then in SQL DML (queries):

SELECT email_address
  FROM CaseEmailAddresses
 WHERE case_id = <inject id here>;

Or while you're in ANSI-92 Query Mode, you can create a stored procedure e.g. assuming case_id is type integer:

CREATE PROCEDURE GetEmailAddresses ( :case_id INT )
AS
SELECT case_id, email_address
  FROM CaseEmailAddresses
 WHERE case_id = :case_id;

Then you can use the technology of your choice to execute this proc while passing the parameter value.

Upvotes: 0

JCro
JCro

Reputation: 696

If you're just after a list of emails that occur at least once in at least one of the tables, then this should do the trick.

SELECT t1.emailAdd from
(
select emailField as emailAdd from Case_Emails
union 
select emailField as emailAdd from Case_Calls
) t1
group by t1.emailAdd
order by t1.emailAdd

If it must only be emails that occur at least once in at least one of the tables and must be linked to a specific case (for example 12345), then this should do that:

    SELECT t1.emailAdd from
    (
    select emailField as emailAdd from Case_Emails where CaseID=12345
    union 
    select emailField as emailAdd from Case_Calls where CaseID=12345
    ) t1
    group by t1.emailAdd
    order by t1.emailAdd

Upvotes: 1

Related Questions