one query from 2 tables within the same database

[Fanflame]

Hi there,

I wonder if someone might assist. I have one database that I am working on and within it there are several Tables. 2 of the tables have similar information on various organisations and I need to query these 2 tables to get a simple mailing list of ALL organisation addresses and the main incumbent there.

Within Access (2007) and in the SQL View window, I have made a simple query to give me a list of all the organisations and incumbents but cannot get it to work correctly. Is there an easy way to get this information out of the 2 tables? I realise that the SQL in SQL View has to be formatted differently. I have also tried the Microsoft Query Wizard but that doesn't work either as it gives in a side by side list which is useless to me.

The Field names that are required are as follows:
<Incumbent>, <Organisation>, <Address1>, <Address2>, <Address3>, <County>, <Postcode>,

Your help and advice will be appreciated.

Fanflame
:banghead:

 

[Alansidman]

Give us the field names in each table that you need. Be sure to specify the table names.

 

[Fanflame]

Hi Alansidman,

And thank you for your reply.

I have been trying to work this out starting with just one Field from each table and then adding another field unitl I have it complete however I am still stuck. As in all things you have to be very accurate and know exactly what you want to see.

I don't want to put the actual table names on the Internet so have shortened them however in the SQL I have decided to use an Alias to shorten them anyway.

As I want to send out a letter to all the Incumbents at each Organisation I have added a checkbox field to each of the tables so that I can view only those records which have the checkbox checked. (This is not actually working either as it shows all records when IS NOT NULL is used and only the unchecked records when the IS NULL is used)

I had tried to use the INNER JOIN operator/command but got really confused by this so have tried to simplify it by using the UNION operator.

Here is what I have:

Tables:
LMC_Org & Other_Org

Fields:
<Organisation>, <Letter>, <Incumbent>, <Salutation>, <Address1>, <Address2>, <Address3>, <Town>, <County>, <Postcode>,

My SQL (shows ALL records even those which are unchecked in <Letter> column)

SELECT a.Letter, a.Organisation, a.Incumbent, a.Salutation, a.Address1, a.Address2, a.Address3, a.Town, a.County, a.Postcode
FROM [LMC_Org] AS a

UNION ALL

SELECT b.Letter, b.Organisation, b.Incumbent, b.Salutation, b.Address1, b.Address2, b.Address3, b.Town, b.County, b.Postcode
FROM [Other_Org] AS b
WHERE Letter IS NOT NULL
ORDER BY Organisation;​

Many thanks for your help and advice.

Fanflame

 

[Cronk]

One would wonder why the data was split into 2 tables. However, drop the All following the Union and put brackets around the 2 select's

eg (SELECT .....) UNION (SELECT .....)

I presume the two individual queries work separately.

 

[Fanflame]

Hi And Thanks for your reply,

I inherited the database and it was messy. I have cleaned up the tables and collated most of the data but these two tables are legacy tables one for member businesses and one for non-member businesses of which there are far more.

I need to do a mailshot to all the businesses but whilst at it am cleaning up the data so that I can make one table with clean and updated data plus a simple checkbox for those businesses that are members of the group.

I have tried your suggestion and the removal of the ALL works okay but not the bracketing as this gives me a syntax error.

If I run the SQL as it is now in the SQL view Window of ACCESS all records are returned and sorted by Organisation name but duplicates are also shown, of which there are several.

The 'WHERE Letter IS NOT NULL' works but returns all entries as either a '0' or '-1' rather than just showing me the records where I have checked the checkbox, which is what I had expected to see. I have been through both tables and checked the records I want to send the mailshot too and wanted one complete list of all Businesses to mailshoot.

SELECT a.Letter, a.Organisation, a.Incumbent, a.Salutation, a.Address1, a.Address2, a.Address3, a.Town, a.County, a.Postcode
FROM [LMC_Org] AS a

UNION

SELECT b.Letter, b.Organisation, b.Incumbent, b.Salutation, b.Address1, b.Address2, b.Address3, b.Town, b.County, b.Postcode
FROM [Other_Org] AS b
WHERE Letter IS NOT NULL
ORDER BY Organisation;​

Many thanks for the recommendation.

Fanflame

 

[Cronk]

If you want only records where the check box is ticked then use
WHERE nz(Letter,0)=-1

 

[Fanflame]

Thanks Cronk,

That worked nicely but I had to put it in both halves of the Union: (what does the nz stand for?)

SELECT a.Letter, a.Organisation, a.Incumbent, a.Salutation, a.Address1, a.Address2, a.Address3, a.Town, a.County, a.Postcode
FROM [LEAF Member Churches] AS a
WHERE nz(Letter,0)=-1
UNION
SELECT b.Letter, b.Organisation, b.Incumbent, b.Salutation, b.Address1, b.Address2, b.Address3, b.Town, b.County, b.Postcode
FROM [Other Churches] AS b
WHERE nz(Letter,0)=-1
ORDER BY Town ASC, Organisation ASC;​

I have also changed the ORDER BY which works now as well.

I have another question: I have on each record the <Prefix>, <First Name>, <Last Name> of each contact (incumbent). As the Column heading of First Name and Last Name have a space and no underscore in them Microsoft SQL doesn't seem to like it and shows a syntax error. I know I can add the underscore and probably will in time but can I add brackets or something to get ACCESS to SQL them so that I can remove a.incumbent above and add a.First Name etc instead?

Fanflame

 

[Cronk]

It's a VBA function. I'll let you google it at vba nz function

I would expect all professional developers would not include spaces in field names. Better to use caps to denote separate words eg FirstName, to avoid your specific problem. Just put square brackets around the field name in the query, just as you have with your table names, eg select [First Name], .....

 

[Fanflame]

Hi Cronk,

Thanks for the explanation I have looked it up and am a little wiser now!
As an ICT Manager I have to cover a lot of subjects and although not trained in programming I have to keep an eye on things overall. At the moment I have offered to help a small charity get to grips with their database which was put together in (a back room somewhere ) ACCESS and added to by different people with different understanding and attitudes. Gradually, with a little help from this forum from time to time, I am improving things for them.

Thanks again for your great help. Have a good one!

Fanflame