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 Replies
ADVERTISEMENT
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
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 1 Replies
View Related
May 20, 2014
I have a question about the fast food project for the restaurant, how to make the structure of the table ! also they want to working by the touch screen , if the database i created by Access and SQL it is worked on it ?
View 2 Replies
View Related
Apr 20, 2015
I'm trying to copy the structure of a table to make a temp table. I'm using CopyObject (which also copies the data). So when I delete the data from the temp table, it also deletes data from the source table. Is the data linked? It should just be deleted from the temp table. Below is the beginning of the code. I've stepped through, and at the last step shown, the data in the source table deletes.
Code:
Dim strFile As String
Dim temp As String
Dim tbl As String
Dim db As DAO.Database
' error handle
On Error GoTo F_Error
[Code] .....
View 3 Replies
View Related
Oct 14, 2013
How can you determine the name of a table that has/is going to be created by a make-table?
View 2 Replies
View Related
Mar 14, 2007
I know that this could open a can of worms but is the above statement true. I have always avoided using delet and append due to database bloat. I know its more difficult to set the field types in a make table query but it means that the database doesn’t need to be compacted nearly as much as delet and append.
If my understanding is incorrect please explain top me why delete and append are better.
Thanks
View 9 Replies
View Related
Sep 25, 2006
Hello All, I'm using MS Access 2003 for a web DB... Am wondering.. is it bad to use Make-Table queries for the web..? Or should I just do all the SQL joining and linking stuff just with more SQL code?
Just curious about the impact Make-Table queries have on the Web.. Slower? Not a good idea?
Thanks for any input you can give
John
View 1 Replies
View Related
Jul 28, 2006
I have a linked table with 3 significant columns in it:
Marque
Model
Volumes
(there's actually about 12, but I only need these)
and I need to create a new table summarising the contents and creating new columns at the same time
Marque
Model
Model_name (concatenate marque and model, easy to do)
Vols sorted: Descending
Rank (this is the problem)
I need to 'Rank' the table so the model with the highest volumes is ranked #1 the second highest is #2 and so on.
Is there a command within access to allow this to happen? in SQL-Plus from Oracle I can use the Rownum command to create the entry, but this does not have an equivilent in access.
At present, I'm creating the table without the rank field, then adding it in design view, setting it for autonumber, saving, and resetting it to number. This is long-winded and frankly, messy and wrong. I shuld be able to do this in one go, but I can't. I've been using access for about 6 years solid and have not been able to resolve this 3-month-old problem and it's driving me mad.
Thanks in advance for your help.
View 3 Replies
View Related
Mar 28, 2006
I have 4 queries needed to power one report. I've tried combining the queries into one so I can run the report off that. However, there is too much data and I get all sorts of errors. My only solution was to toss that data into a table thereby eliminating all the calculations and expressions and cleaning up the data. Is there a more elegant solution to this issue?
View 3 Replies
View Related
Mar 28, 2006
I have 4 queries needed to power one report. I've tried combining the queries into one so I can run the report off that. However, there is too much data and I get all sorts of errors. My only solution was to toss that data into a table thereby eliminating all the calculations and expressions and cleaning up the data. Is there a more elegant solution to this issue?
View 2 Replies
View Related
May 2, 2006
I have created my first Make-table query and it actually worked!!!Yeah! But now, is there a way to make this table updateable each time the query is run?
View 3 Replies
View Related
Apr 9, 2013
Is there a way of keeping/setting the primary key when using a make table query?
View 2 Replies
View Related
Aug 30, 2013
I need to put together a make table query, already got all the fields sorted the way i want the problem is that query is located in my databases Front End and i need the table to be made in the Back End (giggity), the filepath for the back end is actually stored in a "Master Control" table if this makes the process easier...
View 3 Replies
View Related
Jan 2, 2014
I have a complex series of calculations to perform, and I have found the best way to achieve my end is to create new tables by the Make Table queries.
The trouble is I have many tables to create and the downstream tables refer to the previously created tables; I have recently discovered that queries perform much quicker if they are indexed (duplicates OK). I've looked at my tables created by the MakeTable queries, and they all seem not to be indexed.
How do I make them indexed automatically?
View 2 Replies
View Related
Sep 18, 2013
I am developing a Make Table Query from 2 tables, one of which has an "Employee Name" field (lastname,firstname) and the other table has separate fields for LastName and FirstName. I've been able to accomplish almost what I need by:
WHERE ((([Table 1].[EMPLOYEE NAME]) Like [Table 2].[LAST NAME] & "*"));
Which works fine except when there are 2 employees having the same last name, then it generates duplicates. I suspect there must be a way to do this by incorporating the first name field in the sql statement but that's beyond my ability. I realize that names are not good things to base a query on, but the 2 existing tables have been preset and populated by others and I don't really have the capability to change them.
View 3 Replies
View Related
Jul 15, 2014
I have a make table query which creates the new table on the local front end.I have now split the DB, so need to know how I can get this query to make the table on the BE i.e. how do i link it.
View 14 Replies
View Related
Jul 13, 2006
I have a Make Table query that includes a new field concatenated from a numeric field and a memo field, with some assorted text elements -
Issue Description:"("&[number]&") "&[Description]
When I run the Make Table, the new field "Issue Description" is created as a text field, NOT as a memo field.
Is there any way to insure that this field is created as a memo field.
Thanks.
Susan
View 1 Replies
View Related
Jan 14, 2014
I am looking to make a table which adds a sequence number to my records.
Example data would look like this
Acc no Date
1234535 01/01/2013
1234535 05/01/2013
1234535 11/01/2013
9876543 02/01/2013
9876543 22/02/2013
9876543 01/03/2013
I want it to add a deq based on the account no then date in sequence oldest to newest.
Acc no Date Seq
1234535 01/01/2013 1
1234535 05/01/2013 2
1234535 11/01/2013 3
9876543 02/01/2013 1
9876543 22/02/2013 2
9876543 01/03/2013 3
View 5 Replies
View Related
Oct 8, 2013
I am trying to run a make table query that involves using a linked table in Ms from a SharePoint list. However, a few columns I wish to include in my Make table query are calculated columns from the SharePoint list.
The make table query will not run saying "Calculated columns are not allowed in SELECT INTO statements".
Can any think of a solution or a potential work around to this?
I need to create a table with these columns in it.
View 4 Replies
View Related
Jun 26, 2013
I have a current file with GBP 12.00. I wish to remove the GBP in a make table query when ran to print off.
View 2 Replies
View Related
Apr 17, 2013
How do I delete duplicated records without having to make a new table? I would like the records deleted from the original table.
View 13 Replies
View Related
Aug 13, 2013
Wondering whether it is possible to use the 'Make Table' function in conjunction with a union query. My early attempts have failed and I am relatively new to Access.
I am using Access 2010.
View 2 Replies
View Related
Nov 3, 2014
I can add to my make table query UDate: Date() that will add a populated field to the output table that has today's date in it.
I can also add something similar like this for example V_Num: [V_Number] and when ran, will prompt with a dialogue box to add some data, it will then create the table with that new field name and populated with the text from the dialogue box.
But how can I automate this to skip the dialogue box and just add it to the expression?
View 3 Replies
View Related
Aug 8, 2013
I have a make table query that gets its info from another table that is updated weekly. I would like that when we run the Query Type (Make Table), we would like to add a primary key to a field that exists already starting at 1 every time the query is run.
View 3 Replies
View Related
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
