Queries :: Reference (parent) Description In Hierarchical Data Table

Apr 30, 2015

I have a table I'm trying to query information out of. Key fields are below:

RecNo (Key)
ParentRecNo
Description

I need to have a "record" in the query show both the description of itself, as well as it's parent. I was hoping to use Dlookup directly in the query design. If there is no other way, I suppose I could create a temp table and look through the recordset in VBA and dump them into the temp table, but like I said; quick and dirty was the hope here, it's for my own use, and the tables aren't large 50-100 records is what I'll be pulling out when I use this.

View Replies


ADVERTISEMENT

Queries :: How To Make Hierarchical Structure For Table

Jun 18, 2013

I have a table with below fields and data

Code:

DocNo TransmittalNo TransmittaltoCon

Code:
Doc-0001 tt-0002 con-0005
Doc-0005 tt-0002 con-0003
Doc-0001 tt-0002 con-0007

Now I like to make a treeview with 3 level of information of my table that level 1 is (Doc No) level 2 is (Transmittal No) and level 3 is (TransmittaltoCon) , after searching in google I read I have to make another table (query) with parent and position field for example

Code:

ID PARENT POSITION OPTION

Code:
1 0 0 DOC-0001
2 1 0 TT-0002
3 0 1 DOC-0005
4 1 1 CON-0007
5 3 0 TT-0002
6 5 0 CON-0003

View 1 Replies View Related

Queries :: How To Make Hierarchical Structure For Flat Table

Jun 23, 2013

I have 5 tables with below design

Table 1 : Docid (pk),DocNo

Table 2: Transid (pk),Transmittal No

Table 3: Docid (fk to table1),TransID(fk to table2)

Table 4: TransmittatoConID(PK), TransmittaltoCon

Table 5: Docid(fk to table 3),transID(FK to table3), TransmittatoConID(PK)
�combine fields�

I have made a query from top tables that returns the below data

DocNo TransmittalNo TransmittaltoCon

Doc-0001 tt-0002 con-0005

Doc-0005 tt-0002 con-0003

Doc-0001 tt-0002 con-0007

Now I like to make a query with adding new fields (parent) , (position) that shows

hierarchical structure of the fields

level 1 is (Doc No) level 2 is (Transmittal No) and level 3 is (TransmittaltoCon) ,

ID PARENT POSITION OPTION

1 0 0 DOC-0001 2 1 0 TT-0002 3 0 1 DOC-0005 4 1 1 CON-0007 5 3 0 TT-0002 6 5 0 CON-0003

View 1 Replies View Related

Queries :: Return Description Value Relating To PO Table Value

Feb 10, 2014

I have 3 tables joined as attached and Im trying to use the PO from the PO_Detail table to display the Description from the Material_Req Table. The two values are linked as the description in the Material_Req Table is for the PO in the PO_Detail table but I just cant get the results to show this.

View 8 Replies View Related

Table Design - Export Field Name, Data Type & Description

Feb 9, 2005

I was wondering if there is a way to export the Table Design Structure:

1) Field Name
2) Data Type
3) Description

Into a Spreadsheet.

I was able to do this a long time ago, but can't seem to remember. I'm using Access 2000.

Thanks!

Joe

View 3 Replies View Related

Tables :: Multiple Many-to-many Relationships With Hierarchical Data

Aug 5, 2013

I'm building a database about languages and the segments (sounds) they contain. So far it's a many-to-many relationship between languages and segments, and I've set it up as follows.

tblLangInfo: LangID (PK), language name, language family, etc.

tblSegments: SegmentID (PK), Segment -- this table has only one field, with 24 records, each one a type of sound I'm interested in

tblSegmentLangJoin: LangID (PK), SegmentID(PK)

I have another table, tblProcesses, with an (exhaustive for my purposes) list of the "processes" (a linguistic term) a language might have, which also has a many-to-many relationship with tblLangInfo. Thus two more tables:

tblProcesses: ProcessID (PK), Process name

tblProcessLangJoin: LangID (PK), ProcessID (PK)

Here's the fun part...

I'm interested in documenting which segments can participate in which processes, as either a trigger or target. I think this necessitates yet another table, tblProcessParts, with an exhaustive list of the decomposed processes, by which I mean:

Process1_triggers
Process1_targets
Process2_triggers
...

This table is thus also two fields, ProcessPartID (PK) and ProcessName_trigger/target.

The relationship I need to capture is: *given* a language, relate each segment to 0 or more ProcessParts. This relationship is many-to-many, and this on top of the other many-to-many relationships described above.

E.g., "t" in Lang1 might be a trigger and a target for Process1, but "t" in Lang2 might be just a trigger for Process1, while "t" in Lang3 might be neither a trigger nor a target for Process1 (though Lang3 does have Process1), and finally "t" might be neither a trigger nor a target for Process 1 in Lang4 because Lang4 doesn't have Process1, etc.

I've attached a picture to illustrate the relationship I need, since that's likely clearer.

One possibility I thought of was to change tblLangSegmentJoin to have a third field that is the primary key (LangSegID), and relate that to the ProcessPartID table. How to appropriately define my data tables and relationships.

View 3 Replies View Related

Queries :: Recursive Table - Get All Childs For Given Parent

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

Reference A Field On Parent Form From The Child Form

Dec 4, 2005

hi

how do i reference a field on the parent form from the child form e.g.

i have a button on the child form which needs to read a value on the parent form and use it i na calculation etc

thanks

View 1 Replies View Related

Autofill Data To A Main Table From A Reference Table

Mar 19, 2007

First of all apologies for the lack of proper terminology I'm a novice Ms Access user and I like to thank everyone in advance for trying to help.

Ok here is the situation:

I have two tables, NewJobs and Contacts which have the following fields.

Newjobs
--------
JobID (AutoNumber, Primary Key)
JobName
JobDate
JobDescription
JobOwner (Linked to table 'contacts' via LookUp)

Contacts
---------
DisplayName
EmailAddress
Department
Extension

Ok basically what I want is to have a form based on table NewJobs which will allow me to enter new jobs into the database. When I get to JobOwner a drop down list linked to 'Contacts' table will show me all the data from column 'DisplayName' and allow me to select it (saves time on typing). I have already done this and its not a problem.

Now I would also like in the same form to have additional fields from table 'contacts' such as EmailAddress, Department and Extension which will autofill with the right information soon after I select a JobOwner from the drop down list.

So for example if I select 'Joe Bloggs' Access will automatically fill the additional fields in the form with Joe's information (department, extension etc) from the Contacts table.

I hope all this makes sense. Thank you all for your support.

- Mitch

View 3 Replies View Related

Can Excel Spreadsheet Reference Access Table For Its Data

Mar 27, 2015

Can an Excel spreadsheet reference an Access Table for it's data? Sort of like a vlookup, but instead of referencing another spreadsheet, I'd like to pull data in from a database.

View 1 Replies View Related

Queries :: Display Field Description In Query

Dec 18, 2013

Basically, some clever soul has used coded fieldnames but then added a description telling you what the field contains, and I need to return this description.

View 5 Replies View Related

Status Bar Not Showing Field Description When Add Or Edit Data?

Apr 26, 2013

When editing a record in Access 2003, the status bar would show the value of each field's description from the table design grid. Is there a way to get Access 2010 to do the same?

View 1 Replies View Related

Option Button Description Table

Nov 13, 2007

Option Button Description table


I would like to create a table containing description value of my option button group.

This is my description table:
tblLenkeDESC
IDLenkeDescLenkeDESC
1Lenke 1
2Lenke 2
3Lenke 3
4Lenke 4
5Lenke 5
6Lenke 6

And this is my table containing selected option from the form:
tblLenke
IDIDNameLenke
114
225
336
441
552

How can I associate description with the selected options?

Many thanks,

Debbie

View 1 Replies View Related

Tables :: Combo Box - How To Get Description To Appear In The Table

Sep 9, 2013

I have a combo box in a form that is used to populate one of the columns in a table. How do I get the description to appear in the table instead of the primary key?

View 3 Replies View Related

General :: Combo Box Not Return Description To Table

Feb 20, 2015

My combo boxes are not returning the actual combo box values back to my table, instead in the table it is displaying the combo box data list number i.e 1,2,3,1,5,1 etc where it should be displaying a property address.

View 2 Replies View Related

Tables :: Using Access Table Description Attribute

Feb 18, 2013

I am using Access 2010. I have many tables that are downloaded from SAP into Access.

Within each table structure are the following standard Access attributes: Field Name Data Type Description

When I create a query I add the Field Name from the table nothing earth shattering here. But, in addition or instead of Field Name, I want to use the Description attribute. Mostly because the SAP field names are acromyms in German and are useless to the untrained eye. Please see attachment as an example.

Is there a way either using VBA or some special SQL language in any query I write to show me the Description instead of or in addition to the Field Name?

View 1 Replies View Related

Adding A Prefix To Existing Description In Project Table

Apr 17, 2012

How to add a prefix to an existing description in a project table, generated from the project number. Here's a sample of my data:

PROJECT NUMBER DESCRIPTION
01200000 Completed Projects
01601530 Steele Sub

I would like the Descriptions to read:
01200000 MO-20 Completed Projects
01601530 MO-60 Steele Sub

The state, "MO," comes from the first two digits "01" and the coop, "60," comes from the second two digits. I have a table of a thousand or so projects in an Access database that I need to amend the descriptions of to include these prefixes. Is there a simple way to do this in Access or in Excel without writing code?

View 1 Replies View Related

Filter Parent Data By Child Data?

Jul 11, 2006

All,

Is it possible to filter parent records by child records (or subdata) without showing multiple instances of the parent data in the form/query?

Cheers,

Bobadopolis

View 2 Replies View Related

Getting Data From A Parent Form

Apr 10, 2006

I have been having alot of trouble with this and just can't figure it out, I am trying to get data from a parent form into a subform so I can use it in equations. Specifically I have a DOB (Date of Birth) field on the main form, and want to have a =DateDiff("yyyy",[DOB],Date()) to find their age.

I need this in the subform because of some other information that relys on it. The way I have it right now you have to manualy put the DOB in the subform, and it can be different from the value in the main form. Could any of you tell me what I am doing wrong here?

View 4 Replies View Related

Modules & VBA :: Parent Table Revision History - Insert Multiple Records Into Child Table

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

Listbox Displays Data Associated With Parent Form?

Dec 23, 2006

Hey guys,
I was wondering... is there a way to have a listbox display values associated with a parent form ID? in other words... say I have a customer with invoices associated with their name, I want to display a form for that customer with a listbox (or maybe even subform) containing the invoice IDs associated with their name.
Right now, I've got:
SELECT qryClientData.InvoiceID, qryClientData.InvoiceDate FROM qryClientData ORDER BY [InvoiceDate]; to display information in the listbox. How do I modify this to display ONLY Invoices associated with the Clientname on the parent form?

I hope this makes sense. I can clarify if need be, I am just completely stumped. thanks and happy holidays!

View 7 Replies View Related

Forms :: Pre-populating A Subform With Parent Data

Jun 2, 2015

I have a parent and sub form in dataset view.

What I would like to do is prepopulate the subform with data from the parent form when I create a new item of data..

i.e.
When I click the "+" button Field1 on my subform is populated by Field1 on the parent.

I have tried beforeupdate on both the subform properties and the subform.Field1 properties.

View 3 Replies View Related

Modules & VBA :: Subform Calculations Based On Parent-form Data?

Jul 17, 2013

I have a form with a subform in a navigation menu. Something like:

Navigation Form
NavigationSubform
ParentForm (header data and some unbound calculation fields)
Subform (Multiple lines tied to query)

I have some fields in the ParentForm (i.e PF1, PF2) that would effect the values on the Subform (i.e. SF1, SF2). Also, there are some user editable values on the subform that will calculate the remaining fields on the subform (still using data from parentform).

My calculations work fine for the changes made on the subform. However I need to be able to calculate all the children lines on the subform when the form loads or a change is made to certain fields on the parentform. My On_Current event only wants to recalculate the first line.

Parent Form
PF1=10
PF2=3
Subform QTY ADJ SF1 SF2
Line 1 5 .05 10.6 112.89
Line 2 8 .14 10.38 105.15
...
Line N
SF1=PF1+(PF2/QTY)
SF2=SF1*(SF1+ADJ)

So if PF1 or PF2 were changed then all the lines is subform would recalc SF1 and SF2. If changes were made to QTY or ADJ, then that line would recalc SF1 and SF2.

View 3 Replies View Related

Queries :: Parent Child Query - Sum Images In Main Record

Oct 15, 2014

I'm trying to create a capacity report for my database. Originally, I only had the one table, which summed the number of Packs and number of Images, worked out percentage capacity used and then put it in a report, showing for each week of the year. However since then, I've added a child table to this, allowing me to create subrecords. The reason we did this was so we could easily group together multiple mailings under one single master record, so to speak.

Going back to the capacity, I've managed to work it so the Pack capacity is worked out from the mailing quantity in the subrecord. However, the number of Images is only entered onto the Parent record. Now my capacity query is summing the same number of images as there are subrecords, where in reality I only want it to sum the Images in the main record. I know I could add an Images field to the child table and work it out the same way, but I'd rather not do that (because that's not how our business works).

So essentially the question is, how can I sum the Images from the parent table without repeating the sum and massively overexagerating the sum?

View 6 Replies View Related

After Delete Trigger On Parent Table

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

Query On Parent-Child Based Table

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







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