 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / June 2007VBA insert or update
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 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  SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > Hi there, > [quoted text clipped - 28 lines] > > Russ 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 [quoted text clipped - 38 lines] > > > > Russ Have you tried using a crosstab query? SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > Thanks Doug. I appreciate the benefits of 3rd normal form, etc. The > reason [quoted text clipped - 70 lines] >> > >> > Russ 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? > [quoted text clipped - 72 lines] > >> > > >> > Russ Just go into Access, create a New query, and select Crosstab Query. It'll walk you through the rest. SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > No. I'm not familiar with it. I've never heard of it. I used to be a > DBA [quoted text clipped - 129 lines] >> >> > >> >> > Russ 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. > Just go into Access, create a New query, and select Crosstab Query. It'll > walk you through the rest. [quoted text clipped - 132 lines] > >> >> > > >> >> > Russ 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. [quoted text clipped - 132 lines] > >> >> > > >> >> > Russ 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). SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > Uh oh. It's trying to rain on my parade. > [quoted text clipped - 166 lines] >> >> >> > >> >> >> > Russ 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. [quoted text clipped - 172 lines] > >> >> >> > > >> >> >> > Russ 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') SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (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... [quoted text clipped - 218 lines] >> >> >> >> > >> >> >> >> > Russ 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 > 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 [quoted text clipped - 227 lines] > >> >> >> >> > > >> >> >> >> > Russ Those are the names of the months sorted alphabetically. SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) > Doug, > [quoted text clipped - 265 lines] >> >> >> >> >> > >> >> >> >> >> > Russ Oh duh. Just shoot me. > Those are the names of the months sorted alphabetically. > [quoted text clipped - 227 lines] > >> >> >> >> >> > particular project. I have a query that grabs data from > >> >> >> >> >> > the 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. > Have you tried using a crosstab query? > [quoted text clipped - 72 lines] > >> > > >> > Russ 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. SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > 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 [quoted text clipped - 100 lines] >> >> > >> >> > Russ 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 > Hi there, > [quoted text clipped - 24 lines] > > Russ 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") SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) >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 [quoted text clipped - 38 lines] >> >> Russ |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.