I created a sys admin database and the big problem I am having is linking the hostname field in table 1 to the hostname field in table 2. I have tried making both fields primary keys, I have tried creating a relationship to the two fields between the two tables. I have tried adjusting the join types.
Yet, no matter what I try, the hostnames from table 1 will not populate in table 2. I have even tried adding a new hostname to see if it would populate and it won't.
I created a sys admin database and the big problem I am having is linking the hostname field in table 1 to the hostname field in table 2. I have tried making both fields primary keys, I have tried creating a relationship to the two fields between the two tables. I have tried adjusting the join types.
Yet, no matter what I try, the hostnames from table 1 will not populate in table 2. I have even tried adding a new hostname to see if it would populate and it won't.
[General Info] has 2 fields. Name(is a primary Key as well) & Organisation
[Details] has 4 fields. Primary Key, Name, No of products & Type
[Details] is a sub form for [General Info]
It is a one to many relationship. With Name from [General Info] linking to Name from [Details].
I can update via the table directly and [Details] links the name fields automatically. I created a form but the form just cannot register the field automatically and requires the user to type in the Name from [General Info].
I have attached the DB file. Pls help... your help is greatly appreciated!
I'm formatting an Access table and need help linking 1 field to another. I need to specify that Field B will state "N/A" if and only if (back to good old geometry) Field A states "Yes." I've tried using the ValidationRule and Default Value functions but they don't let me refer back to Field A. Any thoughts would be hugely helpful.
In query design view, I have added a query with column (nameA) of names and a table with a column (nameB) of names. I am linking these two fields (nameA and nameB) which contain names. The names are spelled exactly the same so I should get results. Both fields are of the same data type.
But even when I add only the field with names from the query (the field called nameA), I don't get any results.
What is wrong here? Are there restrictions about linking a query to a table?
I have a database design problem that I cannot wrap my head around. There are three tables roughly like this:
tblUsers tblAccuracy tblRecords ----- --------- ------- UserID UserID UserID Name MonthYear Date ... Accuracy NoOfRecords
tblUsers and tblRecords are joined by UserID (Text) in a one-to-many relationship.
tblUsers and tblAccuracy are joined by UserID (Text) in a one-to-many relationship.
tblAccuracy stores User accuracies on a monthly basis where [MonthYear] is a Date/Time column formatted to "mmm yyyy" with each entry being the first of each month (eg. 01/01/2008 to display Jan 2008)
tblRecords stores [NoOfRecords] on a daily basis for each [UserID] where tblRecords.[Date] is a Date/Time column.
---
The problem is that I need a way to query these tables to return a [UserID] from tblUsers, the list of all records entered by that [UserID] for a given month on a daily basis from tblRecords and a single [Accuracy] figure for that [UserID], for that [MonthYear] from tblAccuracy.
tblAccuracy and tblRecords have no primary keys and I'm not sure how to normalize them, or if it is even possible. [MonthYear] is stored eg. as 01/01/2008 for January and [Date] has many records for January and I don't know how to relate them.
Hi Guys, Haven't done anything with Access since I finished uni and I can't remember much. I'm trying to set up a simple database with customer recording and order taking functionality. Just in the very early stages trying to get basic things working.
I have a form where you enter customer details..at the bottom there is a command button named "Place Order", which will be clicked to go to the order form for that customer. Attachement shows very simplified version of the order form. I need to enter a Product ID, and have the Product Name automatically extracted from the Products table.
How can I do this? I would have done a search...but not even sure what this process is called in Access. Thanks for any help.
I need to display a value from a table in a text field on a form based on another text field on the same form.
The table has records as follows:
Month/Year xx Month No xx Year No xx Month ID (xx shows break in columns) Jan-06 xx 1 xx 2006 xx 24 Feb-06 xx 2 xx 2006 xx 25 Mar-06 xx 3 xx 2006 xx 26 Apr-06 xx 4 xx 2006 xx 27 May-06 xx 5 xx 2006 xx 28 Jun-06 xx 6 xx 2006 xx 29 Jul-06 xx 7 xx 2006 xx 30
In one text field I use the DateSerial function to calculate the value of the previous month (e.g.Jul-06). I need to return the MonthID value to the second text field based on the value stored against it is the table, so in case of Jul-06 it would show 30 in text field 2.
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.
Ok, I have a table that contains a number of yes/no fields depending on location.
There are two main parts of my db that use this, one is users (for their departments) and the second is changes (for secondary locations)
I want to have two fields in the table used to reference a set of locations. Meaning i can reference the table and get locations where userID = 3 or alternativly where ChangeNumber = 6
Can somebody help me in making this work. Im currently having issues with duplicate values etc
I am trying to create a table for an assignment on normalization. It is similar to the problem here : http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=88
I am trying to make it so that if a name appears in the Spouse, Child 1,2 or 3 column it will automatically appear in a dependants database with the EmpID and the Name of the Dependants, as separate objects so that the EmpID could be repeated. I have scoured info and I can't seem to make it work. Any help would be appreciated.
I was wondering if there is a way to link one field from a table to another table?
I have a main table that all of the data goes to and then a second table that the completed data goes to. I have made a query to show info from the main table and one field from the second table and it will only pull the info that is on both tables...i want it to tell me the info that is not on the second table:confused:
Sorry if this is confusing, but any help would be appreciated...thank you Cori
I am fairly new to Access and i am trying to create a database that stores member's details and records the payments that they make monthly.
I have 2 tables:
Members:Id number Fname Sname etc
and
Fees: Id number Fname Sname Amt paid Jan Amt paid Feb etc
What i want to happen is that any member's details entered in the members table also appears in the Fees table so that only the amout paid needs to be entered.
Having a little problem, have searched the forums but have not been able to find an answer.
I have a single db with 3 tables, artist, gallery and arts.
The main form should show the work details for each artist, i.e. each piece of art they have done. The problem I have is I think I have a link or relationship problem as when I try to create a new record in the arts table, I have a combo box which populates the artist name and gallery name and it should then store these details in the arts table along with the rest of the details I enter but it doesn't.
Each table has an autonumber and there is a one-to-one relationship from arts to artist and arts to gallery.
I would like to link 2 fields in a form. If I select data within one field with drop down list it should automatically bring up a name within the 2nd field. How would I be able to do this?
I've managed to lind the subforms to this drop down list which works brilliantly but this is a field within a form that I don't know how to link.
Ok. I have used access before to hold data but have always accessed and used it via asp. I have never actually tried to use the access forms and queries! But for a current project i am having to use it.
So far its been fairly simple but im stuck on one point. I have one table that holds user info and has a foreign key from another table. On my form i want the user to be able to lookup the foreign keyes available. Only i want the lookup to show the description field in the other table not the unique id (primary key) but when they slect the relevant row its obviously need to record the primary key of the other table in the foreign key field in my user info table.
How can this be done? Im not sure if i explained this very well. If anyones unsure what i mean please ask my to clarify.
I have to date fields one called from and the other called to suppose to look like this frm 07/05/05 to 07/05/05 what i want to do is when i type the date in the from field the same date will automatically appear in the to field any ideas.
My question is this: I have a form called Family with a RecID field. When I create a new record on this form the RecID field auto assigns a #. Then the user is supposed to click the command button which brings them to another form called Child. This Child form has the RecID field and another called RecChildID. The RecID field is linked to the same RecID field as form the previous form. But when I enter the child form and start it's linked record the RecID field is not filling in/ copying over the same # as the previous form. How can I get it to update the RecID from the first form to the second form? Thanks!
I have two different tables. Table1 contains information such as the name of a patient, which is the primary key, date of birth etc (fields that will not change). Table2, linked by the subject id, contains information about tests that they may have gone through. A form has been made using these two tables, in which table1 is that main form, and table2 is a subform. I want to add a field in table2 called "CurrentAge", which will use the date of birth specified in table1, to calculate their current age, and would like it to be displayed in the subform.
At the moment i have an exisiting database that was not created by me. It consits of about 7 tables and a main table. Each table has a Primary Key that is a joining field. The Main form is built with subforms and when i enter a number in the primary key filed of the main table it enters that number in all the other primary keys in the other subforms. i have now tried to create another subform to put into the main form but i cannot get this "link" between the primary fields to work. can someone please help.
I have a form with contact info, which also has a subform with other data on it. The subform is a continuous mode form. If I go to the "New" line (last line) of the continuous form, I can add information, and the records are linked via the customerID field on both the form and subform.
I want to have a button on that subform that says "New" that opens up a different form in a popup. I can do that part just fine, but when I try to enter data onto the popup form, the customerID field does not populate with the ID number of the person on the main form. It defaults to a value of "0".
So, is there a way to link the 2 forms like you can with forms and subforms? Or is there a way to have the customerID field in the popup form just populate based on the other form?
Not sure if this makes sense, but please help if you can. Thanks.
Hi i am working with a database that contains customer information and sometimes files and attachments will need to be saved into the database. I presume i will have to save the attachments somewhere and then link to them, but i really haven't a clue where to start. Does anybody have any information about this or even know where i can look for information! Thanks in advance! Treasa
I then have various support queries that group customers based on reason for visit, type of visit etc. I need to be able to link this to my main table so that each customer has a category against them. The issue I have is that some customers will have more than one visit ref, which can also mean they have multiple reason for visits. I need to make sure that when I link my queries back to the main table that the right category appears against the customer and their correct visit ref.
Im in the process of developing my first database for my bonsai (small trees) collection. So far, ive developed a table of each of the trees I have, with fields such as Species, Where Bought, Cost etc and ultimately a link to a recent photograph (my next challenge). Ill be adding to this as my collection grows (in number and size). To avoid information duplication I have a separate table of care instructions for each species of tree with type, position they favour (eg Hardy/Delicate), watering requirements, wiring advice etc.
I now want to have a field in each of the records in the first table to its particular species care instructions as simple as that, or so I thought. ie for each different pine I want a link to the care guide for pines, and only pines. And preferably in a table format.
I need a one (second table) to many (first table) relationship and have linked them appropriately through primary field (species) and foreign field (care details). I have tried various Controls in the Design View but cannot get the field to show just one care record (ie the one for that particular species of tree). I realize this is possible, as its a common feature of a good database, and have found it being used in the various templates available - but not how they were created. The best Ive got to is using a Combo Box but this shows all of the care details records and in a column view.
Hi all, Been pulling my hair out about this for a while, so i hope someone's feeling kind:
http://www.agga.dsl.pipex.com/pics/AWF01.gif
I'm using the BHRefPrefix to build part of a reference number. The Principal and Site exist on a separate form, and i need to use both as criteria in order for the correct BHRefPrefix to be returned.
I sort of get it working using two joins, but it doesn't let me add any new records. I've tried straight queries, queries with DLookup expressions, and i just can't get what i need.
