Joining Tables

[joeygigs106]

I have 2 tables. 1 is a name table. The other is an AKA table with multiple names. I joined these 2 tables with an auto number. The created a query for both these tables joiing the numbers together. When I run it I get no results. If I take the AKA table out of the query it works. I can I join these 2 tables in a query so if there is a name in the name query without an aka it will work?








?

 

[plog]

I can I join these 2 tables in a query so if there is a name in the name query without an aka it will work?

I'm sure the answer involves a LEFT JOIN, but for now, Huh?

Can you post sample data from your 2 tables, sample data from what your query is returning and sample data of what you would like it to return? And if you have time, rewrite that sentence to make sense.

 

[cookiegary01]

Joey,
In your AKA table, add a field (Data type = Number and Field Size = Long Integer)
Lets say you name this field IdName.
Now check your Nametable, Let's say the first name is Peter with autonumber 1.
Then go the your AKA table, you should enter 1 in the new field IdName, in the row showing Peter.
Now join these two tables together (Autofield from TableName with IdName in AKATable).
I believe the join should be " One to Many" in your case.
If this works, then add the correct number to all names in AKATable

 

[VilaRestal]

plog is right. It is a left join you want. The way to do that in Access query designer or relationship manager is to:

Right Click on the join
Click Edit Relationship
Click Join Type
Select option 2 or 3 - whichever one says: "Select all records from table names but only those records from table AKA where the joined fields are equal"

 

[joeygigs106]

Gary,
Thanks for the help. I know my post is a little confusing but I will try to add some clarity. I have the 2 tables (NameTbl and AKA table). Their joined in a query by the auto number. I created a form with the name table.Than I added a sub form (aka tabel) to accept multiple AKAs to the name. My problem is if no aka is entered, the entry form the name table doesnt show up in the query.

 

[VilaRestal]

Ah I see. The main form should be based on the Name table only (not on a query joining it with AKA). The subform should be based on the AKA table only (not on a query). The set the master/child fields of the subform to the joining fields of the two tables.

 

[joeygigs106]

When I Jion the two tables with "include ALL records from NameTable and only those in AKA table where the joined fields are equal" I get The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in your SQL statement.

 

[joeygigs106]

Gentleman I got it. I created seperate query with your help than added that query the original. Works Thanks