Linking Tables W/automatically Created Value

Nov 22, 2005

Don't know where this question will rank. Completely weird or completely common.

I've been getting great suggestions from this forum, one of them was to begin creating seperate tables for our data (I'm currently cleaning up a mess created 10 years ago for a nonprofit). The best way I have found to link/associate/whatever tables is to use their STUDENT identification number (since this value never changes). So we linked tests to that number (many tests to each student) by entering each new test as a seperate item with a manually entered stu id. We linked TUTOR and so forth the same way. But now I would like to start linking some more things. Particularly INSTRUCTION HOURS.

The question I have is this. How do I automatically create a value in the HOURS table that links to the value (stu id) in the STUDENT table. So that every student (previous and new) will have ONE associated HOURS set each time new student data is entered.

View Replies


ADVERTISEMENT

Automatically Hide Any Tables Created In The Database Except One Table

Dec 4, 2006

I want to automatically hide all the tables in the database, and automatically hide any new tables that are created, imported thereafter, except one mastertable.
How do i achieve this?

View 4 Replies View Related

Automatically Adding New Records In 5 Other Tables When ID Is Created In Main Table

Jan 13, 2008

Okay I had an idea and I thought I might get ffedback as I am relatively new to this. I went through a period a couple of years ago when I used access alot and was becoming familiar with VBA etc but I havent touched it in 2 years so Im pretty rusty.

I am running a study and need to have to create a database that:

a. collects data about participants
b. Has a number of questionnaires (5), each of which can be filled out by participants.

THe main table has a number of fields that collects info about the participants the most important of which will be the ParticipantID - an automatically generated number which is my primary key.

In table 2/form 2 I will host questionnaire one. This will be linked to Table 1/Form 1 (Particpant Information) by this tables primary key - also the Participant ID. The relationship will be 1:1. Each participant can only have one Participant ID and will only need to fill out questionnaire one once.

Is it possible that when I add a new participant to the Participant Information table/form I also automatically create a record in Table 2/Form 2 (Questionnaire 1), as well as Table 3/Form3 (Questionnaire 2) and so on so that they have the same ParticipantID...?

I was reading a similar query somewhere else and they said to use the Form_AfterInsert Event command? Is this right (see here http://www.pcreview.co.uk/forums/thread-1687644.php)?

I feel a bit stupid but I am willing to learn and try new things Once I get started I think I will be okay. If you could steer me in the right direction it would be much appreciated.

View 12 Replies View Related

Re-linking Tables Automatically?

May 3, 2005

In our company, passwords expire every 90 days (NO EXCEPTIONS) for DB2. This means that all my linked tables can't be accessed. There are many other users using the DB's and when I have to reset my password, I get locked out a lot because I can't seem to re-link the tables fast enough. It also seems that "refreshing" the tables from the Linked Table Manager doesn't work very well.

Is there an easier way??????

View 14 Replies View Related

Created Linking Table

Mar 12, 2013

I have a database with the following tables (among others):

tbl_Projects
tbl_Staff
tbl_ProjectsStaff
tbl_Payroll
tbl_Training
tbl_Clients

tbl_ProjectClients
tbl_ClientContracts
tbl_CostEstimates

As you can see I created two linking tables (project/staff and project/client).After that I linked other tables to the liking table. For example, tbl_Payroll and tbl_Training are linked to the tbl_ProjectStaff primary key.

I don't know much about access so I wanted to check if this is a correct design. I create forms that work well, but I noticed that the linking table doesn't have any data in it. The payroll table and the training table have the staff iDs and Project IDs but the tbl_ProjectStaff is empty.

View 14 Replies View Related

General :: Fill In Date Automatically When New Record Is Created

Dec 18, 2012

I would like to have a date control on a form filled in automatically with the current date when a new record is created (the date would remain as is unless changed manually). I've tried programming it in VB but, being a newbie, have not been able to come up with anything that works. I'm not even sure how to trigger an event to do it just the one time when the record is created.

View 2 Replies View Related

Tables :: ODBC Imported Tables / How To Ensure Index Is Created

May 13, 2015

I am currently using Microsoft Access 2010 32bit, and have one database acting as parent, with a second linked database as a client for people to work with, and the parent database has tables imported from Sage V21 via ODBC. I have used the following code as specified in other examples as follows:

Code:
DoCmd.TransferDatabase acImport, "ODBC Database", "ODBC;DSN=MyDNSMachineName;UID=MyUID;PWD=MyPassword;LANGUAGE=us_english;" & "DATABASE=pubs", acTable, "INVOICE", "INVOICE"

one of the tables has 10k records in it, it only transfers 77 records. After manually attempting an import via the ODBC wizard it finally lets me access all 10k records.I wish to have a single button click delete and import fresh tables without worrying if all the data is coming across.Also, when manually adding a table, I am usually asked by the wizard to specify an index, but with the code above, I am not offered that option and the tables come across with no index. I am led to believe that having tables that link to each other without an index is bad, so how do I ensure an index is created?

View 5 Replies View Related

Linking Forms (and Automatically Creating New Record)

Apr 26, 2006

Please forgive me if I don't explain everything properly, I am relatively new to Access.

I have two tables with the following information:

VOLUNTEER
PK: volunteer_ID (autonumber)
name, address, school, etc

SCHOLARSHIP
PK: scholarship_ID (autonumber)
description (text)
amount (currency)
FK: volunteer_ID (number)

One scholarship can be awarded to only one volunteer.
I have a form to enter all the volunteer information. At the bottom of the form, I would like to place a button to open a new form to award a scholarship to this volunteer. I believe I need to link the forms in the following manner:

VOLUNTEER.volunteer_ID = SCHOLARSHIP.volunteer_ID

The problem is this: The forms are not linked properly because in order to assign a volunteer to a scholarship, the scholarship must already exist. So when I click the button to open the scholarship form, the scholarship form is empty and the volunteer_ID defaults to "0".

I think this could be fixed by somehow making my button create a new scholarship_ID in the scholarship form and THEN linking the volunteer_ID fields.

Does this seem like a solution? If so, how would I implement it? I have a screenshot to help. Thank you in advance for your help.

View 6 Replies View Related

Modules & VBA :: Automatically Linking Database With ODBC Connection

Jun 26, 2015

I am looking to automate the process of linking my Access DB with an ODBC connection to an SQL DB with VBA (unless there's an easier way to do it?) - some sample code - if this is possible at all?

View 2 Replies View Related

Tables :: Created Multiple Tables In Database

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

Linking Excel To Access Automatically - Publishing Daily Report?

Dec 6, 2012

Having experimented with Access for a few months now; I know you can link an Access Database to SharePoint, and you can export Excel files into Access for inclusion in tables.

My question is; If I have a daily report being produced, through script, with an excel output. Is there anyway to automatically publish this in Access, negating the need to manually go into Access to import?

View 5 Replies View Related

Automatically Filling Linking Fields For New Rows In Related Table?

Dec 18, 2012

OK, so I have a database with four tables (Well, more than that, but these are the relevant ones). It's to be used for recording the results of site inspections.

"Tbl_Typicals" is a list of products. We'll call its key "Typical_ID".
"Tbl_Actions" is a list of tests performed on each product. A given product may have many tests, but each test applies to only one product. Its key is "Action_ID". Each row contains a Typical_ID to link on.
"Tbl_PlantComponents" records which products are installed on which site. Its key is "Component_ID". Each row contains a Typical_ID to link on.

The fourth table ("Tbl_Results") contains the results of each test. As a result of the relationships above, each row is specific to a single Action which applies to a particular PlantComponent, which is to say, each row has both a Component_ID and an Action_ID to link on.

So, what I need is a query that pulls all of these together, such that I can use these details as the line items of a subform.

The main form displays the details of the PlantComponent, which is a simple query to relate line items in Tbl_PlantComponents with the data about that particular product in Tbl_Typicals. So far, so easy.

The subform shows the details of each test applicable to that product. It then has toggle buttons and a comment field to indicate the results of the test, the results of which should be stored as a line item in Tbl_Results.

The "easy" way is to use an append query to generate Tbl_Results in advance. This works, but it raises a variety of new issues.

The nice way would be to use a normal SELECT query and have Access fill in the necessary linking fields (the Action_ID and Component_ID) on each row automatically. Now for trivial examples, this is very easy - my main form query manages just that: I created a link between Tbl_PlantComponents and Tbl_Comments (which stores general comments about each PlantComponent which aren't related to a specific test) based on the Component_ID and that works fine - when I edit the Comments field, the row is automatically created and the linked ID field filled in for me.

However, when I need to do it with 2 links, it all falls apart. I've tried everything I can think of, including generating a single-column unique ID to use for the link, but Access just won't autofill for me. It just makes those fields on the form (or in the datasheet view of the query) non-editable because there's no associated row in Tbl_Results. If I create a matching row in Tbl_Results the query works fine, but that's not the point.

Implementation of the query is non-trivial because it requires two outer joins involving 3 tables - All from Tbl_Actions to matching in Tbl_Results, and All from Tbl_PlantComponents to matching in Tbl_Actions. This necessiates splitting the query into two - the first relates Tbl_PlantComponents, Tbl_Typicals and Tbl_Actions (returning one row for each Action for every Component), and the second performs a single outer join (using an AND) between the first query and Tbl_Results.

View 5 Replies View Related

Tables :: Merge Spreadsheet Into Already Created Table

Jul 10, 2013

I've been having problems merging a spreadsheet with data into an already created table..

The main things in common is the "MC", "First Name", "Last Name".

I'm trying to update dates etc that are on the spreadsheet exported from another database, and update my datebase with those new dates..

View 14 Replies View Related

Tables :: Access 2010 - Calculated Field Cannot Be Created

Feb 22, 2013

I have table of transaction data, I want to count the number of successful customer transactions but the table includes cancelled transactions with a negative value. I was therefore looking to create a new calculated field for customer count which report either "1" or"-1" depending on if the price is positive or negative. The idea being a count of this would give the customer count. The expression I am using is:

IIf([Price]>=0, 1, -1)

The problem is it I get an error message saying "The calculated field cannot be created" "verify that expression "IIf([Price]>=0, 1, -1)" includes fields that exist in the current table"

View 2 Replies View Related

Tables :: Two Identical Database - Importing Tables By Linking To Data Source

Dec 3, 2012

I have 2 identical database in terms of structure but it differs in data.

Basically I would like to import data from subDatabase to mainDatabase and ensuring that there are no duplicate records.

I have used the "link to datasource method" through importing the tables to have the "updating" function.

However, this method also means that the records in mainDatabase are also imported over to subDatabase which I do not want.

Is there a method to ensure that the records are shared/update one way only? (i.e. import from subDatabase to mainDatabase and not main to sub?)

View 1 Replies View Related

Tables :: Linking Field Data Between Tables And Within Tables

Sep 26, 2012

Currently trying to build a database for customer management and order placement/tracking. Want to set a couple of rules so that if I for instance click yes of billing and shipping address the same that the database will automatically fill the shipping address with the data I inputted for the billing address in the same table.

The other issue I can see I'll run into is, I want to be able to select one of the company ID's (made up of a three letter abbreviation of the full company name) in the product ordering table and it will automatically fill in the rest of the customer data (phone, email, address etc) data into that form.

View 2 Replies View Related

Tables :: Linking Tables With No Common Data And Different Numbers Of Observations?

Apr 15, 2014

I am doing a project where we are collecting home owner data and information on all the dogs in the household. The data for homeowner has an autonumber primary key because no field is unique or has been consistently collected. I am struggling to link the data for the dogs to the owners because an autonumber primary key will not work since not all homes have dogs. I need to have this set up so that people who are not tech savvy can look up each homeowner (or dog) and get the dog (or home owner) information. And to make things even more fun we need up to 15 potential dogs per household each of whom will have 18 different pieces of data collected.

It looks a little like this (and you can see my not matching ID issue):

Homeowner table
ID First Name Last Name...........Total Dogs
1 Max Maximus 5
2 Min Minimus 0
3 Mus Musculus 1

Dogs 1-5 table
ID Date Dog 1 Name Dog 2 Name .......Dog 5 Name
1 (Max's) 4/11/14 Bobby Billy Betty
2 (Mus's) 4/11/14 Jojo

Min will have no dog records at all, just home information.how to link the dog's to the homeowners .

View 3 Replies View Related

Tables :: Linking 5 Tables As Unable To Exceed 255 Rows

Mar 18, 2013

I have been asked to design a database to replace our old excel sheet to log one of our customers server builds. It a four noded system and i require a seperate log for each node serial models of parts etc, qa checks.

I intitial started by just using one table but forgot the limit was 255 rows, i need about 600 - 700. So my plan is now to transfer to indivial table for each node. Then, a general table named Main for all my other details.

Although this seems simple, i now have the annoying task of being able to link them all together and produce a single PDF file to send to our customer whilst only entering our system serial number once to produce this.

My basic understanding will be to create a System Serial Number field in each table a link it via relationships, but if i produce a report with everything i need would it input my details for all four nodes? If so how would i set this up?

I will be splitting the database once completed and then making a ACCDE file for my techs to use.

View 4 Replies View Related

Tables :: Add A New Child Record When Master Record Is Created?

Dec 21, 2012

I have a master table with about 40 fields. In addition, I created eight child tables with read only datasheet views of limited fields from the master table. The child table views are based on certain critera in the master table and are being refreshed by using delete and append queries tied to the On Current property of a form. By double-clicking a specific child record, a form is opened to allow editing of the master record. Also, I am using Referential Integrity and Cascade Update Related Fields to update the child table when a change is made to the master. However, I do not know how to add a record to one or more child tables when a record is added to the master table. The new child record would have to be based on the same critera that the append queries use so it gets added to the correct child table or tables.

View 5 Replies View Related

Tables :: Linking Data Between Two Tables

Sep 28, 2012

I recently imported an excel file into access. Let's say it contained data for both domestic and international customers. With different things needed from each type of customer, once in Access, it became needed and easier to create separate tables- one for international and one for domestic. However, each customer needs their own unique id - and I need a way to link or relate the tables in a manner that it will not allow duplicate customer numbers between the two tables.

View 7 Replies View Related

Tables :: Linking Fields Between Two Tables

May 23, 2013

I have two tables called Hardware and Desk. They both have two fields called ChangeID and Priority.

What I would like to do is set it so that if we change the Priority in a record existing/created in 'Hardware', the Priority of the related record in 'Desk', also changes.

How can I achieve this?

View 2 Replies View Related

Linking Tables In Two DB's

May 19, 2005

Question for someone..... I was looking at the "LinkTables" in Access..... I see that I can link one database to another..... When I do this I can add the table to my relationship window... I have not played with this too much.. But looks interesting. Now, all the info I might add to this linked table is stored in the other database??? If this linked table is also linked to other tables in the other DB can I use those in queries? Not much covered (that I can find) in the Access help files on this sort of thing.
Thanks

View 3 Replies View Related

Linking Tables

Jun 26, 2006

I am looking to lonk tables in a database.

I have one table a pc tables which has a list of pc's

the other table is a pc software table which has a list of software

i want to build a form to show the pc's and their software how do i do this

View 5 Replies View Related

Help With Linking Tables...please!

Jun 29, 2007

I have a table with job info like: job #, job name, job addy.
I would like my end query to look like this: job #, job name, job notes.
I want the query to list all jobs, job names, and job notes for a particular week(we have a weekly meeting on the status of our jobs), so if I were to query the jobs for 6/26, i would get: job #, job name, and job notes for that date as fields in my query.
I am not sure how to accomplish this. Should I make a seperate table that contains job notes? If so, how would I accomplish this and create a relationship between the job info table and the job notes table?

Thanks in advance!

View 4 Replies View Related

Linking Tables

May 16, 2005

:confused: How can I link a table to one specific item in another table in order to avoid repeating information? (ex: school name that would be narrowed down to its different departments)

View 3 Replies View Related

Linking Two Tables

Oct 4, 2005

Excuse me for sounding extremely dumb, but I have a question that is really bugging me. I have created a database using two tables. The two tables are called User and Assets. In both tables I have set the primary key to be the field Username. I have also linked these two fields.

I have created a form that is my interface for the database and can add and delete from the table (User). I now want to insert a sub form into my form, that displays the contents of my second table (Assets) and reference's the field Username. Hopefully this will enable me to display my table (User) in the Form, whilst only displaying the relevent data in the subform (Assets) for the particular user.

I'm sorry if this sounds confusing, but I would really appreciate your help ...

:confused: :confused: :confused:

View 1 Replies View Related







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