Help With Adding Data To Multiple Tables

Oct 24, 2005

hi, i am new to access and struggling so any help would be great...

i have a db with 3 tables. students contains a Primary Key ('studentid') and then 'firstname' and 'lastname' fields. The two other tables (contactdetails, coursedetails) have 'studentid' as a Primary Key and then they each have other fields.

I set up relationships between the tables (1 to 1) but i would now like to create a form that will allow me to enter data to all three tables at the same time. (this would be ideal as it keeps the data normalised) if this isn't possible i would like to create a form that adds the 'studentid' record to each of the tables.

let me know if you need anymore details but if someone can point me in the right direction it would be cool.

cheers

View Replies


ADVERTISEMENT

Adding Data To Multiple Tables With Subforms

Mar 2, 2015

I am new to access but I am familiar with SQL databases. I have two tables: one for project data and another for project leader data. These tables are linked by a third table that relates projectIDs to leaderIDs. I am currently working on making a form that will go through each project and display the people involved in each project in a subform. I pulling the data from the the leader table that matches the the projectID with a query.

I would like to be able to update the information in this subform directly (which I can currently do) and be able to add new project leaders to a project. What is the best way to add the new leader to this? If a person is already in the database how do I add them with the form without re-adding new information to the table?

View 3 Replies View Related

Tables :: Adding New Customers To Multiple Tables At Same Time

Mar 13, 2014

I am new with Access and I am setting up a data base that will have 16 tables and each table with have over 3,000 customers. The reason for 16 tables is that we will be inputting information on a monthly basis for each of these customers (12 of the tables) and I also have 4 tables that will be perfomring different "tasks" for each customer (one is just to keep the notes we make for each customer, one will be to show any billing done, one is going to be a summary of the entire year along with some additioanl information not entered elsewhere and the final one is our customer information table).

The customer account number is the primary key for all of my tables.I want to know how I can add a new customer (either using query or form) at one time that will populate all the tables. Right now I have everything set up as tables but I can set up queries if need be.

View 14 Replies View Related

Tables :: Adding Record To Multiple Tables

Aug 19, 2015

I am using Access 2007 on my front-end and SQL Server 2014 on the back-end. I have a table of Car Dealers and a table of contacts at the dealerships. These tables are SQL tables. The user can select a dealer and then see everyone that works at that dealership. When they look at this there is a field called Email. This is a hyperlink that they can click on to open Outlook and send an email. The table called DealerEmails is an Access table. My table layout is:

Dbo_Dealers
------------------
ID (PK)
DealerName
DealerAddress
DealerCity
DealerState
DealerZip
DealerPhone
ModifiedBy
ModifiedDate

Dbo_DealerContact
---------------------------
ID (PK)
LastName
FirstName
Postion
DealerID (FK)
ModifiedBy
ModifiedDate

DealerEmails
-----------------
ID (PK)
DealerContactID (FK)
Email
ModifiedBy
ModifiedDate

Now I'm trying to write the code to add a new contact. My code works but I need to obtain the AutoNumber from When I add a new record to the table dbo_DealerContact. My code is:

Code:
Option Compare Database
Option Explicit
Dim adoDealerContacts As New ADODB.Recordset
Dim daoDealerEmails As DAO.Recordset
Private Sub cmdSave_Click()

[Code] ....

I tried to add Me.Dirty=False, but this still returned a value of 0 into my variable intDealerContactID.

I also tried moving intDealerContactID = .Fields("ID").Value outside of the With block.

I'm aware that there is a command in SQL @@Identity. But I'm unsure how to use it in this context.

Is there a way to get the primary key from dbo_DealerContacts so I can insert that into my Emails table?

View 2 Replies View Related

Adding Multiple Tables From Different Databases..

Dec 8, 2006

Hello,

I have around 10 mdbs and in each I have 4 tables, I want to add all the tables from 10 mdb to one mdb, finally I will have 4 toal tables, right now I am using a appendquery to add tables. Is there way using VBA I can do it on the form and with one click of a button I will be able to add. Thanks for any info..

View 1 Replies View Related

Tables :: Adding One Value To Multiple Records

Jul 31, 2013

I am new to Access here, and looking to start up a database that will contain data for thousands of footballers.

I have on one table ("Directory"), all 5000+ players, with nationalities, names etc, and on another ("Flags"), I have all nations of the world and their respective flags (as an attachment).

How do I add the flag of a players nation into the "Directory" table for each record? Is there a quick way? I've tried "Lookup" but it won't let me do it for images, I've tried "Relationships" but without success.....surely I don't have to go through the "add attachment" process for all 5000 records?

View 3 Replies View Related

Problem: Adding A Record Across Multiple Tables.

Feb 27, 2007

I have three tables setup in a 1 to 1 relationship using a Autonumber field as their primary keys. I created the seperate tables to group related data together and reduce the tables sizes in general. So think of it as one main table and two tables of extended data.

I need to be able to add a record to table 1 and have it use the same autonumber to reserve that row for data in tables 2 & 3 so that my relationships stay in sync. I want to update the data in tables 2 & 3 later when that information is available.

I have been breaking the database testing some things out like building a form from a select query using all three tables. When I add the record to table 1 nothing is added to the other two. Things get messy on subsequent attempts to add data to any of the tables.

I have avoided this problems in other tables by not using the autonumber datatype, however, I don't have a good unique key that can be manually entered and kept accurate, so autonumber solves that problem in this case.

My VB is very rusty but I'm thinking there should be a way, before update, to capture the autonumber being used for the new record and write it to the other tables. I'm thinking that would be one solution, but I can't seem to get started on that code. Any help or examples would be great.

Thanks,
Jim

View 4 Replies View Related

Adding Records To Multiple Tables Using One Form

Sep 20, 2004

OK I'm totally lost again.

What I want to do is;

Use one form to add records into several tables.

I have seperated my data into seperate tables and set up one to many relationships.

tblincident
--------------
Key#
Report#
Time
Date
Location

tblPerson
----------------
Key#
Last Name
First Name
Middle Name
Address
etc.

tblItem
----------------
Key#
Make
Model
Serial#
etc.

What i want to do is use one form to enter all this data into the different tables.

There can only be one record in tblIncident that can match multiple records in the other tables.

i.e. for each record in tblIncident, I want to be able to have the capability to have as many persons related to that incident as I need.

I was thinking about setting up a tabctl for the data that has to be entered for tblIncident and then change the property setting for it to go away and then have the next tabctl appear in it's place for entering data into the next table. The problem I am having is that apparently I can only have one table as the control source for the form.

So next I tried using subforms, but that isn't working either. I can't figure out how to make the subform appear in a specific place on my form, sized in specific dimensions I want where I want.

I am just starting to learn about this relationships stuff and it's kind of difficult. How do you make it where you can do this from one form? Do I need to make a query of some sort?

Any help is appreciated.

View 1 Replies View Related

General :: Adding Record To Multiple Tables

Jun 6, 2013

I have a main table with subject id and other fields. I also have 16 other tables which have questionnaire information at 17 different timepoints. In those tables, the subject id is the only connected field between the main table and 17 different years. When I add a new record (by subject id) on the main table, I want it to add that same subject id to the 17 other tables because that subject id will eventually get all 17 years worth of data. But even using subforms, it will not add a new subject to the other tables unless I add some sort of information such as (date received) into the subform.

View 6 Replies View Related

Tables :: Adding Multiple IDs In A Table With Time

Mar 11, 2014

I want to make a table where in I have to input atleast 10 numbers in one reference ID which there is a start time and end time.

A form with inputing one reference ID and then adding multiple numbers at once and saving the data with a start time and end time. Is it possible to capture the time for that specific reference ID?

How to do it or a sample tracker on measuring it.

View 14 Replies View Related

Adding A Field To A Form Using Multiple Tables (And A Combo Box)

Jun 15, 2006

Hello!

It's been a while since I've asked a question here, but I can't seem to figure this problem out.

I am setting up a form using data from 4 different tables, all related, and I can't figure out how to add fields that I didn't initially add during the wizard set-up. I used the wizard because it's easier for me to do that and then go in and make the changes I want to make.


When I get data from just one table, all of the available fields from that table are in the "Field List" no matter what fields I chose to include in the wizard process. That's good. But when I get data from more than one table, only the fields I initially chose in the Wizard process are showing up in the field list. I can't figure out how to add a new and different field from one of those tables.

The way I "solved" this problem the first time was to start over, creating my form from scratch--but now that I've done a lot of work, it just occurred to me that I will need to add 2 more fields. (I actually don't even have those fields in a table yet, but I will add them later.) So, this will be an ongoing problem. Since this form will get much use, by several people, I don't want to have to re-create it every time I want to change something.

So, is this possible? How can I add another field?

Also, while I am at it... A combo box was working beautifully to look up specific records when I was gathering information from only one table. It isn't working anymore.

Thanks in advance!

-Siena

View 8 Replies View Related

Tables :: Adding Multiple Fields With Blank Section

Mar 27, 2014

I am creating a table in access 2010 for my consumable and bench stock report. I made a 12 fields which I name it the month of the year and another 1 field to add the total disburse materials in one whole year. I did this formula to add the 12 fields

[Jan]+[Feb]+[Mar]+[Apr]+[May]+[Jun]+[Jul]+[Aug]+[Sept]+[Oct]+[Nov]+[Dec].

But the problem is its just adding the complete consecutive months that I disburse and the row with blank section the total disburse doesn't show on the total disburse for the whole year. I try to use the code =Nz([Total Disburse],0) but it shows on the screen i cannot be used in calculated column.

View 4 Replies View Related

Adding Records Into Multiple Tables At A Single Moment

Dec 2, 2011

Supposing you have 4 Tables

1.)User
2.)Admin
3.)Teacher
4.)Pupil

Now the "User" table has the ff. fields: ID(auto num),FName,MName,LName,User,Pass,Type.

Now what I want to do is, if I add a new a record in the table "User" and if the record has the data "Admin" in the field "Type" then the record should also be saved in the table "Admin" but if the data is "Teacher" or "Pupil" then it should be also saved in their respective tables.

Question is: Is that possible? If yes, then how??

View 5 Replies View Related

Multiple Users Modifying And Adding Data

Sep 11, 2013

I have created a database for 2/3 users (will progress to more once the system has been smoothed over) who will have the db.file open from 9-5 and will be consistently adding and changing data within 2/3 forms. Most of the time but not always a message comes up saying,

"You do not have exclusive access to this database at this time. Any changes you make may not be allowed to be saved later"

I can understand that it can create data corruption if multiple users are allowed to edit and modify the same data at the same time but surely a database can't be that limited. I thought that splitting the database into a front end on a local server and then a back end on my computer would solve the problem but everyone who is using the front-end still experiences the same problem.

How to be able to overcome issues with multiple users access the same forms and tables? Even when another user is editing one form and another is editing a different form the message will still appear.

View 9 Replies View Related

Import Excel Data From Multiple Sheets Into Multiple Tables In Access

Aug 25, 2012

I would like to know which way is the best way to import excel data from multiple sheets in to multiple tables in access.

For example data from Sheet1 -> Table1, Sheet2->Table2, Sheet3->Table3 etc...

I have tried using this:
Cmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel5, "Table1", "C:Importedfile.xlsx", True, "Sheet1!"

Ironically, data from Sheet2 and Sheet3 seem to be properly imported in to table2 and table3, but some of the data from Sheet1 seems to be missing in Table1 after import.

Any other ways to import the data?

View 1 Replies View Related

General :: Adding New Data Into Tables (via Form)

Jan 31, 2013

I have 2 tables.

Table one is for person ID
Table two is for number of absences

I created a one-to-many relationship with the update/delete option selected. It didn't work when I wanted to add new data into the tables (via form). Then I tried a many-to-many relationship (with a third table) but it doesn't seem logical to do so, as I only need to add particular data into one table, but need a field from the 1st to differentiate records.

(table 1 = one record per person, table 2 = multiple records for 1 person)

View 2 Replies View Related

Modules & VBA :: XML Import - Adding Data To Tables

Apr 8, 2014

Looking for (semi) generic piece of code that knows how to handle (semi) random XML files and structures, preferably creating tables and columns on the fly...

Or some code that will read an XML tree and genericaly will add the data to the tables it can find?

View 2 Replies View Related

Tables :: Adding Field To Table And Updating Data?

Dec 1, 2013

I have a table in my DB called, devices. This contains all the information about various devices we have deployed in the field.

These devices are also contained in 2 other separate MySQL DB's.

What I need to do is add 2 additional field to my access table for the DeviceRecno and DeviceID of the same device from the MySQL DB's.

Adding the field is easy, but I cant think of a way to enter the recno and ID from the other DB's without typing them in manually for each one.

The common between them all is the serial number of the device, and I can get a list of serial numbers, recno's and ID in an excel sheet.

like a vlookup in excel to easily populate all the existing records with the recno and id's from the other db's?

When I created the access system there was no intention to link it to the other DB's for any reason, but that has now changed due to a lot of reasons.

View 4 Replies View Related

Tables :: Linked Table - Adding Entries To Each Row Of Data

Jul 2, 2014

I have a table that is linked to a survey and pulls data from the limesurvey backend.

The user would like to add a local field, so they can add entries to each row of data.

Is that possible ?

View 1 Replies View Related

Tables :: Adding More Data In List Sorted In Numerical Order

Sep 27, 2012

I have a list of 22 soccer players with their weights sorted from heaviest to lightest in numerical order. I want to add further players and have the list reflect their standing each time it changes i.e. Col 1 Player ID, Col2 FirstName, Col 2 SecondName, Col 3 Mass:90 Kgs Col 4 Position In group: 1

A new player registers and is 95Kg.....he becomes #1 and the rest go down etc. Can this be done?

View 6 Replies View Related

Data From Multiple Tables

Jul 22, 2005

Hi All,

I have designed a form that holds data from a table called Purchase, it also has got 3 more fields that are coming from another table called Master. These 3 fields, Code No, PartNo and Description are same in both tables. All i need is a way to bring the data of the stock table on purchase form. As soon as i enter code number in code field, it should check in stock table if the same code exist, if yes then it should bring the all 3 fields in purchase form and display.

Hope someone out there will return my call.

Warm Regards,


Darno

View 1 Replies View Related

Addidng Data To Multiple Tables

Apr 26, 2006

I have a form, created from a query. THe query contains an links about six tables using an AutoNumber ID.

OK, so when users enter their data into this form, I would like the autoincremented number to increase in each of these tables. The catch is that when the user enters data he/she does not have to enter data into every field.

So, some of my tables do not auto-increment and some do. How should I go about resolving this so that when someone enters new data, each table's ID is autoincremented no matter if that table contains information immediately or not? Please Help.:confused:

View 1 Replies View Related

Populating Multiple Tables With Data

Nov 8, 2004

I haven't used access for a long time so I am very rusty.

I have a few tables but I'll just use two for an example:

Table A
Ticket No
Part No
Defect Code
WorkCenter No
Clock No
(Ticket No is Primary Key)

Table B
Clock No
Supervisor
Employee
(Clock No is Primary Key)

Is there a way for me to create a form, that when Clock No is entered it will be placed in both tables?

Thanks in advance.

View 2 Replies View Related

Drawing Data From Multiple Tables

Mar 17, 2007

I am a new user to access and are therefore seeking the assistance of a more experienced user (I'm sure this will be an easy one for all out there).

I want to be able to get records from different tables after the table name has been chosen.

In other words and to be a bit more specific, at the moment in one table I am selecting the tables I want to get information from. Once I select the tables I need to then be able to extract the required data from the selected tables and then put it into a new table with summary totals.

View 8 Replies View Related

Data From Multiple Tables Without Relationships

Aug 15, 2006

K,

Maybe a noob question, but I'm still learning access (I know a little late at the age of 37, but better late then never).
I have a form with several tabs, linked to a table with employee information, works like a charm.
Now, my last tab is called settings, maybe no need to say that the information from this tab needs to be called from a different table called settings.
Certain information, like version number, department, etc I want to display all the time in the header and I want to be able to manipulate that information through the settings tab.
I definately don't want any relationships with the employee table.
Is there a simple way to achieve this, I tried drag and drop but this leads to errors, so definately I do something wrong here.
Much appreciated,

View 6 Replies View Related

Data From Multiple Tables In One Subform?

Oct 5, 2006

Hi!

First of all, I'm kind of a newbie to all of this, but not entirely, bare this in mind please ;-) Also I'm from the Netherlands and my english might be a bit rusty, so bear with me if you please.... :-)

I'm having a problem for which I cannot seem to find a solution. Below I've outlined the situation:

I have a couple of tables up to now:

CustomerTable
CaseDateTimeTable
CaseTechInfoTable

CustomerTable has got a primary key: Clientnumber
CaseDateTimeTable has primary key: Casenumber
CaseTechInfoTable has no primary keys.

CustomerTable only includes (apart from all adress info etc) the clientnumber field which is of importance in this case i think.
CaseDateTimeTable includes both Casenumber and clientnumber.
CaseTechInfoTable also includes both Casenumber and clientnumber. Only both other tables have Primary keys, and this one doesn't.

I've created a relation between CustomerTable - Clientnumber and CaseDateTimeTable - Clientnumber.
Another relation I've made is between CaseDateTimeTable - casenumber and CaseTechInfo - casenumber.

Now, I have made a form in which the name and adress data is fed into the database, in normal form view. Below these textboxes etc. I've created a SubForm in datasheet view in which the existing cases from the at that time selected/displayed customer should be displayed. At the moment only fields from the CaseDateTimeTable can be displayed, but I would also like to display some fields from, for example, CaseTechInfoTable in that same subform.

But how?

When I create a (sub)form via the wizard and for example select:
CaseDateTimeTable - clientnumber
CaseDateTimeTable - Casenumber
CaseTechInfoTable - Problem description

and display these fields in the subform as a datasheet, then the separate cases from each customer are not displayed. I have 5 clients in my test dbase, just about all of them have 2 or 3 cases associated with them. In the example above only the data from two clients is displayed and then only from 1 case each, not the actual amount of cases. So for example:

clientnumber 4 has casenumber 5 and casenumber 6. But only casenumber 5 is displayed. The same happens for clientnumber 2, he has casenumbers 9 and 3 but only casenumber 9 is displayed.

The strange thing is though, that when I create the same (sub)form in datasheet view, but only use fields from the CaseDateTimeTable all cases per client are nicely displayed in the subform.

Why? And how do I solve this problem? I need to solve it so that I can add data from other tables into the subform later on so a nice overview of certain important data can be given per customer per case, even before the case form(s) is/are opened.

I've tried making a query and base a form on that, but for some reason I can't get the query to display all my customers, let alone all cases associated per customer. In the past I have made another (smaller and simpler) database for which I also made a lot of queries. But now? Pff... I'm lost at the moment.

Anyone?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved