Sql

[AlexanderBB]

Hi, anyone up with sql? Trying to develop a query and can't quite get a join right ?

SELECT [Prefix] & [Subletter] AS ff, tblEPData2.TrackNum
FROM tblEPData2, tbl1964
WHERE ((([Prefix] & [Subletter]) Like "EP64*") AND ((tblEPData2.TrackNum) Like "B*"))
ORDER BY [Prefix] & [Subletter];

But I need to output another table containing ff. (So my whole approach may be quite wrong)

Thanks

[geek648]

Are tblEPData2 and tbl1964 joined by particular fields? If so, add this in your WHERE clause : AND (tblEPData2.field1 = tbl1964.field2)

And please explain what you mean by "need to output another table containing ff"

[AlexanderBB]

Hiya, thanks for reply

There's no join (yet) not sure where it should go. At the moment tbl1964 is sitting in design mode grid unused.

ff (if present) will exist in tbl1964 as both Prefix & Subletter in one field. But in tblEPData2 they are 2 separate fields

It's the ones in tbl1964 I want to have as my recordset source.

If I'm not making sense, let me know? Sometimes its hard to explain !

[geek648]

The general idea of a relational database is to NOT have duplicate data in different tables. You put it in one place, and join to it as required

[AlexanderBB]

That may be so, but isn't what I have.

[geek648]

Is it someone else's database, or your own (as suggested by "tbl1964 is sitting in design mode grid unused") ... so it's not too late to re-organise it?

[westconn1]

did you test if the combined values to ff matched the single field?

[AlexanderBB]

>Is it someone else's database, or your own

I'm designing a query (hopefully) to achieve a result. Well, more than that - I will get the desired result but exactly how I'm still looking for. The data (tables) already exist and can't be changed.
In any case, it is worthwhile to see how what I want to do can be done.

Pete, I don't follow. The combined values of ff will either match or be missing from tbl1964.

One problem I have is the join properties (as described in Access) have never made sense to me. But I can't experiment because ff isn't a field I can join AFAIK. ff is two fields added together. Maybe I have to create another field (with both values) and join that to it's matching field in table 2?

All good fun... ploughing on....

[AlexanderBB]

We're there! My inexperience using 2 tables was to blame - and thinking I needed JOIN.

SELECT tbl1964.Serial, tblEPData2.TrackNum
FROM tbl1964, tblEPData2
WHERE (((InStr([tbl1964].[Serial],[tblEPData2].[Prefix]))<>False) AND (Right([tbl1964].[Serial],1)=[tblEPData2].[SubLetter]) AND ((tblEPData2.TrackNum) Like "B*"));

I guess it all makes sense when you see it like that !

Regards, ABB

[si_the_geek]

Whenever there are fields from two or more tables/views returned by a query, you do need a Join... but they don't have to be the kinds that the Access interface can generate (which is only 'simple' Inner/Outer Joins).

The kind of thing you have done is an Inner Join, but much more complicated (and slower to run) than the kind that Access can create automatically.

There are other types of Joins too (such as Cross), but Inner/Outer are by far the most common (and among those, joining on an entire single field is the most common).