Need help with a query

[Wonderer]

Hi All,

Was hoping someone could help with a simple question

I have two tables related bty a code however the code in table A is a varying number and so is the code in table B

However Table B always has a number bigger than A

For Example

Table A Table B

01456 01456789
018944 01894455889

Table (A) has a description with its code where as table (B) has other data osciated with the full code. I therefore pull a query which brings back the description for (A) and the Data form (B)

The problem is i don't just want exact matches i i want any of them from (B) that start with the same code as (A) i.e Code can be found preciding the restof its code in (B) can any help me with this?

I Hope i have'nt confused this to much with all the (A)'s and (B)'s any help would be grately apreciated

Thanks

Rob

 

[Pat Hartman]

Select fld1, fld2, etc
From tblA Inner Join tblB on tblA.YourCode = Left(tblB.YourFullcode, Len(tblA.YourCode));

This is a non-standard join and cannot be represented by the QBE design view. Once you change the join to be anything other than an equi-join, you will only be able to view/edit the query in SQL view. Joins where the two columns are compared with an = are called equi-joins and are the only join type that the Access query builder can handle in design view.

 

[Wonderer]

Absolutly Excellent that's really great

It works really well..

Although one little change i would like to make which is that in the the short code code be like this

7878

787895

Obviously this would this duplicate the result in the join

if say table B had 78789568

Thanks again for your help on this

Rob