DISTINCT different files

[Higgs]

Hello,

I have the following table:

U I AbsTime TypeOfFile
2 0.01 0000001 000001-D00
3 0.01 0000002 000001-D00
4 0.01 0000003 000002-D00
7 0.02 0000004 000003-D00
5 0.03 0000005 000001-D04
6 0.01 0000006 000002-D04
8 0.01 0000008 000001-D00
9 0.02 0000010 000001-D00

On my form i have a combo box which uses the following SQL code:

SELECT DISTINCT TableData.TypeOfFile FROM TableData WHERE (((TableData.TypeOfFile) Is Not Null)) ORDER BY TableData.TypeOfFile

This code lists, based on the table above, the following in the combo box:
000001-D00
000002-D00
000003-D03
000001-D04
000002-D04

The problem i have is that the second time a file of type 000001-D00 is read into the table (as seen above) my SQL code doesn't separate those from the other 000001-D00's that are already in the table.

I have written a function ReturnDate() that takes a time parameter for the current record and returns a date that is relative to a preset date. See Code below.

Public Function ReturnDate(T_End As Double) As Date

Dim FirstDate As Date    ' Declare variables.
Dim IntervalType As String
Dim T_Seconds As Double
Dim T_start As Double

T_start = 815910630

IntervalType = "s"    ' "s" specifies months as interval.
FirstDate = "2010-10-21 15:00"
T_Seconds = T_End - T_start
ReturnDate = DateAdd(IntervalType, T_Seconds, FirstDate)

End Function

What i would like to do is to extend the sql code so that the combo box would include a 'date-tag' so i wont miss out on file types of same type but from different dates. So with the new code the combo box would look something like this:

2011-02-02 000001-D00
2011-02-10 000001-D00
2011-01-01 000002-D00
2011-03-02 000003-D03
2011-02-02 000001-D04
2011-02-01 000002-D04

It is good enough if the first AbsTime record is sent in as parameter to the function for each type of file.

How would i go on doing this?

Any help is much appreciated, hope the explanation is clear.

Thanks heaps

[NeoPa]

Higgs:
The problem i have is that the second time a file of type 000001-D00 is read into the table (as seen above) my SQL code doesn't separate those from the other 000001-D00's that are already in the table.

Does this mean that the value appears twice in your ComboBox? Otherwise I have no idea what you're trying to say here. Maybe you want it to appear twice? I'm confused. I'm guessing with very little info that makes sense, so it's better I wait for clarification.

[Higgs]

Yes, i will try to clarify;

Assume i have a folder containing 1000 files of a certain type i.e 000001-D00. After reading those into my table i get the next folder which might contain files of type 000002-D00. After that i might have a third folder that contains an identical file type which already have been read into the table, so the file type 000001-D00 may occur again.

The field with file types may look something like this;

TypeOfFile
000001-D00
.
.
.
000001-D00
000002-D00
000002-D00
.
.
000002-D00
000001-D00
.
.
000001-D00

Files with same 'names' may contain different data so they are all of interest. ALlthough when displaying my files in the combo box i want it to be easily overviewed whats available within the table. Since there are going to be millions of records of the same file type i would like to group all the file types, but, where the same file type have been read in multiple times (multiple sequences) i want to separate those from eachother and therefore i thought it would be handy to use the datefunction since i have a time field for each record.

I hope this makes it more clear, otherwise please ask me to try again :)

Cheers

[NeoPa]

Before you just talked about records in a table, but now you've introduced files in folders. Unfortunately, although I can probably guess (sort of) how these interrelate, you don't actually say so. This leaves me with more jig-saw pieces to fit into the puzzle than I started with. Unfortunate, but you've clearly made an effort, and I do actually appreciate how complicated most people find laying information out so that it can be understood by someone who doesn't actually have access to it themselves. So let me see if I can make some intelligent guesses to fill in the gaps and you can tell me if I'm too far off piste.

I think you probably have (many many) records with data which includes a field [TypeOfFile] as well as another DateTime field [FirstDate]. You are looking to aggregate (group by and report values that cover the groups) bunches of records which have the same [TypeOfFile] value, as well as [FirstDate] values whose date parts match. Assuming my assumption is correct then you might try :

SELECT   [TypeOfFile]
       , DatePart([FirstDate]) AS [FileDate]
FROM     [TableData]
WHERE    ([TypeOfFile] Is Not Null)
GROUP BY [TypeOfFile]
       , DatePart([FirstDate])
ORDER BY [TypeOfFile]
       , DatePart([FirstDate])