Queries :: How To Write A Query Which Selects Multiple Records From A Table At Once For Updating
Aug 14, 2013
I have a list box whose data is inserted in a table named as "test0" ,now in a macro of vba i want to select all the items in the list box and create there pdf files in a folder at my desired location. So far i have managed to create a pdf file of single item ,but i want to select multiple items at once ,
Code:
SELECT test0.ID, test0.item FROM test0 WHERE (((test0.item)=[ItemNumber]));
So, in this query itemNumber are multiple and i want to create there pdf files at once ..just on a click of one button ?
View Replies
ADVERTISEMENT
Apr 28, 2014
I have a query with many fields. One of them is the combination of two fields and is called "Components Reference". What I try to do is, every time this query is updated, the records of only this field "Components Reference" to be written to the field of another table which also takes records from an another query. I wish I was clear enough.
View 13 Replies
View Related
Aug 22, 2013
why this query pulls every record twice and what I can do to fix it?I did it in design mode because I'm an amateur, but here is the SQL.
SELECT [Patient Data Table].[First Name], [Patient Data Table].[Last Name], [Patient Data Table].[C MRI Facility], [Patient Data Table].[C MRI Sched Date], [Patient Data Table].[C MRI Scan Done]
FROM [Patient Data Table], [Date Range]
WHERE ((([Patient Data Table].[C MRI Scan Done])=Yes) AND ((Month([C MRI Sched Date]))=[Date Range]![From Month]) AND ((Year([C MRI Sched Date]))=[Date Range]![From Year]));
View 6 Replies
View Related
Nov 3, 2014
I have a query that selects the "Active" records for three different selections, A, B or C.. There may be 1, 2 or 3 results for a particular selection. That is Selection A may have one result or active records, but Selection B may have three results.
I want to use data from the query to populate a field on a form. For example, if the results for Selection A, having one "Active" record would be RecordAData. But for Selection B with three "Active" records the result would be RecordBData & " " & RecordBData2 & " " & RecordBData3
My query is:
SELECT tblSomething.ID, tblSomething.D1, tblSomething.D2, tblSomething.D3, tblSomething.D4, tblSomething.D5, tblSomething.D6, [D3] & " " & [D4] & " " & [D5] & "-" & [D6] & " " & "SomeText" AS Header
FROM tblSomething
WHERE (((tblSomething.D1)=Forms!frmSomethingHeaders!D1) And ((tblSomething.D2)=True));
The concatenation in the query is labeled "Header". I want to be able to Concatenate the "Header" which in itself is a concatenation in the query.
I thought that this might be a looping through the query results, but I cannot figure out how to do it. But then, that is only my uneducated guess.
View 11 Replies
View Related
Dec 21, 2005
I'm creating an Access data entry form for computer installations.
Basically the entry form will choose a software and license, then select one or more computers to install the software on.
Then later we can create a report that can track what softwares are installed on the computers, or track what computers has what softwares.
Now my question is for each record of the installation entry form, after we select the software, then we will choose a computer name or more computer names.
If just one computer is easy, but how can I choose multiple computers, which control should I use? and how can I store the computer name list in the installation table, do I have to make each computer installation a record in the table or put multiple computer names in one record in the field like memo, but if I use memo, later it seems i cannot group or search memo field?
Can anyone give me some ideas about what is an easy design solution or samples about these?
Thanks a lot
View 1 Replies
View Related
Dec 19, 2012
I am trying to write a query that will return records from multiple tables. I currently get an error suggesting I create a subquery or else I get far too many records.
The query is EditAttendanceQuery (I left it in a bit of a state). The fields I need are shown in the query. The records I need are based on the Edit AttendanceQuery (Form). I can get the records I need without the CourseNumber and Section, but it all goes downhill when I include them.
View 14 Replies
View Related
Nov 26, 2013
I'm using an UPDATE query to update records in one table (tblMain) from another table (tblTemp)
Here is my SQL :
Quote:
UPDATE [tblMain]
INNER JOIN [tblTemp] ON [tblMain].[MainField1] = [tblTemp].[TempField1]
SET [tblMain].[MainField2] = [tblTemp].[TempField2];
I only want to update the records in tblMain which have a corresponding record in tblTemp (linked by MainField1 / TempField1)
If any record doesn't appear in tblTemp, I want tblMain to retain the existing value for that record.
However, it appears that in such situations, the record in tblMain has it's MainField2 value set to null / ZLS.
I've tried using LEFT JOIN and RIGHT JOIN and also tried WHERE clauses but the result is the same every time.
View 3 Replies
View Related
Sep 7, 2005
Hi,
I'm trying to run a pass thru query into a sybase server from Access. The query contains multiple selects which Access doesn't appear to like very much. The query works fine when run in Sybase.
Is there any way of circumventing this problem?
I could split the three selects into three seperate queries to view the individual results but the third set is dependant on the previous two sets and so therefore is difficult (impossible?) to seperate out as a third individual query.
Thanks for any suggestions?
Select
s.id_prtf Portfolio_ID,
s.id_imnt Instrument_ID,
s.id_imnt_swap Instrument_Swap_ID,
s.id_ccy_std Currency,
s.am_posn Position,
s.am_pnl_eqty PNL_Eqty
into #temptable1
from
SET_VALUATION s, INSTRUMENT i
where
s.id_imnt = i.id_imnt
and s.id_posn_ref is NULL
and s.id_imnt_swap is NULL
and s.am_pnl_eqty > 0
and i.id_typ_imnt != 'SFX'
Select
s.id_prtf Portfolio_ID,
s.id_imnt Instrument_ID,
s.id_imnt_swap Instrument_Swap_ID,
s.id_ccy_std Currency,
s.am_posn Position,
s.pr_imnt_mtm_prev,
sy.pr_imnt_mtm,
s.am_mv_prev,
sy.am_mv
into #temptable1a
from SET_VALUATION s, SET_VALUATION_HIST sy, REFERENCE_DATA r
where
s.id_prtf = sy.id_prtf
and s.id_imnt = sy.id_imnt
and s.pr_imnt_mtm_prev != sy.pr_imnt_mtm
and s.am_mv_prev != sy.am_mv
and r.dt_bus_lst = sy.dt_bus
select
t.*,
tt.pr_imnt_mtm_prev,
tt.pr_imnt_mtm,
tt.am_mv_prev,
tt.am_mv
into #newtemp
from #temptable1 t, #temptable1a tt
where
t.Portfolio_ID = tt.Portfolio_ID
delete from #temptable1
where #temptable1.Portfolio_ID in (select Portfolio_ID from #newtemp)
delete from #temptable1a
where #temptable1a.Portfolio_ID in (select Portfolio_ID from #newtemp)
select "Where id_posn_ref is NULL and id_imnt_swap is NULL is the only criteria"
select * from #temptable1
select "Where SET VAL's pr_imnt_mtm_prev does not equal SET VAL HIST's pr_imnt_mtm, is the only criteria"
select * from #temptable1a
select "Where the output satisfies both Where id_posn_ref is NULL and id_imnt_swap is NULL AND Where SET VAL's pr_imnt_mtm_prev does not equal SET VAL HIST's pr_imnt_mtm"
select * from #newtemp
drop table #temptable1
drop table #temptable1a
drop table #newtemp
View 2 Replies
View Related
Apr 29, 2008
Simple query, but I need to roll the results up with others and a null return is hurting me. I have been chasing my tail around with the nz(), input masks, IIF statements, only to prove my ignorance and possible insanity for expecting different results.
Here we go:
SELECT Count([LDB LOG].DSTHTL) AS ["tot120"]
FROM [LDB LOG]
WHERE ((([LDB LOG].[LDB Rcvd]) Between Now()-120 And #1/1/2008#))
GROUP BY [LDB LOG].[T-7 Date]
HAVING ((([LDB LOG].[T-7 Date]) Is Null));
This is being used to count open items into age buckets, the above is the greater than 120 days (discounting anything prior to 2008). If the T-7 Date is null the item is open. What is happening is that no records are being selected, resulting in the null value back.
Any help would be greatly appreciated as I have spent an unrealistic amount of time on this.
Thanks
Brendan
View 6 Replies
View Related
Jul 1, 2014
I am trying to determine the best method for how to handle this query using Access 2013. I have a clients table that contains the following:
clientID fName lName admissionDate dischargeDate
1 John Doe 05/06/2014 06/27/2014
2 Jane Doe 04/24/2014 05/15/2014
3 Steven Smith 05/15/2014 NULL/Empty
4 Chris Davis 06/12/2014 NULL/Empty
Then there is a WeeklyProgressNotes table that is there for the person that is responsible for auditing the clients charts. It does not contain the actual weeklyprogressnotes, it only contains a Yes/No field and a date field for the date the weeklyprogressnote was completed. Like below:
noteID completed dateCompleted clientID
1 yes 05/08/2014 1
2 yes 05/14/2014 1
3 yes 04/25/2014 2
I am creating a form that the auditor can open to determine what weeks she needs to check for each client to see if they have their weeklyprogressnotes completed that week. The weeks run Mon - Sun and there will be no record in the WeeklyProgressNotes table if she has not yet checked and confirmed for that week. So the form would basically look like this:
fName lName week completed date clientID(hidden)
John Doe 5/19/14-5/25/14 Checkbox Null 1
John Doe 5/26/14-6/1/14 Checkbox Null 1
John Doe 6/2/14-6/8/14 Checkbox Null 1
John Doe 6/9/14-6/15/14 Checkbox Null 1
John Doe 6/16/14-6/22/14 Checkbox Null 1
John Doe 6/23/14-6/29/14 Checkbox Null 1
Jane Doe 4/28/14-5/4/14 Checkbox Null 2
and so on.......
I have thought about creating an SQL statement to select all of the clients and then creating a function that determines their admission date within the specific week and their discharge date withing the specific week and then create a loop with another SQL statement with a BETWEEN clause for all the weeks and determine if there is an entry in the WeeklyProgressNotes table or not. If not then I would display out the above info. I'm not sure if there is an easier, less search intensive way of doing it. Maybe an SQL query that can cut done on some of the looping.
View 5 Replies
View Related
Jan 11, 2015
I have an Access 2007 application that has a Parts Table that contains a list of automotive parts.I have attached a screen shot (parts.jpg) showing the structure.I have another table called Web_Parts that has exactly the same structure as Parts. The Web_Parts table gets its data from a CSV import that I do that is data extracted from an MySQL database used by an eCommerce website.
You will notice that there is a field called "Web_Product_Id" (number). This is the unique ID for each of the products that I have exported from the Web shop system. I need to regularly (probably every couple of weeks), export out of the web shop system and import into the Access environment.
Due to the fact that the data in the web shop system may change (pricing, description, add new items, delete new items etc), I need to find a way that I can simply update any existing records in the Parts table with any new information contained in the Web_Parts table......
View 13 Replies
View Related
Jun 30, 2005
Okay, take three tables.
AreaTable
AreaID
AreaName
Percent
GangTable
GangID
Area_ID
Speed
MasterTable
MasterID
Station
Description
Area_ID
The Relationships
AreaTable.AreaID 1---------> (inf) GangTable.Area_ID
AreaTable.AreaID 1---------> (inf) MasterTable.Area_ID
Initially the query below used Inner Joins, however that limits my list, when i want to see ALL elements from the MasterTable no matter what, thus my inner joins became left and right joins as follows.
Simple Query: Query1
SELECT MasterTable.Station, MasterStable.Description, MasterTable.Area, AreaTable.Percent, GangTable.GangID, GangTable.Speed
FROM (AreaTable RIGHT JOIN MasterTable ON AreaTable.AreaID = MasterTable.Area_ID) LEFT JOIN GangTable ON AreaTable.AreaID = GangTable.Area_ID;
This should simply display All Records in MasterTable, (Multiple Times if Necessary) listing all the elements of AreaTable that are Linked to the MasterTable, and all elements from GangTable that are linked to AreaTable. It does this, and displays them nicely. But I can't edit the fields. I get the result:
1 | Station One | Area 1 | 45% | 204 | 1000
1 | Station One | Area 1 | 45% | 304 | 500
1 | Station One | Area 1 | 45% | 404 | 750
2 | Station Two | Area 1 | 45% | 204 | 1000
2 | Station Two | Area 1 | 45% | 304 | 500
2 | Station Two | Area 1 | 45% | 404 | 750
3 | Station Three | Area 2 | 75% | 254 | 800
3 | Station Three | Area 2 | 75% | 354 | 600
3 | Station Three | Area 2 | 75% | 454 | 700
So you can see that Area 1 has multiple Gangs (204,304,404) and Multiple Stations (1,2). If you do a simple set up like this, you'll find that you can't change the Description field (Rename "Station One" to "Hello World"). It just doesn't work, no matter which way I've tried, I can't seem to make a Query that presents all the information from MasterTable and All the Information IN AreaTable and All the Information in GangTable which will allow me to also edit the fields.
Any Help would be most appreciative, I'm tearing my hair out on this one.
Thanks,
Jaeden "Sifo Dyas" al'Raec Ruiner
ps - It just seems that with Junction Tables and all the many to many relationship designs I have tried, you'd be able to change the non-related fields. I understand that you can't change the "ID" fields, but the others should be editable.
View 2 Replies
View Related
Nov 3, 2005
I created an update query that says it has successfully updated 600 records, but when you go into the table, it has not been updated. I am working with an .mde. Does that make a difference?
I first created a select query to find everyone in TX. Then I changed it to an update query to change all TX to CA. Since it did not update the table, I tried running the update query again and this time it says there are not records to update (since it uppposedly updated my 600 records).
Your help is appreciated.
View 1 Replies
View Related
Jan 6, 2014
I have a table and a query both of which have the same [Resource ID], [Resource Name] fields.
I am trying to update the [CBL_1_Date], and the [CBL_1_kW] fields in the table with the [CBL_x_Date] and [CBL_x_kW] fields of the query. The problem is that when I try to run the query I keep getting "Operation Must Use and Updateable Query" error.
here is the SQL Code
UPDATE tCBLAggregation
INNER JOIN qBestof4CBLs_avg
ON (tCBLAggregation.[Event Date] = qBestof4CBLs_avg.[Event Start Date]) AND (tCBLAggregation.[Resource ID] = qBestof4CBLs_avg.[Resource ID])
SET tCBLAggregation.CBL_1_Date = [qBestof4CBLs_avg].[CBL_x_Date], tCBLAggregation.CBL_1_kW = [qBestof4CBLs_avg].[AvgOfCBL_x_kW];
View 1 Replies
View Related
Aug 9, 2014
I'm working on a project management database for my fathers small business. Data Entry goes as follows: Users enter a Work Order, and all the parts that must be created for that work order. The parts have different processes (tasks) done to them to be created. The database runs a simple system like this as we want it to work. I am now attempting to allow handling of more advanced work orders. Specifically, some parts require the completion of multiple other parts before they can be created.
Here is how the database should handle these events:
1) User clicks button named "requires other parts" when entering a new part.
2) On click a subform is opened that allows the user to enter which other parts need to be completed first.
3) Once all of the parts are completed the next part can begin.
This is my current solution:
1) A query calculates if a part is complete
2) Some form of logic looks up to see if all the required parts are completed. If the answer is yes, a field named ready changes to "Yes" and if the answer is no, a field named ready changes to "no"
View 9 Replies
View Related
Jun 27, 2013
I am the first to admit I struggle with looping through records.
I am trying to loop through a table and collect external income data and write it to a new table.
My issue may be the way the table is laid out. ???
The attached pictures show the tables. The tblExtInc is where the data are. I am trying to create a table that looks like tblExtIncYr that shows each year and separate entries by item.
How would I loop through tblExtInc and grab ex:$2000 every year for upto 100 years but only get the $1000 for year 6,7,8 only?
View 2 Replies
View Related
Sep 7, 2013
I'm using Access 2007.I have 2 tables that are identical in structure.The tables are called [Workorder Parts] and [Workorder Parts Temp]. They both have the same structure:
WorkOrderPartID (Autonumber)
WorkorderID(Number)
PartID(Number)
Quantity(Number)
UnitPrice(Currency)
Notes(Memo)
KitID(Number).
[Workorder Parts] has 128 records in in and [Workorder Parts Temp] has 28 records in it that are a small subset of [Workorder Parts] therefore, the contents of the key field column in [Workorder Parts Temp] is the same as in [Workorder Parts].I tried the following SQL but get an error message saying "Cannot Update "WorkOrderPartID" Field not updateable":
DoCmd.RunSQL "INSERT INTO [WorkOrder Parts] SELECT * FROM [Workorder Parts Temp]"
My aim is that I want all of the records from [Workorder Parts Temp]to be copied to [Workorder Parts] and have their "WorkOrderPartID" fields correctly updated with a new value...
View 14 Replies
View Related
Aug 21, 2006
Hi
I am tryin now (having cured my first troubles lol) to update multiple fields at once in my db from an asp web page. Code involved is
Code:DIM idid = request.querystring("id") strDSNPath = "PROVIDER=MSDASQL;DRIVER={Microsoft Access Driver (*.mdb)};pwd=password; DBQ=" & Server.MapPath("....dbusers.mdb") strSQL_insert = "UPDATE * FROM CustRecords SET " & _"Cust_Name ='" & strName & "', " & _"Cust_LastName ='" & strLastName & "', " & _"Cust_Status ='" & strStatus & "', " & _"Cust_Orient ='" & strOrient & "', " & _"Cust_Address ='" & strAddress & "', " & _"Cust_City ='" & strCity & "', " & _"Cust_Country ='" & strCountry & "', " & _"Cust_PostCode ='" & strPostCode & "', " & _"email ='" & strEmail & "', " & _"Cust_PartName ='" & strPartName & "', " & _"Cust_PartOrient ='" & strPartOrient & "', " & _"Cust_Descrip ='" & strCustDescrip & "', " & _"Cust_Goal ='" & strGoal & "', " & _"Cust_Username ='" & strUsername & "', " & _"Cust_Password ='" & strPassword & "', " & _"Cust_Question ='" & strQuestion & "', " & _"Cust_Answer ='" & strAnswer & "', " & _"Cust_Type ='" & strLastName & "', " & _"Cust_IP ='" & strUserIP & "', " & _" WHERE id="&id Set Conn=Server.CreateObject("ADODB.Connection") Set rs = Server.CreateObject("ADODB.Recordset") Conn.Open strDSNPathConn.Execute strSQL_insert Conn.Close Set Conn = Nothing
Long winded I know but usually works and I am getting the following error
Code:Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement. /sitesec/first_time1.asp, line 179
View 6 Replies
View Related
Feb 9, 2015
I have two tables
1) has email address, and Store ID
2) has multiple records per store
I need to write a procedure to send a separate email to each store that contains the records found in table 2 ( excel format).
View 3 Replies
View Related
Aug 13, 2013
I have a list box populated with record ID's all of which need a date field updated. I have been succesful at using the list box to update single records, but am not sure how to transfer this idea to work with multiple records simultaneously.
The code i am using is:
Private Sub Command13_Click()
Dim i As Integer
Dim strSQL As String
Dim sMessage As String
Set db = CurrentDb()
Dim sTitle As String
For i = List10.ListCount - 1 To 0 Step -1
[Code] .....
View 3 Replies
View Related
Feb 12, 2014
I have a screen with multiple records from a database. At the top of the screen I have a button that lets the user change the mode between read only and edit modes. The current mode is displayed near the button. When the user decides to update a record or records he goes into the edit mode and starts changing the records. The user may change any number of records on the screen. When editing is finished he presses a button called save and close gets feedback and leaves the screen. Now in this process how do I keep track of the records that are changed on the screen . Plus he can hit the page down key and go to the next page and make changes there also. Is it possible to stop the hitting of the page down key. What indicators can I use to spot the records changed?
View 14 Replies
View Related
Mar 4, 2015
I am looking for a query that will return records from a table that have related records in another table. Opposite to the Unmatched Query Wizard.I have two tables: tblSupplier and tblSupplierProducts.The two tables are related by the field "SupplierId".I need the query to only return Suppliers that have Products.
View 3 Replies
View Related
Mar 23, 2015
My colleague has a multiple table database and uses lookups for some fields. The simple query was to extract 3 fields and display all records from those fields. The result of the query was that the selected fields were listed first followed by all other fields which are usually not displayed.
View 5 Replies
View Related
Oct 29, 2014
I have a table with many records, using a form with an update button click event, I would like to update ALL records where the Item in the table = the Item in the form.
There are 6 checkboxes and 6 text boxes that will need to be updated, but right now I am just trying to test if i can even change one text box to keep it simple. I have tried to start by using this:
Code:
Dim mySQL As String
Dim ItemNo As String
Dim SO As String
mySQL = "UPDATE BT200 SET Part = '" & SO & "' WHERE Item = " & ItemNo
DoCmd.RunSQL mySQL
So every time i run this command button, it gives me a prompt "You are about to update 0 row(s)". Nothing gets updated at all, I am clearly missing something vital here.
View 7 Replies
View Related
Mar 4, 2015
i have got 2 tables i am trying query against. in table 1 i have a list of records which only appear once in the table under 1 field. in table 2 list of records which may have the same record appear under the same field numerous of times with different data in other fields on the table. i have created the reationship between both tables but i am getting multiple records appear in the output of the query where i only want the record to appear once in the output.
View 2 Replies
View Related
May 7, 2014
I have a database that has over 20 tables in it and am using Access 2000. Unfortunately I cannot change the structure as it performs specific functions, so I am stuck with it.
I have created a Union Query from these tables yaking data from 5 fields using the Serial Number entered by the user.
SELECT[Workstation].UnitPart,PropertyTag,UnitSerial,Date,Technician
FROM[Workstation]
WHERE((([Workstation].UnitSerial)=[Enter Serial No.];
UNION
SELECT[LAPTOP].UnitPart,PropertyTag,UnitSerial,Date,Technician
[Code] ....
I use a bar code scanner to scan the serial number, and it goes through the tables and returns the results along with the other specified fields.
I would like to scan up to 16 or more different serial numbers and have it return the results. Perhaps scan the first 16 serial numbers, then run the query? Is that possible. The serial numbers are unique and will return a combination of laptops, printers, monitors, etc...
I have created a report from the above union query and it works perfectly with only one serial number entered.
View 14 Replies
View Related
