A Query To Add Records Into 2 Tables
Dec 11, 2005Hi all,
I'd like to know if it's possible, with MS Access to use a query that adds records from 2 tables into 2 other tables.
Thank by advance :)
Twinpath.
ADVERTISEMENT
Forms :: Change Query To Look At Same Tables On SQL Database / Cannot Add Records
Jun 24, 2014I am running access 2007 in Win7. I have a time sheet application that I wrote many years ago. Within same I have a button to press to open a new form to enter time sheet data. The data for the form comes from a query that links Employee and the Timesheet history data file.In an effort to upgrade and make the application more portable and available to multiple users, I decided to migrate the data tables to a Microsoft SQL 2012 database. Various Access reports based on the SQL database run ok. But if I try to open a form to add a new record I get the following error message:-
"Run-Time Error '2105' You can't go to the specified record."
If I debug the error it shows:-
Private Sub
Form_Open(Cancel As Integer)
DoCmd.GoToRecord , , acNewRec
End Sub
In an effort to make sure the original time sheet form load worked ok on the original tables, I changed the query to feed off tables in the local Access program and have no issues. It works perfectly. Also if I open the Timesheet Query that feeds the form I can add new records.However if I change the Query to look at the same tables on the SQL database I cannot add records. I can search all records from first to last but not add a record into the query. The add record arrow is greyed out on the query results.
Queries :: How To Query Number Of Records Not Common To Two Tables
Aug 9, 2013I have two tables, identical in terms of structure (number & names of fields etc.) but different in terms of content (records not necessarily the same, some may be common to both, some may only be present in one or other table)
I want to set up a query to count the number of records which are present in one table (Table A) but not in the other (Table B) using a common identifier field, and put the numerical result in a text box on a form (i.e. use the query as the control source for the text box)
I've managed to come up with the following which provides the list of ID's :
Quote:
SELECT tblA.CommentId
FROM tblA LEFT JOIN tblB ON tblA.Id = tblB.Id
WHERE tblB.Id Is Null;
Which will come in useful elsewhere but how do I adjust this SQL to only return the number of records? I think I have to use COUNT(*) somehow but I'm not familiar with it and not sure of the syntax?
Write A Query That Will Return Records From Multiple Tables
Dec 19, 2012I 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.
General :: Grouping Records - Query To Gather Information From Tables
Mar 7, 2013I have a query that gathers information from the tables in my database and returns something like this:
Code:
Field Pest
1 Insect1
1 Insect2
1 Insect3
2 Insect1
2 Insect3
2 Insect4
Field and Pest are fields in two separate (but joined) tables. Field exists only once in its table, and multiple pests are attributed to each field. I'm looking to create a table or query in which the values would be listed as follows:
Code:
Field Pest
1 Insect1, Insect2, Insect3
2 Insect1, Insect3, Insect4
The purpose for this is to create a legend for a map used by another program based on the information gathered in the database. I do have other criteria for which fields and pests show up in this query.
Queries :: Combine 4 Tables Data That Falls Within Dates - SQL Query Returns No Records
Oct 2, 2013I have a complex database app that has a form called from the main form. It requires two inputs: BeginningDate and EndingDate and I use a calendar picker for date selection. Using data assigned to a variable, I build the SQL query in VBA. The result is:
Code:
SELECT [1733_All Print Orders].[Application], Sum([1733_All Print Orders].[TotalImages]) AS SumOfCCPC
FROM [1733_All Print Orders]
WHERE [Application] = 'CCPC' AND [StatementDate] >= #9/3/2013# AND [StatementDate] <= #9/30/2013#
GROUP BY [1733_All Print Orders];[Application]
[1733_All Print Orders] is a defined query that combines 4 tables together and there are data that falls within the dates for CCPC. But the query returns no records.
I pasted the query to the query builder and using different combinations, I isolated that the [StatementDate] >= #9/3/2013# portion is what returns no records
To complicate matters even worse, prior to today, it worked. I made some adds and changes to another area of the application, but did not touch this code.
Tables :: Linking Records In One Table To Multiple Records In Another And Assign Percentage?
Nov 21, 2012I have a table (tbl Team Info) which contains names and codes for teams within my business (>400 records) and another table (tbl Process) which contains a list of high level tasks (30 records).
I need to create something where for each team name 9in tbl Team Info) I can map them to the tasks that they undertake (in tbl Process) and assign a percentage of time then spend on each task. Each team could map to several different tasks.
Getting Query Criteria To Select All Records Or Specific Records In Query Design Section?
Jun 16, 2014How can I get a Query Criteria To Select All Records or specific records in query design section.
I have a table that shows many departments with credit card transactions. I like to run a query to see specific department, or have an option to see all the departments when the query is run.
Tables :: How To Combine Records Of Three Tables Into One Table
Jul 30, 2013I have three tables. I want to combine all the records in these tables into one table. I need VBA code to do this. The first table is called down1, the second table is called down2 and the third table is called down3. All these tables contain the same fields so I don't think combining them will be a problem.
View 3 Replies View RelatedTables :: Deleting Matching Records From Two Tables
Dec 5, 2012I have two tables "Master List" and "Audit". I want to delete the records from the "Master" that match those in the "Audit". I am using Access 2010 and have used it often but have built very few queries.
View 3 Replies View RelatedTables :: Create Tables From Records In Field?
Sep 5, 2013I'm trying to create a table for every record I have in a field. I have two tables, one titled "Experiments" and another titled "Students". I want the new tables generated from the field EXP_NAME from Experiments. I need it to have seven fields, one copied from Students, five labeled Trial1 thru Trial5, and a final for a grade.
View 10 Replies View RelatedCan I Get An Update Query To Not Add Records To Tables Only Update?
Jun 28, 2005Hi Guys,
I have got a query that updates details from one table2 to table1, "Reference" is the primary key and this is what the query uses to determine which need updating.
It all works great but if table2 contains a record in "Reference" that is not in table1 i just want it to ignore it, currently it just seeems to add them.
Any suggestion guys & gals?
Many thanks
Tim
Tables :: Deleted Records In One-to-one Tables
Apr 25, 2014My db includes two tables, Employees and Users, both with a pk of EmpNum (Autonumber in Employees; Number in Users). Users is a subset of Employees, and in the Users table, the row source for EmpNum is a query of the Employees table. I had a relationship defined with a one-to-one between the two tables, but did not have Enforce Referential Integrity or Cascade selected.
The problem is, I deleted a record out of Users, but it also automatically deleted the related Employees record. Re-creating the employee was quite the task because all related records in several other tables also disappeared. I experimented by deleting the relationship between the two tables and then deleting another user, but it still deleted the related employee. How can this be? The two tables are no longer linked, other than the Users EmpNum lookup.
Tables :: Moving Records Between Tables?
May 7, 2014I have been tasked with creating a simple database for work.
Basically the table tracks the progress of various employment checks. So example headings are Name, CRB, Occupational Health etc. Now basically, once all the checks are completed, I need the record to be moved to another table named 'Live'.
Is there any easy way to achieve this, or will i have to manually move each record once the checks have been completed.
Copying A Set Of Records Across Several Tables To Same Tables With A Few Changes
Apr 8, 2015There is one main policy table, and a collection of related tables that store information associated with each of the policies (coverage amounts, agents, etc). What I'm struggling with though is where they renew every so often (most times it's each year, but some go three or more years). Because I need the database to show everything EXACTLY as it was on each policy term, even expired ones, I don't want to just edit the existing records when policies renew; I need to create a copy of the ENTIRE record (across all related tables), but with a new PK, and the ability for users to change a few items, such as the policy number and effective dates.
There are two keys that I use to identify policies: one is the PK, which is an auto# that will need to change, and the other is a small text ID set when the policy is first issued, in order to make sure that even when policy numbers change from year to year, we can always track the history, so of course that one will need to remain the same. If I have the user click a "Renew" button on the policy view form that goes to a small form that takes what changes I want to allow and converts those into variables, could I then use those variables in a query, and have it copy all of these related records, with the other tables taking the new PK from the main table to keep the overall policy record linked?
Records In Tables
Apr 19, 2005Is there a way that you can take information from multiple forms to create one record in a table?
View 1 Replies View RelatedTables :: Can't Add New Records
Dec 26, 2012Im having a problem with access 2010. I created a new database and created the relationships.However when i tried to create a form i noticed im not able to add any new records to my tables. Even if i don't use the form but directly on the tables the option for new record is greyed out.
View 5 Replies View RelatedCan't Add Records To Tables
Mar 20, 2013I have a patients databasein which i have a patients table that is connected with 4 other tables via PID (one to many).I the have a form in which i select my patient, and i have 4 individual buttons for 4 other pop up forms to enter or edit the information based on which patient i select.The problem is that whenever i add a record i get an error that i have a record related in my main Patients table. I am attaching the file.mydb.zip The problem comes through my main form.
View 5 Replies View RelatedUpdate Query Using 3 Tables: Source, Joining, Destination Tables
Apr 19, 2007I have some experience doing 'Update Query' using two different tables but I'm having a hard time doing an 'Update Query' using 3 tables.
I have my source table TP05XY with the fields 'Mark' 'Date' 'UTM_Edig' and 'UTM_Ndig'. Mark and Date are my primary keys (they together uniquely ID each record). I have my Observations table with the fields 'Mark' 'Date' and 'Obs_ID'. The last table is Locations with 'Obs_ID' 'UTM_E' and 'UTM_N'.
I want to update my fields UTM_E and UTM_N from UTM_Edig and UTM_Ndig. However, to do so, I have to go from my TP05XY table, through Observations table to update Locations table. Table TP05XY is joined to Observations through 'Mark' and 'Date' and Observations table is linked to Locations through 'Obs_ID' field.
I have tried a few options without success ... anyone knows how to do it?
Thanks,
Josée
Query Problem: Records Linked To Records
Oct 31, 2007Hi,
I was wondering if anyone would be kind enough to help me out on this problem I have.
I have a table:
---------------------------------
Source | LinkedTo
---------------------------------
A1 B2
B2 C1
B2 C2
C1 D1
C2 D2
C2 D3
B2 D4
A1 D5
Another table:
-------------------------------
Item | Load
---------------------------------
D1 2
D2 4
D3 6
D4 8
D5 10
So looking at the tables,
C1 = 2
C2 = 4+6 = 10
B2 = C1 + C2 + D4 = 20
A1 = 20 + 10 = 30
Here, there are 4 levels that I have to go into to get the grand total. If I did not know how many levels there were, how would I be able to do this through code? through queries if possible??
If anyone could help, that would be great! Thank you in advance.
Bear
Move Records Between Tables
Nov 22, 2005hiya, im new to this sql scene..!
i have an MS Access db containing a few tables of 'calls recorded' and 'finished calls'.
(i want to move all 'Finished calls' to a seperate 'Finished Calls' table)
anyway, the thing i want to do is to move records from Table1 to Table2, i am able to do this using the following code...
SELECT * INTO Table2
FROM Table1
WHERE Finished='Yes';
this code does work, however it does not delete the records that were moved out of Table1 - they are still there, its more or less copying and pasting them..
any help would be much appreciated!
Archiving Records From Two Tables
Nov 24, 2006I have a database for tracking tasks. each task may have associated documents for it which are kept in a separate table.
I have an archiving procedure which takes closed or completed tasks (from the main table) and appends them to an archive table but it leaves the associated documents in the second table, or if I enforce integrity it will delete them.
Anyone have a suggestion on how i can archive records from two tables at the same time I include the archive code for your information....
Many Thanks as always for your help and assistance
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''
' procedure archives closed or complete records to archive table and then '
' Deletes original records from source table '
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''
On Error GoTo Err_btnArchiveAdhoc_Click
Dim strMySql As String, strResponse As String, intRecordCount As Integer
Dim cnn As ADODB.Connection
Dim rstTemp As ADODB.Recordset
strResponse = MsgBox("By clicking Yes you will Archive any closed or" & _
vbCrLf & "completed tasks and delete the original records from the table", vbExclamation + vbYesNo, gstrAppTitle)
If strResponse = vbNo Then
Exit Sub
Else
Set cnn = CurrentProject.Connection
Set rstTemp = New ADODB.Recordset
' builds sql string to append closed or complete records from the original table into archive table
strMySql = "INSERT INTO tblArchiveAdHocTasks ( Seq_Number, Item_Type, TaskTitle, Task_Description, TaskActions, StartDate, TgtDate, ReviewDate, TaskLead, Status, LastUpdatedBy, estHours, Progress, PersIDAssocPerson1, PersIDAssocPerson2, PersIDAssocPerson3, PersIDAssocPerson4, PersIDAssocPerson5 )"
strMySql = strMySql + " SELECT tblAdHocTask.Seq_Number, tblAdHocTask.Item_Type, tblAdHocTask.TaskTitle, tblAdHocTask.Task_Description, tblAdHocTask.TaskActions, tblAdHocTask.StartDate, tblAdHocTask.TgtDate, tblAdHocTask.ReviewDate, tblAdHocTask.TaskLead, tblAdHocTask.Status, tblAdHocTask.LastUpdatedBy, tblAdHocTask.estHours, tblAdHocTask.Progress, tblAdHocTask.PersIDAssocPerson1, tblAdHocTask.PersIDAssocPerson2, tblAdHocTask.PersIDAssocPerson3, tblAdHocTask.PersIDAssocPerson4, tblAdHocTask.PersIDAssocPerson5"
strMySql = strMySql + " FROM tblAdHocTask "
strMySql = strMySql + " WHERE (((tblAdHocTask.Status)=6 Or (tblAdHocTask.Status)=10))" ' status 6 is closed and 10 is complete
DoCmd.SetWarnings False
'DoCmd.RunSQL strMySql ' runs append query
rstTemp.CursorLocation = adUseClient
rstTemp.Open strMySql, cnn, adOpenDynamic, adLockOptimistic
DoCmd.SetWarnings True ' turn warnings back on
' deletes records that have been archived above
strMySql = "DELETE tblAdHocTask.Seq_Number, tblAdHocTask.Item_Type, tblAdHocTask.TaskTitle, tblAdHocTask.Task_Description, tblAdHocTask.TaskActions, tblAdHocTask.StartDate, tblAdHocTask.TgtDate, tblAdHocTask.ReviewDate, tblAdHocTask.TaskLead, tblAdHocTask.Status, tblAdHocTask.LastUpdatedBy, tblAdHocTask.estHours, tblAdHocTask.Progress, tblAdHocTask.PersIDAssocPerson1, tblAdHocTask.PersIDAssocPerson2, tblAdHocTask.PersIDAssocPerson3, tblAdHocTask.PersIDAssocPerson4, tblAdHocTask.PersIDAssocPerson5"
strMySql = strMySql + " FROM tblAdHocTask"
strMySql = strMySql + " WHERE (((tblAdHocTask.Status) = 6 Or (tblAdHocTask.Status) = 10))" ' status 6 is closed status 10 is complete
rstTemp.CursorLocation = adUseClient
rstTemp.Open strMySql, cnn, adOpenDynamic, adLockOptimistic
End If
MsgBox "Export Complete ", vbInformation, gstrAppTitle
Exit_btnArchiveAdhoc_Click:
Exit Sub
Err_btnArchiveAdhoc_Click:
If Err.Number = 2501 Then ' runtime error as user cancelled delete part of query
Exit Sub
End If
MsgBox "'Error" & Err.Number & "'" & vbCrLf & Err.Description
Resume Exit_btnArchiveAdhoc_Click
End Sub
Find Records From 2 Tables
Jul 31, 2007How would one go about comparing 2 tables and finding those records that are not common to both tables? For instance, I have a table that I suspect some records have been accidently deleted by the user. I would like to compare that table to an older version of the table, and list only those records that exist only in the older version of the table.
Thanks.
Connecting Records From Two Different Tables
Apr 14, 2008is there a possibility to connect two different databases??? I mean is, I have a table called PERSON. under PERSON, there are fields called PERSON NAME, BIRTHDAY and ADDRESS. another table is called SEMINARS, under it are DATES FROM, DATES TO and TITLE.
I want to combine one of the records in PERSON to the 5 records in the SEMINARS. is there a possibility to do that???
to include in the information, there are 10 records in the PERSON and there are 75 records in the SEMINARS. and I want to add more records in SEMINARS in any of the records in PERSON in the near future.
Combining Records From 2 Tables
Jan 3, 2008Hello all,
I have two MS Access tables; table1 contains installing records (fields = serial, installdate) and table2 contains de-installing records (fields = serial, deinstalldate). Tables are structured as followed:
Table1:
serialX installdate1
serialX installdate2
serialX installdate3
Table2:
serialX deinstalldate1
serialX deinstalldate2
I'd like to combine the above two tables to get:
Table3:
serialX installdate1 deinstalldate1
serialX installdate2 deinstalldate2
serialX installdate3 null
Is it possible? Right now, whenever I joined the two table on serialX I got 2 deinstalldate's for each installdate, e.g.:
serialX installdate1 deinstalldate1
serialX installdate1 deinstalldate2
serialX installdate2 deinstalldate1
serialX installdate2 deinstalldate2
..........
Thanks in advance for your time and help! I'd appreciate it!
Best regards,
Andy
Can't Add New Records When Tables Are Joined
Apr 18, 2005I have two tables in my database
Agents
Zip Codes (linked table)
If I create a query and then a form off the query that includes fields from both tables - Access will not let me add new records.
Ultimatley what I am looking to do is have a zip code field in the Agents table that I fill in and then it will automatically look at the zip codes table and then fill in the zip code, county, & state in a different area of the form.
I tried using fields from both tables and tried a query using fields from both tables and it will not let me add new records.
I have a relationship between the fields in both tables.
What am I doing wrong. I know this can be done.
Any help would be great!!!