Querying Column Names/ Running Week totals

[Boxcar74]

Ok I don’t know if this is possible. I Know SQL Ok and it’s been a while since I worked with VB.

But what I’m trying to do is query by the dates in the column names.

For instance I'm tracking different kinds of issues (Example A, B, C)
on 4/26 I start with a query starting 4/21 – 4/25 to get the last 5 days of data, that is simple; I just use the design view and select the columns heading.

Issue 4/21 4/22 4/23 4/24 4/25 4/26 4/27 4/28 4/29
A 1 0 3 0 4 0 1 2 0
B 2 8 6 6 2 1 0 8 1
C 0 2 4 4 4 6 2 3 0


But what I need to do is create a query that AWAYS (everyday) get the last 5 days of DATA without manually updating the query every day.

Any help will be appreciated!!

BTW
In Reality I need the last three queries for the last 13 days, 13 weeks and 13 months data eveyday/week/month and I’m dealing with a DB that has columns going back years and on average 6000 + records.

[Rabbit]

So... you're talking a dynamic crosstab query?

You can use two queries, one to return records on the past 5 days, also returning the date. And the second would be the crosstab. Although note that this isn't very practical for a report because the field names would be changing constantly.

[Boxcar74]

I don't think that would work. THe query results are link to a spreadsheets, so it would get all messed up with that. and in the end I will have too many of them

Is there a way to just pick X amounts of coulmns? From a given date? Or Cloumns 5,4,3,2,1. Iv'e tried many differant ways with no luck.

[Rabbit]

I'm not quite sure I understand, is that sample data not the result of a crosstab query?

[Boxcar74]

No unfortunately the columns heading on the table have Dates in them I have two other tables with weeks and months as the columns names.

The rows I have various issues I’m tracking by category and each row shows the number of times the issue occurred during those dates. (One of the reasons I don’t think I can use a cross tab query is that I am tracking 4 columns of categories)

I'll try to explain with a better example:
Example Column headings (a more real example than I used before)
Category, Subcategory, Issue, Issue type, 4/17, 4/18, 4/19 etc...

Example row would be:
Hardware, hard drive, booting, not booting, 1, 3 2 etc….

(1,3,2 are the number of time this issue occured on that date in the cloumn name)

The dates go in indefinitely and I need to query the last 13 weeks.

I'm not sure wich direction to go with this.

I hope this clarifies it for you.

Rabbit thank you very much. You Input is Appreciated

[Rabbit]

It's very bad design to have your dates as fields because it limits your ability to query the data.

Usually you would have one date field and the date the event occured would go in that field rather than making them their own field. In that case you can just query to the days you want and crosstab them into the column headings. But by designing them as columns, you remove that possibility.

Your only choice is to use an intermediary form that rewrites the querydef.SQL of the query to account for the date changing all the time.

[Boxcar74]

Rabbit,

You’ve validated what I’ve though all along. Bad Design!! (Not mine)

You said “Your only choice is to use an intermediary form that rewrites the querydef.SQL” but I'm not 100% sure what you mean.

Do you mean create a form to manipulate the query? I’ve tried that but I’m not doing it right.

If that is the best way I will learn how to do it. I did it a long time ago and don't recall the the best approach. What do you think best approach for me is and/or best place to start.

Again thanks

[Rabbit]

Rabbit,

You’ve validated what I’ve though all along. Bad Design!! (Not mine)

You said “Your only choice is to use an intermediary form that rewrites the querydef.SQL” but 100% what you mean.

Do you mean create a form to manipulate the query? I’ve tried that but I’m not doing it right.

If that is the best way I will learn how to do it. I did it a long time ago and don't recall the the best approach. What do you think best approach for me is and/or best place to start.

Again thanks

That's exactly what I mean. Seeing as how the column names are always changing depending on the date, you'll have to use VBA code. You'll basically get the query def and change it's SQL.

Dim qdf As QueryDef
Set qdf = CurrentDb.QueryDefs("Query Name")
qdf.SQL = "New SQL string"
 

And the new SQL string will be built to take the current date and select the 5 dates before it.

[Boxcar74]

Sorry I feel like an idiot. Where do I but the code?
I put in a VBA Module and all I get is errors. I haven’t used VB in a long time.

I did it like this EX: Table name "DAILY" with field "CATEGORY" and Query name "QUERY1" and I’m trying to sum 1 column named 4/26/2007

Private Sub SQLSTRING()
Dim qdf As QueryDef
Set qdf = CurrentDb.QueryDefs("QUERY1")
qdf.SQL = "SELECT [DAILY]. CATEGORY, Sum([DAILY].[4/26/2007]) AS [4/26/2007]"
End Sub

I know I’m doing this totally wrong!!

I always get an error at qdf.SQL

Do you want me to connect this to a combo box or command button on a form.

I feel so dumb, I should know this part. But i feel clueless

Thanks

[Rabbit]

You forgot to tell it where to get the records. You need a FROM clause. You can't tell it to SELECT anything without telling it where to SELECT FROM.

And where you put it depends on when you want the query to run. Probably a button.

[Boxcar74]

I still have figuired out what I just posted I just wanted to see why it is formatted the way it is.

Your thoughts Please

In the begining I tried to transpose the data but it didn't play nice if I did the whole thing I'd end up with 6000+ Columns. And the data is a mess.

See Below.
-------------------------------------------------------------------------------------------------------------
Category Hardware Hardware (Columns Names)
SubCategory hard drive Printer
Product Type boot error HP1234
Problem Type Won't Boot not mapped
4/20/2007 0 0
4/21/2007 0 1
4/22/2007 0 1
4/23/2007 2 2
4/24/2007 2 0
4/25/2007 0 4
-------------------------------------------------------------------------------------------------------------

Picture 6000+ Columns and years of data. This is why we formatted it the other way. and I'd have 3 or 4 hundred named Hardware, which i believe is impossible. I hate this project!!


Any Suggestions are appreciated !!!!!!!!!!.

[Boxcar74]

You forgot to tell it where to get the records. You need a FROM clause. You can't tell it to SELECT anything without telling it where to SELECT FROM.

And where you put it depends on when you want the query to run. Probably a button.

I got an Error with the FROM in there I just copied the wrong version to you.
At least I'm getting there.

Thank you Very Much RABBIT. You have been a great help. I thought would require VB and some good VB to make it nice. But I needed to figuire it out my self. I've exhusted every angle but VB, didn't want to relearn it.
Like you said the DB is a bad design.

[Rabbit]

I got an Error with the FROM in there I just copied the wrong version to you.
At least I'm getting there.

Thank you Very Much RABBIT. You have been a great help. I thought would require VB and some good VB to make it nice. But I needed to figuire it out my self. I've exhusted every angle but VB, didn't want to relearn it.
Like you said the DB is a bad design.

I can't tell if this means you don't need any more help, for now anyways.

[Boxcar74]

I think you pointed me in the right direction. I don't need help now, unless you want to do eveything for me. I'm sure I'll come across some issues and will probably be back in a few days.

I got to learn some stuff on my own. More of a refresher of VB.

I have to say I've read so many of "thescripts" forums and they have help alot. Now that I join I'm even more impressed.

Thanks Rabbit

[Rabbit]

I think you pointed me in the right direction. I don't need help now, unless you want to do eveything for me. I'm sure I'll come across some issues and will probably be back in a few days.

I got to learn some stuff on my own. More of a refresher of VB.

I have to say I've read so many of "thescripts" forums and they have help alot. Now that I join I'm even more impressed.

Thanks Rabbit

Not a problem.

I choose to take a more guided approach rather than writing out the solution.
Good luck. Let us know if you have any problems.