By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,397 Members | 1,467 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,397 IT Pros & Developers. It's quick & easy.

Combine Query Problem

P: 45
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
Sep 1 '07 #1
Share this Question
Share on Google+
3 Replies


JConsulting
Expert 100+
P: 603
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

Expand|Select|Wrap|Line Numbers
  1. Select * from table1
  2. Union All
  3. Select * from table2;
  4.  
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
Sep 1 '07 #2

P: 45
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
Sep 1 '07 #3

P: 45
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
Sep 1 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.