Use an append query to add records to a QUERY, can I do this?

[offie]

Or any way to add records to a query. I can't make another simple query because I want there to be an empty cell if there is no data from one table for a row in another.

Ex:

Table 1
A..1..cat
B..2..dog
C..3..pig

Table 2
A..23..wet
B..56..dry
D..89..wet

I want
A..1..cat..23..wet
B..2..dog..56..dry
C..3..pig.. .. ..
D.. .. ..89..wet



And no I can't change the tables (no permission) and no I can't copy a table (linked tables updates daily)

Thanks!

[JoeM]

Here is one way. Let's say that each table has three fields, ID, Field1, and Field2.

First, create a query that returns all your possible IDs. You can do this with a Union Query. You need to create this in SQL View of the Query Builder (can't build it using the GUI). If you Google "Access Union Queries", you can find out more about this.

So that SQL code will look something like this:

SELECT Table_1.ID
FROM Table_1
UNION SELECT Table_2.ID
FROM Table_2;

Let's name this "Union_Query".

Now, let's create a new query with three objects, "Union_Query", "Table_1", and "Table_2". We want to do LEFT OUTER JOINS from the "Union_Query" to each of those two tables, and return their fields.
The SQL code of that will look something like:

SELECT Union_Query.ID, Table_1.Field1, Table_1.Field2, Table_2.Field1, Table_2.Field2
FROM (Union_Query 
LEFT JOIN Table_1 
ON Union_Query.ID = Table_1.ID) 
LEFT JOIN Table_2 
ON Union_Query.ID = Table_2.ID;

This will return what you want.

If you are really adept at writing SQL code, you could write the whole thing as one query. But there is no harm in doing it as a two-step process. Once written, you will only ever need to open the final query.

[offie]

So I have a question about parenthases and commas, where do I put them? On your example why is the end parenthases after the first ON line?

I made this but I get a FROM error, I'm pretty sure that it's just has to do with grouping, what would the appropriate grouping be?

SELECT UnionQuery.ProjectCode, dbo_Project.Title, dbo_Project.StatusCode, dbo_Project.ClientAgencyCode,
dbo_Project.FundingAgencyCode, dbo_Project.ProjectScope, dbo_vw_ProjectEIC.EICFullName,
dbo_vw_ProjectRemark.Remark, dbo_ProjectMilestone.ReceivedDate, dbo_ProjectMilestone.ProjectCnstCompleteDate, dbo_Contract.TradeCode, dbo_Contract.ContractCode, dbo_Contract.ContractAmount, dbo_Contract.BonusAmount
FROM (UnionQuery
LEFT JOIN dbo_Project
ON UnionQuery.ProjectCode = dbo_Project.ProjectCode)
LEFT JOIN dbo_ProjectEIC
ON UnionQuery.ProjectCode = dbo_ProjectEIC.ProjectCode,
LEFT JOIN dbo_vw_ProjectEIC
ON UnionQuery.ProjectCode = dbo_vw_ProjectEIC.ProjectCode
LEFT JOIN dbo_vw_ProjectRemark
ON UnionQuery.ProjectCode = dbo_vw_ProjectRemark.ProjectCode
LEFT JOIN dbo_ProjectMilestone
ON UnionQuery.ProjectCode = dbo_ProjectMilestone.ProjectCode
LEFT JOIN dbo_Contract
ON UnionQUery.ProjectCode = dbo_Contract.ProjectCode;

Thanks so much, if this works you are a real life saver!

[JoeM]

Quite frankly, in the SQL code of that last query, I did not put those parentheses in there. I built the query using the Query Builder, and then just copied the SQL code. So somehow, Access determined that is where it thinks parentheses should be. I find that Access goes a little overboard in their use of parentheses (using them where it really isn't necessary).

I think your code should run fine if you just remove all parentheses from it. Give it a try and see if it works!

[offie]

Just in case anyone else has this problem this is what I did, it have to do withe appropraite ()

SELECT dbo_Project.ProjectCode, dbo_Project.Title, dbo_Project.StatusCode, dbo_vw_ProjectEIC.EICFullName, dbo_vw_ProjectComplete.AwardProceeddate, dbo_vw_ProjectComplete.physicallycompletedate, dbo_vw_ProjectRemark.Remark, dbo_Project.ClientAgencyCode, dbo_Project.FundingAgencyCode, dbo_Project.ProjectScope
FROM (((dbo_Project
LEFT JOIN dbo_vw_ProjectEIC ON dbo_Project.ProjectCode = dbo_vw_ProjectEIC.ProjectCode)
LEFT JOIN dbo_vw_ProjectRemark ON dbo_vw_ProjectEIC.ProjectCode = dbo_vw_ProjectRemark.ProjectCode)
LEFT JOIN dbo_vw_ProjectComplete ON dbo_vw_ProjectRemark.ProjectCode = dbo_vw_ProjectComplete.projectcode)
LEFT JOIN UnionQuery ON dbo_Project.ProjectCode = UnionQuery.ProjectCode;