help with an access query

[wengang]

hey all.
I'm stumped. Help please.

Asssume a set of files (a very large set) that have numerous possible locations that are enumerated: 1=folder, 2=soft copy, 3=different office, 4 = etc

This issue is only concerned with types 1 and 2
The file can exist in more than one location, i.e. in a folder and in soft copy
The folders are also numbered and hold multiple files.

The data table has fields: ID Name Locations

Because there are unlimited numbers of locations for a file, I've used this format for the locations field:
;2; 'this would be only in soft copy
;1,20; ' this would be a file that is only in a folder (folder # 20 in this case)
;1,20;2; ' this would be a file located in folder 20 and in soft copy

OK, the problem.
I need to find all the folders (like #20) wherein all the files in the folder also exist in soft copy:

select * from tblFiles where [every record having locations like '%,FOLDERNUMBER;%' and locations like '%;2;%']

I can't think of any way to do it.
Maybe I need to rewrite the locations system, but there is an indefinite number of location types, and only location type 1 has a sublocatoin number.

Anybody?
Thanks

[jggtz]

You could use InStr function...
I'm sure You can use in the SELECT part of the query, never tried in the WHERE part

http://www.google.com/#hl=es&xhr=t&q...9112bf2bac387e

JG

[wengang]

Thanks. I couldn't see quite what you meant by that.

Anyway, after some thought, I made a new column FOLDER and wrote a quick script to pull the folder number out for each file.
Then the query was very easy to write.
I guess I could have made a UDF but sometimes it's what works fastest.

[leinad31]

This is not solely a query issue but rather a design issue. Data architecture should have employed 3rd normal form http://www.oracle-dox.net/Digital.Pr...1/DDU0013.html

This will give you three tables:
Table A: List of filenames
Table B: List of locations
Table C: Pairs off pk from table A to pk in table B.

Assuming file with file_id = 1 located in folder 1,20, and 2, contents of table C would then be

FILE_ID     FOLDER_ID
1                1
1                20
1                2

That is the better way because performing function calls (InStr) per row in a table incurs CPU overhead.

[wengang]

Yeah, I know you're right about that. I started this db about 5 years ago, and it served our purposes as written until I decided to get rid of the files that we have in soft copy (and realized I needed that data to be distinct in another table) but I was able to script around it and solve it. maybe going forward I will migrate the data according to the above architecture.
Obviously the db is not at peak efficiency if the same data is listed twice.
BTW, is your name Daniel by any chance?