Crosstab Macro

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to create four crostab queries every quarter. I would like to a macro
to perfrom that, is there a way using Macro to create a crosstab query?
Please it would save me lots of time
 
Can you provide some information regarding your crosstabs and why you have
to create new ones each quarter?
 
I work for a very large entity, we track all projects worth over 5 billion
dollors a year. We track four main milestone dates and completion
percentages, to inform us on the entire portfolio. The system we have are
not very streamlined, so I have to download a snap shot of the milestones on
certain dates in the quarters and run comparisons with a baseline and future
quarters. I create a make-table query and have my macro rename it, but the
milestones are new records and not project attributes, so I need the crosstab
to get to the milestones quickly.

I am not sure if I confused you, but the data is in this format

Project No Milesone Date PctComplete
XXXX 160 1/1/02 50%
yyyy 190 12/1/03 65%
XXXX 190 2/23/03 10%
zzzz 220 1/3/03 100%
XXXX 220 5/05/03 0%
XXXX 400 3/3/04 0%


I get that every quarter and need to comapre the dates for project XXX every
quarter for miles tone 160, then 190, then 220, then 400 and so on for aprox.
4000 projects.

I noticed my query is taking a very long time almost an hour,or even longer
 
I have no idea what you want your final display to look like. What query is
taking so long? Are any fields indexed?
 
Yes the Project No is the Key, the final query combines almost ten
subqueries, of which the the four milestone queries are a sub, and they are
in the format below.
 
Ten subqueries may take quite a while to process if they return lots of
records. Some times creating temporary tables will speed up querying or
report. I don't know if we have near a complete picture of what you are
starting with, where you want to go, and why.
 
Quarterly I track 4000 projects, what I track are four milestone dates, they
dates are in a file that is in the format below. I get that file quarterly,
what would be the best way to compare the dates? Thank you for your help
 
This depends on what you mean by compare the dates. Do you over-write or
delete existing data prior to "getting" the new data file?
 
No they want to see the dates of all quarters. I compare the dates to each
other and to other quarters data.
 
You have never provided a display of what you want the final display to look
like based on some existing actual data. Without this information, this
thread could continue for months.
 
ProjNo M160_q1 M160_q2 M160_q3 M160_q4 2-q1
q3-q2
xxxx 1/1/04 3/1/05 5/1/05
14 2mo

Column headings will be the milestones below

Project No Milesone Date PctComplete
XXXX 160 1/1/02 50%
yyyy 190 12/1/03 65%
XXXX 190 2/23/03 10%
zzzz 220 1/3/03 100%
XXXX 220 5/05/03 0%
XXXX 400 3/3/04 0%
 
Back
Top