Newbie Requires SQL Help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

TerryPin - 14 Jun 2007 14:59 GMT

Hi Folks

A little bit of help would be much appreciated. I'm an access novice and an SQL
newbi but I'd really like to know how to combine a couple of SQL routines

If I generate an SQL query called Test1 with the the following script, I can
join 2 access tables with identical structure and this works fine:

SELECT * from Table1
UNION select * from Table2;

If I then run a second query "Test2" with the following script I can filter the
results of the joined tables and this works fine

SELECT Test1.Field1, Test1.Field2, Test1.Field3, Test1.Field4, Test1.Field5,
Test1.Field6, Test1.Field7
FROM Test1
WHERE (((Test1.Field1) Like "aa") AND ((Test1.Field7) Not Like "t"));

So simple question is how do I combine both requirements so I can run it from a
single query

OK I'm sure it's simple but I admit it I'm stuck

Any help much appreciated

Terry

Reply to this Message

Rick Brandt - 14 Jun 2007 15:07 GMT

> Hi Folks
>
[quoted text clipped - 21 lines]
>
> OK I'm sure it's simple but I admit it I'm stuck

SELECT * from Table1
WHERE (((Test1.Field1) Like "aa")
AND ((Test1.Field7) Not Like "t"))

UNION select * from Table2
WHERE (((Test1.Field1) Like "aa")
AND ((Test1.Field7) Not Like "t"))

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message

Rick Brandt - 14 Jun 2007 18:53 GMT

> SELECT * from Table1
> WHERE (((Test1.Field1) Like "aa")
[quoted text clipped - 3 lines]
> WHERE (((Test1.Field1) Like "aa")
> AND ((Test1.Field7) Not Like "t"))

I obviously forgot to change the table names above, but hopefully the point
is still made.

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message

John W. Vinson - 14 Jun 2007 19:05 GMT

>SELECT Test1.Field1, Test1.Field2, Test1.Field3, Test1.Field4, Test1.Field5,
>Test1.Field6, Test1.Field7
> FROM Test1
> WHERE (((Test1.Field1) Like "aa") AND ((Test1.Field7) Not Like "t"));

Rick's got the syntax correct... but I've got to mention a couple of concerns!

The lesser one is the use of Like. The *only* purpose of the Like operator is
that it allows the use of "wildcards" to search for a portion of a field. If
you don't use wildcards it gets you *nothing*:

Field1 LIKE "aa"

and

Field1 = "aa"

are equivalent.

The larger concern is that it seems that you're storing the same "kind" of
data in two tables. This is almost surely incorrect design! If you have
information about a particular kind of entity, you're best off storing it in
*one* table, maybe with another field to identify a subcategory of the
entities. What type of information are you storing in Table1 and Table2?

John W. Vinson [MVP]

Reply to this Message

TerryPin - 15 Jun 2007 13:56 GMT

Firstly Big thanks to You and Rick for the advice, the solution Rick offered
works fine whilst the comment you made regarding the use of "=" instead of
"Like" is well taken I'll admit that already I've drifted into the "Like" mode
and can now see the dangers.

With regards to the comment about storing duplicate data in multiple tables, I
hope my defense stands on firmer ground. I've read up on this and realize the
strengths of keeping data lean. However in my case I am receiving data from 2
different sources in excel format. The data is in a real mess and should
definitely have been offered in DB format. However that's the way they've always
done it so it must be right and I'll never persuade them to change it no matter
how valid my argument and how impressive my reconciliation appears. Sad fact of
life I'm afraid.

Whatever; this is the first time I've linked speadsheets to Access in earnest
and as the updated spreadsheets arrive on a nearly daily basis this is working
really well. I just wanted to learn a bit more and improve and rationalize my
system Which with your help I have done.

Once again a big thanks

Terry

>>SELECT Test1.Field1, Test1.Field2, Test1.Field3, Test1.Field4, Test1.Field5,
>>Test1.Field6, Test1.Field7
[quoted text clipped - 22 lines]
>
> John W. Vinson [MVP]

Reply to this Message