Union Query Problem - Need some 'Splain'

[LouieGrandie]

I have a database that is the data source for some Crystal reports. One table holds data sent from a broker of invoices of my client's product sold to the broker's customers. A SQL Specific Pass Through query reads the invoice history files in the clients accounting system. There is a Union query that simply says "SELECT * FROM {Broker Table} UNION SELECT * FROM {Pass Through Query}. My issue is that in the resulting data set not all of the invoices from the Pass Through Query are displayed. If you run the Pass Through Query there are there but not when I run the Union query. Note that in the Pass Though Query the invoice dates are current so there will be dates up to January of 2015 but in the Broker's table they are a month or so behind so those invoices may only be until November. All the invoices that are missing seem to be the ones past the last date in the Broker table. So my question is, how should I write the Union statement so that ALL the records from the Pass Through Query are displayed in the resulting data set.

Visit Sage's Online Community

http://community.sagemas.com/sagemas/?category.id=SageMAS90and200Disc

 

[dhookom]

First, I never use "SELECT *" since I'm a control freak and must include field names. You can try use UNION ALL

SELECT * FROM {Broker Table} UNION [COLOR=#A40000][highlight #FCE94F]ALL[/highlight][/color] SELECT * FROM {Pass Through Query}

Duane
Hook'D on Access
MS Access MVP

 

[LouieGrandie]

OK so you feel it should be SELECT {Field Names} FROM {Broker Table} UNION ALL SELECT {Field Names} From {Pass Through Query} One more question. Does it matter which is listed first, the Broker table or the Pass Through QUery?

Visit Sage's Online Community

http://community.sagemas.com/sagemas/?category.id=SageMAS90and200Disc

 

[dhookom]

The order of tables shouldn't matter if the field names and data types are the same. The field names from the first SELECT statement will be used in the output.

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

Do you have any WHERE parts:[tt]
SELECT * FROM {Broker Table}
[blue]WHERE ...[/blue]
UNION
SELECT * FROM {Pass Through Query}
[blue]WHERE...[/blue]
[/tt]
If you don't, UNION vs UNION ALL should not make much difference.
UNION ALL wil give you all records, UNION works like DISTINCT eliminating duplicate records.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.