Replacing query

hi. i are replacing queries in my tables to get the database ready to convert to SQLServer.
Am having a problem replacing a query that is brings in a table for a drop down and inserting the selection in the record.
during creation, the record shows a date, id, task, hours.
the datatable needs date, id, task_code, task_name, task_job, hours.
On "task" the drop-down allows for selection of several choices that are 3 columns wide. i can get the columns to show but only the first column currently goes in my new table. the table's columns that the selection comes from is "task_code,task_name, task_job". columns are needed as different areas use the same task code but use different jobs or names.

I was told to use a insert into statement, however am having problems with it.

have as follows:
SQLStatement = "SELECT [Task_code], [Job_name], [Task_name] FROM tablea; "
rec.Open SQLStatement, , adOpenDynamic

job = rec(1)
task = rec(2)


SQLStatement = " Insert into [tableb]([Job_name],[Task_name]) " & _
" values (" & job & ", " & task & " ) "

rec2.Open SQLStatement, , adOpenDynamic

db.Execute (SQLStatement)

any point in the right direction would be greatly appreciated.

Posted On: Monday 22nd of October 2012 05:18:48 AM Total Views:  634
View Complete with Replies

Related Messages:

Need help replacing data in tables   (271 Views)
quite a few years ago I wrote a database and access, because I have not worked with access in such a long time I have hired someone to do the programming for me. I would ask these questions of the person doing the programming but they always take quite a while to get back to me so I thought I would try this for instead. My problem or question is that every time the database is upgraded I have to transfer the data from the old versions tables to the new versions tables, this always gives me difficulty. my main difficulty is with the linked tables. I have one table that keeps the order number and the customer's name and this table is linked to the order details table which carries all the data related to that specific order. can anyone explain or point me in the right direction to figure out how to update these tables. I may not be wording this post correctly but I hope you can get the jest of what I'm trying to achieve.
User is forced to first record upon requery after deleting a record   (214 Views)
I have the following code in a form which will delete a record from a table; however, upon the requery it takes the user to the first record of the form. How can I capture and move the user to the next record if one exists (or last record if one doesn't) instead of making them move to the first record Code: DoCmd.RunSQL "DELETE FROM [mytable] WHERE [recordID] = " & Me.recordID Me.Requery
Export query to excel   (263 Views)
I'm using Access 2000. I've done some searching around and found similar posts online but I'm having a tough time getting my head round this one... I have a query called "QryMonthlySales" with about 10 fields and 400 rows of data. I want to export the query to excel, saving the excel file to a folder location chosen by the user via a form. Before exporting the query to excel, I want to give the user the option to specify the time period. I have a form which allows the user to select a start and end date. Before exporting the query, I will need to select the data via a where clause, something like Period >= txtStartDate and Period
Want query with running count based on group   (230 Views)
Here's my scenario. A table with applications, a table with developers. One-To-Many relationship. One application could have 1 or 20 developers (no limit, twenty is the current max). Client requires a report with all developers in their own column. My first thought was to use Access's cross-tab, but it creates too many columns and errors out. Next thought was to create a query that listed the application, developer, and a count. Then to create a query with a case statement and 30 columns across (since 20 is the current max after 6 years of running think I'm "fairly safe" with increasing it by 50%). so something like this: App Developer Position 1 bob 1 1 Sue 2 1 Rick 3 2 Rick 1 3 Frank 1 3 Maria 2 ... and so on Then use a bunch of case statements based on the position. I can't find a way to do the running count based on app. So I'm looking for suggestions on how to do this or a better way to write this miserable query.
Showing query results on a report more than once   (224 Views)
I want to make a report that takes information from a query. It will display this information, then in the middle it will be a check that some of the information is printed on, and then on the last part of the report it will display all the information it did at the top. I can get the top part to work and the check, but I can't get the bottom part to work, it will only display the last record. If anyone had any advice on how to do this I would apprecate it: Example: Query Result 1 Query Result 2 Query Result 3 ------- Infomation ------- Query Result 1 Query Result 2 Query Result 3 (I can't get the bottom one to work)
How do i create a subform which displays query results?   (356 Views)
hi im currently trying to complete an access project involving a music player creation. i've created a form to search through the database for specific songs (e.g by title, artiste etc.). how do i create a subform in the form so that users can type in their search in the form and see the results in the subform just below
Cannot enter data on form created from query   (286 Views)
I created a query with 2 tables. Then I used the menu to automatically create a form for the query data. It will not allow me to change any data on the form. Of course, I can change the data directly in the query results, but I need a form for another user.
Filtering recs from query ...   (220 Views)
Form layout is: Form Header -- Has detail information, i.e., Name & RoomNumber Detail Section -- 1 Row is defined. Has 3 fields form a query: RoomNumber, Asset#,Desc. plus a command button to mark it as inventoried. The three fields are defined as text with the CONTROL SOURCE pointing to the fields of the query. Section is setup to GROW. Form Footer -- Command button to exit. When I display the form, everyting comes up great. I get all 3 detail records showing in the Detail Section. What I am trying to do is apply a filter so that I only see the records for the room number displayed in the Form Header. I have applied a filter (and turned it on) during FORM LOAD, but it does not seem to work. Since it is giving me all the detail records instead of none, it appears that the filter is not being accepted at all. Or is it in the wrong place Since I need to have a command button displayed on each line, I think I need to stay with the text box process. I have tested successfully using a LIST BOX, but no ability to use the much needed command button. Any suggestions on how to properly apply the filter Thank You. Bob
Distinct records from multiple columns in SQL query   (260 Views)
I am using ASP with MS Access Database and have the created the following query for my recordset Code: SELECT galleryID, picCat, galleryName, galleryDate, picID, picName FROM tblGallery INNER JOIN tblPics ON tblPics.picCat = tblGallery.galleryID I now wish to display the recordset in a repeating table but want to remove any records returned with the same Code: galleryID I have been searching for two days and although I have found a lot of similar issues which suggest using DISTNCT, GROUP BY etc... I have not yet been able to find a solution that works. I am now running out of time for my deadline so any feedback/help would be highly appreciated Cheers Kaden712
Non-updatable query issue   (425 Views)
I've got a small DB that's been developed for materials management duties at an electronics manufacturing business. We're a contract manufacturer, who builds circuit boards of our customer's designs, using their Bill of Materials. My problem is, I need to pull a new piece of data into a form, and that requires aggregation... and I'll explain why below. (the form is built on an updatable query over a star-schema design) That aggregation (I think) is causing the entire query to become non updatable - I need a solution! About the query: It's built on a star schema, the dimension tables are used to populate drop-down selections in the form, and the only table being updated is the central fact/matrix table. The sourcing of the parts for these electronic assemblies throws an interesting twist- Essentially I have three potential part numbers for any given part that goes into these assemblies: BOM PN - the part number specified by the customer. Used PN - sometimes, purchasing has to buy an alternative part that has the same characteristics. Customer PN - Customers sometimes assign their own part number for reference, particularly when they don't require a specific vendor's part. So, my Parts table (a dimension table) contains data on parts tht we've actually purchased: Mfg PN - which ties to Used PN - parts we've actually bought, and are therefore in our inventory system. Manufacturer - The manufacturer of that part number. Description - straight off the customer's BOM. The Form: Right now it's nearly as simple as my Parts table - it's got controls for MfgPN, Manufacturer, and Quantity. To pull in the Customer PN for reference, I have to pull it from the Assembly Details table, and that means I need to basically do a "select distinct", since the corresponding Mfg PN's are in there many times. How can I include the Customer PN, so that it corresponds accurately with the existing data on the inventory management form - without making that query non-updatable
Howto Stop query from asking for value?   (239 Views)
I have a query Ive made that does some math with current values. Fields: Month, Delay Min Before, Delay Min After, Delay Min Saved, Percent: Round((([SumOfDelay Min Before]-[SumOfDelay Min After])/[SumOfDelay Min Before])*100,1) The last one is to get a percent saved from Before and After. My issus is when I run this query it will ask for "SumofDelay Min Before" and again for "SumOfDelay Min After". If I just click thru both and the query will output the correct numbers for me. From this query I made a sub from. The trouble is when I open the form for use it asks me every time for both values when the refresh button is hit. Im thinking Ill have to make a 2nd query with the last field to get this to work correctly. Sound right Edit: more info The query has "totals" clicked. Month is Group by, Before/After/Saved are all SUM, Percent is Group by.
Unequal query relationship   (240 Views)
Hi i need some help pls! I have a local table (Table A) with some data in and i have a link to a table (Table B) which is populated from sql/odbc. I set the join to include all records from A and only those from B that are equal, when I try to run this i get an error stating: "that it could not be executed as it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that in your sql statement" I have no idea how to do this, can you help pls
Select query access 2000   (345 Views)
hello there I have a problem. I have a really simple table here: table1 with two fields A and B. The content of the table is: a1, b11 a1, b12 a1, b13 a2, b21 a2, b22 a2, b23 A is primary key. My question is: I need to build a query which will get these rows a1, b11/nb12b13/n a2, b21/nb22/nb23 from the content of the table1 that I mentioned. Would you please suggest some way to build a select which would get these two rows I could think of a query which would look like something like this: select A, T1.B,"/n",T2.B from table1 as T1, table1 as T2 where T1.A = T2.A but this not only does not solve my problem but it is my closest approach to a possible solution.
How to query for query text?   (264 Views)
Hi all. This is probably a pretty easy one, I've just never done it so I'm not sure how to get at the information. I need to get my hands on some code which will query for the text of all my queries. I basically need to search through all my queries (this would include ones behind forms, but if I can just get at the text in the standalone queries, it would be a start) in order to search for a few tables/queries being used in those queries, so I can alter them to a new approach. What I want to do is spit all the query strings out into a text file or something and search through to find out which ones are utilizing the queries/tables in question, so I can start working on changing them. Is there a way to do this with the querydefs collection or with the msysqueries object
Requery Question   (274 Views)
I have a list box on a main form. When the list box is updated it should requery within per the after update procedure two subforms [sfrmAgency] and [sfrmEmployment]. The after update procedure is returning an error stating that it "can't find the field 'sfrmAgency'...", however [sfrmEmployment] works okay. The same thing happens on my OnCurrent procedure for the main form. The after update procedure includes: [Forms]![frmPerson]![sfrmEmployment].Requery [Forms]![frmPerson]![sfrmAgency].Requery
can anyone modify this query statement   (377 Views)
hi im a first time user of access, can anyone modify this query statement... select IDno, LName + ', ' + FName as Name, course, Status, (select count(io.AC) from in_out_books io where io.IDno = b.IDno and io.DBorrowed >= '2008/03/01' and io.DBorrowed = '2008/03/01' and io.DBorrowed
Displaying query results in a form   (296 Views)
Hi , I have a query which totals the amount of all invoices for a specific project number. I would like to take this total and have it displayed on the form from where overall project information is inputed. Right now I have a table which shows general project information and a table showing invoice infomation (linked to the general table via a project #) I also have two forms, one for general info and there's a button which opens the invoice form. When the invoice form opens, it opens an invoice with the corresponding project number shown on the general form. I would like the total invoice amount (from the query) displayed on the corresponding general form. ex: in the general form for project# 100, I click invoice once and enter $50, I click invoice again and enter $100. The query automatically shows a total of $150 for project# 100. I would like this $150 displayed on the general form for quick reference.
How do i pull subform data to a query?   (370 Views)
, I have a form with a subform. I have the subform running a update query on a command button in the subform. The subform has the textboxes and I cant seem to get the query to pull the data from those textboxes. I have used the normal [forms]![form_name]![textbox_name] in the past but everything I try to pull the subform textbox data is not working. I have tried [forms]![form_name]![subform_name]![textbox_name] [forms]![form_name]![subform_name].[form]![textbox_name] [forms]![form_name]![subform_name].[form].[textbox_name] Nothing works. What is the correct syntax for this
[Reports] Help with query-reports   (281 Views)
, I have made a custom form to open up when I start a query which then enters the information I enter in the form, into the query. The query then produces the results in a report. My problem is, I wanted it to display all the records when I leave a field blank. BUT when I enter a "Location/Contract", it brings up the records I want with that location/contract but it also brings up some records that don't have an entry there. Am I making sense If not, here is some screenshots: I enter "Bridge Cross Rd" And it brings up those without a location/contract... Any Ideas The code for my report is: Code: Option Compare Database Private shadeNextRow As Boolean Const shadedColor = 13356495 ' Const shadedColor = 15726583 ' alternative shade colors ' Const shadedColor = 14078404 ' Const shadedColor = 13356495 ' Const shadedColor = 14281974 Const normalColor = 16777215 Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) On Error GoTo Detail_Format_Error ' Choose a color based on the shadeNextRow value If shadeNextRow = True Then Me.Section(acDetail).BackColor = shadedColor Else Me.Section(acDetail).BackColor = normalColor End If ' Switch the color for the next row shadeNextRow = Not shadeNextRow Detail_Format_Exit: Exit Sub Detail_Format_Error: MsgBox "Error " & Err.Number & ": " & Err.Description Resume Detail_Format_Exit End Sub Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer) If PrintCount = 1 Then txtPageSum = txtPageSum + Cost End If End Sub Private Sub PageHeaderSection_Print(Cancel As Integer, _ PrintCount As Integer) txtPageSum = 0 End Sub Private Sub Report_Close() DoCmd.Close acForm, "Master" End Sub Private Sub Report_NoData(Cancel As Integer) MsgBox "There are no records to report", _ vbExclamation, _ "No Records" Cancel = True End Sub Private Sub Report_Open(Cancel As Integer) ' Set public variable to true to indicate that the report ' is in the Open event bInReportOpenEvent = True ' Open Sales By Category Dialog DoCmd.OpenForm "Master", , , , , acDialog ' Cancel Report if User Clicked the Cancel Button If IsLoaded("Master") = False Then Cancel = True ' Set public variable to false to indicate that the ' Open event is completed bInReportOpenEvent = False End Sub And my Form: Code: Private Sub cmdCancel_Click() DoCmd.Close End Sub Private Sub Form_Open(Cancel As Integer) If Not bInReportOpenEvent Then ' If we're not called from the report MsgBox "For use with the Reports only", _ vbOKOnly Cancel = True End If Form_Open_Exit: Exit Sub End Sub Private Sub OK_Click() Me.Visible = False End Sub And for some reason this is what my Query looks like:
Calculate Yes/No fields in a query   (623 Views)
In a query that utilizes a table containing a yes/no field, is it possible to calculate the yes/no field and get a numeric return If so, can you share how this could be accomplished.