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 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

Relational Structure To Flat Excel File?

Jul 17, 2007

Hope someone can help with this.

Basically we have an Access database with a main table, and several linked tables, joined on a unique ID.

For example :

tblFeatures :

FeatureID
FeatureName
etc

tblCategories
FeatureID
Category

So each feature can have many categories in a one-to-many relationship.

What I have a requirement to do is to pull that out as a flat file in Excel, ie

FeatureID, FeatureName, Category1, Category2
etc

What's the best way to go about this?

Cheers,
Iain

View 2 Replies View Related

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 1 Replies View Related

General :: How To Make Structure Of Table

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

Modules & VBA :: Copy Structure Of A Table To Make A Temp Table

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

Queries :: Make Table Query - Find Table Name

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

Never Use ‘make Table’ Queries.

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

Make-table Queries For Web?

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

Row Numbering In Make Table Queries

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

Avoiding Make-Table Queries

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

Avoiding Make-Table Queries

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

Make-Table Queries - Can Table Be Updated?

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

Queries :: Maintain Primary Key In Make Table?

Apr 9, 2013

Is there a way of keeping/setting the primary key when using a make table query?

View 2 Replies View Related

Queries :: Make Table Query In Different File

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

Tables :: Make Table Queries - Indexing?

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

Queries :: Make Table Query On Like Fields

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

Queries :: Make Table Query To Linked BE

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

Make Table Queries And Concatenated Memo Fields

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

Queries :: Make Table To Add Sequence Number To Records

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

Queries :: Make Table Query With Calculated Columns

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

Queries :: Make Table Query - Remove Characters

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

Queries :: Delete Duplicated Records Without Having To Make A New Table?

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

General :: Combining Make Table With Union Queries

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

Queries :: How To Add Populated Text Field At Make Table Run

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

Queries :: Make Table Query That Adds A Primary Key

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







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