Newbie Requires SQL Help

[TerryPin]

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

 

[Rick Brandt]

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

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"))

 

[Rick Brandt]

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"))

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

 

[John W. Vinson]

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]

 

[TerryPin]

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