Frustration has set in...

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

Jessica

okay, i tried to explain this in an earlier post, but i
don't think i did a good job. i have 2 sets of queries
(CUR & PREV) that pull basically the same data except its
for two different time periods but over the same window of
time ie: jan to dec. i want to add a 3rd query that shows
the difference between the two (VAR) per month. i have
all the queries created, but i don't know how to get the
first two queries merged with the third. the fields are:

CUR_BASE - PREV_BASE = VAR_BASE

how do i get them all into one query that i can then
create a crosstab query from???

thanks in advance to anyone who can help!!!
Jessica :)
 
okay, i tried to explain this in an earlier post, but i
don't think i did a good job. i have 2 sets of queries
(CUR & PREV) that pull basically the same data except its
for two different time periods but over the same window of
time ie: jan to dec. i want to add a 3rd query that shows
the difference between the two (VAR) per month. i have
all the queries created, but i don't know how to get the
first two queries merged with the third. the fields are:

CUR_BASE - PREV_BASE = VAR_BASE

how do i get them all into one query that i can then
create a crosstab query from???
Click to expand...

Is there some field in the Query which identifies which record in CUR
should be linked to which record in PREV? Could you post the SQL?
 
right now they're in a bunch of select queries... I can
post the SQL for each of them. Query 1 and 2 are joined
in a union query... but i don't know if i should bring in
Query 3 before or after the union. oh and the "WORKING"
field is what connects them all...

Query 1: SELECT [0054 CURRENT HRS UNION].WBSNUM, [0054
CURRENT HRS UNION].CHARGE_NUMBER, [0054 CURRENT HRS
UNION].ESDATE, [0054 CURRENT HRS UNION].CBGT AS CUR_BCWS,
[0054 CURRENT HRS UNION].[PROJECT ID], [0054 CURRENT HRS
UNION].WORKING, 2 AS [ORDER], "CUR BCWS" AS [VALUE]
FROM [0054 CURRENT HRS UNION];
Query 2: SELECT [0064 PREVIOUS HRS UNION].WBSNUM, [0064
PREVIOUS HRS UNION].CHARGE_NUMBER, [0064 PREVIOUS HRS
UNION].ESDATE, [0064 PREVIOUS HRS UNION].CBGT AS
PREV_BCWS, [0064 PREVIOUS HRS UNION].PROJECTID, [0064
PREVIOUS HRS UNION].WORKING, 1 AS [ORDER], "PREV BCWS" AS
[VALUE]FROM [0064 PREVIOUS HRS UNION];
Query 3: SELECT [0065 PREVIOUS BUDGET].WORKING, [0055
CURRENT BUDGET].CHARGE_NUMBER, [0055 CURRENT
BUDGET].ESDATE, [0055 CURRENT BUDGET].CUR_BCWS, [0065
PREVIOUS BUDGET].PREV_BCWS, [0055 CURRENT BUDGET]!CUR_BCWS-
[0065 PREVIOUS BUDGET]!PREV_BCWS AS VAR_BCWS
FROM [0065 PREVIOUS BUDGET] INNER JOIN [0055 CURRENT
BUDGET] ON [0065 PREVIOUS BUDGET].WORKING=[0055 CURRENT
BUDGET].WORKING;

i'd appreciate it if you can help... thank you!!!! :)
Jessica
 
right now they're in a bunch of select queries... I can
post the SQL for each of them. Query 1 and 2 are joined
in a union query... but i don't know if i should bring in
Query 3 before or after the union. oh and the "WORKING"
field is what connects them all...

Query 1: SELECT [0054 CURRENT HRS UNION].WBSNUM, [0054
CURRENT HRS UNION].CHARGE_NUMBER, [0054 CURRENT HRS
UNION].ESDATE, [0054 CURRENT HRS UNION].CBGT AS CUR_BCWS,
[0054 CURRENT HRS UNION].[PROJECT ID], [0054 CURRENT HRS
UNION].WORKING, 2 AS [ORDER], "CUR BCWS" AS [VALUE]
FROM [0054 CURRENT HRS UNION];
Query 2: SELECT [0064 PREVIOUS HRS UNION].WBSNUM, [0064
PREVIOUS HRS UNION].CHARGE_NUMBER, [0064 PREVIOUS HRS
UNION].ESDATE, [0064 PREVIOUS HRS UNION].CBGT AS
PREV_BCWS, [0064 PREVIOUS HRS UNION].PROJECTID, [0064
PREVIOUS HRS UNION].WORKING, 1 AS [ORDER], "PREV BCWS" AS
[VALUE]FROM [0064 PREVIOUS HRS UNION];
Query 3: SELECT [0065 PREVIOUS BUDGET].WORKING, [0055
CURRENT BUDGET].CHARGE_NUMBER, [0055 CURRENT
BUDGET].ESDATE, [0055 CURRENT BUDGET].CUR_BCWS, [0065
PREVIOUS BUDGET].PREV_BCWS, [0055 CURRENT BUDGET]!CUR_BCWS-
[0065 PREVIOUS BUDGET]!PREV_BCWS AS VAR_BCWS
FROM [0065 PREVIOUS BUDGET] INNER JOIN [0055 CURRENT
BUDGET] ON [0065 PREVIOUS BUDGET].WORKING=[0055 CURRENT
BUDGET].WORKING;

i'd appreciate it if you can help... thank you!!!! :)
Jessica
Click to expand...

I'd just join the UNION query (the name of which I do not know) to
Query3 by [Working] - unless there are multiple records for each value
of [Working] in the UNION query. It appears that these queries are
both already based on Union queries; this is getting pretty deep here!

What have you tried? What results did you get? What didn't it give you
that it should, or what did it give you that you didn't want?
 
John - thanks for your help. Unfortunately, i've tried
your suggestion before with no success. i can't turn it
into a crosstab query when i'm done. i need the cross tab
to look something like this:

working, projectID, WBS Num, Charge Num, Order, Value =
Row Headings
ESDATE = Column Heading
value is the BASE - where order #1 = CUR, order #2 = PREV
and order #3 = VAR

I.E.:
jan 03 feb 03 mar 03
wkg proj wbs c/n order=1 value=CUR 5 10 5
wkg proj wbs c/n order=2 value=PREV 6 6 8
wkg proj wbs c/n order=3 value=VAR -1 4 -3

AND YES, there are multiple records for each value of
working. it's very deep... hopefully not too deep!! :)

thanks again!
 
John - thanks for your help. Unfortunately, i've tried
your suggestion before with no success.
Click to expand...

I'm having some major computer problems and will need to reinstall
Windows... so I'm afraid I won't be able to look at this for a couple
of days. You might want to repost to see if someone else can help! If
I'm able to recover my newsgroup files after reformatting (which I
expect and hope) I'll try to get back to you.
 
Back
Top