Combine Query Problem

[NCRStinks]

Hi Every1

Having a slight issue on creating a query to combine 2 queries, this year and last years data.

The thing is, I have data for this year for all stores, however on last year I dont for every record.

On the "combined" query, it only returns records with both this year and last year data. It "drops" any records without last years data.

Is there any way I can make the query return " " rather than null so all records are pulled through??

Many Thanks


Dan

[JConsulting]

Hi Every1

Having a slight issue on creating a query to combine 2 queries, this year and last years data.

The thing is, I have data for this year for all stores, however on last year I dont for every record.

On the "combined" query, it only returns records with both this year and last year data. It "drops" any records without last years data.

Is there any way I can make the query return " " rather than null so all records are pulled through??

Many Thanks


Dan

Hi Dan,
Generally this is done as a union query. If your field count matches in both tables, you can do something like so

Select * from table1
Union All
Select * from table2;
 

This should stack your results from both tables into one recordset vs trying to join them
Let us know if this is or is not the way you wish to go...and we'll provide more assistance.
J

[NCRStinks]

Hi There

I have just tried the union query you suggested. Unfortunately this puts the data from the last year query into a different row.

I would like to have the data for this year and last in the same row, that way I can analyse the % change etc.

Is this possible?

Many Thanks


Dan

[NCRStinks]

Its ok, I have managed to resolve this!

I have now created a relationship on store number in the query itself, showing all field for this year, and only those where last year are equal.


Thanks for your help



Dan