Tables :: How To Deal With Exception To Related Tables

Aug 27, 2013

I am having an issue where a small percentage of my records do not follow my related Tables.

ex: 3 tables
tblEmployee
-intEmployeeID
-intWorkCenterID

tblWorkcenter
-intWorkCenterID

tblManager
-intManagerID
-intEmployeeID
-intWorkCenterID

where:
tblemployee.intworkcenterID -- tblworkcenter.intWorkcenterID -- tblManager.intWorkCenterID

Basically I have my general employee information in the tblemployee table, the workcenter information in the tblworkcenter, and the manager of the Workcenter in the tblManager. This is correct for 99% of the employee population. however, there are a few employees whose manager isn't the 'general' manager for the employees assigned workcenter.

How I can adjust an individual employees manager, while maintaining my structure? or would i have to go back and add the managers ID to a field in the tblEmployee Table?

View Replies

Tables :: Developing Related Multi Level Child Parent Tables?

Jan 14, 2013

I have created a table that acts as a header for my data and a second table that acts as line item data. What I need to do now is add a second child table that uses the line item data as its parent table and stores associated line items for each record. Is this possible?

This is a skeleton view of what I'm going for:

Master Table:

tlbAuditReportHeader
- AuditDate
- AuditArea
- Auditor

Sub Table:

tblDiscrepancy
- Discrepancy
- CorrectiveAction
- ActualCompletionDate
- VerifiedDate
- Notes

Sub table to Sub Table

tblFollowUp
- FollowUpDate
- AssignedTo
- SpokeWith
- EstimatedCompletionDate
- Notes

Sometimes tasks change hands or are pushed back depending on work load. It would be nice to be able to track something like this.

View 1 Replies View Related

Moving Data From 2 Related Tables To 2 New Tables As An Archive

Apr 3, 2007

I have a form displaying the 11 fields of the parent/primary table using a selection from a combo box. I am using queries and vba code modules respective to form, combo box and command buttons. I have initial code that uses the two fields from the combo box selection to append same to a new parent/primary archive table. I now want to add to the append SQL the remaining fields to the parent/primary archive table. When I add the second sql string for the remaining fields to the same procedure and execute I keep getting 'null in primary key'. If I copy the primary record and paste same into the archive table it works.

Private Sub Command26_Click()
On Error GoTo Err_Archive_Primary_Click

Dim strSQL As String
Dim strSQL2 As String

strSQL = "INSERT INTO ARC_289325045 ([Survey Point ID], [Survey Area Detail], [Date On Site]) " & _
"VALUES ('" & Me.cboAreaDetailDate.Column(0) & "','" & Me.cboAreaDetailDate.Column(1) & "'," & _
"#" & Me.cboAreaDetailDate.Column(2) & "#)"

CurrentDb.Execute strSQL, dbFailOnError

'strSQL2 = "INSERT INTO ARC_289325045 (RecordID, UnitID, UserName, [TimeStamp], [Survey Point - Area], Measurement, NewArea, [EXIT Form] ) " & _
'"SELECT FORM_ID_289325045.RecordID, FORM_ID_289325045.UnitID, FORM_ID_289325045.UserName, FORM_ID_289325045.TimeStamp, FORM_ID_289325045.[Survey Point - Area], FORM_ID_289325045.Measurement, FORM_ID_289325045.NewArea, FORM_ID_289325045.[EXIT Form] " & _
'"FROM FORM_ID_289325045"

'CurrentDb.Execute strSQL2, dbFailOnError

Exit_Archive_Primary_Click:
Exit Sub

Err_Archive_Primary_Click:
MsgBox Err.Description
Resume Exit_Archive_Primary_Click

End Sub

The next step is to do the same for the child table and append related records to the child archive table.

View 4 Replies View Related

Moving Data From 2 Related Tables To 2 New Tables As An Archive

Apr 3, 2007

View 1 Replies View Related

Tables :: Splitting One Table Into 3 Related Tables

Jul 16, 2014

I'm trying to split a table up because I now realize it won't be able to do what I need in the future. It wasn't designed properly at the outset, and I'm trying to correct it now.

This is a database of pregnancies and deliveries.

The single table does not cater well for multiple pregnancies (twins, triplets, etc), and also I foresee problems when mothers come back in future for another delivery.

Therefore I am trying to separate data into 3 tables: Mother, Delivery and Baby.

This is because each mother can deliver more than once, and each delivery can have more than one baby.

I have set up a trial database, with just a few fields in each table to see if this works. An screenshot of the table relationships is attached.

The primary key of the Mother table is linked to the Delivery table, and ditto the primary key of the Delivery table and the Baby table.

I'm not sure how to migrate the data over, in terms of the primary keys of each table, because in the new tables, these should be an AutoNumber field, so that they are unique numbers.

View 14 Replies View Related

Tables :: Update Data In Related Tables?

Oct 22, 2014

I have to make a Costing System but for that I need to enter our Expense details in database according to Fiscal year and months.

I need a table for Fixed expenses and one for Variable Expenses and then I need one or more Forms to update data in those tables. Now I've created a table with Fixed Expenses. I have to update Year and Amount in that. Now it is only letting me one entry per Expense.

I want multiple entries for one Expense say 'Advertising' for different years. I'm thinking may be I need to make more than one Table, may be one for Expenses with ExpenseID and other for Years with Year and YearID and the third one for Amount with columns Amount, Method of Payment, Date and Notes. I did tried this but I think I'm not creating proper relation may be because its only updating for one year.

I'm using Access 2003.

View 2 Replies View Related

Tables :: Saving Data To Related Tables?

Sep 27, 2012

I have a built a database to record and collate info about clinical trials that are run within my department. I have one large table [Trial Info] which contains all trial info and have created a second and third table [Milestones] & [Comments] to collect dates of various milestones and a notes that occur throughout the course of a study.

The milestones (dates) are recorded in the 2nd table and the comments are recorded in the 3rd table.

All data is entered by staff on forms. The first form captures basic info about the study, once this has been entered, the idea is to click a button to open the second form to document the trial milestones, this form also contains a subform for listing any free text comments required during the life of the study which are stored in the 3rd table.

I have setup the relationships between tables as best I can.

The main table with basic trial info is the parent table, linking the primary key to the foreign key in the second table, the foreign key of the third table is related to the primary key in the second table so these tables should be looking up the correct clinical trial as far as I can tell.

The form containing data from the main table has a button which I have set to open the Milestones & Comments form, and I have set this to open to the record that relates to the record in view in the primary form. The primary key is related to the foreign look-up key in the milestones table.

This opens the form fine, however this is not updating the foreign look-up key, this remains '0' which I assume is the cause of the following error when I try to save changes to the record

Quote:

You cannot add or change a record because a related record is require in table 'Trial Info'

How do I trigger the foreign key to update to the primary key so that the first time a milestone page is opened for a new study it creates a matching record?

View 4 Replies View Related

Tables :: New Row To Two Related Tables With One Form

Aug 6, 2013

So I have a form, it has a few fields from a related table in it, related by the name. I click 'new' on the form, but when I click save Access complains about the related table does not have a matching record. How do I have it create a new record?

View 2 Replies View Related

Updating Related Tables

May 15, 2006

Hi everyone!

My names james, im 18 and im a data manager for a school.

My schools database stores student data such as names, classes and exam grades. I have a table containing each students personal details such as name and address. Each pupil is assigned a unique PupilID which is the primary key. I then have a number of other tables linked to it with one to many relationships. Each of these tables holds a different set of exam results. The tables are linked through PupilID. The primary keys for the other tables are auto numbers.

The problem is this: when i add a new student to the Students table i cannot get their PupilID to automatically be entered into the other tables containing grades as part of a new field. How can i make this happen?

Thanks.

View 9 Replies View Related

Updating Related Tables.

Jan 22, 2007

Ok due to user error (and my own fault for not archiving an old database) we now have two databases which have had data entered into with the same table structure.

The table structure we have at the moment is with the related field in []:

Propertytbl[Propertycode] is related to Systemtbl[sysID] which is related to Assessmenttbl.

There is also another Temperaturetbl linked to Systemtbl by [sysID]

The problem i have is sysID is an autonumber. I can't simply paste in the difference in data as the assessmenttbl records and Temperaturetbl records will point to a different system (as the sysID will change in the Systemtbl data)

Can anyone think how i can do this either through using queries or programmatically so that i don't have to enter 5000 records manually.

I've tried using append queries but this produces the same affect as above.

Many thanks

Matt Collins

View 1 Replies View Related

Updating From Related Tables

Apr 2, 2007

I have a combo box in my main form that gets its list of physician names from tblPhysician. When the physician is chosen and the record saved, it saves the PhysicianID to my main table, tblOncReg.

When I delete a physician, any record that had that name chosen continues to have that name in the record despite it being deleted from tblPhysician.

I thought that setting relationships would solve it, but when I select "Enforce Referential Integrity" and "Cascade Update Related Fields", I get an error saying "Invalid field definition 'ID' in definition of index or relationship.", and it doesn't set the relationship.

Would setting the relationship solve that problem?

View 4 Replies View Related

Can Related Tables Have Same Field?

Sep 2, 2007

In a case where two tables are related, can the field names which relate them be the same? Or is it necessary to use different field names?

Robert

View 4 Replies View Related

Appending Related Tables

Jan 26, 2006

whats the best way of appending tbl_orders and the related data in tbl_order details to the two identical archive tables when the "COMPLETE" column is "YES" in tbl_orders

View 1 Replies View Related

AND Query, Across Related Tables

Jan 12, 2007

Hello, I apologize in advance if there have been lots of questions like this, but the search won't let me use AND as a search term!

I have two related 1 to many tables. The parent table contains clients, and the child table contains categories, each client can have many categories. (i originally intended to have the categories be columns in the client table, in which case what i want to do is easy, however, then it becomes a nightmare when the user wants to add a new category hence the related situation described.)

Is there an easy way to find all the clients who have both category 7 and category 10? I can do it writing a query to find all the 7s, then another to find all the 10s, and a third to find those which have both. I am hoping there is an easier way, as i need to give the user a way to search via categories in whatever combination they fancy. The OR's i can do easily it's the AND's that are causing the problem.

The only idea i have at the moment is to make a temp table with the the clientid, and a long field holding each of the category ids, seperated by commas, and then searching using like "*7*" and like "*10*".

Does anyone have any better ideas, i'm hoping i'm missing something really obvious......

Thanks, Anna

View 1 Replies View Related

Tabs With Related Tables

Aug 16, 2005

Hi,

I have a database with a couple of tables. All of which have a 1:1 relationship to another table, patient:
- patient (id (auto_increment), name, address, etc)
- nutrition (patient_id, some, stuff, etc)
- sleep (patient_id, foo, bar)

The patient_id in nutrition and sleep is a primary key and corresponds with the id of a patient (my 1:1 relationships)

I have a couple of forms. One for each table actually. In this form the user should be able to add/modify a patient's data. These forms are not to be used by themselves.

I have another form(the main form), which has a tab-control element on it. There are 3 tabs on this and on these tabs the 3 different forms are displayed.

Now for the problem:
- when I add a new record to the database using the main form, there is no problem when adding a new patient. Woohoo! When I switch to another tab, , however, the problems start: I can enter new information, but when I leave the tab I get an error: "Index or Primary key cannot contain a null value." Blech.

I want Access to realise that it should use the id that is given to the patient in the first tab, but it seems it cannot get it automatically. (I am not sure, but I think the problem didn't occur at first, so I think I may have done something which is causing this problem.)

Any help here is appreciated.

PS I was unable to find any posts on this forum that can help me with this. The closest I could find was: http://www.access-programmers.co.uk/forums/showthread.php?t=39933

View 4 Replies View Related

Lookup Query For Related Tables

Sep 9, 2005

I have a table of 'things' and two related tables, type and subtype. Each thing has a type and a subtype. The types table contains simply Index (autonumber) and Type (text). The Subtype table Index (autonumber), type (number) and subtype (text), where index and type form the primary key and type is a foreign key to the types table. Thus each type has its own sub-set of subtypes.
In the design of the Things table I have set the lookup for Type and Subtype to be a dropdown, but of course the subtype drop-down shows all subtypes, not just valid ones. If I select a subtype that is incorrect for the selected type then of course I get an error, but it would be nice to set the query in the subtype lookup's Row Source to just display the valid subtypes. At the moment it just says "SELECT index, type FROM subtype" How do I put in a WHERE clause that references the Type field for the current row: "SELECT index, type FROM subtype WHERE type=<type selected in current row>"?

View 3 Replies View Related

Date Related Query Across Tables

Jun 13, 2006

Hi, Ive got another query I need to do which I just dont know where to start from.
I have two tables, membersdata and bankdata. These tables are linked in a relationship thou the field 'description' in both tables.
What I want to do is be able to input a date and have the fields I require from both tables displayed. the dates are stored in the bankdetail table. I have made a query that allows me to input the dates in the bankdata table and that works but I need the membersdata to be shown on this query also.

How can I get the query to show up all the data i requre from both tables?

This query will give me the information of people that have paid us in the dates selected. I also need to revese trhe query so it shows the people who have not paid. so Im basically showing the records with a null value in the description field.

This ones totally caved in my brain this time of the morning! Any help would be fantastic!

Cheers
Phill

View 1 Replies View Related

Querie Involving Related Tables

Mar 28, 2007

hello, I have tables which have relationships in them. for example, ID appears within 3 different tables for the same item. How can I set up a query to delete all records with that ID from all of the tables in one go?
Thank you,
JDRasq

View 1 Replies View Related

Form Deisgn For Related Tables

Apr 17, 2005

This will be a definate newbie question but here it goes.... I am new to Access and fighting my way through building a database to learn. I have broken my tables down to as normalized as I am willing to go, mabey a little further than needed. So now I have several tables that are related. I need to know how to make a form update several tables at once and prevent the user from having to mess with the autonumbers that are created. Here is a sample of my tables:

All Primary Keys (PK) are autonumbers

tblStaff
StaffID PK
StaffTypeID FK to tblStaff
FirstName
MiddleInitial
LastName
SSN
AddressID FK to tblAddresses

tblStaffTypes
StaffTypeID PK
StaffType

tblAddresses
AddressID PK
AddressTypeID FK to tblAddressTypes
AddressLine1
AddressLine2
CityID FK to tblCities
StateID FK to tblStates
ZipID FK to tblZips

tblAddressTypes
AddressTypeID PK
AddressType

tblCities
CityID PK
City

tblStates
StateID PK
City

tblZips
ZipID PK
Zips

I now know that I may have taken the normalization a little further than needed but I have it done and would like to use it if I can.

I want a form where a user can add a staff member with all of their information on one form (name, ssn, and address) without having to deal with the autonumbers or needing to do it in a certain order to make it work. I also don't want excessive use of combo boxes (state is OK). So far I haven't found a way to do this, please help.

View 7 Replies View Related

Use One Form To Add Data To Two Related Tables

Jul 24, 2006

I need help. I am not sure if this is a form issue or a logic issue i have a db for tracking students the classes they take and their notetakers for classes. Each class is most likely unique to each student but sometime there are multiple students in each class that need a notetaker and there should only be one notetaker per class. I have 3 tables and they are all linked through a unique iD called CRN (number).

I am looking for a way to add a student to a class and create a class at the same time in one form. I am trying to limit the redunancy by adding a class each time and then adding a student to the class.

Any suggestions would be great. I have gone through 10+ redesign and am just at a loss on how to accomplish this.

View 1 Replies View Related

Tables :: Related Table IDs Not Updating

Dec 3, 2012

I'm not sure why a couple tables are not being updated after entering data. I think my relationships are correct. I've attached the database.

steps:
open Main form
add addres then try to add an owner. it doesn't work the first time but it does work the second time. add test data and that works. now if you look at the related tables (OwnerInfo, WellLocation, TestData) they all have p_id which is the wellLocation Id

now open gernalinfo form
enter in info (tests requested doesn't work right now but you can click on them in the tbl GeneralInfo)
Now if you go to the table OwnerInfo and TestData the key p_id has not been updated. But it's there in WellLocation (ID).

Why are these tables not getting updated?

View 3 Replies View Related

Forms :: 2 Related Tables By ID Field

Apr 28, 2014

I have 2 related tables by ID field.

In a subform I have all fields of a one table, when I make click in a button, open other form with info related by these ID.

All is ok but I have some doubts:

If in the second form (second table) there is no registers, how could shows me the ID by default and that the ID field is not empty

If I create a new register inside second form with a button, I would like that the ID field was always filled because the user don't need to know the ID.

View 2 Replies View Related

Cascading Delete Between Two Tables (1 To 1 Related)

Oct 17, 2014

I am not too clear about why it happens. See following diagram:

My TBLItemID table is having several 1-to-1 related tables

I have enforced referential integrity and checked off Cascade delete related records

When I remove a record from TBLItemID the same row will be deleted from every one of the related tables

Yet when I go into any of the related tables and remove a row, via the 1-to-1 relationship I would expect that it will propagate through the whole web and remove the same row from every table. However that is not true. It will only remove its own record without touching any one else's.

How is cascading delete supposed to work? Does it work only when we remove records from parent table?

View 1 Replies View Related

Populating Unbound Related Tables?

Nov 14, 2012

I need to make one database with information about torque values according to one defined table. I make 3N so I can avoid duplicating data, by my problem now is how to enter data into the related tables and save the data using a save button. I want to have control about when data is saved that is the reason because I used unbound controls to enter data and I want to use also a new button to add new records.

View 11 Replies View Related

Pull Unique Recs From Many-to-Many Related Tables

May 13, 2005

Greetings all! We have a mentor-mentee database that's been setup with a many-to-many design. We're storing essentially the same data in each table except for the field names. We thought we were good to go but one additional request was made to determine a count of employees participating in the program (regardless of whether mentor or mentee) in each section of the company. We don't want each person counted multiple times if they are both a mentor and a mentee, or if they have >1 mentee or >1. I created a couple of queries to pull unique empIDs from each table to narrow down each table BUT I'm stuck on being able to pull unique id's from both tables.

I first tried the unmatched query wizard from both viewpoints but then I don't get those cases where a mentor is also a mentee. In other attempts, I end up with a cartesian product. I've cruised through the queries section but haven't come up with any examples related to this type of situation.

I need a way to pull both unmatched records from each table while also getting essentially the first occurrence of instances where mentorID=MenteeId. I'm no SQL expert so if someone can think of a statement that would suffice, it would be greatly appreciated.

View 2 Replies View Related