Tables :: Calling Sub Tables In Parent Table
Oct 26, 2012
I am working on a complex database, as i have to call sub tables in the parent table. but i don't know how i can create it. My database format is as below
Village name, UC Name, District, village basic information, village disasters history, village school safety plan, village DMC members, Hazard Map of the village,
After the field of District i have to call sub tables against each field with its details. How I can create it. in Hazard Map field i have to attach the map img files.
View Replies
ADVERTISEMENT
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
Jul 6, 2006
Sorry 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
View 3 Replies
View Related
Jun 21, 2013
I am trying to call a subroutine from a VBA routine to append a record to an existing table. Below is the code for opening my recordsets for reading followed by the code creating my table to append to:
Public Sub UpdateLotsizes()
Dim curDatabase As DAO.Database
Dim ProdPlanDB As DAO.Database
Dim rstQryLot As DAO.Recordset
Dim rstInvLoc As DAO.Recordset
Set curDatabase = CurrentDb
[Code] ....
To call my subroutine, I simply put in the code:
ExceptionReportAddRecord
And this is the code of the subroutine:
Public Sub ExceptionReportAddRecord()
Dim rstExcRpt As Recordset
Set rstExcRpt = curDatabase.OpenRecordset("tblExceptionReport")
With rstExcRpt
.AddNew
.Fields("item_no").Value = txtItemNumber
[Code] .....
I am receiving "Run-time error 434: Object required" at the line I marked with ...
View 4 Replies
View Related
Jul 27, 2015
I have one parent table with 6 child tables (all child tables are linked to the parent table in the relationship through the BoardID autonumber field, and they are all one-to-one relationships). All of the tables feed into one form. I have added the "add new record" button to the form, but when I click on it, it only adds a record to the parent table. The only way I have found to solve this is to go into each individual table and add a new record. Is it possible to have a new record added to every table when I click the "add new record" button on the form?
View 7 Replies
View Related
Sep 18, 2012
I have two tables, Articles and Versions, related 1 to many. In my Versions table I want to set up a field that will start at 0 for the first version record related to an article record, and as version records are added in relation to that same article record I want it to increment by 1, no gaps. It ideally would be editable. It will not likely count above 5.
The result would be
Code:
article1 version0
article1 version1
article1 version2
article2 version0
article2 version1
article2 version2
article2 version3
What's the best way to accomplish this?
View 3 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
May 29, 2014
I have two tables of data, each relating to three business branches (branches A, B and C).
Table 1 shows the expenditure of each branch (by fuel, premises and wages).
Table 2 shows a number of units for each branch (mileage, floorspace and sales).
What I would like to do is calculate unit costs, based on the expenditure in Table 1, divided by a relevant unit in Table 2. The catch is that I want to have a third table which allows the user to specify which expenditure (from Table 1) is combined with which unit (from Table 2) to generate the calculated unit costs. I've been able to do this in Excel, and have attached an example. I've also attached an incomplete Access version with the first two tables. Given the complexity of my actual data, I feel this could be better handled in Access than Excel.
View 6 Replies
View Related
Feb 18, 2013
I have an Access Table with about 28,000 Automobile dealerships across the country shown. I've joined a new/small phone contact table to this to keep up with our phone contacts with the dealership and followup efforts. When I search/filter on the dealership table all is fine. However when I search/filter on the phone contact table with a few test entries, I get nothing at all. I supposed that after joining the tables, I'd be able to do a search on the field named follow up date and find/filter today's date or other dates and locate which dealerships to contact when the correct date arrives. But nothing.
View 14 Replies
View Related
Mar 29, 2013
I have a feed from ODBC to Access DB. I need to populate several new tables with the contents of the original table. I know I'm being stupid but it's been a long week.
View 1 Replies
View Related
Jul 8, 2013
I need to combine two tables that have identical structure to one table. Different people have used them to insert data from different parts of our project. I have tried to find information about how to do this but so far with little luck.
View 13 Replies
View Related
Jul 30, 2013
I have three tables. I want to combine all the records in these tables into one table. I need VBA code to do this. The first table is called down1, the second table is called down2 and the third table is called down3. All these tables contain the same fields so I don't think combining them will be a problem.
View 3 Replies
View Related
Apr 12, 2013
I've got a working database with pretty good structure etc. How I would go about creating 3 prepopulated records on a many side of a relationship on entering a new record in the one side of the relationship.
View 6 Replies
View Related
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
Oct 27, 2014
I have a db with 2 tables one with 10 fields and the other with 11 fields. In these tables there are 4 and 5 non common fields in tables respectively.
How can I join these tables in a 3rd table with all the fields from both tables.?
View 1 Replies
View Related
Aug 23, 2013
I have 10 tables. None of them have any entries in them. Just field names. Is there a way to combine them all into one table.
View 14 Replies
View Related
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
Sep 8, 2014
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.
View 14 Replies
View Related
Jan 25, 2014
I am new to MS access. I have to delete records from the 2 tables, when the parent record gets deleted. I have a delete button in my form wherein the user deletes the particular record from the parent table.
When the delete button is pressed, the parent record gets deleted from the table. But I need to write a after delete trigger on the parent table to be able to delete the relevant records from the other two child tables, based on the current parent id.
what code should I write ? and should I locate the records in the before delete trigger and then write the code to delete the child record in after delete trigger ?
View 7 Replies
View Related
Oct 16, 2007
Hi
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.
View 3 Replies
View Related
Feb 25, 2014
I've a table that looks something like this:
id, parentID, name
ParentId links to id.
Now I would like to get all "child-nodes" for a given parentId.
The recursion goes 4 levels deep.
What I've so far is:
Code:
SELECT t1.id, t2.id, t3.id, t4.id
FROM tblPersons AS t4
INNER JOIN (tblPersons AS t3
INNER JOIN (tblPersons AS t1
INNER JOIN tblPersons AS t2 ON t1.id = t2.parentId) ON t3.parentId = t2.id) ON t4.parentId = t3.id
WHERE (t1.id=1234);
Ok that works but the result is quiet ugly
What I get is something like
Code:
id1...id2...id3...id4
1 20 50 51
1 20 50 52
1 20 60 53
1 20 60 54
Now I could use VBA to take a look at each column and store all unique numbers... but is there a more simpler way?
View 7 Replies
View Related
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
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
Apr 23, 2013
I am using calculated field as a data type in access 2010.
They are working fine.
However, I added a new field and now the final calc won't work.
I have Subtotal adding loads of fields together. Works fine.
Then I have a VATunit field which is a double integer, so enter 20 and my next field is VATTotal calculates the SubTotal + the VATunit by doing (Subtotal/100)*VATunit. This calculation is fine and gives me the correct amount.
The next field is a Total field. Which adds Subtotal and the VATTotal together. Howver, the Total is the same as Subtotal. It is not adding the VATTotal to it?
View 2 Replies
View Related
Apr 19, 2007
I have some experience doing 'Update Query' using two different tables but I'm having a hard time doing an 'Update Query' using 3 tables.
I have my source table TP05XY with the fields 'Mark' 'Date' 'UTM_Edig' and 'UTM_Ndig'. Mark and Date are my primary keys (they together uniquely ID each record). I have my Observations table with the fields 'Mark' 'Date' and 'Obs_ID'. The last table is Locations with 'Obs_ID' 'UTM_E' and 'UTM_N'.
I want to update my fields UTM_E and UTM_N from UTM_Edig and UTM_Ndig. However, to do so, I have to go from my TP05XY table, through Observations table to update Locations table. Table TP05XY is joined to Observations through 'Mark' and 'Date' and Observations table is linked to Locations through 'Obs_ID' field.
I have tried a few options without success ... anyone knows how to do it?
Thanks,
Josée
View 1 Replies
View Related
Jul 19, 2014
I have being playing with ms access but I really don't know much about it or databases in general.I have created a very simple database to gather twitter following/followers data for research purposes.One table (table01) has a field for the "boss" user (=the user who I gather data for), another field for "client" (=bosses followers or friends).Both fields are numeric and contain the users id's.In order to distinguish if the link is follower or friend there is a third field, called type which can be either 1 (=follower) or 2 (=friend).So the data would look like this:
boss - client - type
12345, 67890, 1
12345, 54321, 2
If user with user id 12345 had a follower (type 1) with user id 67890 and a friend with user id 54321...In order to avoid getting duplicate rows I also added a unique identifier which is of the form boss_id-user_id-type.So the above row looks like this:
12345-67890-1, 12345, 67890, 1
12345-54321-2, 12345, 54321, 2
That works just fine.For several reasons I also needed data of the form source - target.So I also made another table (table02) of this form.
67890, 12345
12345, 54321
...
In table 2 you don't need the "type" field since the position of the user id shows the type of relationship.Still, you need a unique identifier in order to avoid duplicates, so I added on with the form: source_id-client_id..So table02 lookes like this
67890-12345, 67890, 12345
12345-54321, 12345, 54321
...
Both tables also have a date/time stamp for each line.As you can see, table01, having also a type field is bigger than table02.The problem is when I try to append data, exactly the same data in both tables.Appending data to table01 is ok, while appending data to table02 (which is smaller, having one less field) takes a really long time, maybe 10 times as long as appending data to table01.To make sure that no query's are causing the problem I have tried first creating temporary tables with the data to be appended, no duplicates, nothing that would cause the database to make extra calculations and used a simple update query with no filters to append data.Still I get the same result, table02 takes a very long time to finish while table01 finishes in no time.
View 2 Replies
View Related
