VBA insert or update

G

Guest

Hi there,

Seems like no matter how much I try to avoid it, I'm just going to have to
learn to program in VBA...

I have created a summary table in the form of:
name, project, jan, feb, mar...

Each month column will contain the number of hours a person has worked for a
particular project. I have a query that grabs data from the source DB and
returns it in this fashion:

ANSWER SET
name, project, sumofhours

I can run that for a particular month. What I want to do is create some VBA
code whereby I crank off a SQL statement returning a particular month' ANSWER
SET. I then inspect each row returned and perform the following:

Is there a row already existing in my summary table for this project/resource?
If YES, then UPDATE the row in the summary table w data from ANSWER SET.
If NO, then INSERT the row in the summary table w datat from ANSWER SET.

How do I inspect each row in the ANSWER SET so that I can act upon it?

Thanks very much.

Russ
 
D

Douglas J. Steele

Having repeating fields like that (jan, feb, mar, ....) is seldom a good
idea in a relational database. Instead of 12 fields, you should have 12
rows, one for each month.

For information about writing queries that do what you're trying to do
(update existing rows or insert rows that don't exist), check my November,
2003 "Access Answers" column in Pinnacle Publication's "Smart Access". You
can download the column (and sample database) for free at
http://www.accessmvp.com/djsteele/SmartAccess.html
 
G

Guest

I purloined a book that is helping greatly. Using DAO, how do I open my
recordset w an existing query? I don't want to have to paset the SQL into
the function.

Set myRecordset As =myDatabase.OpenRecordset?????

thanks
 
G

Guest

Thanks Doug. I appreciate the benefits of 3rd normal form, etc. The reason
I'm trying to get the data into this particular structure is that ultimately
it is going to land in an excel spreadsheet in that format. If I grab the
data using the ANSWER SET format, it returns 600k rows and even if I wanted
to drop that into Excel I would have to put really scary logic trying to
traverse all those rows, which even if I could do that would slow the Excel
Calc function down to the point of making it non-operational. It's ugly I
know, but under the circumstances it really does need to be in this format.
If in this columnar fashion I think I can be down to a few thousand rows.

I tried to simply write a SQL statement that just brought it back in the
format I need, but the source DB is so normalized it takes many, many lines
of SQL just to get ANSWER SET. Even w DBA help I don't think we could figure
out how to force ANSWER SET to stretch out into columns for each month.

Thanks. I'll check out your article. - Russ
 
D

Douglas J. Steele

Are you saying you have a query that you want to use to open the recordset?
Just use the name of the query.

Set myRecordset = myDatabase.OpenRecordset("NameOfQuery")
 
G

Guest

No. I'm not familiar with it. I've never heard of it. I used to be a DBA
12 years ago, but I'm not up on the latest greatest. What I'm doing now
isn't really my "job" per se. I'm just trying to get something done. I went
to my assigned DBA and dropped this query on her, and she punted on the idea
of turning it into columns. This is what brings back my ANSWER SET for 1
month.

I'll google crosstab query. Thanks for all your help!

SELECT ADMUSER_RSRC.RSRC_NAME, ADMUSER_PROJECT.PROJ_SHORT_NAME,
Format$(ADMUSER_RSRCHOUR.WORK_DATE, 'mmmm') AS [MONTH],
Sum(ADMUSER_RSRCHOUR.HR_CNT) AS SumOfHR_CNT
FROM ((((ADMUSER_PROJWBS INNER JOIN (ADMUSER_RCATVAL INNER JOIN
(((ADMUSER_PROJECT INNER JOIN ADMUSER_TASK ON ADMUSER_PROJECT.PROJ_ID =
ADMUSER_TASK.PROJ_ID) INNER JOIN ((ADMUSER_RSRCHOUR INNER JOIN ADMUSER_RSRC
ON ADMUSER_RSRCHOUR.RSRC_ID = ADMUSER_RSRC.RSRC_ID) INNER JOIN
ADMUSER_TASKRSRC ON ADMUSER_RSRCHOUR.TASKRSRC_ID =
ADMUSER_TASKRSRC.TASKRSRC_ID) ON ADMUSER_TASK.TASK_ID =
ADMUSER_TASKRSRC.TASK_ID) INNER JOIN ADMUSER_RSRCRCAT ON ADMUSER_RSRC.RSRC_ID
= ADMUSER_RSRCRCAT.RSRC_ID) ON ADMUSER_RCATVAL.RSRC_CATG_ID =
ADMUSER_RSRCRCAT.RSRC_CATG_ID) ON ADMUSER_PROJWBS.PROJ_ID =
ADMUSER_PROJECT.PROJ_ID) INNER JOIN ADMUSER_PROJPCAT ON
ADMUSER_PROJECT.PROJ_ID = ADMUSER_PROJPCAT.PROJ_ID) INNER JOIN
RMISEXTN_RMIS_PROJ_INFO ON ADMUSER_PROJECT.PROJ_ID =
RMISEXTN_RMIS_PROJ_INFO.PROJ_ID) INNER JOIN RMISEXTN_RMIS_PROJ_PORTFOLIO ON
RMISEXTN_RMIS_PROJ_INFO.RMIS_PROJ_PORTFOLIO_ID =
RMISEXTN_RMIS_PROJ_PORTFOLIO.RMIS_PROJ_PORTFOLIO_ID) INNER JOIN
ADMUSER_PCATVAL ON ADMUSER_PROJPCAT.PROJ_CATG_ID =
ADMUSER_PCATVAL.PROJ_CATG_ID
WHERE (((ADMUSER_RSRCHOUR.WORK_DATE)>=#4/1/2007#) AND
((ADMUSER_RSRCHOUR.WORK_DATE)<#5/1/2007#) and
((ADMUSER_PROJPCAT.PROJ_CATG_TYPE_ID)=26) AND
((ADMUSER_RSRCRCAT.RSRC_CATG_TYPE_ID)=10) AND
((ADMUSER_PROJWBS.PROJ_NODE_FLAG)="Y"))
GROUP BY ADMUSER_RSRC.RSRC_NAME, ADMUSER_PROJECT.PROJ_SHORT_NAME,
Format$(ADMUSER_RSRCHOUR.WORK_DATE,'mmmm'),
Year(ADMUSER_RSRCHOUR.WORK_DATE)*12+DatePart('m',ADMUSER_RSRCHOUR.WORK_DATE)-1
ORDER BY ADMUSER_RSRC.RSRC_NAME;
 
D

Douglas J. Steele

Just go into Access, create a New query, and select Crosstab Query. It'll
walk you through the rest.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


xrbbaker said:
No. I'm not familiar with it. I've never heard of it. I used to be a
DBA
12 years ago, but I'm not up on the latest greatest. What I'm doing now
isn't really my "job" per se. I'm just trying to get something done. I
went
to my assigned DBA and dropped this query on her, and she punted on the
idea
of turning it into columns. This is what brings back my ANSWER SET for 1
month.

I'll google crosstab query. Thanks for all your help!

SELECT ADMUSER_RSRC.RSRC_NAME, ADMUSER_PROJECT.PROJ_SHORT_NAME,
Format$(ADMUSER_RSRCHOUR.WORK_DATE, 'mmmm') AS [MONTH],
Sum(ADMUSER_RSRCHOUR.HR_CNT) AS SumOfHR_CNT
FROM ((((ADMUSER_PROJWBS INNER JOIN (ADMUSER_RCATVAL INNER JOIN
(((ADMUSER_PROJECT INNER JOIN ADMUSER_TASK ON ADMUSER_PROJECT.PROJ_ID =
ADMUSER_TASK.PROJ_ID) INNER JOIN ((ADMUSER_RSRCHOUR INNER JOIN
ADMUSER_RSRC
ON ADMUSER_RSRCHOUR.RSRC_ID = ADMUSER_RSRC.RSRC_ID) INNER JOIN
ADMUSER_TASKRSRC ON ADMUSER_RSRCHOUR.TASKRSRC_ID =
ADMUSER_TASKRSRC.TASKRSRC_ID) ON ADMUSER_TASK.TASK_ID =
ADMUSER_TASKRSRC.TASK_ID) INNER JOIN ADMUSER_RSRCRCAT ON
ADMUSER_RSRC.RSRC_ID
= ADMUSER_RSRCRCAT.RSRC_ID) ON ADMUSER_RCATVAL.RSRC_CATG_ID =
ADMUSER_RSRCRCAT.RSRC_CATG_ID) ON ADMUSER_PROJWBS.PROJ_ID =
ADMUSER_PROJECT.PROJ_ID) INNER JOIN ADMUSER_PROJPCAT ON
ADMUSER_PROJECT.PROJ_ID = ADMUSER_PROJPCAT.PROJ_ID) INNER JOIN
RMISEXTN_RMIS_PROJ_INFO ON ADMUSER_PROJECT.PROJ_ID =
RMISEXTN_RMIS_PROJ_INFO.PROJ_ID) INNER JOIN RMISEXTN_RMIS_PROJ_PORTFOLIO
ON
RMISEXTN_RMIS_PROJ_INFO.RMIS_PROJ_PORTFOLIO_ID =
RMISEXTN_RMIS_PROJ_PORTFOLIO.RMIS_PROJ_PORTFOLIO_ID) INNER JOIN
ADMUSER_PCATVAL ON ADMUSER_PROJPCAT.PROJ_CATG_ID =
ADMUSER_PCATVAL.PROJ_CATG_ID
WHERE (((ADMUSER_RSRCHOUR.WORK_DATE)>=#4/1/2007#) AND
((ADMUSER_RSRCHOUR.WORK_DATE)<#5/1/2007#) and
((ADMUSER_PROJPCAT.PROJ_CATG_TYPE_ID)=26) AND
((ADMUSER_RSRCRCAT.RSRC_CATG_TYPE_ID)=10) AND
((ADMUSER_PROJWBS.PROJ_NODE_FLAG)="Y"))
GROUP BY ADMUSER_RSRC.RSRC_NAME, ADMUSER_PROJECT.PROJ_SHORT_NAME,
Format$(ADMUSER_RSRCHOUR.WORK_DATE,'mmmm'),
Year(ADMUSER_RSRCHOUR.WORK_DATE)*12+DatePart('m',ADMUSER_RSRCHOUR.WORK_DATE)-1
ORDER BY ADMUSER_RSRC.RSRC_NAME;




Douglas J. Steele said:
Have you tried using a crosstab query?
Click to expand...
Click to expand...
 
G

Guest

Geeze Doug, that is really cool. It is almost exactly what I want and
simplicity itself. One problem is this. It is bringing back data like this:

NAME PROJECT APRIL MAY
russ aaa 6
russ bbb 10
russ bbb 20

Is there a switch somewhere where I can make it return like this:
NAME PROJECT APRIL MAY
russ aaa 6
russ bbb 10 20

You are on a role! Any more magic up your sleeve? Thanks again.
 
D

Douglas J. Steele

What did you specify as the function after you'd told it what to use for the
row and column headers? Choosing Sum should give you what you're looking
for.
 
G

Guest

Doug,

Eureka!! I figured it out. (Well ok, we did.) Once I removed the line:

Year(ADMUSER_RSRCHOUR.WORK_DATE)*12+DatePart('m',ADMUSER_RSRCHOUR.WORK_DATE)-1

the thing did the Group By the way I wanted it to. You have been an
immeasureable help! Without a doubt you saved DAYS of work here.

Thanks again.

Douglas J. Steele said:
Just go into Access, create a New query, and select Crosstab Query. It'll
walk you through the rest.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


xrbbaker said:
No. I'm not familiar with it. I've never heard of it. I used to be a
DBA
12 years ago, but I'm not up on the latest greatest. What I'm doing now
isn't really my "job" per se. I'm just trying to get something done. I
went
to my assigned DBA and dropped this query on her, and she punted on the
idea
of turning it into columns. This is what brings back my ANSWER SET for 1
month.

I'll google crosstab query. Thanks for all your help!

SELECT ADMUSER_RSRC.RSRC_NAME, ADMUSER_PROJECT.PROJ_SHORT_NAME,
Format$(ADMUSER_RSRCHOUR.WORK_DATE, 'mmmm') AS [MONTH],
Sum(ADMUSER_RSRCHOUR.HR_CNT) AS SumOfHR_CNT
FROM ((((ADMUSER_PROJWBS INNER JOIN (ADMUSER_RCATVAL INNER JOIN
(((ADMUSER_PROJECT INNER JOIN ADMUSER_TASK ON ADMUSER_PROJECT.PROJ_ID =
ADMUSER_TASK.PROJ_ID) INNER JOIN ((ADMUSER_RSRCHOUR INNER JOIN
ADMUSER_RSRC
ON ADMUSER_RSRCHOUR.RSRC_ID = ADMUSER_RSRC.RSRC_ID) INNER JOIN
ADMUSER_TASKRSRC ON ADMUSER_RSRCHOUR.TASKRSRC_ID =
ADMUSER_TASKRSRC.TASKRSRC_ID) ON ADMUSER_TASK.TASK_ID =
ADMUSER_TASKRSRC.TASK_ID) INNER JOIN ADMUSER_RSRCRCAT ON
ADMUSER_RSRC.RSRC_ID
= ADMUSER_RSRCRCAT.RSRC_ID) ON ADMUSER_RCATVAL.RSRC_CATG_ID =
ADMUSER_RSRCRCAT.RSRC_CATG_ID) ON ADMUSER_PROJWBS.PROJ_ID =
ADMUSER_PROJECT.PROJ_ID) INNER JOIN ADMUSER_PROJPCAT ON
ADMUSER_PROJECT.PROJ_ID = ADMUSER_PROJPCAT.PROJ_ID) INNER JOIN
RMISEXTN_RMIS_PROJ_INFO ON ADMUSER_PROJECT.PROJ_ID =
RMISEXTN_RMIS_PROJ_INFO.PROJ_ID) INNER JOIN RMISEXTN_RMIS_PROJ_PORTFOLIO
ON
RMISEXTN_RMIS_PROJ_INFO.RMIS_PROJ_PORTFOLIO_ID =
RMISEXTN_RMIS_PROJ_PORTFOLIO.RMIS_PROJ_PORTFOLIO_ID) INNER JOIN
ADMUSER_PCATVAL ON ADMUSER_PROJPCAT.PROJ_CATG_ID =
ADMUSER_PCATVAL.PROJ_CATG_ID
WHERE (((ADMUSER_RSRCHOUR.WORK_DATE)>=#4/1/2007#) AND
((ADMUSER_RSRCHOUR.WORK_DATE)<#5/1/2007#) and
((ADMUSER_PROJPCAT.PROJ_CATG_TYPE_ID)=26) AND
((ADMUSER_RSRCRCAT.RSRC_CATG_TYPE_ID)=10) AND
((ADMUSER_PROJWBS.PROJ_NODE_FLAG)="Y"))
GROUP BY ADMUSER_RSRC.RSRC_NAME, ADMUSER_PROJECT.PROJ_SHORT_NAME,
Format$(ADMUSER_RSRCHOUR.WORK_DATE,'mmmm'),
Year(ADMUSER_RSRCHOUR.WORK_DATE)*12+DatePart('m',ADMUSER_RSRCHOUR.WORK_DATE)-1
ORDER BY ADMUSER_RSRC.RSRC_NAME;




Douglas J. Steele said:
Have you tried using a crosstab query?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks Doug. I appreciate the benefits of 3rd normal form, etc. The
reason
I'm trying to get the data into this particular structure is that
ultimately
it is going to land in an excel spreadsheet in that format. If I grab
the
data using the ANSWER SET format, it returns 600k rows and even if I
wanted
to drop that into Excel I would have to put really scary logic trying
to
traverse all those rows, which even if I could do that would slow the
Excel
Calc function down to the point of making it non-operational. It's
ugly I
know, but under the circumstances it really does need to be in this
format.
If in this columnar fashion I think I can be down to a few thousand
rows.

I tried to simply write a SQL statement that just brought it back in
the
format I need, but the source DB is so normalized it takes many, many
lines
of SQL just to get ANSWER SET. Even w DBA help I don't think we could
figure
out how to force ANSWER SET to stretch out into columns for each month.

Thanks. I'll check out your article. - Russ

:

Having repeating fields like that (jan, feb, mar, ....) is seldom a
good
idea in a relational database. Instead of 12 fields, you should have
12
rows, one for each month.

For information about writing queries that do what you're trying to do
(update existing rows or insert rows that don't exist), check my
November,
2003 "Access Answers" column in Pinnacle Publication's "Smart Access".
You
can download the column (and sample database) for free at
http://www.accessmvp.com/djsteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi there,

Seems like no matter how much I try to avoid it, I'm just going to
have
to
learn to program in VBA...

I have created a summary table in the form of:
name, project, jan, feb, mar...

Each month column will contain the number of hours a person has
worked
for
a
particular project. I have a query that grabs data from the source
DB
and
returns it in this fashion:

ANSWER SET
name, project, sumofhours

I can run that for a particular month. What I want to do is create
some
VBA
code whereby I crank off a SQL statement returning a particular
month'
ANSWER
SET. I then inspect each row returned and perform the following:

Is there a row already existing in my summary table for this
project/resource?
If YES, then UPDATE the row in the summary table w data from ANSWER
SET.
If NO, then INSERT the row in the summary table w datat from ANSWER
SET.

How do I inspect each row in the ANSWER SET so that I can act upon
it?

Thanks very much.

Russ
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Uh oh. It's trying to rain on my parade.

1) It looks like I can't run the TRANSFORM statement from w/in Excel. I
think no matter, I'll just use my Access intermediate table to house the
answer set and then select from that from Excel. Problem is
2) When I try to run the insert into it looks to me like the next thing it
wants to see after the insert line is a select statement, not a transform
statement. Is my short cut short circuited? (SYNTAX ERROR ON INSERT INTO)

INSERT INTO RUSS_ACTUAL_HOUR_SUMMARY
TRANSFORM Sum(ADMUSER_RSRCHOUR.HR_CNT) AS SumOfHR_CNT

Douglas J. Steele said:
Just go into Access, create a New query, and select Crosstab Query. It'll
walk you through the rest.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


xrbbaker said:
No. I'm not familiar with it. I've never heard of it. I used to be a
DBA
12 years ago, but I'm not up on the latest greatest. What I'm doing now
isn't really my "job" per se. I'm just trying to get something done. I
went
to my assigned DBA and dropped this query on her, and she punted on the
idea
of turning it into columns. This is what brings back my ANSWER SET for 1
month.

I'll google crosstab query. Thanks for all your help!

SELECT ADMUSER_RSRC.RSRC_NAME, ADMUSER_PROJECT.PROJ_SHORT_NAME,
Format$(ADMUSER_RSRCHOUR.WORK_DATE, 'mmmm') AS [MONTH],
Sum(ADMUSER_RSRCHOUR.HR_CNT) AS SumOfHR_CNT
FROM ((((ADMUSER_PROJWBS INNER JOIN (ADMUSER_RCATVAL INNER JOIN
(((ADMUSER_PROJECT INNER JOIN ADMUSER_TASK ON ADMUSER_PROJECT.PROJ_ID =
ADMUSER_TASK.PROJ_ID) INNER JOIN ((ADMUSER_RSRCHOUR INNER JOIN
ADMUSER_RSRC
ON ADMUSER_RSRCHOUR.RSRC_ID = ADMUSER_RSRC.RSRC_ID) INNER JOIN
ADMUSER_TASKRSRC ON ADMUSER_RSRCHOUR.TASKRSRC_ID =
ADMUSER_TASKRSRC.TASKRSRC_ID) ON ADMUSER_TASK.TASK_ID =
ADMUSER_TASKRSRC.TASK_ID) INNER JOIN ADMUSER_RSRCRCAT ON
ADMUSER_RSRC.RSRC_ID
= ADMUSER_RSRCRCAT.RSRC_ID) ON ADMUSER_RCATVAL.RSRC_CATG_ID =
ADMUSER_RSRCRCAT.RSRC_CATG_ID) ON ADMUSER_PROJWBS.PROJ_ID =
ADMUSER_PROJECT.PROJ_ID) INNER JOIN ADMUSER_PROJPCAT ON
ADMUSER_PROJECT.PROJ_ID = ADMUSER_PROJPCAT.PROJ_ID) INNER JOIN
RMISEXTN_RMIS_PROJ_INFO ON ADMUSER_PROJECT.PROJ_ID =
RMISEXTN_RMIS_PROJ_INFO.PROJ_ID) INNER JOIN RMISEXTN_RMIS_PROJ_PORTFOLIO
ON
RMISEXTN_RMIS_PROJ_INFO.RMIS_PROJ_PORTFOLIO_ID =
RMISEXTN_RMIS_PROJ_PORTFOLIO.RMIS_PROJ_PORTFOLIO_ID) INNER JOIN
ADMUSER_PCATVAL ON ADMUSER_PROJPCAT.PROJ_CATG_ID =
ADMUSER_PCATVAL.PROJ_CATG_ID
WHERE (((ADMUSER_RSRCHOUR.WORK_DATE)>=#4/1/2007#) AND
((ADMUSER_RSRCHOUR.WORK_DATE)<#5/1/2007#) and
((ADMUSER_PROJPCAT.PROJ_CATG_TYPE_ID)=26) AND
((ADMUSER_RSRCRCAT.RSRC_CATG_TYPE_ID)=10) AND
((ADMUSER_PROJWBS.PROJ_NODE_FLAG)="Y"))
GROUP BY ADMUSER_RSRC.RSRC_NAME, ADMUSER_PROJECT.PROJ_SHORT_NAME,
Format$(ADMUSER_RSRCHOUR.WORK_DATE,'mmmm'),
Year(ADMUSER_RSRCHOUR.WORK_DATE)*12+DatePart('m',ADMUSER_RSRCHOUR.WORK_DATE)-1
ORDER BY ADMUSER_RSRC.RSRC_NAME;




Douglas J. Steele said:
Have you tried using a crosstab query?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks Doug. I appreciate the benefits of 3rd normal form, etc. The
reason
I'm trying to get the data into this particular structure is that
ultimately
it is going to land in an excel spreadsheet in that format. If I grab
the
data using the ANSWER SET format, it returns 600k rows and even if I
wanted
to drop that into Excel I would have to put really scary logic trying
to
traverse all those rows, which even if I could do that would slow the
Excel
Calc function down to the point of making it non-operational. It's
ugly I
know, but under the circumstances it really does need to be in this
format.
If in this columnar fashion I think I can be down to a few thousand
rows.

I tried to simply write a SQL statement that just brought it back in
the
format I need, but the source DB is so normalized it takes many, many
lines
of SQL just to get ANSWER SET. Even w DBA help I don't think we could
figure
out how to force ANSWER SET to stretch out into columns for each month.

Thanks. I'll check out your article. - Russ

:

Having repeating fields like that (jan, feb, mar, ....) is seldom a
good
idea in a relational database. Instead of 12 fields, you should have
12
rows, one for each month.

For information about writing queries that do what you're trying to do
(update existing rows or insert rows that don't exist), check my
November,
2003 "Access Answers" column in Pinnacle Publication's "Smart Access".
You
can download the column (and sample database) for free at
http://www.accessmvp.com/djsteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi there,

Seems like no matter how much I try to avoid it, I'm just going to
have
to
learn to program in VBA...

I have created a summary table in the form of:
name, project, jan, feb, mar...

Each month column will contain the number of hours a person has
worked
for
a
particular project. I have a query that grabs data from the source
DB
and
returns it in this fashion:

ANSWER SET
name, project, sumofhours

I can run that for a particular month. What I want to do is create
some
VBA
code whereby I crank off a SQL statement returning a particular
month'
ANSWER
SET. I then inspect each row returned and perform the following:

Is there a row already existing in my summary table for this
project/resource?
If YES, then UPDATE the row in the summary table w data from ANSWER
SET.
If NO, then INSERT the row in the summary table w datat from ANSWER
SET.

How do I inspect each row in the ANSWER SET so that I can act upon
it?

Thanks very much.

Russ
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

1) Yes, I believe you can only run TRANSFORM statements from within Access,
not from outside.

2) Save your crosstab query. Create an Append query based on that saved
crosstab query, not on the table (and the Transform statement).

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


xrbbaker said:
Uh oh. It's trying to rain on my parade.

1) It looks like I can't run the TRANSFORM statement from w/in Excel. I
think no matter, I'll just use my Access intermediate table to house the
answer set and then select from that from Excel. Problem is
2) When I try to run the insert into it looks to me like the next thing
it
wants to see after the insert line is a select statement, not a transform
statement. Is my short cut short circuited? (SYNTAX ERROR ON INSERT
INTO)

INSERT INTO RUSS_ACTUAL_HOUR_SUMMARY
TRANSFORM Sum(ADMUSER_RSRCHOUR.HR_CNT) AS SumOfHR_CNT

Douglas J. Steele said:
Just go into Access, create a New query, and select Crosstab Query. It'll
walk you through the rest.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


xrbbaker said:
No. I'm not familiar with it. I've never heard of it. I used to be a
DBA
12 years ago, but I'm not up on the latest greatest. What I'm doing
now
isn't really my "job" per se. I'm just trying to get something done.
I
went
to my assigned DBA and dropped this query on her, and she punted on the
idea
of turning it into columns. This is what brings back my ANSWER SET for
1
month.

I'll google crosstab query. Thanks for all your help!

SELECT ADMUSER_RSRC.RSRC_NAME, ADMUSER_PROJECT.PROJ_SHORT_NAME,
Format$(ADMUSER_RSRCHOUR.WORK_DATE, 'mmmm') AS [MONTH],
Sum(ADMUSER_RSRCHOUR.HR_CNT) AS SumOfHR_CNT
FROM ((((ADMUSER_PROJWBS INNER JOIN (ADMUSER_RCATVAL INNER JOIN
(((ADMUSER_PROJECT INNER JOIN ADMUSER_TASK ON ADMUSER_PROJECT.PROJ_ID =
ADMUSER_TASK.PROJ_ID) INNER JOIN ((ADMUSER_RSRCHOUR INNER JOIN
ADMUSER_RSRC
ON ADMUSER_RSRCHOUR.RSRC_ID = ADMUSER_RSRC.RSRC_ID) INNER JOIN
ADMUSER_TASKRSRC ON ADMUSER_RSRCHOUR.TASKRSRC_ID =
ADMUSER_TASKRSRC.TASKRSRC_ID) ON ADMUSER_TASK.TASK_ID =
ADMUSER_TASKRSRC.TASK_ID) INNER JOIN ADMUSER_RSRCRCAT ON
ADMUSER_RSRC.RSRC_ID
= ADMUSER_RSRCRCAT.RSRC_ID) ON ADMUSER_RCATVAL.RSRC_CATG_ID =
ADMUSER_RSRCRCAT.RSRC_CATG_ID) ON ADMUSER_PROJWBS.PROJ_ID =
ADMUSER_PROJECT.PROJ_ID) INNER JOIN ADMUSER_PROJPCAT ON
ADMUSER_PROJECT.PROJ_ID = ADMUSER_PROJPCAT.PROJ_ID) INNER JOIN
RMISEXTN_RMIS_PROJ_INFO ON ADMUSER_PROJECT.PROJ_ID =
RMISEXTN_RMIS_PROJ_INFO.PROJ_ID) INNER JOIN
RMISEXTN_RMIS_PROJ_PORTFOLIO
ON
RMISEXTN_RMIS_PROJ_INFO.RMIS_PROJ_PORTFOLIO_ID =
RMISEXTN_RMIS_PROJ_PORTFOLIO.RMIS_PROJ_PORTFOLIO_ID) INNER JOIN
ADMUSER_PCATVAL ON ADMUSER_PROJPCAT.PROJ_CATG_ID =
ADMUSER_PCATVAL.PROJ_CATG_ID
WHERE (((ADMUSER_RSRCHOUR.WORK_DATE)>=#4/1/2007#) AND
((ADMUSER_RSRCHOUR.WORK_DATE)<#5/1/2007#) and
((ADMUSER_PROJPCAT.PROJ_CATG_TYPE_ID)=26) AND
((ADMUSER_RSRCRCAT.RSRC_CATG_TYPE_ID)=10) AND
((ADMUSER_PROJWBS.PROJ_NODE_FLAG)="Y"))
GROUP BY ADMUSER_RSRC.RSRC_NAME, ADMUSER_PROJECT.PROJ_SHORT_NAME,
Format$(ADMUSER_RSRCHOUR.WORK_DATE,'mmmm'),
Year(ADMUSER_RSRCHOUR.WORK_DATE)*12+DatePart('m',ADMUSER_RSRCHOUR.WORK_DATE)-1
ORDER BY ADMUSER_RSRC.RSRC_NAME;




:

Have you tried using a crosstab query?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks Doug. I appreciate the benefits of 3rd normal form, etc.
The
reason
I'm trying to get the data into this particular structure is that
ultimately
it is going to land in an excel spreadsheet in that format. If I
grab
the
data using the ANSWER SET format, it returns 600k rows and even if I
wanted
to drop that into Excel I would have to put really scary logic
trying
to
traverse all those rows, which even if I could do that would slow
the
Excel
Calc function down to the point of making it non-operational. It's
ugly I
know, but under the circumstances it really does need to be in this
format.
If in this columnar fashion I think I can be down to a few thousand
rows.

I tried to simply write a SQL statement that just brought it back in
the
format I need, but the source DB is so normalized it takes many,
many
lines
of SQL just to get ANSWER SET. Even w DBA help I don't think we
could
figure
out how to force ANSWER SET to stretch out into columns for each
month.

Thanks. I'll check out your article. - Russ

:

Having repeating fields like that (jan, feb, mar, ....) is seldom a
good
idea in a relational database. Instead of 12 fields, you should
have
12
rows, one for each month.

For information about writing queries that do what you're trying to
do
(update existing rows or insert rows that don't exist), check my
November,
2003 "Access Answers" column in Pinnacle Publication's "Smart
Access".
You
can download the column (and sample database) for free at
http://www.accessmvp.com/djsteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi there,

Seems like no matter how much I try to avoid it, I'm just going
to
have
to
learn to program in VBA...

I have created a summary table in the form of:
name, project, jan, feb, mar...

Each month column will contain the number of hours a person has
worked
for
a
particular project. I have a query that grabs data from the
source
DB
and
returns it in this fashion:

ANSWER SET
name, project, sumofhours

I can run that for a particular month. What I want to do is
create
some
VBA
code whereby I crank off a SQL statement returning a particular
month'
ANSWER
SET. I then inspect each row returned and perform the following:

Is there a row already existing in my summary table for this
project/resource?
If YES, then UPDATE the row in the summary table w data from
ANSWER
SET.
If NO, then INSERT the row in the summary table w datat from
ANSWER
SET.

How do I inspect each row in the ANSWER SET so that I can act
upon
it?

Thanks very much.

Russ
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

This sure has been lots of progress for one day. Thanks again for all the
help. Tomorrow I will investigate your Append query...

One more snag seems to be this. My Transformed answer set doesn't return
the data in date order. Here is acutal results...

NAME PROJ_SHORT_NAME April February January March May
ANIL C00013015 8 9

The last part of the SQL statement for the above is this:

GROUP BY ADMUSER_RSRC.RSRC_NAME, ADMUSER_PROJECT.PROJ_SHORT_NAME
ORDER BY ADMUSER_RSRC.RSRC_NAME, ADMUSER_PROJECT.PROJ_SHORT_NAME
PIVOT Format$(ADMUSER_RSRCHOUR.WORK_DATE,'mmmm');

I tried changing the sql to (below), but that didn't change anything. How
can I get it to come out in January, February, March order? -thx

GROUP BY ADMUSER_RSRC.RSRC_NAME, ADMUSER_PROJECT.PROJ_SHORT_NAME,
Format$(ADMUSER_RSRCHOUR.WORK_DATE,'mmmm')
ORDER BY ADMUSER_RSRC.RSRC_NAME, ADMUSER_PROJECT.PROJ_SHORT_NAME,
Format$(ADMUSER_RSRCHOUR.WORK_DATE,'mmmm')
PIVOT Format$(ADMUSER_RSRCHOUR.WORK_DATE,'mmmm');








Douglas J. Steele said:
1) Yes, I believe you can only run TRANSFORM statements from within Access,
not from outside.

2) Save your crosstab query. Create an Append query based on that saved
crosstab query, not on the table (and the Transform statement).

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


xrbbaker said:
Uh oh. It's trying to rain on my parade.

1) It looks like I can't run the TRANSFORM statement from w/in Excel. I
think no matter, I'll just use my Access intermediate table to house the
answer set and then select from that from Excel. Problem is
2) When I try to run the insert into it looks to me like the next thing
it
wants to see after the insert line is a select statement, not a transform
statement. Is my short cut short circuited? (SYNTAX ERROR ON INSERT
INTO)

INSERT INTO RUSS_ACTUAL_HOUR_SUMMARY
TRANSFORM Sum(ADMUSER_RSRCHOUR.HR_CNT) AS SumOfHR_CNT

Douglas J. Steele said:
Just go into Access, create a New query, and select Crosstab Query. It'll
walk you through the rest.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


No. I'm not familiar with it. I've never heard of it. I used to be a
DBA
12 years ago, but I'm not up on the latest greatest. What I'm doing
now
isn't really my "job" per se. I'm just trying to get something done.
I
went
to my assigned DBA and dropped this query on her, and she punted on the
idea
of turning it into columns. This is what brings back my ANSWER SET for
1
month.

I'll google crosstab query. Thanks for all your help!

SELECT ADMUSER_RSRC.RSRC_NAME, ADMUSER_PROJECT.PROJ_SHORT_NAME,
Format$(ADMUSER_RSRCHOUR.WORK_DATE, 'mmmm') AS [MONTH],
Sum(ADMUSER_RSRCHOUR.HR_CNT) AS SumOfHR_CNT
FROM ((((ADMUSER_PROJWBS INNER JOIN (ADMUSER_RCATVAL INNER JOIN
(((ADMUSER_PROJECT INNER JOIN ADMUSER_TASK ON ADMUSER_PROJECT.PROJ_ID =
ADMUSER_TASK.PROJ_ID) INNER JOIN ((ADMUSER_RSRCHOUR INNER JOIN
ADMUSER_RSRC
ON ADMUSER_RSRCHOUR.RSRC_ID = ADMUSER_RSRC.RSRC_ID) INNER JOIN
ADMUSER_TASKRSRC ON ADMUSER_RSRCHOUR.TASKRSRC_ID =
ADMUSER_TASKRSRC.TASKRSRC_ID) ON ADMUSER_TASK.TASK_ID =
ADMUSER_TASKRSRC.TASK_ID) INNER JOIN ADMUSER_RSRCRCAT ON
ADMUSER_RSRC.RSRC_ID
= ADMUSER_RSRCRCAT.RSRC_ID) ON ADMUSER_RCATVAL.RSRC_CATG_ID =
ADMUSER_RSRCRCAT.RSRC_CATG_ID) ON ADMUSER_PROJWBS.PROJ_ID =
ADMUSER_PROJECT.PROJ_ID) INNER JOIN ADMUSER_PROJPCAT ON
ADMUSER_PROJECT.PROJ_ID = ADMUSER_PROJPCAT.PROJ_ID) INNER JOIN
RMISEXTN_RMIS_PROJ_INFO ON ADMUSER_PROJECT.PROJ_ID =
RMISEXTN_RMIS_PROJ_INFO.PROJ_ID) INNER JOIN
RMISEXTN_RMIS_PROJ_PORTFOLIO
ON
RMISEXTN_RMIS_PROJ_INFO.RMIS_PROJ_PORTFOLIO_ID =
RMISEXTN_RMIS_PROJ_PORTFOLIO.RMIS_PROJ_PORTFOLIO_ID) INNER JOIN
ADMUSER_PCATVAL ON ADMUSER_PROJPCAT.PROJ_CATG_ID =
ADMUSER_PCATVAL.PROJ_CATG_ID
WHERE (((ADMUSER_RSRCHOUR.WORK_DATE)>=#4/1/2007#) AND
((ADMUSER_RSRCHOUR.WORK_DATE)<#5/1/2007#) and
((ADMUSER_PROJPCAT.PROJ_CATG_TYPE_ID)=26) AND
((ADMUSER_RSRCRCAT.RSRC_CATG_TYPE_ID)=10) AND
((ADMUSER_PROJWBS.PROJ_NODE_FLAG)="Y"))
GROUP BY ADMUSER_RSRC.RSRC_NAME, ADMUSER_PROJECT.PROJ_SHORT_NAME,
Format$(ADMUSER_RSRCHOUR.WORK_DATE,'mmmm'),
Year(ADMUSER_RSRCHOUR.WORK_DATE)*12+DatePart('m',ADMUSER_RSRCHOUR.WORK_DATE)-1
ORDER BY ADMUSER_RSRC.RSRC_NAME;




:

Have you tried using a crosstab query?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks Doug. I appreciate the benefits of 3rd normal form, etc.
The
reason
I'm trying to get the data into this particular structure is that
ultimately
it is going to land in an excel spreadsheet in that format. If I
grab
the
data using the ANSWER SET format, it returns 600k rows and even if I
wanted
to drop that into Excel I would have to put really scary logic
trying
to
traverse all those rows, which even if I could do that would slow
the
Excel
Calc function down to the point of making it non-operational. It's
ugly I
know, but under the circumstances it really does need to be in this
format.
If in this columnar fashion I think I can be down to a few thousand
rows.

I tried to simply write a SQL statement that just brought it back in
the
format I need, but the source DB is so normalized it takes many,
many
lines
of SQL just to get ANSWER SET. Even w DBA help I don't think we
could
figure
out how to force ANSWER SET to stretch out into columns for each
month.

Thanks. I'll check out your article. - Russ

:

Having repeating fields like that (jan, feb, mar, ....) is seldom a
good
idea in a relational database. Instead of 12 fields, you should
have
12
rows, one for each month.

For information about writing queries that do what you're trying to
do
(update existing rows or insert rows that don't exist), check my
November,
2003 "Access Answers" column in Pinnacle Publication's "Smart
Access".
You
can download the column (and sample database) for free at
http://www.accessmvp.com/djsteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi there,

Seems like no matter how much I try to avoid it, I'm just going
to
have
to
learn to program in VBA...

I have created a summary table in the form of:
name, project, jan, feb, mar...

Each month column will contain the number of hours a person has
worked
for
a
particular project. I have a query that grabs data from the
source
DB
and
returns it in this fashion:

ANSWER SET
name, project, sumofhours

I can run that for a particular month. What I want to do is
create
some
VBA
code whereby I crank off a SQL statement returning a particular
month'
ANSWER
SET. I then inspect each row returned and perform the following:

Is there a row already existing in my summary table for this
project/resource?
If YES, then UPDATE the row in the summary table w data from
ANSWER
SET.
If NO, then INSERT the row in the summary table w datat from
ANSWER
SET.

How do I inspect each row in the ANSWER SET so that I can act
upon
it?

Thanks very much.

Russ
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Actually, it is returning it in date order. You told it to use the text
representation of the month: April, February, January, March etc. is how
they sort alphabetically.

Try:

PIVOT Format$(ADMUSER_RSRCHOUR.WORK_DATE,'mmmm') IN ('January', 'February',
'March', 'April', ..., 'December')

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


xrbbaker said:
This sure has been lots of progress for one day. Thanks again for all the
help. Tomorrow I will investigate your Append query...

One more snag seems to be this. My Transformed answer set doesn't return
the data in date order. Here is acutal results...

NAME PROJ_SHORT_NAME April February January March May
ANIL C00013015 8 9

The last part of the SQL statement for the above is this:

GROUP BY ADMUSER_RSRC.RSRC_NAME, ADMUSER_PROJECT.PROJ_SHORT_NAME
ORDER BY ADMUSER_RSRC.RSRC_NAME, ADMUSER_PROJECT.PROJ_SHORT_NAME
PIVOT Format$(ADMUSER_RSRCHOUR.WORK_DATE,'mmmm');

I tried changing the sql to (below), but that didn't change anything. How
can I get it to come out in January, February, March order? -thx

GROUP BY ADMUSER_RSRC.RSRC_NAME, ADMUSER_PROJECT.PROJ_SHORT_NAME,
Format$(ADMUSER_RSRCHOUR.WORK_DATE,'mmmm')
ORDER BY ADMUSER_RSRC.RSRC_NAME, ADMUSER_PROJECT.PROJ_SHORT_NAME,
Format$(ADMUSER_RSRCHOUR.WORK_DATE,'mmmm')
PIVOT Format$(ADMUSER_RSRCHOUR.WORK_DATE,'mmmm');








Douglas J. Steele said:
1) Yes, I believe you can only run TRANSFORM statements from within
Access,
not from outside.

2) Save your crosstab query. Create an Append query based on that saved
crosstab query, not on the table (and the Transform statement).

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


xrbbaker said:
Uh oh. It's trying to rain on my parade.

1) It looks like I can't run the TRANSFORM statement from w/in Excel.
I
think no matter, I'll just use my Access intermediate table to house
the
answer set and then select from that from Excel. Problem is
2) When I try to run the insert into it looks to me like the next
thing
it
wants to see after the insert line is a select statement, not a
transform
statement. Is my short cut short circuited? (SYNTAX ERROR ON INSERT
INTO)

INSERT INTO RUSS_ACTUAL_HOUR_SUMMARY
TRANSFORM Sum(ADMUSER_RSRCHOUR.HR_CNT) AS SumOfHR_CNT

:

Just go into Access, create a New query, and select Crosstab Query.
It'll
walk you through the rest.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


No. I'm not familiar with it. I've never heard of it. I used to
be a
DBA
12 years ago, but I'm not up on the latest greatest. What I'm doing
now
isn't really my "job" per se. I'm just trying to get something
done.
I
went
to my assigned DBA and dropped this query on her, and she punted on
the
idea
of turning it into columns. This is what brings back my ANSWER SET
for
1
month.

I'll google crosstab query. Thanks for all your help!

SELECT ADMUSER_RSRC.RSRC_NAME, ADMUSER_PROJECT.PROJ_SHORT_NAME,
Format$(ADMUSER_RSRCHOUR.WORK_DATE, 'mmmm') AS [MONTH],
Sum(ADMUSER_RSRCHOUR.HR_CNT) AS SumOfHR_CNT
FROM ((((ADMUSER_PROJWBS INNER JOIN (ADMUSER_RCATVAL INNER JOIN
(((ADMUSER_PROJECT INNER JOIN ADMUSER_TASK ON
ADMUSER_PROJECT.PROJ_ID =
ADMUSER_TASK.PROJ_ID) INNER JOIN ((ADMUSER_RSRCHOUR INNER JOIN
ADMUSER_RSRC
ON ADMUSER_RSRCHOUR.RSRC_ID = ADMUSER_RSRC.RSRC_ID) INNER JOIN
ADMUSER_TASKRSRC ON ADMUSER_RSRCHOUR.TASKRSRC_ID =
ADMUSER_TASKRSRC.TASKRSRC_ID) ON ADMUSER_TASK.TASK_ID =
ADMUSER_TASKRSRC.TASK_ID) INNER JOIN ADMUSER_RSRCRCAT ON
ADMUSER_RSRC.RSRC_ID
= ADMUSER_RSRCRCAT.RSRC_ID) ON ADMUSER_RCATVAL.RSRC_CATG_ID =
ADMUSER_RSRCRCAT.RSRC_CATG_ID) ON ADMUSER_PROJWBS.PROJ_ID =
ADMUSER_PROJECT.PROJ_ID) INNER JOIN ADMUSER_PROJPCAT ON
ADMUSER_PROJECT.PROJ_ID = ADMUSER_PROJPCAT.PROJ_ID) INNER JOIN
RMISEXTN_RMIS_PROJ_INFO ON ADMUSER_PROJECT.PROJ_ID =
RMISEXTN_RMIS_PROJ_INFO.PROJ_ID) INNER JOIN
RMISEXTN_RMIS_PROJ_PORTFOLIO
ON
RMISEXTN_RMIS_PROJ_INFO.RMIS_PROJ_PORTFOLIO_ID =
RMISEXTN_RMIS_PROJ_PORTFOLIO.RMIS_PROJ_PORTFOLIO_ID) INNER JOIN
ADMUSER_PCATVAL ON ADMUSER_PROJPCAT.PROJ_CATG_ID =
ADMUSER_PCATVAL.PROJ_CATG_ID
WHERE (((ADMUSER_RSRCHOUR.WORK_DATE)>=#4/1/2007#) AND
((ADMUSER_RSRCHOUR.WORK_DATE)<#5/1/2007#) and
((ADMUSER_PROJPCAT.PROJ_CATG_TYPE_ID)=26) AND
((ADMUSER_RSRCRCAT.RSRC_CATG_TYPE_ID)=10) AND
((ADMUSER_PROJWBS.PROJ_NODE_FLAG)="Y"))
GROUP BY ADMUSER_RSRC.RSRC_NAME, ADMUSER_PROJECT.PROJ_SHORT_NAME,
Format$(ADMUSER_RSRCHOUR.WORK_DATE,'mmmm'),
Year(ADMUSER_RSRCHOUR.WORK_DATE)*12+DatePart('m',ADMUSER_RSRCHOUR.WORK_DATE)-1
ORDER BY ADMUSER_RSRC.RSRC_NAME;




:

Have you tried using a crosstab query?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks Doug. I appreciate the benefits of 3rd normal form, etc.
The
reason
I'm trying to get the data into this particular structure is that
ultimately
it is going to land in an excel spreadsheet in that format. If I
grab
the
data using the ANSWER SET format, it returns 600k rows and even
if I
wanted
to drop that into Excel I would have to put really scary logic
trying
to
traverse all those rows, which even if I could do that would slow
the
Excel
Calc function down to the point of making it non-operational.
It's
ugly I
know, but under the circumstances it really does need to be in
this
format.
If in this columnar fashion I think I can be down to a few
thousand
rows.

I tried to simply write a SQL statement that just brought it back
in
the
format I need, but the source DB is so normalized it takes many,
many
lines
of SQL just to get ANSWER SET. Even w DBA help I don't think we
could
figure
out how to force ANSWER SET to stretch out into columns for each
month.

Thanks. I'll check out your article. - Russ

:

Having repeating fields like that (jan, feb, mar, ....) is
seldom a
good
idea in a relational database. Instead of 12 fields, you should
have
12
rows, one for each month.

For information about writing queries that do what you're trying
to
do
(update existing rows or insert rows that don't exist), check my
November,
2003 "Access Answers" column in Pinnacle Publication's "Smart
Access".
You
can download the column (and sample database) for free at
http://www.accessmvp.com/djsteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi there,

Seems like no matter how much I try to avoid it, I'm just
going
to
have
to
learn to program in VBA...

I have created a summary table in the form of:
name, project, jan, feb, mar...

Each month column will contain the number of hours a person
has
worked
for
a
particular project. I have a query that grabs data from the
source
DB
and
returns it in this fashion:

ANSWER SET
name, project, sumofhours

I can run that for a particular month. What I want to do is
create
some
VBA
code whereby I crank off a SQL statement returning a
particular
month'
ANSWER
SET. I then inspect each row returned and perform the
following:

Is there a row already existing in my summary table for this
project/resource?
If YES, then UPDATE the row in the summary table w data from
ANSWER
SET.
If NO, then INSERT the row in the summary table w datat from
ANSWER
SET.

How do I inspect each row in the ANSWER SET so that I can act
upon
it?

Thanks very much.

Russ
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Doug,

Your last suggestion works like a charm. Just for my edification, I don't
understand your point where you say it is returning them in date order. How
is April, Feb, Jan - A,F,J sorted?

thanks

Douglas J. Steele said:
Actually, it is returning it in date order. You told it to use the text
representation of the month: April, February, January, March etc. is how
they sort alphabetically.

Try:

PIVOT Format$(ADMUSER_RSRCHOUR.WORK_DATE,'mmmm') IN ('January', 'February',
'March', 'April', ..., 'December')

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


xrbbaker said:
This sure has been lots of progress for one day. Thanks again for all the
help. Tomorrow I will investigate your Append query...

One more snag seems to be this. My Transformed answer set doesn't return
the data in date order. Here is acutal results...

NAME PROJ_SHORT_NAME April February January March May
ANIL C00013015 8 9

The last part of the SQL statement for the above is this:

GROUP BY ADMUSER_RSRC.RSRC_NAME, ADMUSER_PROJECT.PROJ_SHORT_NAME
ORDER BY ADMUSER_RSRC.RSRC_NAME, ADMUSER_PROJECT.PROJ_SHORT_NAME
PIVOT Format$(ADMUSER_RSRCHOUR.WORK_DATE,'mmmm');

I tried changing the sql to (below), but that didn't change anything. How
can I get it to come out in January, February, March order? -thx

GROUP BY ADMUSER_RSRC.RSRC_NAME, ADMUSER_PROJECT.PROJ_SHORT_NAME,
Format$(ADMUSER_RSRCHOUR.WORK_DATE,'mmmm')
ORDER BY ADMUSER_RSRC.RSRC_NAME, ADMUSER_PROJECT.PROJ_SHORT_NAME,
Format$(ADMUSER_RSRCHOUR.WORK_DATE,'mmmm')
PIVOT Format$(ADMUSER_RSRCHOUR.WORK_DATE,'mmmm');








Douglas J. Steele said:
1) Yes, I believe you can only run TRANSFORM statements from within
Access,
not from outside.

2) Save your crosstab query. Create an Append query based on that saved
crosstab query, not on the table (and the Transform statement).

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Uh oh. It's trying to rain on my parade.

1) It looks like I can't run the TRANSFORM statement from w/in Excel.
I
think no matter, I'll just use my Access intermediate table to house
the
answer set and then select from that from Excel. Problem is
2) When I try to run the insert into it looks to me like the next
thing
it
wants to see after the insert line is a select statement, not a
transform
statement. Is my short cut short circuited? (SYNTAX ERROR ON INSERT
INTO)

INSERT INTO RUSS_ACTUAL_HOUR_SUMMARY
TRANSFORM Sum(ADMUSER_RSRCHOUR.HR_CNT) AS SumOfHR_CNT

:

Just go into Access, create a New query, and select Crosstab Query.
It'll
walk you through the rest.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


No. I'm not familiar with it. I've never heard of it. I used to
be a
DBA
12 years ago, but I'm not up on the latest greatest. What I'm doing
now
isn't really my "job" per se. I'm just trying to get something
done.
I
went
to my assigned DBA and dropped this query on her, and she punted on
the
idea
of turning it into columns. This is what brings back my ANSWER SET
for
1
month.

I'll google crosstab query. Thanks for all your help!

SELECT ADMUSER_RSRC.RSRC_NAME, ADMUSER_PROJECT.PROJ_SHORT_NAME,
Format$(ADMUSER_RSRCHOUR.WORK_DATE, 'mmmm') AS [MONTH],
Sum(ADMUSER_RSRCHOUR.HR_CNT) AS SumOfHR_CNT
FROM ((((ADMUSER_PROJWBS INNER JOIN (ADMUSER_RCATVAL INNER JOIN
(((ADMUSER_PROJECT INNER JOIN ADMUSER_TASK ON
ADMUSER_PROJECT.PROJ_ID =
ADMUSER_TASK.PROJ_ID) INNER JOIN ((ADMUSER_RSRCHOUR INNER JOIN
ADMUSER_RSRC
ON ADMUSER_RSRCHOUR.RSRC_ID = ADMUSER_RSRC.RSRC_ID) INNER JOIN
ADMUSER_TASKRSRC ON ADMUSER_RSRCHOUR.TASKRSRC_ID =
ADMUSER_TASKRSRC.TASKRSRC_ID) ON ADMUSER_TASK.TASK_ID =
ADMUSER_TASKRSRC.TASK_ID) INNER JOIN ADMUSER_RSRCRCAT ON
ADMUSER_RSRC.RSRC_ID
= ADMUSER_RSRCRCAT.RSRC_ID) ON ADMUSER_RCATVAL.RSRC_CATG_ID =
ADMUSER_RSRCRCAT.RSRC_CATG_ID) ON ADMUSER_PROJWBS.PROJ_ID =
ADMUSER_PROJECT.PROJ_ID) INNER JOIN ADMUSER_PROJPCAT ON
ADMUSER_PROJECT.PROJ_ID = ADMUSER_PROJPCAT.PROJ_ID) INNER JOIN
RMISEXTN_RMIS_PROJ_INFO ON ADMUSER_PROJECT.PROJ_ID =
RMISEXTN_RMIS_PROJ_INFO.PROJ_ID) INNER JOIN
RMISEXTN_RMIS_PROJ_PORTFOLIO
ON
RMISEXTN_RMIS_PROJ_INFO.RMIS_PROJ_PORTFOLIO_ID =
RMISEXTN_RMIS_PROJ_PORTFOLIO.RMIS_PROJ_PORTFOLIO_ID) INNER JOIN
ADMUSER_PCATVAL ON ADMUSER_PROJPCAT.PROJ_CATG_ID =
ADMUSER_PCATVAL.PROJ_CATG_ID
WHERE (((ADMUSER_RSRCHOUR.WORK_DATE)>=#4/1/2007#) AND
((ADMUSER_RSRCHOUR.WORK_DATE)<#5/1/2007#) and
((ADMUSER_PROJPCAT.PROJ_CATG_TYPE_ID)=26) AND
((ADMUSER_RSRCRCAT.RSRC_CATG_TYPE_ID)=10) AND
((ADMUSER_PROJWBS.PROJ_NODE_FLAG)="Y"))
GROUP BY ADMUSER_RSRC.RSRC_NAME, ADMUSER_PROJECT.PROJ_SHORT_NAME,
Format$(ADMUSER_RSRCHOUR.WORK_DATE,'mmmm'),
Year(ADMUSER_RSRCHOUR.WORK_DATE)*12+DatePart('m',ADMUSER_RSRCHOUR.WORK_DATE)-1
ORDER BY ADMUSER_RSRC.RSRC_NAME;




:

Have you tried using a crosstab query?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks Doug. I appreciate the benefits of 3rd normal form, etc.
The
reason
I'm trying to get the data into this particular structure is that
ultimately
it is going to land in an excel spreadsheet in that format. If I
grab
the
data using the ANSWER SET format, it returns 600k rows and even
if I
wanted
to drop that into Excel I would have to put really scary logic
trying
to
traverse all those rows, which even if I could do that would slow
the
Excel
Calc function down to the point of making it non-operational.
It's
ugly I
know, but under the circumstances it really does need to be in
this
format.
If in this columnar fashion I think I can be down to a few
thousand
rows.

I tried to simply write a SQL statement that just brought it back
in
the
format I need, but the source DB is so normalized it takes many,
many
lines
of SQL just to get ANSWER SET. Even w DBA help I don't think we
could
figure
out how to force ANSWER SET to stretch out into columns for each
month.

Thanks. I'll check out your article. - Russ

:

Having repeating fields like that (jan, feb, mar, ....) is
seldom a
good
idea in a relational database. Instead of 12 fields, you should
have
12
rows, one for each month.

For information about writing queries that do what you're trying
to
do
(update existing rows or insert rows that don't exist), check my
November,
2003 "Access Answers" column in Pinnacle Publication's "Smart
Access".
You
can download the column (and sample database) for free at
http://www.accessmvp.com/djsteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi there,

Seems like no matter how much I try to avoid it, I'm just
going
to
have
to
learn to program in VBA...

I have created a summary table in the form of:
name, project, jan, feb, mar...

Each month column will contain the number of hours a person
has
worked
for
a
particular project. I have a query that grabs data from the
source
DB
and
returns it in this fashion:

ANSWER SET
name, project, sumofhours

I can run that for a particular month. What I want to do is
create
some
VBA
code whereby I crank off a SQL statement returning a
particular
month'
ANSWER
SET. I then inspect each row returned and perform the
following:

Is there a row already existing in my summary table for this
project/resource?
If YES, then UPDATE the row in the summary table w data from
ANSWER
SET.
If NO, then INSERT the row in the summary table w datat from
ANSWER
SET.

How do I inspect each row in the ANSWER SET so that I can act
upon
it?

Thanks very much.

Russ
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Those are the names of the months sorted alphabetically.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


xrbbaker said:
Doug,

Your last suggestion works like a charm. Just for my edification, I don't
understand your point where you say it is returning them in date order.
How
is April, Feb, Jan - A,F,J sorted?

thanks

Douglas J. Steele said:
Actually, it is returning it in date order. You told it to use the text
representation of the month: April, February, January, March etc. is how
they sort alphabetically.

Try:

PIVOT Format$(ADMUSER_RSRCHOUR.WORK_DATE,'mmmm') IN ('January',
'February',
'March', 'April', ..., 'December')

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


xrbbaker said:
This sure has been lots of progress for one day. Thanks again for all
the
help. Tomorrow I will investigate your Append query...

One more snag seems to be this. My Transformed answer set doesn't
return
the data in date order. Here is acutal results...

NAME PROJ_SHORT_NAME April February January March May
ANIL C00013015 8 9

The last part of the SQL statement for the above is this:

GROUP BY ADMUSER_RSRC.RSRC_NAME, ADMUSER_PROJECT.PROJ_SHORT_NAME
ORDER BY ADMUSER_RSRC.RSRC_NAME, ADMUSER_PROJECT.PROJ_SHORT_NAME
PIVOT Format$(ADMUSER_RSRCHOUR.WORK_DATE,'mmmm');

I tried changing the sql to (below), but that didn't change anything.
How
can I get it to come out in January, February, March order? -thx

GROUP BY ADMUSER_RSRC.RSRC_NAME, ADMUSER_PROJECT.PROJ_SHORT_NAME,
Format$(ADMUSER_RSRCHOUR.WORK_DATE,'mmmm')
ORDER BY ADMUSER_RSRC.RSRC_NAME, ADMUSER_PROJECT.PROJ_SHORT_NAME,
Format$(ADMUSER_RSRCHOUR.WORK_DATE,'mmmm')
PIVOT Format$(ADMUSER_RSRCHOUR.WORK_DATE,'mmmm');








:

1) Yes, I believe you can only run TRANSFORM statements from within
Access,
not from outside.

2) Save your crosstab query. Create an Append query based on that
saved
crosstab query, not on the table (and the Transform statement).

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Uh oh. It's trying to rain on my parade.

1) It looks like I can't run the TRANSFORM statement from w/in
Excel.
I
think no matter, I'll just use my Access intermediate table to house
the
answer set and then select from that from Excel. Problem is
2) When I try to run the insert into it looks to me like the next
thing
it
wants to see after the insert line is a select statement, not a
transform
statement. Is my short cut short circuited? (SYNTAX ERROR ON
INSERT
INTO)

INSERT INTO RUSS_ACTUAL_HOUR_SUMMARY
TRANSFORM Sum(ADMUSER_RSRCHOUR.HR_CNT) AS SumOfHR_CNT

:

Just go into Access, create a New query, and select Crosstab Query.
It'll
walk you through the rest.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


No. I'm not familiar with it. I've never heard of it. I used
to
be a
DBA
12 years ago, but I'm not up on the latest greatest. What I'm
doing
now
isn't really my "job" per se. I'm just trying to get something
done.
I
went
to my assigned DBA and dropped this query on her, and she punted
on
the
idea
of turning it into columns. This is what brings back my ANSWER
SET
for
1
month.

I'll google crosstab query. Thanks for all your help!

SELECT ADMUSER_RSRC.RSRC_NAME, ADMUSER_PROJECT.PROJ_SHORT_NAME,
Format$(ADMUSER_RSRCHOUR.WORK_DATE, 'mmmm') AS [MONTH],
Sum(ADMUSER_RSRCHOUR.HR_CNT) AS SumOfHR_CNT
FROM ((((ADMUSER_PROJWBS INNER JOIN (ADMUSER_RCATVAL INNER JOIN
(((ADMUSER_PROJECT INNER JOIN ADMUSER_TASK ON
ADMUSER_PROJECT.PROJ_ID =
ADMUSER_TASK.PROJ_ID) INNER JOIN ((ADMUSER_RSRCHOUR INNER JOIN
ADMUSER_RSRC
ON ADMUSER_RSRCHOUR.RSRC_ID = ADMUSER_RSRC.RSRC_ID) INNER JOIN
ADMUSER_TASKRSRC ON ADMUSER_RSRCHOUR.TASKRSRC_ID =
ADMUSER_TASKRSRC.TASKRSRC_ID) ON ADMUSER_TASK.TASK_ID =
ADMUSER_TASKRSRC.TASK_ID) INNER JOIN ADMUSER_RSRCRCAT ON
ADMUSER_RSRC.RSRC_ID
= ADMUSER_RSRCRCAT.RSRC_ID) ON ADMUSER_RCATVAL.RSRC_CATG_ID =
ADMUSER_RSRCRCAT.RSRC_CATG_ID) ON ADMUSER_PROJWBS.PROJ_ID =
ADMUSER_PROJECT.PROJ_ID) INNER JOIN ADMUSER_PROJPCAT ON
ADMUSER_PROJECT.PROJ_ID = ADMUSER_PROJPCAT.PROJ_ID) INNER JOIN
RMISEXTN_RMIS_PROJ_INFO ON ADMUSER_PROJECT.PROJ_ID =
RMISEXTN_RMIS_PROJ_INFO.PROJ_ID) INNER JOIN
RMISEXTN_RMIS_PROJ_PORTFOLIO
ON
RMISEXTN_RMIS_PROJ_INFO.RMIS_PROJ_PORTFOLIO_ID =
RMISEXTN_RMIS_PROJ_PORTFOLIO.RMIS_PROJ_PORTFOLIO_ID) INNER JOIN
ADMUSER_PCATVAL ON ADMUSER_PROJPCAT.PROJ_CATG_ID =
ADMUSER_PCATVAL.PROJ_CATG_ID
WHERE (((ADMUSER_RSRCHOUR.WORK_DATE)>=#4/1/2007#) AND
((ADMUSER_RSRCHOUR.WORK_DATE)<#5/1/2007#) and
((ADMUSER_PROJPCAT.PROJ_CATG_TYPE_ID)=26) AND
((ADMUSER_RSRCRCAT.RSRC_CATG_TYPE_ID)=10) AND
((ADMUSER_PROJWBS.PROJ_NODE_FLAG)="Y"))
GROUP BY ADMUSER_RSRC.RSRC_NAME, ADMUSER_PROJECT.PROJ_SHORT_NAME,
Format$(ADMUSER_RSRCHOUR.WORK_DATE,'mmmm'),
Year(ADMUSER_RSRCHOUR.WORK_DATE)*12+DatePart('m',ADMUSER_RSRCHOUR.WORK_DATE)-1
ORDER BY ADMUSER_RSRC.RSRC_NAME;




:

Have you tried using a crosstab query?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks Doug. I appreciate the benefits of 3rd normal form,
etc.
The
reason
I'm trying to get the data into this particular structure is
that
ultimately
it is going to land in an excel spreadsheet in that format.
If I
grab
the
data using the ANSWER SET format, it returns 600k rows and
even
if I
wanted
to drop that into Excel I would have to put really scary logic
trying
to
traverse all those rows, which even if I could do that would
slow
the
Excel
Calc function down to the point of making it non-operational.
It's
ugly I
know, but under the circumstances it really does need to be in
this
format.
If in this columnar fashion I think I can be down to a few
thousand
rows.

I tried to simply write a SQL statement that just brought it
back
in
the
format I need, but the source DB is so normalized it takes
many,
many
lines
of SQL just to get ANSWER SET. Even w DBA help I don't think
we
could
figure
out how to force ANSWER SET to stretch out into columns for
each
month.

Thanks. I'll check out your article. - Russ

:

Having repeating fields like that (jan, feb, mar, ....) is
seldom a
good
idea in a relational database. Instead of 12 fields, you
should
have
12
rows, one for each month.

For information about writing queries that do what you're
trying
to
do
(update existing rows or insert rows that don't exist), check
my
November,
2003 "Access Answers" column in Pinnacle Publication's "Smart
Access".
You
can download the column (and sample database) for free at
http://www.accessmvp.com/djsteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
Hi there,

Seems like no matter how much I try to avoid it, I'm just
going
to
have
to
learn to program in VBA...

I have created a summary table in the form of:
name, project, jan, feb, mar...

Each month column will contain the number of hours a person
has
worked
for
a
particular project. I have a query that grabs data from
the
source
DB
and
returns it in this fashion:

ANSWER SET
name, project, sumofhours

I can run that for a particular month. What I want to do
is
create
some
VBA
code whereby I crank off a SQL statement returning a
particular
month'
ANSWER
SET. I then inspect each row returned and perform the
following:

Is there a row already existing in my summary table for
this
project/resource?
If YES, then UPDATE the row in the summary table w data
from
ANSWER
SET.
If NO, then INSERT the row in the summary table w datat
from
ANSWER
SET.

How do I inspect each row in the ANSWER SET so that I can
act
upon
it?

Thanks very much.

Russ
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Oh duh. Just shoot me.



Douglas J. Steele said:
Those are the names of the months sorted alphabetically.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


xrbbaker said:
Doug,

Your last suggestion works like a charm. Just for my edification, I don't
understand your point where you say it is returning them in date order.
How
is April, Feb, Jan - A,F,J sorted?

thanks

Douglas J. Steele said:
Actually, it is returning it in date order. You told it to use the text
representation of the month: April, February, January, March etc. is how
they sort alphabetically.

Try:

PIVOT Format$(ADMUSER_RSRCHOUR.WORK_DATE,'mmmm') IN ('January',
'February',
'March', 'April', ..., 'December')

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


This sure has been lots of progress for one day. Thanks again for all
the
help. Tomorrow I will investigate your Append query...

One more snag seems to be this. My Transformed answer set doesn't
return
the data in date order. Here is acutal results...

NAME PROJ_SHORT_NAME April February January March May
ANIL C00013015 8 9

The last part of the SQL statement for the above is this:

GROUP BY ADMUSER_RSRC.RSRC_NAME, ADMUSER_PROJECT.PROJ_SHORT_NAME
ORDER BY ADMUSER_RSRC.RSRC_NAME, ADMUSER_PROJECT.PROJ_SHORT_NAME
PIVOT Format$(ADMUSER_RSRCHOUR.WORK_DATE,'mmmm');

I tried changing the sql to (below), but that didn't change anything.
How
can I get it to come out in January, February, March order? -thx

GROUP BY ADMUSER_RSRC.RSRC_NAME, ADMUSER_PROJECT.PROJ_SHORT_NAME,
Format$(ADMUSER_RSRCHOUR.WORK_DATE,'mmmm')
ORDER BY ADMUSER_RSRC.RSRC_NAME, ADMUSER_PROJECT.PROJ_SHORT_NAME,
Format$(ADMUSER_RSRCHOUR.WORK_DATE,'mmmm')
PIVOT Format$(ADMUSER_RSRCHOUR.WORK_DATE,'mmmm');








:

1) Yes, I believe you can only run TRANSFORM statements from within
Access,
not from outside.

2) Save your crosstab query. Create an Append query based on that
saved
crosstab query, not on the table (and the Transform statement).

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Uh oh. It's trying to rain on my parade.

1) It looks like I can't run the TRANSFORM statement from w/in
Excel.
I
think no matter, I'll just use my Access intermediate table to house
the
answer set and then select from that from Excel. Problem is
2) When I try to run the insert into it looks to me like the next
thing
it
wants to see after the insert line is a select statement, not a
transform
statement. Is my short cut short circuited? (SYNTAX ERROR ON
INSERT
INTO)

INSERT INTO RUSS_ACTUAL_HOUR_SUMMARY
TRANSFORM Sum(ADMUSER_RSRCHOUR.HR_CNT) AS SumOfHR_CNT

:

Just go into Access, create a New query, and select Crosstab Query.
It'll
walk you through the rest.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


No. I'm not familiar with it. I've never heard of it. I used
to
be a
DBA
12 years ago, but I'm not up on the latest greatest. What I'm
doing
now
isn't really my "job" per se. I'm just trying to get something
done.
I
went
to my assigned DBA and dropped this query on her, and she punted
on
the
idea
of turning it into columns. This is what brings back my ANSWER
SET
for
1
month.

I'll google crosstab query. Thanks for all your help!

SELECT ADMUSER_RSRC.RSRC_NAME, ADMUSER_PROJECT.PROJ_SHORT_NAME,
Format$(ADMUSER_RSRCHOUR.WORK_DATE, 'mmmm') AS [MONTH],
Sum(ADMUSER_RSRCHOUR.HR_CNT) AS SumOfHR_CNT
FROM ((((ADMUSER_PROJWBS INNER JOIN (ADMUSER_RCATVAL INNER JOIN
(((ADMUSER_PROJECT INNER JOIN ADMUSER_TASK ON
ADMUSER_PROJECT.PROJ_ID =
ADMUSER_TASK.PROJ_ID) INNER JOIN ((ADMUSER_RSRCHOUR INNER JOIN
ADMUSER_RSRC
ON ADMUSER_RSRCHOUR.RSRC_ID = ADMUSER_RSRC.RSRC_ID) INNER JOIN
ADMUSER_TASKRSRC ON ADMUSER_RSRCHOUR.TASKRSRC_ID =
ADMUSER_TASKRSRC.TASKRSRC_ID) ON ADMUSER_TASK.TASK_ID =
ADMUSER_TASKRSRC.TASK_ID) INNER JOIN ADMUSER_RSRCRCAT ON
ADMUSER_RSRC.RSRC_ID
= ADMUSER_RSRCRCAT.RSRC_ID) ON ADMUSER_RCATVAL.RSRC_CATG_ID =
ADMUSER_RSRCRCAT.RSRC_CATG_ID) ON ADMUSER_PROJWBS.PROJ_ID =
ADMUSER_PROJECT.PROJ_ID) INNER JOIN ADMUSER_PROJPCAT ON
ADMUSER_PROJECT.PROJ_ID = ADMUSER_PROJPCAT.PROJ_ID) INNER JOIN
RMISEXTN_RMIS_PROJ_INFO ON ADMUSER_PROJECT.PROJ_ID =
RMISEXTN_RMIS_PROJ_INFO.PROJ_ID) INNER JOIN
RMISEXTN_RMIS_PROJ_PORTFOLIO
ON
RMISEXTN_RMIS_PROJ_INFO.RMIS_PROJ_PORTFOLIO_ID =
RMISEXTN_RMIS_PROJ_PORTFOLIO.RMIS_PROJ_PORTFOLIO_ID) INNER JOIN
ADMUSER_PCATVAL ON ADMUSER_PROJPCAT.PROJ_CATG_ID =
ADMUSER_PCATVAL.PROJ_CATG_ID
WHERE (((ADMUSER_RSRCHOUR.WORK_DATE)>=#4/1/2007#) AND
((ADMUSER_RSRCHOUR.WORK_DATE)<#5/1/2007#) and
((ADMUSER_PROJPCAT.PROJ_CATG_TYPE_ID)=26) AND
((ADMUSER_RSRCRCAT.RSRC_CATG_TYPE_ID)=10) AND
((ADMUSER_PROJWBS.PROJ_NODE_FLAG)="Y"))
GROUP BY ADMUSER_RSRC.RSRC_NAME, ADMUSER_PROJECT.PROJ_SHORT_NAME,
Format$(ADMUSER_RSRCHOUR.WORK_DATE,'mmmm'),
Year(ADMUSER_RSRCHOUR.WORK_DATE)*12+DatePart('m',ADMUSER_RSRCHOUR.WORK_DATE)-1
ORDER BY ADMUSER_RSRC.RSRC_NAME;




:

Have you tried using a crosstab query?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks Doug. I appreciate the benefits of 3rd normal form,
etc.
The
reason
I'm trying to get the data into this particular structure is
that
ultimately
it is going to land in an excel spreadsheet in that format.
If I
grab
the
data using the ANSWER SET format, it returns 600k rows and
even
if I
wanted
to drop that into Excel I would have to put really scary logic
trying
to
traverse all those rows, which even if I could do that would
slow
the
Excel
Calc function down to the point of making it non-operational.
It's
ugly I
know, but under the circumstances it really does need to be in
this
format.
If in this columnar fashion I think I can be down to a few
thousand
rows.

I tried to simply write a SQL statement that just brought it
back
in
the
format I need, but the source DB is so normalized it takes
many,
many
lines
of SQL just to get ANSWER SET. Even w DBA help I don't think
we
could
figure
out how to force ANSWER SET to stretch out into columns for
each
month.

Thanks. I'll check out your article. - Russ

:

Having repeating fields like that (jan, feb, mar, ....) is
seldom a
good
idea in a relational database. Instead of 12 fields, you
should
have
12
rows, one for each month.

For information about writing queries that do what you're
trying
to
do
(update existing rows or insert rows that don't exist), check
my
November,
2003 "Access Answers" column in Pinnacle Publication's "Smart
Access".
You
can download the column (and sample database) for free at
http://www.accessmvp.com/djsteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
Hi there,

Seems like no matter how much I try to avoid it, I'm just
going
to
have
to
learn to program in VBA...

I have created a summary table in the form of:
name, project, jan, feb, mar...

Each month column will contain the number of hours a person
has
worked
for
a
particular project. I have a query that grabs data from
the
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel lookup across entire workbook 1
VBA Excel - Select a value from one cell in Excel to update a SQL table 1
Excel VBA For excel 0
Sumif / Count across multiple tables 0
Updating related tables (parent / child) records using VBA 2
Macro for Revision Summary (Tracked changes) 0
how do I add up a column from one query and put it in another table? 0
vba macro that allows insertion of symbols at cursor 0

Top