I have to combine groups to account, then when account is a group in next time, list or combobox, this group must not to be in that list. what is the solution ? SQL or VB. I append file...
View RepliesInserting data from History Table LIKE "P" Type into Preventive Maintenance Table and LIKE "R" Type into Repair Table.
I don't quiet get it because it says "PM Data Update" but nothing happened. Below is the code:
Code:
Private Sub cmdUpdate_Click()
Dim strSQL
Dim dbMNT As Database
[Code].....
if you have a table of data (for example, where I have a long list of investors who should not show up in my queries and I don't want to try typing a very long list in a query's IN statement).
So, I created a "Quick Tutorial" to show how easy it is to use a table to exclude data from a query.
PHP Code:
Dim strSQL As String 'Add Absence Data to tblHour.
strSQL = "INSERT INTO tblHour (WorkDate,EmployeeID,Hours) "
strSQL = strSQL & "VALUES (#" & Me.AbsenceDteTo & "#, '" & Me.EmployeeID & "', '" & Me.txtAbsHrs & "')"
CurrentDb.Execute strSQL, dbFailOnError
[Code] ......
I am trying to use SQL to run queries in our access database in order to (hopefully) speed things up. I'm trying to create code that basically takes data from one table and inserts it into another whilst doing calculations on the data.
However I can't get past this:
Code:
Private Sub Test_Click()
Dim strSQL As String
strSQL = "CREATE TABLE [TempRedAmberGreen]" & _
"AS (SELECT " & _
"[ID_CHK] String," & _
"[Red] String," & _
"[Amber] String," & _
"[Green] String)" & _
"FROM [035 - Meter Point HH Data];"
DoCmd.RunSQL strSQL
End Sub
It keeps saying "Run-time error '3292': Syntax error in field definition.
I have data in a field and I want to write a query to return all the values except the ones that have 2014/* in them. So I want to see the blank ones as well as the one with 2013. So I basically want to return B, C, D, E, F, G, H, J, K, L.
I tried the NOT LIKE function but couldn't get it to work.
I have a query that shows data from January to March (which is the current month). is there a possibility to exclude the current month
The query has the following fields
SegmentDate
PCC
AgencyName
SegmentData
I have two tables, one is GENETIC and the other one is BoneSampleDNAprofile. Both tables have the field CY-Code. I want to create small form with 2 text box fields and one command button on the form to be able to insert CY-Code from the table GENETIC to designated DNA_LAB_Code in the table BoneSampleDNAprofile.
In that small form I would type DNA_LAB_Code and CY-Code where after pressing command button will insert CY-Code for the designated DNA_LAB_Code. This is continuous process after our Laboratory receive results from DNA laboratory. I have attached two .png files which are example of two tables.
I want to set my insert such that
Insert into tableA (x,y,z) = (SELECT x,y,z FROM tableB)
WHERE NOT EXISTS (select x,y FROM tableA,tableB WHERE tableA.x = tableB.x AND tableA.y = tableB.y)
Basically I want to insert data in tableA from tableB if it does not exist in tableA yet.
Access says I'm not writing an updateable query.
I have been successfully using the following statement in Access 2010 to retrieve data from a large csv relational database:
SELECT [1995_1].RPT_REC_NUM, [1995_1].PRVDR_NUM, [1995_2].WKSHT_CD, [1995_2].LINE_NUM, [1995_2].CLMN_NUM, [1995_2].ITM_VAL_NUM
FROM 1995_1 INNER JOIN 1995_2 ON [1995_1].RPT_REC_NUM = [1995_2].RPT_REC_NUM
GROUP BY [1995_1].RPT_REC_NUM, [1995_1].PRVDR_NUM, [1995_2].WKSHT_CD, [1995_2].LINE_NUM, [1995_2].CLMN_NUM, [1995_2].ITM_VAL_NUM
HAVING ((([1995_2].WKSHT_CD)="A000000") AND (Not ([1995_2].LINE_NUM)="09500") AND (([1995_2].CLMN_NUM)="0100" Or ([1995_2].CLMN_NUM)="0200"))
ORDER BY [1995_1].RPT_REC_NUM, [1995_2].LINE_NUM;
This query returns one long column of line numbers (LINE_NUM) representing the itemized salary (CLMN 0100) and non-salary expenses (CLMN 0200) and a total for each of the organizations represented in the data base. My question is: is it possible to modify the query so that it returns only organizations whose data passes that following test that checks to see if the itemized line numbers 00100 to 10099 equal the total for each organizations line 10100: SUM(LINE_NUM 00100:10099)=SUM(LINE_NUM 10100:10100).
I have tried several times to upload a sample file but have been prevented from doing so because of a missing security token. I have communicated this to the administrator.
Is it possible to select some data in queries in the same db to a table?
Many thanks!
Hi
Here's a brain-teaser for you all.
I want to create a copy of a table which I do using the code below.
SELECT * INTO My_Temp_Table FROM My_Table WHERE ColumnA = 100
Great, all works well.... but I want to exclude a column when the temp table is being built. I know I could specify all the columns I want leaving out the undesired column (rather than using SELECT *) but there are lots of them and, whilst in development, the table columns are liable to change. So I'd like to copy all coumns that are in force at any given time except one in particular?
I thought it would be something like :-
SELECT * INTO My_Temp_Table FROM My_Table WHERE ColumnA = '100' AND Columnname NOT LIKE 'ColumnB';
But this doesn't work.
Any ideas please?
Many Thanks
Hi All
I am new to this, hope you can help!!
I have 2 tables,
Customers and Routes
I want to auto insert data from Routes when I select the route Number from Customers,
the tables to auto update are
CruiseNumber:
CruiseName:
CruiseDate:
from
RouteNumber:
RouteName:
RouteDate:
I have read HELP in Access but I am a layman when it comes jargon. 'from the many to the few'??
Hope you can help....in 'Plain English'
I have a report, which is based on query that links various table.
There is also a form, which starts when the report opens, which has dual function: 1.) user selects criteria to filter out only particular customer but 2.) is for user to add some new data that are not available in query/linked tables.
I need to be able to create now a command button that would add all records from the report to for example 'Report Table'. My report has a tabular form but I have also lots of text-boxes in Report Header that would need to be added too.
I`m currently having the problem to export data from an SQL server into a table. I managed to open a recordset but I`m incapable of adding the recordset to an existing table. I found similar threads but I am still not able to generate functioning code.
Code:
Function fDAOServerRecordset()
Dim db As DAO.Database
Dim dblcl As DAO.Database
Dim rssql As DAO.Recordset
[code]....
I have problem with inserting sub-form data to specific table. i have 2 table and one form.
table A is for DLOOKUP, table B is the table i want my sub-form data to insert in.
my table A have ID,name and class.
my table B have ID and other column.
Table A' ID and Table B' ID is related.
i trying to insert my sub-form to table B instead it insert the data to table A.
I have calculated files in a form which is summimg the working hrs of each employ�es for a particular data.
I am able to show the same in the form but want to add this value in the table.
Is is possible to add this data from the form to the able?
Access 2010. Can vba code be written to delete and insert data from a table in one routine.
Something like Delete * Insert into select from where order by....
I tried it with a query but have to write two.
i wanna to insert a new field in the table ... which fill automatically with the date in which i modified the data in this record ...and then i'll insert this field in a report
View 1 Replies View Relatedhow to create a form in access to insert/update/delete data from a table in sql server?
View 4 Replies View RelatedIs there any possibilities to open form after INSERT INTO? I think Ms Access can't fast refresh data in the table after that, so form opens up clean:/
Code:
...
strSQL = "INSERT INTO tblZlecenia (id_zlecenia_info, DataPrzyjecia) VALUES ('" & ostateczne & "', Date())"
CurrentDb.Execute strSQL, dbFailOnError
DoCmd.OpenForm "Formularz2", WhereCondition:="ID_Zlecenia=" & ostateczne
I'm on my way on creating a simple Database for a company. This database contains several tables, one of the tables will record information about the training that had been completed by each employee. There are about ten sections of training that should be completed.
For instance, I have two tables called 'Development' and 'CSA_Lisence'. 'Development' is the table that record the information about training which containing ten checkboxes (which represent ten sections of training) and CSA_Lisence will be automatically requeried when all of the checkboxes on Development are fully checked.
Here's the step I've been worked on :
First, I made a function called 'CheckCompletion' to ensure whether all the checkboxes are checked :
Code:
Public Function CheckCompletion() As Boolean
Dim blnComplete As Boolean
Dim strCompletionSummary As String
strCompletionSummary = Basic_Inspection & Certifying_Staff & Safety_Management_System & Regulation_Part_145 & Part_M & EWIS & Fuel_Tank_Safety_Level_2 & Dangerous_Goods & Human_Factor & Basic_Supervisory_Training
[Code] ....
Second, I made a function called 'UpdateEmployee' to handles if all boxes are checked :
Code:
Public Function UpdateEmployee()
Dim emp_numb As Long
Dim emp_name As Long
Dim strsql As String
emp_numb = [Forms]![development].[employee_number].Value
[Code] ....
Then, I put this code on every checkbox's after update event (example only) :
Code:
Private Sub Basic_Inspection_AfterUpdate()
Call UpdateEmployee
End Sub
The problem is, nothing happened with the tables. However, when I managed to remove the 'If checkCompletion' condition, it worked and the 'CSA_Lisence' is requeried, but I will have ten multiple records with same contents (I just need one record per employee). I guess there's something wrong or missed in my code. Or i need to remove something?
I am attempting to insert a record with selected data into a temp table and I am getting "Run-time error '3075': Syntax error in (comma)...". Here is the code:
Code:
Private Sub XferDataToTempTable()
Dim db As Database
Dim strSQL As String
Set db = CurrentDb
[code]...
I am at work, and I have acquired a database that prints labels. They now want the database to be coded so that after certain labels are printed the database will print a blank label. I have the code figured out as a Do While statement in order to print the blank label. The problem I am having is that I am trying to use the Insert Into command to insert the filepath into the table that adds the blank label.
|DoCmd.RunSQL "INSERT INTO Rod_tmakLabels ( Print, [Order] ) SELECT Yes AS Expr1, 'Rods Labels' AS Expr2"|
If I run the above command, it just adds the text "Rods Labels" at the end of the table. Is there anyway with the INSERT INTO command that I can insert the new label between the 2nd and 3rd row and add another row? Or is the command designed only to add a new row to the end? I haven't had any luck searching for this yet.
Table TBL_NEWDATA is used to append new data to table TBL_PERSON_ALLOCATIONS.
TBL_NEWDATA { Person_ID, Department_ID }
TBL_PERSON_ALLOCATIONS { Person_ID, Department_ID, ... }
I need to devise a query to append data for a particular Department_ID from TBL_NEWDATA to TBL_PERSON_ALLOCATIONS where that data does not already exist there. i.e. for Department_ID 'Research', I would want to append 'Person_ID', 'Department_ID' (in this case: 'Research') to TBL_PERSON_ALLOCATIONS for any tuples not already held.
INSERT INTO TBL_PERSON_ALLOCATIONS (Person_ID, Department_ID)
SELECT Person_ID, Department_ID
FROM TBL_NEWDATA
WHERE TBL_NEWDATA.Department_ID='Form...'
[code]...
This Query takes a single argument from a control (Forms!Main!IN_Department), and this is the Department_ID to be updated.Is there any way to do this using a single query or will I have to use sub queries? I'd hoped not to as to keep the database as concise as possible.
I am trying to use a combo box to select the Company in an input record form for my Transactions. In the Transaction table, each record contains the Company ID, but not the company name (I have a relationship with a Company ID primary key in a separate table that has all the companies information).
I would like to be able to select the proper company in the combo box and have the form save the value as the Company ID number with the rest of the input data in a record (it will then refer to the correct company name in the other table if I query it because of the ID key).