[AccessD] Query problem

Hi Sander

OK. But that what was it looked like from your sample data.
Also, if you don't know what to look for, you're in trouble ...

Anyway, my suggestion will give you less than 12.000 names.
Next step could be to detract the numeric last part of the names. You
can do that with Val(InstrRev()).

What you also can do is to look up the left part of the filename:

  Select Distinct
    Left([Filename],[n]) As FileStart
  From
    YourTable

If n=1, you'll probably get the alphabet; if n=40 you'll get all
12.000 records. Increment from 1 and you'll get an idea of the names.
Write these selections to a temp table.

/gustav


> No that didn't do the trick. The problem is I do NOT know the values! So it's not just the first word (filename could be: customer01.xls, customer02.xls)
> It gets the first word, true but it didn't solve the problem.
 
> As mentioned before there are a lot of other fields that need to be displayed...
 
> A real nasty one. I've got some SQL-guru's over here looking at the problem. They've came up with the most exotic things..none of them worked.
 
> I'll have another look at it.
 
> Thanx anyway.
 
> Sander

> Gustav Brock <gustav at cactus.dk> wrote:
> Hi Sander

> Well, you need the first word of the filename.

> Try selecting on

> Select Distinct
> Left([Filename],
> IIf(InStr([Filename]," ")=0,
> Len([Filename]),
> InStr([Filename], " ")-1)) As File
> From
> tblFiles;

> /gustav


>> Thanx Gustav! That's exactly what I need!
>> Can you please provide me a query for the other 12.000(+) records? Oh, btw I do not know all filenames...yet!

>> :-) there's a little bit of sarcasme in the above :-)

>> I need to know which files where accessed during the last 7 days and I want to filter out 'period versioning'. So i've got a lot of files like:
>> Customer Aug 03.xls
>> Customer Sept 03.xls
>> Expens Aug 03.xls
>> Expens Sept 03.xls
>> etc etc

>> I want the query to return:
>> Customer
>> Expens

>> This way I know how many Excel-apps we've got...remember the 12000 records? that wasn't sarcasme :-(

>> HTH

>> Gustav Brock wrote:
>> Hi S

>>> i've got a query problem.
>>> I've got a table in A2k with 27 fields. The first field is "Filename". I've got the following data in the table:
>>> Filename Field2, Field3, Field4, etc...
>>> "Filename1", blabla, blabla, blabla, blabla, 
>>> "Filename2", blabla, blabla, blabla, blabla, 
>>> "Filename3", blabla, blabla, blabla, blabla, 

>>> I need a query that displays:
>>> "Filename1", blabla, blabla, blabla, blabla, 

>>> Problem: All other fields have diff info for all records!!

>> Well

>> Select * From tblSD Where Filename = 'Filename1';

>> will do that.
>> But that's too obvious. What are you trying to do?

>> /gustav