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.
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?
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?
What I have is a database setup with multiple tables in which different areas of my DC can input information simultaneously into their respective tables. I then have another database linked to it for myself to have a live view of each updated record. I would like to see all the records of each table in 1 single table (possibly just sorted ascending by time). Each table has the same Field headings but may have different qtys of records. As I will then have it linked to an Excel table to VLOOKUP from it.
I have tried Union coding but always get Syntax Error etc.
I'm using the MS Access 2010 ExportwithFormating action to export three tables to a single MS Excel 2010 workbook. The action overwrites the first excel worksheet each time instead of saving all three worksheets in a single excel workbook.
How can I export three tables into a single excel workbook.
I have a database with a table with company names, then a relationship to another table that shows that companies' address, but I also want there to be an address 2 and 3 and so on, and some of our companies have multiple sites.So what Im asking is how would you be able to show multiple data, on a sing record.
I've got a form called 'frmStudent' which is linked to 'tblStudent'. I have designed it as a tabbed form, the first tab has "Student name", "Student Number", etc. The second tab is linked to visits by the student to a mentor (such as a tutor etc). Here I'd like to have "Date of visit", "Mentor Name", etc.
So far I've been able to get the first tab to work (I'm guessing because it's linked to 'tblStudent'), but can't get my 2nd tab to work as I have no clue on how to link it with 'tblVisits'. Is there a VBA methor or perhaps a nother method to do this?
I'm attaching a link to the sample of what I'm doing (please shift-open into frmStudent to view what I'm talking about). Any ideas and suggestions would be greatly appreciated!
I have a tabbed form that contains multiple rows of data just like the following: (it's a service checklist for a store where the user makes choices from the combo boxes: e.g. good, fair, poor, etc)
Since the combo boxes describe data that relates only to that element I can't put all the data needed into a single table. There are 40 of these rows so does that mean I have to create 40 tables? If so, how do I add the data to them from the form? Any help with this problem would be greatly appreciated!
I'm trying to create a report that's based on a query, and the query has three fields: [PersonName], [PersonDate], [PersonShift]. This table holds records for people that worked on certain days and certain shifts. What I want to do is create a report that gives a graphical calendar display of each day in a month, and on any day that the person has a record (and sometimes there are more than one), I'd like to see just the PersonShift records showing in that day's box.
tblPersons PersonName PersonDate PersonShift Jason 4/10/14 FIRST Jason 4/13/14 FIRST Jason 4/13/14 SECOND
So if I were to print this report for Jason, I'd get all the days in April laid out like a calendar, and on 4/10/14 you'd see "FIRST" in the box, and on 4/13/14. you'd see FIRST and SECOND in the box. All the other boxes would be blank.how to display the calendar, how to display the dates. I'm able to return records to those boxes by creating 31 separate queries, one for each day of the month, and each query returns records for that day. The queries are added to the report as subreports. It all works beautifully.
The thing is, I'm running 31 queries every time I pull the report. Is there a way to code a single field on a report that will run a SELECT statement on the table using variables that are located in fields elsewhere in the record?
If I have a PersonName field on the header of the report, and I have a PersonDate field in the detail of the report's record, can I create a new field in the detail of that record that runs a SELECT statement on qryPersons, and filters the tblPersons by the PersonName on the header of the report and on PersonDate in the record?
I want a field on a report that runs a SELECT statement on tblPersons, I want the field to return only the PersonShift records for that person based upon the PersonDate. Each of the fields on my report have a CalendarDate field, and I want the SELECT statement to return records where the PersonDate matches the CalendarDate, and again, it should only display PersonShift records.
I need to set up an attendance database, that has multiple statuses available for a single day.Example: employee can be present, he can have a sick leave, he can be away on training, or business trip, etc... And for some of those statuses, like business trip, i need to be able to freely enter a comment, stating where he is etc..
Anyway, the key is that this database should be able to offer a "headcount" option, and traceability for past statuses for at least a year, for every and all employees. Now i just need to set up the database tables and relationships.
I wrote a database several years ago and recently pulled it out to give to a friend. The problem is, back then (not knowing better) I set the Employee table up with as a single field "NAME". Now, in order to make it effective, I need the Employee's name in four (4) parts (First, Middle, Last, Suffix).
I have several queries based on the "NAME" field and and would like to avoid changing all of them. I have a simple form "frmUpdateEmployees" that populates the Employee table and Name field. I was hoping to change the form and/or add a query that would be easier and more simple.
Table: Employee Field: Name Form: frmUpdateEmployees Queries: 16 that depend on the table and field above.
I have a tblCustomer table to track customer information. One field is for flights the customer books. I expect that a customer might book more than one flight, which should affect what he owes. (My tblFlights table includes fields for the cost of each flight.) How should I make the Flight field in tblCustomer so it accepts multiple flights and reflects on the customer's final bill and my own accounting tables that I haven't even created, yet?
I'm probably overthinking everything. This is just an Intro to Access class, and I only need 3 tables with 1 relationship. So far, I have 9 primary tables, 4 duplicate tables (for M:N relationships) and 11 relationships. I have to have tables for financial data, customer info and product/service info. I wasn't sure what I would need for my fictional airline, so I created tables to track everything I could think of, hoping to cover all 3 required types of data in the process.
We have two facilities that receive four different types of shipments. I wanted to set up a database to track these shipments. Is there a way for me to set up tables such that the person entering the information can leverage the same date and location field for each entry type? For example, we may only receive two types of shipments today and four tomorrow. I want to avoid having the clerk enter the date and location two times today and four times tomorrow for each data entry.
Here's an example of what I mean above:
Date Location Type Qty 5/16 1 A 10 5/16 1 B 1 5/17 1 A 1,000 5/17 1 B 100 5/17 1 C 1 5/17 1 D 11
I'd like for the clerk to select from a fixed number of locations (1 and 2) and a fixed number of types (A, B, C, or D) and provide the date and quantity received.
My database allows you to log issues (see attached)
An issue will belong to the project, the project may have multiple test plans.
The issue will also be the primary responsibility of a company, and that company may have multiple contacts (people)
Now, i could create a form over the top of each table but then if someone wanted to add a new project, testplans for that project, suppliers (companies) for that project AND new contacts for those companies, they are going to end up going through loads, and loads of forms.
So my question is, what approach would you recommend to create a control panel where they could add these items with the fewest number of page switches possible? (preferably none.
Currently we track areas of non-conformance for a fleet of flight simulators. Each flight simulator has a particular ID number. In some instance an area of non-conformance is associated with a single simulator, at other times it is a fleet wide issue and applies to all or some simulators. In order to track as well as advise leadership and the contractor responsible for maintenance of the simulators of the situation we generate individual response letters.
In order to track each instance of non-conformance my idea was to create a new record for each deficiency. In the event that it is applicable to multiple simulators I would like to fill out the form with all pertinent data and then place a checkbox associated with each simulator and when the record is saved, it creates one record for each simulator with a checkbox ticked.
Once the deficiency on each simulator is fixed, I would check a box for a field called rescinded, which would remove that particular deficiency on that particular simulator from the active list of deficiencies but the others would still remain because they are associated with unique records.
I have 5 tables that I would like to input data in. It can only be done with a single form. The fields I want to input in have the same names in all 5 tables, for example:
Table 1: Name Age DOB
Table 2: Name Age DOB
Table 3: Name Age DOB
Table 4: Name Age DOB
Table 5: Name Age DOB
Is it possible to input data into all of these fields in each table using one textbox for each field?
Preferably without having to use code but if it cannot be done without it then that would be fine.
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.
I believe this can be done with a query, but am having problems with it.
What i have is a db with a table for employee another table for training items and a third table to tie them both together. These employees are divided into several "teams" (team 1, team 2 etc ...) The training items are assigned to some or all of the teams. ( i know that this is a many to many relationship and i believe that is where i get confused ) Now the problem. What i would like to do is have a form that will allow the user to assign a new training item to teams 4, 5 and 6 and have access automatically create a record in the table. Now that the 20 or so employees that are assigned these training items will have a record that the user can simply fill in a date when the training has been completed by the employee.
I believe what i am looking for is an append query. Is that right?? Have been playing with this one for awhile and have no luck so i would appreciate any help i can get on this one
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:
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?
Hi, thanks in advance for any help you can offer. I've got a table that has
Date Time Tag ID Power Level
throughout the day a computer listens to several tags (transmitters) and records the power level of the signal generated by the tag each 3 seconds. What I'd like to do is build a query that gives the Date, Time and Maximum Power level reading for each tag ID. I only want 1 record per tag per day
I've tried using "group by" and max in the query but this gives me all the times throughout the day.
I have a comments on my form. I have contacts with them having from 1 to many customers accts. I am trying to come up with a way to update the comments for a contact that adds those comments to all their customers records...but not to those that have been closed. How would I add these comments to the comments on the form without overwriting the current comments.
Comments - Memo Contact - Text
It's quite a pain, copying and pasting from record to record.
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.
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..
Hi forum, I have this database with a main form and 5 subfoms. For the Address Subform, I need to add multiple addresses for each person I add. How we currently do it is enter the multiple addresses per person, one at a time. Is there an easier way to do this.
