Need search within each value to check against a list

  • Thread starter Thread starter Andrew
  • Start date Start date
A

Andrew

I've done a lot of searching and cannot find anything within the forums or
access itself that will do this (and my VB experience is limited to writing
macros for word and excel). Version used is 2003.

-Basically this is what I'm working with; it's only a small part of what I'm
actually out of a very large database, but it's the one part I can't figure
out on my own:

[report].[title] -> every title has the country in it, ONCE, like: "bla
bla bla - yadda yadda, and something (Namibia)"
[report].[reportID]
[country].[countryname] -> a list of every country, eg
Namibia;Russia;Austria...


-I need the following in three columns:

[title] ; [countryname] ; [reportID]
 
Just one correction. [report].[title] should be more like
[ReportReadByAUser].title
(because the report table already has the report ID, duh...)



Any help here is much appreciated!
 
How are the tables related? Without a relationship in the query you will get
a Cartesian effect like this --
Title ReportID countryname
Sam 1 USA
Sam 1 GB
Sam 2 USA
Sam 2 GB
Jim 1 USA
Jim 1 GB
Jim 2 USA
Jim 2 GB

This is just from two of each. If you have 30 of each then it will produce
30x30x30 or 27000 records.
 
.... you could do a soft join using report title and the country peice
but how the country is in the title needs to be exatally the same in
every title ie (nambia) (USA) what i am saying all country names in
titles need to be in brackets AND no other brackets are to be in the
titles or something like that

yours table structure should be

report >0---|| country

so the countryid should be in reports telling it that this report
belongs to this country

Regards
Kelvan
 
as a note on my above post you really wouldnt need to do a join just
use the expression

country: Mid([title],InStr([title],"(")+1,InStr([title],")")-
InStr([title],"(")-1)

IF you data looks like

reportid title
1 gfdgsdfgdfg (USA)
2 asdfasdfasdf (nambia)
3 asdfasdfsdafasdf (gb)
4 asdasdfasdfsad (new zealand)

the result would be

reportid title country
1 gfdgsdfgdfg (USA) USA
2 asdfasdfasdf (nambia) nambia
3 asdfasdfsdafasdf (gb) gb
4 asdasdfasdfsad (new zealand) new zealand

alternativally if you want to remove the country from the title you
need to use the expression

reporttitle: Left([title],InStr([title],"(")-1)

AS LONG AS THE FORMAT IS THE ABOVE

Regards
Kelvan
 
Back
Top