Self Join Table Form Development

I have a table with a field that relies on another field in the same table. Very much like the Employee/Boss table design that is used for self join examples. I have developed the tableand query but do not know how to make the form that would support the table design.

The field in the table are:

strID (Primary Key)

I think it will require a two form approach, one to create the employee and one to assign the boss. But I want the boss to be a combo box with only employees that have been created.

Sponsored Links:

Related Topics

Left Join That Includes Null values on Right Table, Even If Match Found
I then have a table of answers for those questions. Each user gets the entire list of questions in a continuous form. I would like to join the answers table to the questions table. Here is my join statement.

10 plus tables to join
I have 10 + tables that I need to join together Easy when they have info in them, but sometimes info will only be in 1 table or 2,3,4 etc.

I can force an entry into each table - but that seems a bit crude (I.e add a record for every new entry in my main table into the 10 child tables - but this seems to be a waste - I only want to join tables when there is info in them - when I am doing this I can join two tables fine - but adding another one where there is no data FK seems to throw it. I will have about 10-12 tables to join.

WHERE as part of JOIN not after
I am stuck on a JOIN problem that I can't see to work around.

I have a left join with criteria on the joining field. The problem is when the tables join on that field, some of the 1st tables rows are being removed by the where clause. I think what's happening is the join is putting the tables together, then removing rows based off of the criteria.

What I would like is the where clause to remove rows from table 2 first, then do the join with table 1 so ALL rows from table one remain.

Implementing the Equivalent of a FULL OUTER JOIN in Microsoft Access
There are many different kinds of joins, like INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN. The first three of these different join types are implemented in Access, but FULL OUTER JOIN is not. If you need to perform a FULL OUTER JOIN in Access, are you "up a creek, without a paddle"? No, you are not. This article will show you how you can write some SQL code in Access to implement a simulated FULL OUTER JOIN.

inner join question
I have two tables with names and amounts

Table 1 - contains a whole range of names
Table 2 - contains ONLY some of the names ON TABLE 1

I created an inner join between the names on both of these table as I need the amounts from tble 1 with the names of table 2

Is it possible to also get a list of names and amounts from table 1 which DO NOT APPEAR IN TABLE 2

this was code used to find the similar names on tbl 2 with amounts from tbl1

SELECT tbl2name, tbl1Amount
FROM list2 INNER JOIN list1 ON tbl2name = tbl1name;

SQL Left Join
I have a master table with 6 rows in it. Id like to run a query that would grab all those rows, then join a join table with it. Regardless if theres a match, I want all 7 rows from the master to display.

I've done this before only catch is now I need to use a where clause which removes the non-matching rows.

join table
I have two tables
Employees - primary key empid-autonumber
Products -primary key itemnumber-text

and I have a join table called "transactions" - I use foreigh key for this table.
I created a form, using query builder to bring all records from this two tables into a form but is repeating information. Do I have to filled out "transaction table"

Make-table query out of join query
Is it possible to create a make-table query out of a join query? If yes, how do I incorporate the INTO statement in my join query?
Whenever I change the type of the join query from SELECT to MAKE-TABLE, all my SQL statement gets deleted

Update with LEFT JOIN
I've been trying to figure out how to use VB to create a LEFT JOIN and perform an update on the results of the query.
The join works fine and returns the data to my form as expected.

I will summarize in the hope that this is an obvious problem. Please let me know if I need more detail.

Set rs = db.OpenRecordset("SELECT . ")
rs!Blah = frm!txtBlah

The problem is that when I select a value to update that exists in the "right" table, it creates a second entry in the "left" table and never updates the "right" table

Inner Join on Sorted Columns
Does an inner join on two columns ALWAYS match one record against the entire other table?

I have two temporary tables I am putting an INNER JOIN on two columns (both columns are numeric values, but column1 is stored as text). In TABLE1, these two columns identify unique combinations (i.e. could be used as primary key) but in TABLE2 these two columns are not unique. I am performing an inner join and pulling back various other columns from the two tables. I want to know if sorting each temporary table prior to the inner join will speed up the processing, or slow it down.

In other words, will the server acknowledge the fact that the tables have been sorted so it will take each unique two column combination from TABLE1 and only search TABLE2 until it has found the matching records (or it sees it has reached numbers that exceeds itself) and iterate to the next record from TABLE

Relationship/Join Clarification
A clarification: The term join and the term relationship, are they basically synonymous? Is every relationship a join and does every join establish a relationship?

On this forum, I hear the two terms tossed around quite a bit and its sometimes difficult to tell if theyre used interchangeably.

Also, when you use a Lookup field at table level, the table underlying the Lookup field now shows in the relationship window. Is that relationship any different than the kind of primary key to foreign key typical of other relationships?

Copy Table Data Macro To Another Database
Is it possible to copy a table data macro from one data base to another?

I have a database that I use for development. Once happy with the development, I copy the changes over to a live database. With the backend (data) database I have developed a database that compares the development database to the live database, so I know what changes need to be made. This database also makes the changes to the live database.

With 2010 Access has introduced table based data macro's, which I want to start using. How can I copy these from the development database to the live database? At this stage I simply want to copy / import them. I will look at automating the process later

Help with database join
I am trying to join two tables but keep getting attached error.

The tables which l cannot join are company and fleet data and management vehicles subform table.

I don't mind how the two tables are joined as long as l can create records without any problems and then create a query to link which combines the two.

Multiple Table Queries
Most of the queries you create in Microsoft Access will more that likely need to include the data from more than one table and you will have to join the tables in the query. The capability to join tables is the power of the relational database.
As you know, in order to join database tables, they must have a field in common. The fields on which you join tables must be the same or compatible data types and they must contain the same kind of data, however they do not have to have the same field name (although they probably will). Occasionally, the two database tables that you want to bring the data from may not have a field in common and you will have to add another table to the query with the sole purpose of joining the tables.

Combines source-table records when used in any FROM clause.

Which Join Type Should be Select Between Tables
You can specify join types between tables or fields in Microsoft Access 2003 to expand or narrow the results of your search. For example, choosing a full join expands the results to include records that match and do not match the join condition. By defining the join type between Microsoft Access tables, you can stipulate which records a query uses for its action. The join line shows Access how tables are related. Here are some steps to help you define the correct join type between tables in Access 2003.

Join two tables
I have two tables as shown below

Im trying to create a join between the two tables to use in a form

I have tried a lot of code but cant seem to get it to work

My latest attempt been:

SELECT [Payment].[PaymentID], [Payment].[LeaseID], [Payment].[DateDue], [Payment].[DatePaid], [Payment].[PaymentTypeID], [Payment].[Amount], [Payment].[PaymentCategoryID], Payment.Notes FROM Payment INNER JOIN (Tenant ON Tenant.TenantID=Payment.TenantID)

( no laughing please )

Am I anywhere near the correct syntax for this join or am I as far out as a lighthouse

Query Join Properties
I’m creating a Query with two tables. I want all the records from table 1 to display and only those records from table two to display. I have set the Join Properties to reflect this. I’m unable to get any data from table two to show.

Data types are the same and I even created the same type Join Properties in the Relationship window. What am I missing?

Creating a Self-Join Query to relate data within a table in Microsoft Access
When working with Microsoft Access Queries you will no doubt work with a range of join types, from the default Inner Join to the more complicated Outer Joins. In some cases, however, the related data is all within a single table. In this situation you will use a special join type that can be used to retrieve the desired data.
This type of relationship does not exist between multiple database tables, it is actually a relationship that exists between the records within a single database table.
Consider the scenario where all of the data you require is contained within a single table, but you may need to be able to relate columns within that table to each other to extract the desired data. Usual examples of this type of data relate to Employee information, where the table may have both an Employee's ID number for each record and also a field that displays the ID number of an Employee's supervisor or manager.
To retrieve the data you need to be able to relate the data to itself. For this purpose the Self-Join is designed to do exactly this action.

Join 4 Tables in 1 Query
I have a Table joining problem.

I have 4 table and each table have 3 columns each. All Tables have ID as their primary key.

Here's the table names and columns (a, b , c are column names)

Tab1 - ID, a, b
Tab2 - ID, c, d
Tab3 - ID, e, f
Tab4 - ID, g, h

I need to change the order of columns in retrieved data should this order ID, a, h, c,g,b,d, e, f
Can Order by statement can be use with Table joining?

I can join 2 tables but, I need to join all 4. Not only that I need the exact order (not the ID, a ,b, c, d, e, f, g, h order)

Using Order by is the biggest problem I have.