update table from query results Results


Page 1 of 26.
Results 1...20 of 504

Sponsored Links:



Alright, i'm almost sure this isn't the best way of doing this,

But i wanna build a scoring system out of three criteria. cost, delivery, and qaulity. I have those tables built along with a contact table where there over all score will be tallied up. Each contact can have more than one entry in the criteria.

So what I did was i built a query to make a new table for each contact to generate the score and the contact id, then using that ID, i update it to the contact table using the UPDATE function...however wheni have 3 criteria, and 400 contacts..this obviously becomes painfully slow.

The question is, is there a way to directly pull the sql query results (summing the total of each contacts score for each criteria), then storing it into the contact table without making a new table in the process?




Hi,

I have a table Branches and I want to update it with the result of a query.

Is it possible? Here is what I've done:

Private Sub GetData_Click()
Dim SQL As String
SQL = "UPDATE Branches " & _
"SET Branches.[A Outlets] = d.[SumOfA]" & _
"Where (((Branches.[BranchId]) = " & Me.Text86 & "));"
DoCmd.RunSQL SQL
End Sub

d is the name of the query

The query is this:
SELECT Sum(CityMarket.[A Outlets]) AS SumOfA
FROM CityMarket
WHERE (((CityMarket.BranchId)=[Forms]![RegBranch]![RegBranch1].[Form].[BranchId]));




Hello everyone!

I'm trying to send results from my from to a table.
Results on the from are from query and some are typed in textboxes. Those from query are in combobox and one is in listbox which is the result from the combobox choice. Here's the code I'm trying to get to work (I've found this on the forum):

Private Sub Command61_Click()

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblexpenses", dbopendynaset)
rst.MoveFirst
rst!eqpmntcode = cboEqpmnt
rst!downtime = downtime
rst!partcost = List0
rst!total = total
rst.Update
rst.Close

End Sub

When I run it, "invalid argument" error appears and lists this row: Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblexpenses", dbopendynaset) error appears. What's wrong?

Thank you!

Marko




I wonder if anyone can help me. This appears, at first glance, like it should be easy to accomplish, but I've been pulling my hair out!

I'm creating a database to handle my organisation's graduate recuitment scheme. As part of the scheme, each graduate is assigned a mentor.

I've set up a query (qryMentorSearch) which returns all mentors that are suitable for a given graduate. The query results are displayed on a form (frmMentorResults). The form displays one record at a time, with navigation buttons to browse through the records.

Now the tricky part. As each mentor can have more than one graduate assigned to him/her, and each graduate can have more that one mentor during their training, I've created a separate table called tblMentorAssignments which has the following fields

[Assignment ID] - Primary key for the assignment table
[Graduate ID] - foreign key from tblGraduates
[Mentor ID] - Foreign Key from tblMentors
[Date] - Date of assignment

I've placed a command button on a form that should update tblMentorAssignments with the graduate and the mentor currently displayed on frmMentorResults. The command button runs an update query with "[Forms]![frmMentorResults]![Mentor ID]" as the critera for updating the Mentor ID field on tblMentorAssignments. However, it seems to create an assignment for EACH of the mentors returned by qryMentorSearch. How do I select just the current record?

I'd be grateful for any help with this




i've got a query that calculates the total number of hours done by a team of workers. how do i get the result of that sum query to reflect in 'collective_performance' in my 'target' table?




Hi Guys,


I have a master table and a calls table.

I have a field in the master table that i would like to update with the results of the following query

SELECT calls.guid, master.Name AS customer, Count(calls.guid) AS NoOfCalls
FROM master LEFT JOIN calls ON master.GUID = calls.guid
GROUP BY calls.guid, master.Name;

I need to use an update statement

but how do i get TimesCustCalled field in the master table populated with the results of the query above using an update statement?

i have attached my sample database

Any help is greatly appreciated

Thanks Attached Files sample.mdb (156.0 KB, 8 views) Reply With Quote 02-23-2010, 01:51 PM #2 NassauBob Not THAT Green Windows Vista Access 2007 Join Date Feb 2010 Location Augusta, GA Posts 61 Well, is there ONE field from the query you would populate the ONE field in the master table with? Because I know you cannot put the results of any query into another table unless the fields you intend to populate are matched in number and type by the query results.

In other words, an update query needs to return the same number of attributes (columns) that you intend to update in the table.




Good afternoon all,

I have a query which picks up completed work requests from a work request table (which is linked to Sharepoint)

I would like to add some of the information from that query to a previously created work request table where work requests were entered manually (I want to now update it via the query)

But I cannot get it to update all the results, just the top line.
Here is my code:

CurrentDb.Execute "INSERT INTO tblWorkRequests(WorkRequestID,AnalystName,DateOfRe quest,Requestor,WorkRequestTitle,WorkRequestDescri ption,RequestCompletionDate)VALUES ('" & Me.txtID.Value & "', '" & Me.Assigned_To.Value & "', '" & Me.Created.Value & "', '" & Me.Work_Request_Creted_by.Value & "', '" & Me.Title.Value & "', '" & Me.Description.Value & "', '" & Me.Modified.Value & "')"

This works and doesnt bring back any error messages on a button click which is on a form showing the query results

But, It only adds the one record every time.
If I place my cursor onto the second record on the form, and press the button, it will then, and only then add the next record.

I have tried lots of loops but have myself gone round in circles getting no where. Can anyone help?




Hello

Please excuse me if this is in the wrong area. I am attempting to solve this problem with VBA so I figured this might be the place.

I work on an Access 2007 database that has some pretty complex queries. One of these uses multiple steps in order to ultimately produce query results in a data sheet view.

What one of the users does at this point is to filter on certain columns based on whatever further criteria he may have. Once he is complete with the filter set of rows he would like to edit one of the fields for multiple records.

Now we already have a process in place where the source records can be altered but not directly from the dataset I described. He would normally do a copy and paste of the pertinent fields from Access into Excel, make his changes and save the spreadsheet. He can then import those records into the Access database. We have a macro set up to do the import from the file automatically for him.

My question is can I programmatically have the filtered results copied and sent to a temp table? Once in a table I could have the user make his changes, save it and possibly run another macro that would update the source data. If I can simply do copy of filtered results to a table in Access I can handle the rest. It would also skip the step with Excel which is preferable.

I tried several approaches, but since this is a plain old query any copying of it seems to copy the query object(thus the unfiltered results), not the results. This also doesn't put the results in a place where they are editable.

I even attempted a sendkeys macro but I really don't want to go down that road. I really feel like this is something simple, I just do not know all the in's and out's of VBA syntax.

Thanks for any help you may be able to provide,

Heywood




Hey Everybody,

I wanted to see if I could get an auto-email created using the results of a couple queries. On a weekly basis I would like to send an updated email (automatically) pulling 4 or 5 fields from 4 separate queries.

I can do a Make-Table and 3 appends to get them all in 1 Table but I would rather have them separated in the email. I would like the body to look like this:

Projects Completed:
Project# Client Date Completed
12345 Acme Inc 1/1/2010

Projects Past Due:
Project# Client Date Due
54321 ABC Co 1/1/2010

Etc...

These are the fields I already have set up in my queries. I know I can transfer them to an excel doc and just attach that to the email, but I would rather have it printed in the subject of the email if possible. I have the auto email set up and working, just need to get the query results to transfer to the body of the email.

Thanks in advance!!!




Dear all ,

i am maintaining a Access 2007 database for a customer. I have tables, queries and some modules. i have to generate query results and give it to the customers every week. as input i am importing excel tables.

Now the problem is ,
i have generated a query result and exported as excel file, that will be handed over to around 5 people. everyone will change the data in a single particular column and all of them will send me the files back. Now i have to updated the changes made in the 5 copies of this excel file and import it into the database again. How should i do it in the best way? i am afraid to overwrite the changes made by each other and in the end just left with the changes made by only one of them..
Please help

Best Regards
Oraclesoft




I have a budget application with two tables:

tblTitles: TitleID (key long int)
PastYrBudgetAmt
CurrentYrAmt
NextYrAmt

tblDetail: DetLinkID (matches TitleID)
DetailID
DetailDescription
DetailPastYr
DetailCurrentYr
DetailNextYr

There is a one to many relationship between these two.

I have two forms visible on my screen. The Title form is a multi-line form that I use to select the title wanted. The tblTitle is bound to this form. The Detail form is also a multiline . I have written code so that when I select one record on the title form, all of the Details are shown for editing or adding new ones.

The problem I have is that many of the Titles do NOT have Details, but DO HAVE a single dollar value for the title category itself. I could create a "dummy" detail for each one, but that seems like a lot of extra work inputing. What I would like to do is input these Title values directly (and keep them in the tblTitles) AND, where there are DETAILS, sum the details and then place the sum in the Titles table.

I KNOW it is not good practice to put calculated values into a table, but there are a number of reasons I want to do so in this application.
My real question is how do you Sum one form (based on a key field) and place the result in a table for another form.

I've run into not updateable query messages. Help suggests a DSUM function, but I can't get it to work. Any help would be appreciated.




Hello there,

Just looking for some advise. I have a master database that is based in the office and stored on the server. However the nature of our work takes us out of the office to visit clients at remote sites. During these visits answers will be recorded to a number of questions, usually in MS Word on a laptop. These answers are currently held in a table on the master database so when they return to the office they have to sit down and input the results.

I was going to design a small database that contained a table identical to the one held in the master database in the office and a form so that they could enter the results to the questions directly.

What I was then hoping to do was to import the new entries to the master database table when they return to the office so they dont have to re-enter the results. So what would be the best way to go about this? Would I need to design an Append query to add this new data from the small database table to the current master database table? I dont want to import the data from MS Word into MS Access as this would involve the users having to do too much. I want them only to have to click on a button to copy across the data.

All advice will be much appreciated.




I know that this is probably a very easy question but as it has been two years since I took a class to learn access (and don't remember alot, but using book to help where I can) I really need some help. I'm preparing a project for my accounting information systems class and have created a table that all invoices created are placed. I've created a query to calculate the sum of the remaining balances on these invoices. I need to be able to update an accounts receivable table with the result found in the query to create an outstanding balance total. I've created each table with a CustomerIDNumber field in order to determine which customer made the invoice, made payment to the invoice, and what their accounts receivable status is. I've made relationships between all of these customerIdNumber fields in each table and customerName fields. Please help as this project is due in 2 weeks.




My problem is a query using QBE with the following SQL:


SELECT DEPT228.dept_id, POSCODE228.To, DEPT228.Deptname, GOV609.IT_GLOBAL, GOV609.F21
FROM POSCODE228 RIGHT JOIN (DEPT228 RIGHT JOIN GOV609 ON DEPT228.dept_id = GOV609.Deptcode) ON POSCODE228.Poscode = GOV609.Poscode
GROUP BY DEPT228.dept_id, POSCODE228.To, DEPT228.Deptname, GOV609.IT_GLOBAL, GOV609.F21
HAVING (((GOV609.IT_GLOBAL)=Yes))
ORDER BY DEPT228.dept_id, POSCODE228.To DESC;

Explanation: GOV609 is my main table, joined DEPT228 is a list of 700 departments in up to 600 city locations, IT Global is a boolean operator, joined Postcode TO is a salary range identifier and F21 is the tag I want to update.

The problem, for a newwy at SQL is the query uses 'Totals' to get one IT tagged person per city, which is what I want, BUT:
If I try to update query I lose the 'totals' or if I add the AUTONUMBER, then that totals every record.

Or if I Make table query I also lose the 'totals' option.

Which option or expression will hold the selected query result for the update?




I have a query form that allows user to search by two criteria (in which, user can select "And" or "Or" clause for the two criteria). I also allow user to filter the results by date range. On the form, I have two command button, one will prompt the results in a query table, and the other will prompt a report.

Problem 1
I manage to prompt report with a date range (i.e. two unbound text boxes for start date and end date). But, I dont' manage to disable the filter if the date range is null. Below is the code for this report command button. Should I use a toggle button to make a select case?

Private Sub cmdReport_Click()
Dim varItem As Variant
Dim strDocName As String
Dim str1MainCate As String
Dim str2MainCate As String
Dim str2MainCateCondition As String
Dim strDate As String
Dim strSQL As String
Dim strFilter As String
' Build criteria string for 1st ComboBox
For Each varItem In Me.fstMainCate.ItemsSelected
str1MainCate = str1MainCate & ",'" & Me.fstMainCate.ItemData(varItem) & "'"
Next varItem
If Len(str1MainCate) = 0 Then
str1MainCate = "Like '*'"
Else
str1MainCate = Right(str1MainCate, Len(str1MainCate) - 1)
str1MainCate = "IN(" & str1MainCate & ")"
End If
' Build criteria string for 2nd Combo Box
For Each varItem In Me.SecMainCate.ItemsSelected
str2MainCate = str2MainCate & ",'" & Me.SecMainCate.ItemData(varItem) & "'"
Next varItem
If Len(str2MainCate) = 0 Then
str2MainCate = "Like '*'"
Else
str2MainCate = Right(str2MainCate, Len(str2MainCate) - 1)
str2MainCate = "IN(" & str2MainCate & ")"
End If
' Get 1toggle button condition
If Me.optAnd2MainCate.Value = True Then
str2MainCateCondition = " AND "
Else
str2MainCateCondition = " OR "
End If
' Build SQL statement
strSQL = " SELECT NewsClips.IssueDate, NewsClips.Title_Eng, NewsClips.Titile_Chi, NewsClips.NewsSource, NewsClips.[1CategoryMain], NewsClips.[1Sub-Category], NewsClips.[2CategoryMain], NewsClips.[2Sub-Category], NewsClips.hyperlink, NewsClips.FirstTwoPara, NewsClips.Notes, NewsClips.attachment FROM NewsClips " & _
"WHERE NewsClips.[1CategoryMain] " & str1MainCate & _
str2MainCateCondition & "NewsClips.[2CategoryMain] " & str2MainCate & ";"
' Build criteria string for Date
If Not IsNull(Me![dateTo]) Then
strDate = strDate & " NewsClips.IssueDate Between #" + Format(Me![datefrom], "mm/dd/yyyy") + "# AND #" & Format(Me![dateTo], "mm/dd/yyyy") & "#"
'Format(Me.dateTo, "mm/dd/yy")
Else
strDate = strDate & " NewsClips.IssueDate >= #" + Format(Me![datefrom], "mm/dd/yyyy") + "#"
End If
' filter string
strFilter = strDate
' Open report
strDocName = "RptCateDateQry"
DoCmd.OpenReport strDocName, acViewDesign, , strFilter
With Reports(strDocName)
.RecordSource = strSQL
.Filter = strFilter
.FilterOn = True
End With
DoCmd.Save acReport, strDocName
DoCmd.OpenReport strDocName, acViewPreview
Exit_cmdReport_Click:
End Sub

Problem 2
I have no idea how to filter the query results by date range in the query table. Below is the code of the query table button, which do not offer the filter feature. Would really appreciate it if you can give me some advice.

Private Sub cmdOK_Click()
On Error GoTo cmdOK_Click_Err
Dim blnQueryExists As Boolean
Dim cat As New ADOX.Catalog
Dim cmd As New ADODB.Command
Dim qry As ADOX.View
Dim varItem As Variant
Dim strDate As String
Dim str1MainCate As String
Dim str2MainCate As String
Dim str1MainCateCondition As String
Dim str2MainCateCondition As String
Dim strSQL As String
' Check for the existence of the stored query
blnQueryExists = False
Set cat.ActiveConnection = CurrentProject.Connection
For Each qry In cat.Views
If qry.Name = "QryCateDateForm" Then
blnQueryExists = True
Exit For
End If
Next qry
' Create the query if it does not already exist
If blnQueryExists = False Then
cmd.CommandText = "SELECT NewsClips.IssueDate, NewsClips.Title_Eng, NewsClips.Titile_Chi, NewsClips.NewsSource, NewsClips.[1CategoryMain], NewsClips.[1Sub-Category], NewsClips.[2CategoryMain], NewsClips.[2Sub-Category], NewsClips.hyperlink, NewsClips.FirstTwoPara, NewsClips.Notes, NewsClips.attachment FROM NewsClips"
cat.Views.Append "QryCateDateForm", cmd
End If
Application.RefreshDatabaseWindow
' Turn off screen updating
DoCmd.Echo False
' Close the query if it is already open
If SysCmd(acSysCmdGetObjectState, acQuery, "QryCateDateForm") = acObjStateOpen Then
DoCmd.Close acQuery, "QryCateDateForm"
End If
' Build criteria string for Date
If Not IsNull(Me![dateTo]) Then
strDate = strDate & " NewsClips.IssueDate Between #" + Format(Me![datefrom], "mm/dd/yyyy") + "# AND #" & Format(Me![dateTo], "mm/dd/yyyy") & "#"
'Format(Me.textStartDate, "mm/dd/yy")
Else
strDate = strDate & " NewsClips.IssueDate >= #" + Format(Me![datefrom], "mm/dd/yyyy") + "#"
End If

' Build criteria string for 1MainCate
For Each varItem In Me.fstMainCate.ItemsSelected
str1MainCate = str1MainCate & ",'" & Me.fstMainCate.ItemData(varItem) & "'"
Next varItem
If Len(str1MainCate) = 0 Then
str1MainCate = "Like '*'"
Else
str1MainCate = Right(str1MainCate, Len(str1MainCate) - 1)
str1MainCate = "IN(" & str1MainCate & ")"
End If
' Build criteria string for 2MainCate
For Each varItem In Me.SecMainCate.ItemsSelected
str2MainCate = str2MainCate & ",'" & Me.SecMainCate.ItemData(varItem) & "'"
Next varItem
If Len(str2MainCate) = 0 Then
str2MainCate = "Like '*'"
Else
str2MainCate = Right(str2MainCate, Len(str2MainCate) - 1)
str2MainCate = "IN(" & str2MainCate & ")"
End If
' Get 1MainCate condition
If Me.optAnd1MainCate.Value = True Then
str1MainCateCondition = " AND "
Else
str1MainCateCondition = " OR "
End If
' Get 2MainCate condition
If Me.optAnd2MainCate.Value = True Then
str2MainCateCondition = " AND "
Else
str2MainCateCondition = " OR "
End If
' Build SQL statement
strSQL = " SELECT NewsClips.IssueDate, NewsClips.Title_Eng, NewsClips.Titile_Chi, NewsClips.NewsSource, NewsClips.[1CategoryMain], NewsClips.[1Sub-Category], NewsClips.[2CategoryMain], NewsClips.[2Sub-Category], NewsClips.hyperlink, NewsClips.FirstTwoPara, NewsClips.Notes, NewsClips.attachment FROM NewsClips " & _
"WHERE NewsClips.[1CategoryMain] " & str1MainCate & _
str2MainCateCondition & "NewsClips.[2CategoryMain] " & str2MainCate & _
str1MainCateCondition & strDate & ";"
' Apply the SQL statement to the stored query
cat.ActiveConnection = CurrentProject.Connection
Set cmd = cat.Views("QryCateDateForm").Command
cmd.CommandText = strSQL
Set cat.Views("QryCateDateForm").Command = cmd
Set cat = Nothing
' Open the Query
DoCmd.OpenQuery "QryCateDateForm"

' If required the dialog can be closed at this point
' DoCmd.Close acForm, Me.Name
' Restore screen updating
cmdOK_Click_Exit:
DoCmd.Echo True
Exit Sub
cmdOK_Click_Err:
MsgBox "An unexpected error has occurred." _
& vbCrLf & "Procedure: cmdOK_Click" _
& vbCrLf & "Error Number: " & Err.Number _
& vbCrLf & "Error Description:" & Err.Description _
, vbCritical, "Error"
Resume cmdOK_Click_Exit
End Sub


Sorry for posting this question again, as I thought it's better to make it a seperate posting, rather than a reply to my early post. Your advice will be greatly appreicated.




Hi,

I have Access 2002 on Windows XP.

The last version of Access I've used was 97 but I'm getting back into it. I've read a couple of things that recommend creating a form based on a query, not a table, especially if a calculated field is involved.

When I create a select query based on 1 table, I can change/add/delete records right in the results of the select query, which will carry over to the form just fine.

However, when I use an additional table and join them in my select query, I can no longer update any of the fields that show in the query result. The link I'm using is just a 1 to 1.

How can I get around this? I'm using the second table just for lookup purposes (use the value of one of the fields in a calculation), but I want to be able to update the fields from table 1 from the form.

Thanks.




I have 2 related tables. The form to enter new data into the tables is set up with a subform for the second table. I want to allow the user to query for a specific record, have it display in a form that looks exactly like the data entry form, and be able to update the tables from that. I first tried an exact copy of the data entry form, using a subform for the second table. It allows me to update the 2nd table from the query results displayed, but won't allow updates to the main form. So then I tried just doing one form with all the results displayed, no subform, but then it won't allow me to update ANYTHING.

What am I doing wrong? Any help is appreciated.

Thanks
Stacey




Hi,

I have created a form with drop downs to set the parameters for a specific query. I have also created a command button to run the query when the desired parameters have been chosen. The query then pops up when the run button is pressed.

Is it possible to add a button (attached to the query results) to open up the relevant record. I am trying to work it so that the user can update the relevant records from the query results. I guess to do this, I will need to have the results pop up in a sub form, rather than just popping up on the screen as is currently the case.

I am pretty new to this so excuse me if this is a stupid question.

Thanks in advance

K




Hi,

In my database, there is a table ("Price") which is updated from another table (End-user price). I try to make a query from upated "Price" table, but the result of the queriy is still data from the original/un-updated "Price" table.

I tried to make "make a table query" from the updated PRICE table, too, but still the result still shows the original/un-updated table. I use Access 97.

Could you please help to solve this issue?

Thank you,
Tim




I have a table for clients (tblClients). Clients details are input into the table from a form (frmClients). Clients notes are to be filled in from the same form (frmClients) via a memo field (txtNotes) and stored into a different table (tblNotes).
I have a primary key field in tblClients (ClientsID) as well as using that as a foreign key in tblNotes.

I have created a query (tblNotesForClientsQuery) which displays results properly on its own. But I can't connect it to txtNotes on frmClients .

How do I get the proper notes to display for each client in frmClients?

And when a new note is input into txtNotes on frmClients, to update the table tblNotes with the correct note for each client?
Thanks in advance.


Page 1 of 26.
Results 1...20 of 504