OK, here's a way to do it (for just one week, but you said that one week
is enough).
Let's say your big Table contains the following stuff:
[Forecast Table] Table Datasheet View:
Item Store Week 1 Week 2 ... Week 52
Nbr Forecast Forecast Forecast
---- ----- -------- -------- --------
xxxx 34 3 3 2
xxxx 35 2 1 3
I define the following function, about half of which is comments to let
the computer know why I'm having it do the stuff it does. What it does
is to change a Query according to my desires.
I can make the week number known by entering it into a Form, and that
works, but the code as written here pops up a dialogue box asking you to
enter the number of a week.
Oh, yes... you'll need to set a reference to DAO in your VBA window to
get this to work.
[Module 1] contains this VBA code:
Option Compare Database
Option Explicit
'This uses the specified number,
' for example 17, to set up
' SQL for Query [Q_MyQuery],
' looking something like this:
'
' SELECT [Item], [Store Nbr],
' "Week 17" as Week,
' [week 17 forecast] as Forecast
' FROM [Forecast Table];
'
Function ExtractWeek()
Dim strWeekNum As String
strWeekNum = Format$( _
InputBox("What week?"), "0")
'... or, you could use the following
' statement instead, to get the value
' from a Form:
'strWeekNum = Format$( _
Forms!F_Week!txbWeekNum, "0")
CurrentDb.QueryDefs("Q_MyQuery").SQL _
= "SELECT [Item], " _
& "[Store Nbr],""Week " _
& strWeekNum _
& """ as Week, " _
& "[Week " _
& strWeekNum _
& " Forecast] as Forecast " _
& "FROM [Forecast Table];"
End Function 'ExtractWeek
To invoke it, I have a Macro with just two Actions in it, though of
course you might want to enhance it a bit.
[M_RunQuery]
Actions
RunCode
Function Name: ExtractWeek ()
Comment: Specify SQL based on value
in Form [F_Week]
OpenQuery
Query Name: Q_MyQuery
View: Datasheet
Data Mode: Read Only
Comment: Display the results
Running this Macro causes Access to munch on it for a little while (but
it could be a big while, if you have a million records) and then display
the results. If you open the Query in SQL view, you'd find the
following, assuming you'd entered 52 when you ran the Macro:
[Q_MyQuery] SQL:
SELECT [Forecast Table].Item,
[Forecast Table].[Store Nbr],
"Week 52" AS Week,
[Forecast Table].[Week 52 Forecast]
AS Forecast
FROM [Forecast Table];
.... and the datasheet that pops up contains the following records:
[Q_MyQuery] Query Datasheet View:
Item Store Nbr Week Forecast
---- --------- ------- --------
xxxx 34 Week 52 2
xxxx 35 Week 52 3
This Macro TAKES THE PLACE OF the Union Query, if all you would then do
is to filter it by week. Why would you go to the trouble of collecting
stuff with the sole purpose of throwing it away?? Now, if you want 2 or
more weeks, that's a different story.
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
Thanks for your response. This is something that will need to be ran each
week, so your second suggestion using vba sounds like the way to go.
Unfortunately, I'm pretty illiterate when it comes to vba. I know enough to
make minor adjustments to macros I've recorded in Excel, but I can't write
code from scratch. Can you give me an example of what the vba might look
like. This code looks like the key to limiting this massive query to just
one week. With what you're suggesting, would you have the query set up as a
large union query like I originally had, and then the vba limits this so it
doesn't have to crunch the whole thing? Or will the setup be different? Any
additional guidance would be appreciated. I feel like I'm so close!
Jeff
Jeff
:
I don't know for sure, but it seems that "Query too complex" is a weird
error message if what's wrong is too many records. Maximum recordset
size for a Query is 1 gigabyte, so with 52 million records that's a
limit of about 20 bytes per record (in Access 2000 -- check Help for
Access specifications for your version).
Instead of a Union Query, what you might do (especially if you're going
to do this only once) is to run each SELECT in the Query as a separate
Append Query, to a Table with just the four fields you identified:
[Item]
[Store Nbr]
[Week]
[Forecast]
There'll be lots of records, but each one should be short. You can make
it even shorter by changing the data type of [Week] to be Integer or
Byte type, instead of String. You might also be able to shorten [Store
Nbr]. (Such attempts don't always work; a short value might be stored
internally in a 4-byte Long Integer -- I'm not sure what your version of
Access does.)
If you must do this repeatedly, such as on a linked Table over which you
have no control, you might consider writing some VBA code to calculate
the SQL for the Query that you need to run for a particular week. Your
VBA function (part of a Module) could return a string something like
"SELECT [Item], [Store Nbr],""Week 17"" as Week, " _
& "[week 17 forecast] as Forecast FROM [Forecast Table];"
that you could then run as a Query. (The 17 in this example would be
the result of some calculation.) Doing this would allow you to ignore
all of the 51 fields that you don't care about each week, and greatly
reduce the size of the dataset you must deal with.
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
goofy11 wrote:
Thanks for the reply. As you said what I'm trying to do is probably not
condusive to using Access. Perhaps I'll have to go back to the drawing board
on this one. Using the process you suggested is working, but so far it's
been running for 20 minutes and isn't done. I have no idea how long it will
take to display all 52 weeks worth. Since this will be a weekly process,
this won't be practical.
All I really need is 1 week from this table, which would be about 1,000,000
rows. But which week will be dependant on another query that will provide a
max week value. Let me know if you have any other ideas. Thanks for your
help.
Jeff
:
Dear Goof:
You could try using 4 queries, each appending 13 weeks worth of data to the
new table.
52 million rows will be a lot. Do some sizing on it before attempting.
This is beyond what I'd recommend using in a Jet database, but you're
already at and beyond that point with the old data.
Make the data a separate database and link to it.
Tom Ellison
By checking the newsgroups, I was able to find how to do a reverse cross
tab
query to normalize my data. The problem is that my data set is apparantly
too large. My table is about 1,000,000 lines with 52 weeks going across
the
top. So the end result of the reverse cross tab would be about 52,000,000
lines. When I try to run the query, I get an error message "Query is too
complex". I'm assuming this is because my data set is too big. Is that
correct? If so, is there another way to go about this? My data is
currently
structured like so:
Item Store Week 1 Forecast Week 2 Forecast......Week
52
Forecast
xxxx 34 3 3
2
xxxx 35 2 1
3
I would like to get the data in this format:
Item Store Week Forecast
xxxx 34 Week 1 3
xxxx 35 Week 2 3
My union query looks like this:
SELECT [Item], [Store Nbr],"Week 1" as Week, [week 1 forecast] as Forecast
FROM [Forecast Table]
UNION ALL
SELECT [Item], [Store Nbr],"Week 2" as Week, [week 2 forecast] as Forecast
FROM [Forecast Table]
ETC.......
I really don't need all 52 weeks worth. My end goal is to filter this
query
to only give me the data from the corresponding week number from another
query (only 1 week). So if I had another query that gave me MaxWeek, for
instance, then I would want to return the records from this query for the
MaxWeek. Hopefully that makes sense. Does anyone have ideas on this?
Thanks,
Jeff
