Can SQL help me???

  • Thread starter Thread starter Jessica
  • Start date Start date
J

Jessica

First, let me apologize because this may be long... but
i'm DESPERATE!!! i cannot figure this out...

Background: i have 3 queries where #1 gives me current
data, #2 gives me previous data and #3 gives me the
variance between 1 and 2. there is no primary key. the
following fields should be the same between the 3 queries:
WBSNUM, CHARGE_NUMBER, and WORKING. the ESDATE field
represents months, and there may or may not be data for
each ESDATE for a CUR BCWS or PREV BCWS.

Goal: show a separate row for CUR BCWS, PREV BCWS and VAR
BCWS per WBSNUM/CHARGE_NUMBER with the ESDATE as columns
in a crosstab query.

here are the queries in SQL:
#1: SELECT [HRS UNION].WBSNUM, [HRS UNION].CHARGE_NUMBER,
[HRS UNION].ESDATE, [HRS UNION].CBGT AS CUR_BCWS, [HRS
UNION].[PROJECT ID], [HRS UNION].WORKING, 1 AS
[ORDER], "CUR BCWS" AS [VALUE]
FROM [HRS UNION];

#2: SELECT [PREV HRS UNION].WBSNUM, [PREV HRS
UNION].CHARGE_NUMBER, [PREV HRS UNION].ESDATE, [PREV HRS
UNION].CBGT AS PREV_BCWS, [PREV HRS UNION].PROJECTID,
[PREV HRS UNION].WORKING, 2 AS [ORDER], "PREV BCWS" AS
[VALUE] FROM [PREV HRS UNION];

#3: SELECT TABLE_WBS.WBSNUM, [VAR].CHARGE_NUMBER,
[VAR].ESDATE, [VAR].VAR_BCWS AS BCWS, [VAR].WORKING,
TABLE_WBS.PROJECTID, 3 AS [ORDER], "VAR BCWS" AS [VALUE]
FROM [VAR] INNER JOIN TABLE_WBS ON [VAR].WORKING =
TABLE_WBS.PROJECTID;

hope i gave enough, and clear, info! Any help is GREATLY
appreciated... again, i apologize because this is so
complex. if it's easier to email to discuss, please post
address and i will respond.
thanks!!
Jessica
 
Would you be able to do a union on the 3 queries (or
switch the first to a make table and the 2 and 3rd to
append tables. Then do a cross table query on
WBSNUM, CHARGE_NUMBER, Value as the row headings
with the ESDATE as the column heading
and sum the order.
I am assuming ESDATE changes
 
Back
Top