I have an employee table(parent) and family (child)members table. I have a field in the Family table called employee which i have created the one(employee) to many (family relationship). When I add a new family member why does the employee id not show automatically show in family table. I have to add it manually.
View RepliesI have a table for a multiple parents linked to a child table. I need to figure out a way to only allow 1 parent to be coded as primary, 1 as secondary, and then the rest as other... I thought about making Primary/Secondary/Other a primary key. But then I can only have 1 other. I would have to make a finite number of parents that could be entered and I want an infinite number.... My end goal is to have a report that only has a primary and second parent on it, but the rest of the parents still exist in the table...
*Child_ID
Parent_First_Name
*Primary/Secondary/Other
how do I set up supplier name in the order table like the example in the
northwind database?
what I mean is set supplier ID so that it shows combo box with names instead of numbers.
I have a query that runs - off the back of this, a report. The query will show a name eg dave however the report will show Daves unique key (eg 3) and not just say dave how do I sort this?
View 2 Replies View RelatedHi
I've compiled a make table query and would really be happy if I could automatically assign a primary key to one of the fields i.e account ref.
Any ideas, suggestions gratefully appreciated
Cheers
paul
I'm pretty new to access and I've been working on a pretty complex project...
Anyways, my first problem I'm having is that I want Microsoft Access to automatically create a key number. This number depends only on the year, and then it would concatonate another number.
Example: 5-3000, then 5-3001, 5-3002... The five corresponds to the year, and the other part is just incrementing by one for each new record.
What would be the best way to do this? I don't necessarily need the dash in there, it could simply read 53000, 53001, 53002, etc.
Thanks for the help!
can I create primary key with macros automatically.
I have created table with Query and I need primary key in this table. I put new field for primary key but after restarting it was delete every time.
Hi guys
I am trying despereatly to fulfill this function with a macro
any help will be appreciated
I know this may be easy but the help files isnt helpful- so what else is new ;)
Database- C:sports.mbd
table - tblbasketball
current primary key - ID
new primary key needed- ID, EMPNO
The following codes dont work :(
ALTER TABLE tblbasketball
DROP CONSTRAINT PK_tblbasketball PRIMARY KEY
I want make one database about office register. I gave primary key for auto increment serial number . That is working fine. Now problem is some times I want delete client name in between table .That is maybe first row or 5th row or anywhere . at the time the row deleted which is i was selected but the primary key is not update . for example I want delete 8th row . after deleting I not seen 8 in primary key place i have seen only ....,5,6,7,9,10...... etc. how to give auto increment and decrement in the primary key......
View 3 Replies View RelatedI am new in ms access , I want make one database about office register. That is Have done . I gave primary key for auto increment serial number . That is working fine. Now problem is some times I want delete client name in between table .That is maybe first row or 5th row or anywhere . at the time the row deleted which is i was selected but the primary key is not update . for example I want delete 8th row . after deleting I not seen 8 in primary key place i have seen only ....,5,6,7,9,10...... etc. So how to give auto increment and decrement in the primary key......
View 2 Replies View RelatedHere's a query that the bottom listview in the attached form i.e. a listview representing a table of calls(many) to fims (1 top listview)
Code:
SELECT calls.id, calls.firm_id, calls.called, calls.said, calls.spoke_to, calls.next
FROM calls
WHERE (((calls.firm_id)=[firms].[id]))
ORDER BY calls.called DESC , calls.next DESC;
When I run the thing...I get a dialog asking me for firm id.
I want to change this so when I move up and down the firms LV (top)... the bottom LV updates taking firm id from the top LV with focus.
Access 2003.
I have 2 tables, master & child. with a one to many relationship.
On one of my forms I want to display some of the fields from the master table and only the last entry from my child table.
How would i accomplish this?
I have a parent table (tblLabels) and a child table (tblRevision) where the revision history for the parent table is kept.
The parent table is populated via an excel import and may have several records imported at once. Instead of having the user manually enter a new record note in the child table for each record imported into the parent table, I've created a form that collects the necessary data (date, person who added the record, person who authorized the record, and notes) and then creates a revision history for each new record.
This is what I have so far:
Code:
Private Sub cmdAddNotes_Click()
Dim strSQL As String
Dim RevisionDate As String
Dim RevisionRevisedBy As String
Dim RevisionDesc As String
[Code] ....
When I run the code nothing happens. No error, no new records create, etc. My suspicion is that I may need to use an INSERT INTO query instead of an UPDATE query, but I'm not sure how to go about matching up the foreign keys if that's the case.
I hv a relationship as follows:
Person 1 ------- N Assignment
Person
(PersonID, lastName, firstName...)
Assignment
(AssignmentID, PersonID, AssignmentDate, Score)
I would like to show the Score of the latest assignment in the Person Form, how can I do it?
Thx in advance!!
I have two tables in my access database with Parent Child relationship.Then i have below query based on which i have a datasheet form.
SELECT MOC_EMERGENCY.[MOC NUMBER], MOC_EMERGENCY.PERMANENT_MOC, MOC_DATA.TITLE
FROM MOC_DATA INNER JOIN MOC_EMERGENCY
ON MOC_DATA.[MOC NUMBER] = MOC_EMERGENCY.[MOC NUMBER]
When i delete a record in this form, my parent table record (in MOC_DATA table) is automatically deleted.
NEw to access. I have a parent child relationship. I thought that referential integrity automatically added new records to the child table. Am I wrong? Is there a workaround solution to add new records?
View 14 Replies View RelatedHi
I have a parent-child base table (accounts) with the following fields:
- id
- parentid
- name
every record's parentid is either 0 ( which means it is in the most upper level) or another record's id (which means it is the other record's child)
different levels of records are being used. I mean some accounts are used in the most 1st level with no childs, some in the second level, ....
I need a query to list the accounts which are not parent to any othe records, regardless of what level they are defined.
Thanks again for the help.
Table has a relationship with master table. Joined on TractID primary table - auto number, TractID child table - number. This works as it should when adding a new record.
What I am trying to do is create a new key for each new record added in the child table with an ID that looks like this: TractID.A, TractID.B, TractID.C etc. for each new record added in the child table. if so where do I look, how to accomplish it?
I have an unbound new contact data entry form that adds records to many different tables. I have no problems adding one record to a child table at a time, but one of the things we are collecting is a list of online networks a person belongs to, and I want to be able to stick a "check all that apply box" on my form and have it add multiple records to the child table. Here's the basic set up, I have a parent table with the main contact information with a primary key field "IID", a lookup table with "Facebook, LinkedIn, Twitter..." etc in it with a primary key field "online_id", and a bridge table to link the two which should have multiple entries for IID, one for each online_id. I want to use a listbox (or something like it) that the user can select multiple online networks and then have records added to the bridge table. I can figure out how to add the listbox on the form, what I can't figure out is how to get the values out of the listbox. T
View 13 Replies View RelatedSorry this is so long but I am trying to make it as detailed as possible...
The Setup
My database is similar to the sample "Service Call" db. I have a main table called "tblTT" which has an autonumber primary key. The table also contains several foreign keys such as UserID (not an autonumber) from "tblUser", TechID (also not an autonumber) from "tblTech", etc. I have the relationships setup with "Enforced Referential Integrity" for both updating and deleting records. Each relationship has a RIGHT OUTER JOIN ("Join 3" in access) so that all records from child (tblTT) and only equal from parent (tblUser, tblTech, etc.) will be included. I have a form for nearly every table which serves different purposes but the main function of the DB is to create new Trouble Tickets (TT's), a.k.a. service calls. Therefore the main form used is my "frmTT" form in add mode. The form contains all the fields from my "tblTT" table and contains (directly) no fields from any other table (I guess indirectly it contains fields from all the parent tables...).
The Problem
When I pull up "frmTT" and try to create a new Trouble Ticket for a user that does not yet exist in the table "tblUser" I get the error "You cannot add or change a record bcause a related record is required in table 'tblUser'".
What I want is for my users (the "Techs") to be able to create a new Trouble Ticket without having to worry about populating the "tblUser" table (and other parent tables) first.
My Solution
I was going to (and unless someone can find an answer for me still will) fix this using VB script by setting up a query to check all the parent tables for the values in their corresponding fields in the form. If the query returns no results an "INSERT INTO" statement will run to populate the parents tables so that the form will save itself into the Trouble Ticket table ("tblTT").
I feel that this is a huge work around and not the proper fix. I would prefer to do this the right way both to have a correctly setup DB and for future reference. Can anyone help me with this?
Thanks,
Andy
I am trying to determine the best method for how to handle this query using Access 2013. I have a clients table that contains the following:
clientID fName lName admissionDate dischargeDate
1 John Doe 05/06/2014 06/27/2014
2 Jane Doe 04/24/2014 05/15/2014
3 Steven Smith 05/15/2014 NULL/Empty
4 Chris Davis 06/12/2014 NULL/Empty
Then there is a WeeklyProgressNotes table that is there for the person that is responsible for auditing the clients charts. It does not contain the actual weeklyprogressnotes, it only contains a Yes/No field and a date field for the date the weeklyprogressnote was completed. Like below:
noteID completed dateCompleted clientID
1 yes 05/08/2014 1
2 yes 05/14/2014 1
3 yes 04/25/2014 2
I am creating a form that the auditor can open to determine what weeks she needs to check for each client to see if they have their weeklyprogressnotes completed that week. The weeks run Mon - Sun and there will be no record in the WeeklyProgressNotes table if she has not yet checked and confirmed for that week. So the form would basically look like this:
fName lName week completed date clientID(hidden)
John Doe 5/19/14-5/25/14 Checkbox Null 1
John Doe 5/26/14-6/1/14 Checkbox Null 1
John Doe 6/2/14-6/8/14 Checkbox Null 1
John Doe 6/9/14-6/15/14 Checkbox Null 1
John Doe 6/16/14-6/22/14 Checkbox Null 1
John Doe 6/23/14-6/29/14 Checkbox Null 1
Jane Doe 4/28/14-5/4/14 Checkbox Null 2
and so on.......
I have thought about creating an SQL statement to select all of the clients and then creating a function that determines their admission date within the specific week and their discharge date withing the specific week and then create a loop with another SQL statement with a BETWEEN clause for all the weeks and determine if there is an entry in the WeeklyProgressNotes table or not. If not then I would display out the above info. I'm not sure if there is an easier, less search intensive way of doing it. Maybe an SQL query that can cut done on some of the looping.
I'm having trouble with a new project I'm working on. The application is mainly going to be used to display data, which comes from a linked table. It has to be a linked table (in my opinion) because it's replaced once per week from a fresh data dump. For each of those records, though, there will be notes made in a local table named "Custom-Data". My trouble is displaying a mix of information from the linked table, "Roster", and "Custom-Data" because linked tables can't be assigned a primary key.
Essentially, when a record is pulled up, a bunch of data from "Roster" will be shown in addition to the comments from "Custom-Data".
When I have a 3rd table looking at the row source of the 2nd table, which is looking at the row source of the 1st table. I only get numbers.
Everything I have read so far points to using a query as a solution. However, the query I made is not updating the information from the 2nd table.
I have attached a few examples.
Attachment 6247
this below shows. I changed "Bakersfield-test1" in the original table. However, the query I made does not update, still showing "Bakersfield-test". When you click on the drop down, it shows the updated "Bakersfield-test1", but it does not repopulate my column with the updated info from the table.
Attachment 6248
I have one DB that is used for creating/storing customer ID's, and another DB that is used for creating/storing job information for customers.I have linked the table from the customer DB to the job DB.
There is a table in the job database that holds customer name and ID, and some VBA that generates unique job codes.
Is it possible to have the data from the linked table automatically update into the existing table?
ok , i have a primary table that have a field of "job", then i create another table that have a field of "job" too , then how can i do this task?
Whatever i type a data into the primary table("Job" field) i want it to duplicate the data into the another table("Job" field) . is it possible to do this? can anyone guild me to do ?
Is it possible for the primary key of one table to act as the foreign key in another table for more than one columns? What I'm trying to do is create a table for a Committee which will have 1 student and 5 professors! So Can I import the faculty ID for each of the 5 faculty members?When I try creating the second relationship, access automatically creates a new Faculty table for the relationship!
View 6 Replies View Related