Get Results From Query Into Table
Aug 30, 2005This is a rael daft question, but how do I get the results of a query into a table?
View Replies
ADVERTISEMENT
How Put Results Of The Query Into The Table
Oct 13, 2005Hi!
I have query with one column results
How to put automatically results of this query into one column table
Thank's a lot
UPDATE Table From Query Results?
Jun 9, 2006Alright, 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?
Use Query Results From Report To Make Table
Aug 2, 2006Hello,
I have a query that prompts the user for input to generate a report.
I would like to in essence copy that same record set and append it to a different table (archive table).
Not quite sure how to go about it.
Thank you
Locking A Table Based On Query Results
Aug 24, 2006I have a query that checks an expiration date field and displays the word "Expired" in another field if applicable. This query is used to look up items, then the user would enter whether or not it is approved right in the resulting data grid thereby entering that approval into the table being used for the query. Can I do something with the query so that if the item returned has expired, a user would not be able to enter anything into the approval field? Something like, if field1 = "expired" then lock the table? I can't use a form, I have to just do it in the basic query or forget it. I would very much appreciate any suggestions!
View 1 Replies View RelatedQuery Returns Results From One Table/query But Not From Other Table/query
Mar 2, 2007Advanced apologies for the long question.
I have a table providing a list of all members of a local bowling club, with the following fields - MemID, MemName (table is tblMembers)
I also have two more tables providing details of matches we play in a local bowling league. The first table holds the basic match information (called tblMatches) - MatchID, Date, Opponents, ScoreFor, ScoreOpps. I've used a MatchID field as more than 1 match can be played on a single date.
The other table provides details of who played in what match and on what bowling rink and is called tblMatchDetails, with the following fields - MatchID, MemID, Rink (numeric), Result (numeric - 1 for win, 0 for loss). Each match uses different rinks, and not all rinks are used in a match, however, each rink can only be used once in a match.
We have 10 rinks, and I was looking to see if I could get a table to show me the match details along with the rinks in numerical order with who played on them in what match and their result.
For example:
Field Row: Date - Opponents - ScoreFor - ScoreAgainst - Rink 1 Player - Rink 1 Result - Rink 2 player - Rink 2 result - [and son on upto] - Rink 10 player - Rink 10 result.
Row 1: 01/01/2005 - Example BC - 4 - 3 - [blank] - [blank] - Fred - 1, etc
Row 2: 08/01/2005 - Other BC - 5 - 2 - John - 0 - [blank] - [blank], etc
I produced a query for each rink in turn which looked in tblMatchDetails and provided the following (the queries are called qryRinkOneDets - or whatever rink numebr was used):
MatchID - PlayerID with a criteria field of rink = "1" or whatever number rink it is for.
I then produced a second query providing a link of the matches (tblMatches) and the rink queries (qryRinkOneDets, etc), with the joins between the MatchID fields in tblMatches and the individual queries to provide every record from tblMatches and only those records from qryRinkOneDets where MatchID was equal.
However, the results I got only showed the match details and no info from the Rink query. If I change the join from RIGHT to LEFT, then I get the records from the Rink query but not from the Matches table. If I then do a join where only the MatchID's from the table and query are equal (an INNER join) then I get no records at all.
Can anyone figure out what I'm doing wrong?
Many thanks in advance
Queries :: Use Query Results To Fill In Records In Another Table
Aug 18, 2015I have a query that outputs results like:
Company ID | Data A | Data B | Data C
101 | results |results |results
102 |results |results |results
103 |results |results |results
104 |results |results |results
105 |results |results |results
In another Table containing additional company information, I have the primary key as the company ID, and I want to make the query that outputs the above table, auto-fill the blank fields in the existing Company information with the same headings as Data ABC etc.
However, I don't want the query to add full new records (which I think is the Append Query?), instead I want the existing company records have additional fields (Data ABC) added, with information from the Query added.
Forms :: Binding Results Of Query To Textbox Then Writing Value To Table?
May 19, 2015Basically, I originally wanted to create a form which I can input data into Access with by using a button (I was unable to get Access to append the information from the forms to the table, so it didn't work out!). I did not want the fields on the form to be linked to a table, as then a record (and most importantly an auto-number) is created as soon as somebody starts typing. Should somebody stop typing halfway through and quit the form an autonumber will have been generated, which makes the number of "users" seem higher than it actually is. I got around this by changing the field from autonumber to number and then creating a query that selects the maximum value of ID in the table, then adds 1 to it (which is essentially the lowest unique number. I tend to call this newID).
The problem I now have is setting this to be written to the table alongside the data from the form (the rest of the fields on the form are now connected to the table, as I am no longer using an autonumber). I have tried the following: Setting the form to run the expression "[ID] = [qryMaximumUserID]![NewID] " on load. This returns the error "The object doesn't contain the Automation object 'qryMaximumUserID.' Setting the control source of the text box to be dLookup. This fills the textbox with the correct value, but then it doesn't write it to the table! Setting the default value of the field to be 0, then running an update query to update any ID of 0 (criteria "0" to the value of newID "update to: [qryMaximumUserID].[NewID]. "). However this doesn't work as whenever the query is run it asks for a parameter to be entered, rather than just taking the value from the other query. Writing a macro that is run on load to SetValue of item:[ID] to expression: [Forms]![qryMaximumUserID]![NewID]. However this returns an error "Microsoft Access cannot find the referenced form 'qryMaximumUserID' you entered in the expression.
Tables :: Summation Of Fields In A Table Based On Query Results
Nov 5, 2013I have a form that users can input data into and based on that data it runs a query and generates a report. These reports can be different based on user entered data on the form. My issue is within the report I would like to sum certain fields. The problem with trying to sum theses fields is that they show up on each row so I have hidden duplicates but when trying to sum the field it still trys to count the hidden duplicates thus giving a value that is of no use.
I have tried many methods to sum but one of the problems I continue to run into when I create a text box and build an equation and reference the field I would like to sum is when the report runs it is asking for a value to be entered for the field I am attempting to sum. I shouldn't need to enter a value as I am trying to obtain the value.
Modules & VBA :: ADO With SQL To Add Append Query Results To Excel 2007 Table
Oct 16, 2014I have successfully used VBA to populate select query results into an excel worksheet on open, for a co-worker. Now I am trying to populate the records from an append query to the bottom of those results, which are now in a table on an excel spreadsheet, Contractor EIF. I am trying to make this work because my co-worker modifies the results in the table, deleting rows, adding fill color etc. and as new projects begin he would like those added to the projects already in the table (without rewriting the entire table).
Private Sub Window_Open()
Dim strDB As String
Dim strMyPath As String
Dim strDBName As String
[code]....
Modules & VBA :: Send Query Results In Table In Body Of Email
May 24, 2014I have created some code to get the records of a query and it puts them in the body of the email but it doesn't format it well for example the email looks like
James | halliwell | 31
Leanne | smith |27
Alexis | smith |8
I would like it to be in a table is this possible,
Code:
Public Function SendEmail()
Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Set MyDB = CurrentDb
[Code] .....
Queries :: Update Table With Values From Field Using Results From Select Query
Sep 23, 2014Let's say Table (T1) has fields F1 and F2. After a massive update to T1, there are some records with F1 = "" because a Dlookup using F2 as criteria to another Table (T2) resulted in a null. I created a select query to show unique T1F2 values where T1F1 = "". The user can use this query to find out which F2 values need to be added to T2.
How do I create an update query that will update T1F1 with values from T2 using the T1F2 results from the select query to again use the Dlookup to T2 (of course after T2 has been updated to contain the missing F2's)?
Show Current Record Value From Table In Combobox In Addition To Query Results
May 3, 2015I have a table, with a related value in another table. E.g. A Items table with a batch value from another table.
I have a form to enter how many of these items has been used and from which batch number they belong.
The batch number is from a dropdown, and batches can be finished(exhausted) and marked such in the table so they no more show in the dropdown.
All this works fine, until, I go back to a entry which was from a batch that has been finished. The combobox is empty although the (Already finished) batch number is mentioned in the table. This is perfectly normal as my query for the combobox is :
Code:
SELECT ItemBatch.ItemId, ItemBatch.ItemBatchNumber, ItemBatch.Finished, ItemBatch.ItemName
FROM ItemBatch
WHERE (((ItemBatch.Finished)=False)
AND ((ItemBatch.ItemName)=[Forms]![ItemMasterForm]![ItemDataSheet].[Form]![ItemName]));
What I want is to show the current batch number as well. I tried to make this query get the current value, but wasn't successful. I tried to make a calculated field based on the dropdown and show its value.
Is there any way I can show the batch number in the datasheet? I have to use a datasheet and not a form, because there will be many sub records for the main form, and having a form will be very uneasy.
Changing Data In Query Results Changes Data In Source Table
Dec 17, 2012I have a table and a simple query that pulls results from the table. Nothing too crazy. But, if I were to go in and change some of the data/values in the query results it will change the respective data in the table. I know that this cannot be right. What do I have to do to either prevent the ability to change query results and/or prevent any changes in the query from altering the original data in the table.
View 2 Replies View RelatedQuery Results Minus Query Results = New Query?
Apr 1, 2008I used to queries ,1 to get items that are taken ( its all about sign in sign out for equipment) and other query is list of all items.
How can i make 3rd query which will give me all but taken items from query1?
(of course items from query 1 are in query2)
thx in advance
Is This Possible...two Results From Same Table?
Feb 8, 2006I'm doing a table for Future Business Leaders of America competitive events assignments.
I want the same table to hold Name and Event Name and be able to sort
However, I want the database to be able to sort to tell me each event that a person has, and at the same time, how many people are in each event. Presently, I have to switch between databases.
On one table, the event name is the primary key, and on the other the person's name is the primary key.
For the event name table, categories are "Event Name" , "Participant 1" , "Participant 2", "participant 3", etc.
For the Person's name table, Categories are "Name", "event 1" , "event 2", "event 3", etc.
I want to have ONE database that will sort lists for all participants in each event AND that will sort into a report or form so that I can inform participants of their events.
How can I do this?
I probably was not very clear, so ask lots of questions if you're unsure.
THANKS A LOT!!
Too Many Results From One Table And Other Problems
Aug 16, 2005ok, i'm not the strongest SQL programmer, so i can't figure this out. i'm working on a report with the following source code:
SELECT vio_access_mid.safety_survey_date, vio_access_mid.violation, vio_access_mid.comments, vio_chemical_mid.violation AS chem_vio, vio_chemical_mid.comments AS chem_comm, vio_chemical_mid.safety_survey_date AS chem_date
FROM vio_access_mid INNER JOIN vio_chemical_mid ON vio_access_mid.lab_id = dbo.vio_chemical_mid.lab_id
WHERE EXISTS
(SELECT ehs_labs.lab_id, tbl_list_building.building_desc, tbl_list_department.department_desc, ehs_labs.lab_room, ehs_list_lab_type.lab_type_desc, ehs_princ_inv.princ_inv_fname, ehs_princ_inv.princ_inv_lname
FROM ehs_labs INNER JOIN tbl_list_building ON ehs_labs.building_id = tbl_list_building.building_id INNER JOIN tbl_list_department ON ehs_labs.department_id = tbl_list_department.department_id INNER JOIN ehs_list_lab_type ON ehs_labs.lab_type_id = ehs_list_lab_type.lab_type_id INNER JOIN ehs_princ_inv ON ehs_labs.lab_id = ehs_princ_inv.lab_id
WHERE (ehs_labs.building_id = '0146') AND (ehs_labs.lab_room = '5678'))
AND (vio_access_mid.safety_survey_date = '6/1/2005') AND (vio_chemical_mid.safety_survey_date = '6/1/2005')
when i run the code, i get the correct amount of records (3) from vio_access_mid, but i also get 3 from vio_chemical_mid when in reality there are only 2 records. i will eventually add other vio_*_mid tables that will have a variety of results (some may have 0 while others might have 4 or 5, etc).
i would eventually like to be able to include the info from the WHERE EXISTS clause in my results, but one thing at a time...
anyone have suggestions on how to get the correct info from each vio_*_mid table?
Using Expression Results In A Table
May 26, 2005I am writing a basic database to track performance for a stationery company.
Basically the database is used to track how long it takes certain depts to complete a task, so each dept ticks a box when they pass the task to the next dept and this populates a field with the date and time that they tick the box using the expression =IIf([Passed to Collate]="-1",Now()," ")
My problem is the box that you place the expression is also the box that you would select which column of the table the result would be sent to and I'm not sure how else you would run querys to establish total time and average time for the task.
I'm sure I'm missing something easy as I can't see why you wouldn't be able to save the result of an expression to the table and use it in querys/reports etc.
Any help would be greatly appreciated.
Col.
Add Form Results To Table
Jun 23, 2005I'm sure this has been asked before, so I'm looking for search hints:
I would like to select query results displayed in a form, by check box, and add them to a table. The results displayed in the query are random and this poses a problem, at least for me anyway. :rolleyes: Any ideas?
Thanks in advance.
Need Help Formatting Results In Table
Apr 13, 2005I am trying to create custom "icons" using values retrieved from an access db. Right now, I'm pulling records that match the user's name(Fname) or "default", then assigning the returned values variables and plugging those variables in as the image, URL, and title of the "icon". The problem is that this setup will only retrieve the first record from the db that matches "Fname" or "default" and I want to create seperate "icons" for each record that matches "Fname". I can pull all matching records using the maketable(), but then I don't know how to format these as I have here to make additional "icons". My source code is included below:
Code: <% if Fname <> "" thenopencnset rs = Server.CreateObject("ADODB.RecordSet") SQL="SELECT ID, Date_Time, URL, Icon, Title, Description, Status FROM Submission WHERE Technician ='" & Fname & "' OR Technician='Default'" Set rs = cn.Execute(sql, , adCmdText)ID = rs.fields(0)URL = rs.fields(2)Icon = rs.fields(3)Title = rs.fields(4)rs.closecloseCNend if%><a href="<%=URL%>" target="new"><img border="0" title="<%=Title%>" src="menupics/<%=Icon%>.gif"></a><br><font color="#FFFFFF" face="Arial" size="2"><%=Title%></font>
Any help anyone could provide on this would be greatly appreciated. Thanks in advance. -Chris Gordon
Queries :: Join Results Of Unmatched Query With Matched Query To Include Null
Mar 24, 2013I am trying to do the good 'ol sales report (query) to include customers with no sales.
I have a customers table, account number table, sales table & sales (line) detail table. (all linked in that order)
If I run a query to show customers (in the customer table) with account numbers, that works
An unmatched query to show customer without an account number works (but of course the unmatched account number field isn't shown).
How can I get the two two be shown together with the "unmatched" having a null or 0 for their account number?
I am guessing in principle, the resulting solution can be modified to show customers without sales alongside those with sales?
Queries :: Update A Query Based On Results From Another Query Using Count Function
Apr 2, 2013I run a physical therapy office and patients come in for treatment either 3, 4 or 5 times per week. My database is used to track these frequencies (among other things).
I have 3 queries which count how many patients come in 5, 4 and 3 times/week.
In my main table I have fields called "how many 5's", "how many 4's" and "how many 3's".
I have tried to design an update query which will update those fileds in my main table to reflect the counts in the 3 queries mentioned above.
(I'm not using SQL view, I'm using the query design view)
In the "update to:" row, I use the Build function and locate the count I'm looking for.
Problem: when I run the query I get the error: Operation must use an updateable query.
Build A Table Of Search Results
Mar 28, 2006Using Access 2000 is it possible to specify a starting location ie (c:) and using VBA search every sub folder from this point for files ending .mdb or .xls?
I want to be able to build a table with an record showing the file name and directory for each file found.
Any help most appreciated.
Regards,
Dalien51
Exporting The Results Of A SQL Statement Into A Table
Oct 10, 2005I am going to attempt to explain the problem:
I design a form and all my work is based on that form.
I have two tables which I join together as following.
The unfitex field is calculating using fields from both tables as shown below.
Dim db As Database
Dim rst As DAO.Recordset
Dim sqlstr As String
v = Combo37.Value
sqlstr = "SELECT postdecgor.N1, postdecgor.Age, postdecgor.UNFITpc, postdecgor.DECENTpc, postdecgor.HHSRSpc, [UNFITpc]*[sample]/100 AS UNFITex, postdecgor.DECENTex, postdecgor.HHSRSex, [Sample size].[Age dwelling], [Sample size].sample FROM postdecgor LEFT JOIN [Sample size] ON postdecgor.Age=[Sample size].[Age dwelling]WHERE ((postdecgor.N1)='" & v & "'); "
Set rst = db.OpenRecordset(sqlstr, dbOpenDynaset)
Set Form.Recordset = rst
I then set up some textbox which shows the results as following.
N1.ControlSource = "N1"
Age.ControlSource = "age"
UNFITpc.ControlSource = "unfitpc"
DECENTpc.ControlSource = "decentpc"
HHSRSpc.ControlSource = "HHSRSpc"
UNFITex.ControlSource = "numunfit"
UNFITex.ControlSource = "unfitex"
DECENTex.ControlSource = "decentex"
HHSRSex.ControlSource = "HHSRSex"
It works, I can see the results on the screen, however I would like to save the results into a table and I am not quite sure how to go about it.
Can you help??
Thanks for your help
Display Multiple BE Table Results
Sep 1, 2007:confused: I have 3 identical fe be database running each with a table called cars that has identical form values just obviously different data. I want to create a table or query to display all the results from these but whenever i try and make a query i cannot seem to work it out. Anyone got any ideas? :confused:
View 2 Replies View RelatedShow Results In A Form Or A Table
Nov 20, 2006Hi,
I have a list-box(Category) and a 'ok' button on the form.
My OK button has the following [Event Procedure]:
Private Sub OK_Click()
Me.Visible = False
DoCmd.RunSQL "Select [Barg Unit],[Medical Option],[Medical Coverage Tier] FROM RetireeCensus Group By [" & Category & "];"
End Sub
First of all, is my syntax correct in the above codes?
Second, what VBScript codes do I need to add in so that when I click the 'OK' button, a report or a table will show the query results from my 'Select' statement?
Thank you.