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. -- KARL DEWEY Build a little - Test a little "Andrew" wrote: > 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!
... 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
