Insert Data In Two Related Tables Simultaneously
Dec 11, 2004
I want to insert a huge number of data (Customer and their adress) into 2 related tables in Access. Each table in access is related by some kind of ID (Autonumber-Primary key). The first table is CUSTOMER , the other one is CUSTOMER_ADRESS . Access relate each Customer to his Adress, so if I import data into one table and then import data into another table how would the database know that all the data pertains to the same person. How to insert the new list of customer and adress without using a form?
Thanks for your help.
View Replies
ADVERTISEMENT
Apr 3, 2007
I have a form displaying the 11 fields of the parent/primary table using a selection from a combo box. I am using queries and vba code modules respective to form, combo box and command buttons. I have initial code that uses the two fields from the combo box selection to append same to a new parent/primary archive table. I now want to add to the append SQL the remaining fields to the parent/primary archive table. When I add the second sql string for the remaining fields to the same procedure and execute I keep getting 'null in primary key'. If I copy the primary record and paste same into the archive table it works.
Private Sub Command26_Click()
On Error GoTo Err_Archive_Primary_Click
Dim strSQL As String
Dim strSQL2 As String
strSQL = "INSERT INTO ARC_289325045 ([Survey Point ID], [Survey Area Detail], [Date On Site]) " & _
"VALUES ('" & Me.cboAreaDetailDate.Column(0) & "','" & Me.cboAreaDetailDate.Column(1) & "'," & _
"#" & Me.cboAreaDetailDate.Column(2) & "#)"
CurrentDb.Execute strSQL, dbFailOnError
'strSQL2 = "INSERT INTO ARC_289325045 (RecordID, UnitID, UserName, [TimeStamp], [Survey Point - Area], Measurement, NewArea, [EXIT Form] ) " & _
'"SELECT FORM_ID_289325045.RecordID, FORM_ID_289325045.UnitID, FORM_ID_289325045.UserName, FORM_ID_289325045.TimeStamp, FORM_ID_289325045.[Survey Point - Area], FORM_ID_289325045.Measurement, FORM_ID_289325045.NewArea, FORM_ID_289325045.[EXIT Form] " & _
'"FROM FORM_ID_289325045"
'CurrentDb.Execute strSQL2, dbFailOnError
Exit_Archive_Primary_Click:
Exit Sub
Err_Archive_Primary_Click:
MsgBox Err.Description
Resume Exit_Archive_Primary_Click
End Sub
The next step is to do the same for the child table and append related records to the child archive table.
View 4 Replies
View Related
Apr 3, 2007
I have a form displaying the 11 fields of the parent/primary table using a selection from a combo box. I am using queries and vba code modules respective to form, combo box and command buttons. I have initial code that uses the two fields from the combo box selection to append same to a new parent/primary archive table. I now want to add to the append SQL the remaining fields to the parent/primary archive table. When I add the second sql string for the remaining fields to the same procedure and execute I keep getting 'null in primary key'. If I copy the primary record and paste same into the archive table it works.
Private Sub Command26_Click()
On Error GoTo Err_Archive_Primary_Click
Dim strSQL As String
Dim strSQL2 As String
strSQL = "INSERT INTO ARC_289325045 ([Survey Point ID], [Survey Area Detail], [Date On Site]) " & _
"VALUES ('" & Me.cboAreaDetailDate.Column(0) & "','" & Me.cboAreaDetailDate.Column(1) & "'," & _
"#" & Me.cboAreaDetailDate.Column(2) & "#)"
CurrentDb.Execute strSQL, dbFailOnError
'strSQL2 = "INSERT INTO ARC_289325045 (RecordID, UnitID, UserName, [TimeStamp], [Survey Point - Area], Measurement, NewArea, [EXIT Form] ) " & _
'"SELECT FORM_ID_289325045.RecordID, FORM_ID_289325045.UnitID, FORM_ID_289325045.UserName, FORM_ID_289325045.TimeStamp, FORM_ID_289325045.[Survey Point - Area], FORM_ID_289325045.Measurement, FORM_ID_289325045.NewArea, FORM_ID_289325045.[EXIT Form] " & _
'"FROM FORM_ID_289325045"
'CurrentDb.Execute strSQL2, dbFailOnError
Exit_Archive_Primary_Click:
Exit Sub
Err_Archive_Primary_Click:
MsgBox Err.Description
Resume Exit_Archive_Primary_Click
End Sub
The next step is to do the same for the child table and append related records to the child archive table.
Edit/Delete Message Reply With Quote Quick reply to this message
JJKramer
View Public Profile
Send a private message to JJKramer
Find all posts by JJKramer
Add JJKramer to Your Buddy List
Sponsored Links
View 1 Replies
View Related
Oct 22, 2014
I have to make a Costing System but for that I need to enter our Expense details in database according to Fiscal year and months.
I need a table for Fixed expenses and one for Variable Expenses and then I need one or more Forms to update data in those tables. Now I've created a table with Fixed Expenses. I have to update Year and Amount in that. Now it is only letting me one entry per Expense.
I want multiple entries for one Expense say 'Advertising' for different years. I'm thinking may be I need to make more than one Table, may be one for Expenses with ExpenseID and other for Years with Year and YearID and the third one for Amount with columns Amount, Method of Payment, Date and Notes. I did tried this but I think I'm not creating proper relation may be because its only updating for one year.
I'm using Access 2003.
View 2 Replies
View Related
Sep 27, 2012
I have a built a database to record and collate info about clinical trials that are run within my department. I have one large table [Trial Info] which contains all trial info and have created a second and third table [Milestones] & [Comments] to collect dates of various milestones and a notes that occur throughout the course of a study.
The milestones (dates) are recorded in the 2nd table and the comments are recorded in the 3rd table.
All data is entered by staff on forms. The first form captures basic info about the study, once this has been entered, the idea is to click a button to open the second form to document the trial milestones, this form also contains a subform for listing any free text comments required during the life of the study which are stored in the 3rd table.
I have setup the relationships between tables as best I can.
The main table with basic trial info is the parent table, linking the primary key to the foreign key in the second table, the foreign key of the third table is related to the primary key in the second table so these tables should be looking up the correct clinical trial as far as I can tell.
The form containing data from the main table has a button which I have set to open the Milestones & Comments form, and I have set this to open to the record that relates to the record in view in the primary form. The primary key is related to the foreign look-up key in the milestones table.
This opens the form fine, however this is not updating the foreign look-up key, this remains '0' which I assume is the cause of the following error when I try to save changes to the record
Quote:
You cannot add or change a record because a related record is require in table 'Trial Info'
How do I trigger the foreign key to update to the primary key so that the first time a milestone page is opened for a new study it creates a matching record?
View 4 Replies
View Related
Jul 24, 2006
I need help. I am not sure if this is a form issue or a logic issue i have a db for tracking students the classes they take and their notetakers for classes. Each class is most likely unique to each student but sometime there are multiple students in each class that need a notetaker and there should only be one notetaker per class. I have 3 tables and they are all linked through a unique iD called CRN (number).
I am looking for a way to add a student to a class and create a class at the same time in one form. I am trying to limit the redunancy by adding a class each time and then adding a student to the class.
Any suggestions would be great. I have gone through 10+ redesign and am just at a loss on how to accomplish this.
View 1 Replies
View Related
Sep 12, 2013
I have a form within my database in which the user will enter data which will go into 2 separate tables. These 2 tables (Job and Client) are related. At the moment I have a subform in which the user enters Job information, and the main form where the user will enter client information.
The problem being is that the 2 sets of data do not associate themselves with each other, despite being related (The Client will be related to a job number. A client can have many jobs but a job can only have one client etc). It has to be done manually in the table which is not ideal as the DB will be split and rolled out to users via Access Runtime. I have been working on this DB for a while now and the problem is most likely right in front of me but I cannot see it!
View 4 Replies
View Related
Mar 3, 2014
I have a database I made to store a list of users and information about each user.
I have a UserDetail table, languages table, previous experience table, current experience table.
UserDetail table as follows:
UserID (PK)
First Name
Last Name
Full Name (calculated)
Department (using a multi select combo box. There are 3 departments and some people are in both).
I need to do the same thing on each table and each table is very similar so I'll just list one. This is the Previous Experience table:
PreviousXPID (PK)
Previous experience (e.g. IT, Marketing, Chemistry)
I have three junction tables. One for each Previous Experience, current experience and language table. The Previous experience junction table has:
ID (PK)
UserID
PreviousXPID
I created a relationship between the userdetail table and the junction table then the junction table to the Previous Experience table.
Now what I'd like to be able to do is edit people to add previous experience, current experience and languages to each. I already have a form that lets me add a new user to the database. It's just based on the UserDetails table. If I could have a way (perhaps using 3 multi select list boxes. One box for previous experience, one for current experience and one for languages) on that form to add the other details to that person, that would be ideal. I'd like to create a new user. E.g. Joe Bloggs in department 1 who speaks Dutch, used to have Marketing, Chemistry and aeronautical experience and now works in IT.
I can create the new user by putting last first/last name and selecting the department. But to add experience or languages, I have to go into that junction table and add the numbers myself. e.g. user 1, has experience 1,3,4 and 5. I'd like to see the name that relates to the ID and be able to select it from the list in the table I have.
View 11 Replies
View Related
Nov 17, 2014
I currently have a pharmaceutical lot database set up in the following format:
MFGData (table w/Manufacturing Info)
QAData (table w/ Quality Assurance Info)
QCData (table w/ Quality Control Info)
PASData (table w/ Process & Analytical Science Info)
SCData (table w/ Supply Chain Info)
[Code] ....
Each table has a corresponding form for data entry in each area. The tables were subdivided in this way in order to limit each department's ability to edit the data of other departments. The only field common to each table is the drug lot number, or "Lot #" (which is the primary key of each table).
I wanted to make it so that when Manufacturing enters a new lot number on frmMFGData, it automatically creates that lot number in the other 4 tables. This process mirrors our actual real world business process, where drugs are manufactured and assigned new lot numbers by our manufacturing team, and then other departments simply reference those numbers when doing their part.
To accomplish this, I went ahead and set up 1 to 1 relationships between the various tables using their "Lot #" fields, establishing referential integrity and enabling cascading updates. However, when I attempted to enter a new lot number into frmMFGData (the manufacturing form), it didn't seem to appear in any of the other tables. If I edit an existing lot number and change it to something else, the change does carry over to the other tables, so I know that the cascading updates are working in some capacity.
If cascading updates cannot "cascade" new records, then is there any other way to accomplish this?
View 13 Replies
View Related
Aug 7, 2014
I'm trying to create a system where if I enter data into one field it will automatically appear in the corresponding field in another table. For instance if I enter the values 10,12,15 into a field called QID in table A I would like this to appear automatically in a field called QID in table B. Both tables are related and I have enforced referential integrity and 'cascade' options but this still doesn't seem to allow one table to automatically update another. My ultimate aim would be to have a form that you filled in data for the field QID once that then propagated to both Table A and Table B.
View 2 Replies
View Related
Jul 16, 2013
I am trying to create a form in which users can save new supplier data to a database. I am using two insert statements which insert similar data in to two similar tables. The insert statements appear to have no problems and no errors are produced upon execution yet the new data doesn't save into the tables. Here's the code;
Code:
Private Sub Command14_Click()
Dim sqlstr As String
Dim dbs As Database
[code]...
View 11 Replies
View Related
Aug 28, 2014
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.
View 1 Replies
View Related
Aug 9, 2006
Simple Question: Is there a way to connect two fields in different tables, so that when I enter information in one table, it automatically fills in the appropriate field in the new table?
I would be using a form to enter the data. When I enter information in the form, I want it to automatically update the corresponding fields in the sub-forms, even though they're not displayed in the form. This is especially important when I'm adding new records--the linked (parent/child) fields MUST be updated, of course, or the records won't be linked when I open the record again.
Thanks,
Siena
ETA: Okay, I can see that it does automatically fill in the primary key field, but I need it to fill in at least one other field as well.
View 3 Replies
View Related
Jan 14, 2013
I have created a table that acts as a header for my data and a second table that acts as line item data. What I need to do now is add a second child table that uses the line item data as its parent table and stores associated line items for each record. Is this possible?
This is a skeleton view of what I'm going for:
Master Table:
tlbAuditReportHeader
- AuditDate
- AuditArea
- Auditor
Sub Table:
tblDiscrepancy
- Discrepancy
- CorrectiveAction
- ActualCompletionDate
- VerifiedDate
- Notes
Sub table to Sub Table
tblFollowUp
- FollowUpDate
- AssignedTo
- SpokeWith
- EstimatedCompletionDate
- Notes
Sometimes tasks change hands or are pushed back depending on work load. It would be nice to be able to track something like this.
View 1 Replies
View Related
Jul 16, 2014
I'm trying to split a table up because I now realize it won't be able to do what I need in the future. It wasn't designed properly at the outset, and I'm trying to correct it now.
This is a database of pregnancies and deliveries.
The single table does not cater well for multiple pregnancies (twins, triplets, etc), and also I foresee problems when mothers come back in future for another delivery.
Therefore I am trying to separate data into 3 tables: Mother, Delivery and Baby.
This is because each mother can deliver more than once, and each delivery can have more than one baby.
I have set up a trial database, with just a few fields in each table to see if this works. An screenshot of the table relationships is attached.
The primary key of the Mother table is linked to the Delivery table, and ditto the primary key of the Delivery table and the Baby table.
I'm not sure how to migrate the data over, in terms of the primary keys of each table, because in the new tables, these should be an AutoNumber field, so that they are unique numbers.
View 14 Replies
View Related
Aug 27, 2013
I am having an issue where a small percentage of my records do not follow my related Tables.
ex: 3 tables
tblEmployee
-intEmployeeID
-intWorkCenterID
tblWorkcenter
-intWorkCenterID
tblManager
-intManagerID
-intEmployeeID
-intWorkCenterID
where:
tblemployee.intworkcenterID -- tblworkcenter.intWorkcenterID -- tblManager.intWorkCenterID
Basically I have my general employee information in the tblemployee table, the workcenter information in the tblworkcenter, and the manager of the Workcenter in the tblManager. This is correct for 99% of the employee population. however, there are a few employees whose manager isn't the 'general' manager for the employees assigned workcenter.
How I can adjust an individual employees manager, while maintaining my structure? or would i have to go back and add the managers ID to a field in the tblEmployee Table?
View 7 Replies
View Related
Jul 2, 2010
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.
View 2 Replies
View Related
Aug 6, 2013
So I have a form, it has a few fields from a related table in it, related by the name. I click 'new' on the form, but when I click save Access complains about the related table does not have a matching record. How do I have it create a new record?
View 2 Replies
View Related
May 15, 2006
Hi everyone!
My names james, im 18 and im a data manager for a school.
My schools database stores student data such as names, classes and exam grades. I have a table containing each students personal details such as name and address. Each pupil is assigned a unique PupilID which is the primary key. I then have a number of other tables linked to it with one to many relationships. Each of these tables holds a different set of exam results. The tables are linked through PupilID. The primary keys for the other tables are auto numbers.
The problem is this: when i add a new student to the Students table i cannot get their PupilID to automatically be entered into the other tables containing grades as part of a new field. How can i make this happen?
Thanks.
View 9 Replies
View Related
Jan 22, 2007
Ok due to user error (and my own fault for not archiving an old database) we now have two databases which have had data entered into with the same table structure.
The table structure we have at the moment is with the related field in []:
Propertytbl[Propertycode] is related to Systemtbl[sysID] which is related to Assessmenttbl.
There is also another Temperaturetbl linked to Systemtbl by [sysID]
The problem i have is sysID is an autonumber. I can't simply paste in the difference in data as the assessmenttbl records and Temperaturetbl records will point to a different system (as the sysID will change in the Systemtbl data)
Can anyone think how i can do this either through using queries or programmatically so that i don't have to enter 5000 records manually.
I've tried using append queries but this produces the same affect as above.
Many thanks
Matt Collins
View 1 Replies
View Related
Apr 2, 2007
I have a combo box in my main form that gets its list of physician names from tblPhysician. When the physician is chosen and the record saved, it saves the PhysicianID to my main table, tblOncReg.
When I delete a physician, any record that had that name chosen continues to have that name in the record despite it being deleted from tblPhysician.
I thought that setting relationships would solve it, but when I select "Enforce Referential Integrity" and "Cascade Update Related Fields", I get an error saying "Invalid field definition 'ID' in definition of index or relationship.", and it doesn't set the relationship.
Would setting the relationship solve that problem?
View 4 Replies
View Related
Sep 2, 2007
In a case where two tables are related, can the field names which relate them be the same? Or is it necessary to use different field names?
Robert
View 4 Replies
View Related
Jan 26, 2006
whats the best way of appending tbl_orders and the related data in tbl_order details to the two identical archive tables when the "COMPLETE" column is "YES" in tbl_orders
View 1 Replies
View Related
Jan 12, 2007
Hello, I apologize in advance if there have been lots of questions like this, but the search won't let me use AND as a search term!
I have two related 1 to many tables. The parent table contains clients, and the child table contains categories, each client can have many categories. (i originally intended to have the categories be columns in the client table, in which case what i want to do is easy, however, then it becomes a nightmare when the user wants to add a new category hence the related situation described.)
Is there an easy way to find all the clients who have both category 7 and category 10? I can do it writing a query to find all the 7s, then another to find all the 10s, and a third to find those which have both. I am hoping there is an easier way, as i need to give the user a way to search via categories in whatever combination they fancy. The OR's i can do easily it's the AND's that are causing the problem.
The only idea i have at the moment is to make a temp table with the the clientid, and a long field holding each of the category ids, seperated by commas, and then searching using like "*7*" and like "*10*".
Does anyone have any better ideas, i'm hoping i'm missing something really obvious......
Thanks, Anna
View 1 Replies
View Related
Aug 16, 2005
Hi,
I have a database with a couple of tables. All of which have a 1:1 relationship to another table, patient:
- patient (id (auto_increment), name, address, etc)
- nutrition (patient_id, some, stuff, etc)
- sleep (patient_id, foo, bar)
The patient_id in nutrition and sleep is a primary key and corresponds with the id of a patient (my 1:1 relationships)
I have a couple of forms. One for each table actually. In this form the user should be able to add/modify a patient's data. These forms are not to be used by themselves.
I have another form(the main form), which has a tab-control element on it. There are 3 tabs on this and on these tabs the 3 different forms are displayed.
Now for the problem:
- when I add a new record to the database using the main form, there is no problem when adding a new patient. Woohoo! When I switch to another tab, , however, the problems start: I can enter new information, but when I leave the tab I get an error: "Index or Primary key cannot contain a null value." Blech.
I want Access to realise that it should use the id that is given to the patient in the first tab, but it seems it cannot get it automatically. (I am not sure, but I think the problem didn't occur at first, so I think I may have done something which is causing this problem.)
Any help here is appreciated.
PS I was unable to find any posts on this forum that can help me with this. The closest I could find was: http://www.access-programmers.co.uk/forums/showthread.php?t=39933
View 4 Replies
View Related
Sep 9, 2005
I have a table of 'things' and two related tables, type and subtype. Each thing has a type and a subtype. The types table contains simply Index (autonumber) and Type (text). The Subtype table Index (autonumber), type (number) and subtype (text), where index and type form the primary key and type is a foreign key to the types table. Thus each type has its own sub-set of subtypes.
In the design of the Things table I have set the lookup for Type and Subtype to be a dropdown, but of course the subtype drop-down shows all subtypes, not just valid ones. If I select a subtype that is incorrect for the selected type then of course I get an error, but it would be nice to set the query in the subtype lookup's Row Source to just display the valid subtypes. At the moment it just says "SELECT index, type FROM subtype" How do I put in a WHERE clause that references the Type field for the current row: "SELECT index, type FROM subtype WHERE type=<type selected in current row>"?
View 3 Replies
View Related
