Help With Queries

[paradox34690]

Hi everyone!

So, I'm sure I'm not the first to ask this, but i haven't found a clear example on the interwebs tonight and have had enough of banging my head.

Situation:

I have a table setup with the following headers, each of them is capable of filtering:

StationCallSign

ProgramTitle

AirStart

EndTime

Date

User

I need to be able to iterate through the filters as explained below and am wondering if it can be done with Autofilters the same way that i would with a PivotTable.

1. User
2. Date
3. ProgramTitle
4. AirStart

Each user will have multiple dates, which will have multiple programtitles, which MAY have multiple AirStart values. Once it gets to that point, I have the rest, it's getting there that I don't know about.

Is it possible to iterate through each of these similar to this? This example is for how to sloppily iterate through all values on a pivot table to select/deselect items automatically:

Dim PT As PivotTable


Set PT = Sheets("Sheet1").PivotTables("PivotTable1")


username = Sheets("Sheet1").Range("A4").Text


For Each Pi In PT.PivotFields("User").PivotItems
    If (Pi.Value = username) Or (Pi.Value = "(blank)") Then
        Pi.Visible = True
    Else
        Pi.Visible = False
    End If


Next Pi

Can I do a nested "For Each item in Autofilter.Criteria1" << I know this is not the correct way of writing, i simply have no other idea of how to explain it. Please let me know if I don't make sense and I will do what I can to make sense.

[Aflatoon]

The autofilter list items are not exposed in the object model unlike pivotitems. You would need to loop through the actual data ranges and build lists of unique items - with a Collection or Dictionary - and then set the autofilters appropriately. If you need the lists to cascade (i.e. only show the items applicable to the current filter) then you would have to check the row visibility as you loop through the range.

Can I ask why you have to do this?

[paradox34690]

Long story short, I'm the IT guy that also happens to be pretty decent at programming for the company I work for. We're going through a way to determine operational efficiency metrics and since I'm the guy that figured out how to do it in the first place, you get one guess who's been delegated with the task.

The data location for the table is a separate MS Access MDB database with over a million records containing historical data going to the beginning of the annual year. It has new data appended to it on a bi-weekly basis from an Oracle DB that I do not have direct access to.

Due to the nature of the company I work for and confidentiality agreements, I can't get into much in the way of specifics for the data except to say that this:
1. There may be 10 rows of data that are all exactly the same except for 1 column entry in each line; even when filtered all the way down as explained in my original post.
2. When filtered as described above, despite the multiple lines that will still be viewed, only the first line of each filtered result is needed so that a duration calculation can be done between the values in "AirStart" and "EndTime" - Essentially to determine the duration of the ProgramTitle listed.
3. Assume 365 entries required for Date values, but only to current date.

Once all of this is determined, and I'm able to do a program duration calculation, I'm going to take these values and append them back into the MDB database in a new column (perhaps in a different table in the database, but make it relational) so these calculations only need to be performed on new data.

[SamT]

Can we say that for each DB Record with a unique (User & Date & Program Title), you need the Duration = (EndTime - AirStart)?

Although I am barely a newbie at DBs, even if you add Station CallSign, it still sounds like a very simple Access Query to me.

How about I move this thread over to the Access Forum? I bet they can give you a simple, short solution that can be refined to return data for any given range of Dates, and/or Users, and/or Programs, and/or Stations, and/or etc, etc, etcetera. They can probably even process any new data without further intervention.

Please do not start a new thread, we would prefer to just move this one.

[paradox34690]

Sure, let's give it a shot! Never know, might learn something new!

[Aflatoon]

To be honest, even if you wanted to do it from within Excel, I'd suggest querying the Access db rather than trying to iterate through the autofilter.