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 Replies
ADVERTISEMENT
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
May 30, 2014
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.
View 8 Replies
View Related
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
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 11 Replies
View Related
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
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
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
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
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
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
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
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
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
Nov 29, 2007
Can a Append Query move all my data stored in multiple tables to another database with a identical table structure?
Because as I try to work the query, I keep getting prompted to "Select a table" I want to append to, and I don't want to append to just one table...
View 4 Replies
View Related
Nov 28, 2012
I have access that is required to be accessed by multiple users and register their activities which is reflected with the tables in the background and form sends also the time that they are putting their activities.
So i split the tables and put them in a share folder in one of the PCs. however, the problem is about the timing and dates is not correct usually, and i found out some of the PCs their dates are not on sync. I asked and found out sometimes it is required to change the timing in their PCs.
Therefore, i was wondering instead of fetching the timing from their PCs why not to put the timing of share PC (i.e. timing/Date of the share PC which has the splitted tables)
so can i do like this:
User PC: (Form1) & user activity => Share PC: (Split table) & take time of the share
How can i do that?!
View 6 Replies
View Related
Feb 10, 2015
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.
View 1 Replies
View Related
Oct 23, 2013
I am working on a database which has two tables used as part of a registration and login process.
I would like a couple of fields from table one to automatically update in table two, once the fields in table one are populated without using an 'on click' event.
The reason I would prefer not to use an onclick is because the completion of the form used to generate the users table does not require any buttons for the data to save.
View 1 Replies
View Related
Mar 7, 2013
I have multiple tables that are created from some software that puts them each into the access database. each table is one year of data. the software cannot put all years into one table because of memory. I want to combine these tables into one. In addition, I need to do some manipulatins: the dates come over as MM-DD-YYYY strings and I need to convert them to dates MM/DD/YYYY, and also I need to parse a string to extract the General Ledger account number. I know how to use this easily from within Excel VBA using dateserial, and Instr etc., but now sure how/if I put some code into access and have it called to do this for me automatically. Each night the most recent year is automatically updated in access, so the combination file needs to be refreshed whenever the most recent year file changes.
I currently doing all of this in excel: first initialize the NewCombinedTable, then one by one dumping each year file into an excel worksheet, doing the work needed, then appending it to the NewCombinedTable. However, I think it would work much quicker if I was able to trigger the Combination to happen in Access along with the data manipulations.
I also need to know whether DAO or ADO is best for me to use with 2010...
View 1 Replies
View Related
Feb 19, 2013
I have created multiple tables in my database and now for some reason it keeps telling me it can not find a few of my existing fields.
View 5 Replies
View Related
Dec 10, 2013
I have 5 tables and 2 forms. The primary form is what I input all the information into (Tracking) and the other form is to update employee information (update form).
The "Tracking" form is where I add information to 4 of the 5 tables. Here is where I'm stumbling. Would it be more practical to just have 1 table and just expand the fields, or have the form put the information into the separate tables. Those 4 tables are Employee, phone, spotter and radio.
I'm wanting to keep a running tally of who doesn't turn in what equipment on what day.
View 3 Replies
View Related
May 21, 2014
I'm trying to make a query to filter or show only those customers when it is the time for their monthly payment.The query I have consists of four fields which are
1- Order ID
2- Payment
3- Date (Default value set to Date ())
4- Date for next time Payment (Default value set to date () + 30)
I made another field called "states". In this field I putted the following expression
Code:
IIf(([Date for next time Payment]-Date())= 0 "Should Pay";" ")
Then, I set the criteria for such field to "should pay" so that only customer "should pay" will appear in the query datasheet. However, this method has the following shortcomings:
1- It works only for one day (alert day) ,i.e., customers will filtered only when the expression is true.
2- Customers who have paid will still appear as a "should pay" until the day (the day that make the expression true) finish.
3- Customers whose pay late (maybe after 1 week) will disappear form the query datasheet after alert day finish.
To overcome the above shortcomings, i modified the expression to
Code:
IIf(([Date for next time Payment]-Date())<-1 And ([Date for next time Payment]-Date())>-15;"Should Pay";" ")
This method will extend the alert duration to 15 days, so the customers who haven't pay yet will appear in the query datasheet for 15 days. However, the customers who have payed will appear also, and that is the problem. The problem here is I can't compare the current payment date with previous one for one customer. method to create suitable expression, or even another query scheme ??
View 1 Replies
View Related
Apr 17, 2014
I have two tables, One table containes customer name, etc., tblcustomers the other table contains the tbltimelog, log of activity start time end time. No issues. I want to generate a form based on the dateofentry for the time record. I am not able to get a summary lets say I spent 20 hours on ABC and 20 hours on CBS. If I spent 3 different days on ABC and 6 different days on CBS it will not add them together it will list it ABC 3 times and CBS 6 times. I have tried the distinct function but it doesn't work because the date is distinct. Is there a way to do this as an SQL or whatever. Thinking I could just create a new table and calculating the totals but that seems to be a waste.
View 3 Replies
View Related
May 18, 2013
I am creating a database of medieval labor contracts and have come across an issue.
I have a table of Contracts, and a second table of People. I want the table of People to show every contract in which that person appears. Each contract has multiple roles - there is always at least a Laborer and an Employer.
The same person might appear as a laborer in one contract, and an employer in a second contract and I want my People table to pull every contract in which that person appears, regardless of the role they play in the contract.
So far I have not been able to get this to work. I set up two different one-to-many relationships which link the People table primary key (personID) to two separate columns in the contract table. However, in the People table, instead of pulling contracts in which the person appears as either Laborer or Employer, it will only pull contracts in which the person appears as both Laborer AND employer (a situation which will never occur in my actual data but which I tried out as a test).
View 2 Replies
View Related
Oct 4, 2013
I have three fields in one table that need to be related to the PK of another table.
tblProject - Engineer_ID, Producer_ID, and Project_Maner_ID
tblEmployee - Employee_ID (PK)
employees can take on any of the positions for a given project, so i'll need to have multiple employees filling up different roles for each project.
when i try to set up the relationships i get the following message:
A relationship already exists.
Do you want to edit the existing relationship? To create a new relationship, click No.
I click No, and it creates a table named tblEmployee_1. Why? is this ok?
View 2 Replies
View Related
Dec 20, 2005
I have a database that tracks staff training in a health trust. It's pretty simple, tb_staff, tb_courses_done which is 1:n and a tb_courses 1:n on the staff_id of tb_courses_done.
I want to know how to go about adding a table(s) that will allow me to book staff onto courses and track if they attended or not. At present it's 1 staff member has many courses so any courses entered are "done." I have a main form with staff details and it has a subform where I add completed courses.
Hope that's clear?
Thanks.
View 1 Replies
View Related
