Need help for mailing list query

[razjafry]

Hi Experts,
I have two tables- contact and organization. Both tables have the mailing addresses. Some contacts in contact table don't belong to any org so I give the link to those as individual which is an entry as organization in Org.. table.
There are certain organization which don't have any contact person listed. These two tables are linked as Org..as master and contact as slave.
I want to create a mailing list.Criteria would be
To include Contact Name, Organization name, Mailing address
where
if contact is an individual, it should include only contact's name & address - should not show individual as org name
if contact belongs to an organization then org...should be there,
if no contact associated with org then org.. name & address should show,

Everything will be based on ysnMail check box option which is in both tables.

Thanks for help to figure all this out.
raz

[NeoPa]

This should be easy enough to sort out if you provide the information required.

Let's start with meta-data for both tables. I may also need to revisit the rather garbled explanation of what is stored where (and how) but I'll have another look through it again when I have the meta-data handy before I will know if I can make sense of it.

This will work much better if you can post the meta-data (info about the layout / structure) of the table in the same way as I use in my example. Click on the Reply button and you will have access to all the codes I've used.

Table Name=[tblStudent]
 Field; Type; IndexInfo
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

[razjafry]

Hi NeoPa,
As per your instruction I am posting MetaData of both tables. Thanks a lot for your help.

Note: tblOrg has One-To-Many relationship on tblContact
Table: [tblOrg]

"intOrgID",number,PK"
"coop_yn",ysnCheckbox
"Active/Inactive",ysnCheckbox
"strOrgName",text
"strAddr1"text
"strAddr2",text
"strCity",text
"strProv",text
"strPC",text
"strCountry",text
"ysnMailAddrSame",ysnCheckbox
"strMailAddr1",text
"strMailAddr2",text
"strMailCity",text
"strMailProv",text
"strMailPC",text
"strMailCountry",text
"strOrgPhone",text
"strOrgEMail",text
"Mail/Don't Mail",ysnCheckbox
"Email/Don't mail",ysnCheckBox






Table: [tblContact]

"LastUpdated", Date
"intOrgIDNum",number,FK
"Active/Inactive",ysncheckbox
"intContactID",number,PK
"strTitle",text
"strGender",text
"strFName",text
"strMName",text
"strLName",text
"strConPhone1",text
"strConPhone2",text
"strConFax",text
"strConEMail",text
"strWrkPhone",text
"strExt",text
"strWrkEmail",text
"strWorkFax",text
"strCurrentEmployer",text
"PreviousEmplyr",text
"HomeAdd1",text
"HomeAdd2",text
"City",text
"Prov",text
"PCode",text
"Country",text
"strRegion",text
"strGeographicArea",text
"strRiding_Name",text
"strRiding",text
"Mail_Don't Mail",ysnCheckbox
"Email_Don't Email",ysnCheckbox



Both tables are related and we need the results as contact Name:First Name & Last Name together ( If an organization without a contact name then name of the Organization would show instead of contact name), then Organization name ( if a contact person is associated as "Individual" in table organization then word " Individual" should not show for mailing list),
mailing address from table tblOrg if a contact is linked to an organization other than "Individual", otherwise mailing address from table tblContact (i.e if Org name is "Individual").

[NeoPa]

So, in the format I requested it would be :

Table Name=[tblOrg]
 Field; Type; IndexInfo
intOrgID; number; PK
coop_yn; Boolean
Active/Inactive; Boolean
strOrgName; String
strAddr1; String
strAddr2; String
strCity; String
strProv; String
strPC; String
strCountry; String
ysnMailAddrSame; Boolean
strMailAddr1; String
strMailAddr2; String
strMailCity; String
strMailProv; String
strMailPC; String
strMailCountry; String
strOrgPhone; String
strOrgEMail; String
Mail/Don't Mail; Boolean
Email/Don't mail; Boolean

Table Name=[tblContact]
 Field; Type; IndexInfo
LastUpdated;  Date/Time
intOrgIDNum; Numeric; FK
Active/Inactive; Boolean
intContactID; Numeric; PK
strTitle; String
strGender; String
strFName; String
strMName; String
strLName; String
strConPhone1; String
strConPhone2; String
strConFax; String
strConEMail; String
strWrkPhone; String
strExt; String
strWrkEmail; String
strWorkFax; String
strCurrentEmployer; String
PreviousEmplyr; String
HomeAdd1; String
HomeAdd2; String
City; String
Prov; String
PCode; String
Country; String
strRegion; String
strGeographicArea; String
strRiding_Name; String
strRiding; String
Mail_Don't Mail; Boolean
Email_Don't Email; Boolean

[NeoPa]

Let me see if I can clarify your requirements a little. After rereading your original post (the language makes it a little hard to understand) I'm not so sure it can be done easily after all :(

What I think you need is a list showing all records from both tables, but matching records appear only once.
To illustrate with an example :

Table1       Table2
 A  1         A  11
 B  2         B  12
 C  3         D  14
 E  5         E  15

What I think you want is :

A 1 11
B 2 12
C 3
D   14
E 5 15

Is this right?

[razjafry]

Hi NeoPa,
sorry if I wasn't clear enough in my question.
To me looks like you are getting it right though and can be a good starting point to custamize further if it need to be.
Thanks for your help.
raz

[NeoPa]

I'm afraid the bad news is that Access doesn't support this type of (OUTER) join.

Fully-fledged back-end systems do tend to (MS SQL for instance) but MS Access doesn't. The way I can think of doing it is quite inefficient (poor performance for large datasets) but can be done. I will look at providing a template including some of the fields you need if you're interested.

[razjafry]

Hi NeoPa,
Thanks for your effort for looking into it.
I think it won't be that large dataset and if some kind of solution is there then it should be good enough. So would you please spare some time and give me the solution/template.
Regards,
Raz

[NeoPa]

You're on my list Raz, but I ran out of time tonight.

I'll try to get to it tomorrow or the next day. If not - bump the thread as a reminder.

[razjafry]

Hi NeoPa,
Thanks for having me in your list. I'll be looking forward to your help whenever you get a chance.
Thanks,
Raz

[NeoPa]

No worries Raz.

Try the following to see if it works for you. It will need tarting up but the principles should all be there and we can finalise details when we know we're on the right lines.

SELECT [ID],
       Max(subQ.OrgAddr1) AS OrgAddr1,
       Max(subQ.OrgAddr2) AS OrgAddr2,
       Max(subQ.OrgCity) AS OrgCity,
       Max(subQ.ContAddr1) AS ContAddr1,
       Max(subQ.ContAddr2) AS ContAddr2,
       Max(subQ.ContCity) AS ContCity
FROM (SELECT intOrgID AS ID,
             strMailAddr1 AS OrgAddr1,
             strMailAddr2 AS OrgAddr2,
             strMailCity AS OrgCity,
             Null AS ContAddr1,
             Null AS ContAddr2,
             Null AS ContCity
      UNION ALL SELECT
             intOrgIDNum AS ID,
             Null AS OrgAddr1,
             Null AS OrgAddr2,
             Null AS OrgCity,
             HomeAdd1 AS ContAddr1,
             HomeAdd2 AS ContAddr2,
             City AS ContCity) AS subQ
GROUP BY [ID]

[razjafry]

Hi NeoPa,
Thanks for the help but somehow I am unable to get anything out of this so far as it ask at least one table or query to be part of this code.
I am sorry if it sounds like some extra work for you.
Raz

[NeoPa]

Have you tried running it as it is Raz?

[razjafry]

Yes I tried to run it as is and it gives the error message that I need to specify at least one table or query.

[NeoPa]

I can't see that you should be getting that from that SQL Raz.

Try seeing if the following runs at all (It's the subquery part) :

SELECT intOrgID AS ID,
       strMailAddr1 AS OrgAddr1,
       strMailAddr2 AS OrgAddr2,
       strMailCity AS OrgCity,
       NULL AS ContAddr1,
       NULL AS ContAddr2,
       NULL AS ContCity
UNION ALL SELECT
       intOrgIDNum AS ID,
       NULL AS OrgAddr1,
       NULL AS OrgAddr2,
       NULL AS OrgCity,
       HomeAdd1 AS ContAddr1,
       HomeAdd2 AS ContAddr2,
       City AS ContCity

[NeoPa]

Now I've seen that I can see the problem.

Try this instead :

SELECT [ID],
       Max(subQ.OrgAddr1) AS OrgAddr1,
       Max(subQ.OrgAddr2) AS OrgAddr2,
       Max(subQ.OrgCity) AS OrgCity,
       Max(subQ.ContAddr1) AS ContAddr1,
       Max(subQ.ContAddr2) AS ContAddr2,
       Max(subQ.ContCity) AS ContCity
FROM (SELECT intOrgID AS ID,
             strMailAddr1 AS OrgAddr1,
             strMailAddr2 AS OrgAddr2,
             strMailCity AS OrgCity,
             NULL AS ContAddr1,
             NULL AS ContAddr2,
             NULL AS ContCity
      FROM [tblOrg]
      UNION ALL SELECT
             intOrgIDNum AS ID,
             NULL AS OrgAddr1,
             NULL AS OrgAddr2,
             NULL AS OrgCity,
             HomeAdd1 AS ContAddr1,
             HomeAdd2 AS ContAddr2,
             City AS ContCity
      FROM [tblContact]) AS subQ
GROUP BY [ID]

[razjafry]

This query runs but gives very few records.
I expect around 3000 record on the base the selection I am looking for but it gives only around 100 records so need to look into this query quite in detail to figure out the right combination.
Thanks for your help.
Raz

[NeoPa]

OK Raz.

Let me know what you discover :)