Query Error when trying to join 2 tables and group by a field

[Maurice Caler]

I may be going about this incorrectly but I thought I was doing the right thing. I have 2 tables with the same coulms. The data in each is identicle but each has columns will no data. So I want to combine the tables so that the missing data fills in. So I created a union query with a 'group by' expression. The union query works if I don't include the group expression. But the group expression returns an error that says, "You tried to execute a query that does not include the specific expression 'Issue' as part of an aggregate function. I'm not sure what I'm doing wrong. Here is my query:

SELECT SheetNumber, Issue, ProjectName, Discipline, Description, SheetSize, FileName
FROM ProjectList
UNION SELECT SheetNumber, Issue, ProjectName, Discipline, Description, SheetSize, FileName
FROM PartialProjectList
GROUP BY SheetNumber;

[dsatino]

I think you have a misconception regarding what UNION query will do for you. A UNION will simply 'stack' (for lack of a better term) the results of one query on another.

You won't be able to fill in the missing data using this method because the 'missing' data is NULL and you won't be able to group NULL data with non-NULL data.

[NeoPa]

The UNION aspect is irrelevant here. DSatino explains what UNION does quite well, but it's not related to this problem.

The problem is that whenever GROUP BY is used in a query the SELECT clause can only be made up of :

1. Either items in the GROUP BY clause
2. or items that are aggregated (Sum(), Count(), etc)

It is logically nonsense to ask for items that are not grouped and not aggregated as there is no possible way that it could determine which value you were after.

Consider the following data (in a table [Table1]) :

[B][U]Field1  Field2[/U][/B]
 AAA       1
 AAA       2
 AAA       3
 BBB       7
 BBB       9

If the SQL said :

SELECT   [Field1]
       , [Field2]
FROM     [Table1]
GROUP BY [Field1]

How could the server grant the request? What value to return for [Field2] across the three records where [Field1] = "AAA"?

The SELECT clause would need to be something like :

SELECT   [Field1]
       , Sum([Field2]) As SumofField2

[dsatino]

That too.

I think what we're both trying to say is you need to do a little research on SQL in general.