Comparing years

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I do not know if this is possible but :

I have two tables with the same Title information except one is 2003 and one
is 2004.
Is there away that I can have a query compare 2003 to 2004. The trick some
of the field information (example 2003 has customers not into 2004 and visa
versa) is not the same.... I know a union query would get me all the data but
it puts them in rows instead of columns......
 
Hi,



SELECT a.*, b.*
FROM (( SELECT ClientID FROM table2003
UNION
SELECT ClientID FROM table2004) As x
LEFT JOIN table2003 As a ON a.ClientID=x.ClientID)
LEFT JOIN table2004 As b ON b.ClientID=x.ClientID





The UNION collect all the ClientID values, without duplication (UNION ALL
would keep the duplicated values). The two left join collect the data, if
any, from the initial tables, with the right association through the
ClientID value.



Hoping it may help,
Vanderghast, Access MVP
 
Hithanks for all you help, I am very new at this. I am gettinga syntax
error, could you possibly help me...



SELECT [SumOfSALES].[Sales by Compenent 2003], [SumOfSALES].[Sales by
Component 2004]
from ((Select CUST from [Sales by Component 2003]
union
select CUST from [Sales by component 2004]) as x
left join [Sales by Component 2003] as a on [SumOfSALES].CUST = x.CUST)
left join [Sales by Component 2004] as b on [SumOfSALES].CUST= x.CUST;
 
Hi,


What is SumOfSALES? a table? if not, it cannot be use to the left of the
dot in the syntax tableName.FieldName

Try:


SELECT a.*, b.*
from ((Select CUST from [Sales by Component 2003]
union
select CUST from [Sales by component 2004]) as x
left join [Sales by Component 2003] as a on a.CUST = x.CUST)
left join [Sales by Component 2004] as b on b.CUST= x.CUST;



In the LEFT JOIN, once we use the alias ( AS a, AS b) for the table we have
to use the alias, not the table name. So, I assume it should be
a.CUST=x.CUST, and b.CUST=x.CUST, in the ON clauses..



Hoping it may help,
Vanderghast, Access MVP



jeanne said:
Hithanks for all you help, I am very new at this. I am gettinga syntax
error, could you possibly help me...



SELECT [SumOfSALES].[Sales by Compenent 2003], [SumOfSALES].[Sales by
Component 2004]
from ((Select CUST from [Sales by Component 2003]
union
select CUST from [Sales by component 2004]) as x
left join [Sales by Component 2003] as a on [SumOfSALES].CUST = x.CUST)
left join [Sales by Component 2004] as b on [SumOfSALES].CUST= x.CUST;








Michel Walsh said:
Hi,



SELECT a.*, b.*
FROM (( SELECT ClientID FROM table2003
UNION
SELECT ClientID FROM table2004) As x
LEFT JOIN table2003 As a ON a.ClientID=x.ClientID)
LEFT JOIN table2004 As b ON b.ClientID=x.ClientID





The UNION collect all the ClientID values, without duplication (UNION ALL
would keep the duplicated values). The two left join collect the data, if
any, from the initial tables, with the right association through the
ClientID value.



Hoping it may help,
Vanderghast, Access MVP
Click to expand...
Click to expand...
 
Thanks,

I see now, I thought you were just using symbols for a and b.
This worked great thanks!

Michel Walsh said:
Hi,


What is SumOfSALES? a table? if not, it cannot be use to the left of the
dot in the syntax tableName.FieldName

Try:


SELECT a.*, b.*
from ((Select CUST from [Sales by Component 2003]
union
select CUST from [Sales by component 2004]) as x
left join [Sales by Component 2003] as a on a.CUST = x.CUST)
left join [Sales by Component 2004] as b on b.CUST= x.CUST;



In the LEFT JOIN, once we use the alias ( AS a, AS b) for the table we have
to use the alias, not the table name. So, I assume it should be
a.CUST=x.CUST, and b.CUST=x.CUST, in the ON clauses..



Hoping it may help,
Vanderghast, Access MVP



jeanne said:
Hithanks for all you help, I am very new at this. I am gettinga syntax
error, could you possibly help me...



SELECT [SumOfSALES].[Sales by Compenent 2003], [SumOfSALES].[Sales by
Component 2004]
from ((Select CUST from [Sales by Component 2003]
union
select CUST from [Sales by component 2004]) as x
left join [Sales by Component 2003] as a on [SumOfSALES].CUST = x.CUST)
left join [Sales by Component 2004] as b on [SumOfSALES].CUST= x.CUST;








Michel Walsh said:
Hi,



SELECT a.*, b.*
FROM (( SELECT ClientID FROM table2003
UNION
SELECT ClientID FROM table2004) As x
LEFT JOIN table2003 As a ON a.ClientID=x.ClientID)
LEFT JOIN table2004 As b ON b.ClientID=x.ClientID





The UNION collect all the ClientID values, without duplication (UNION ALL
would keep the duplicated values). The two left join collect the data, if
any, from the initial tables, with the right association through the
ClientID value.



Hoping it may help,
Vanderghast, Access MVP

I do not know if this is possible but :

I have two tables with the same Title information except one is 2003
and
one
is 2004.
Is there away that I can have a query compare 2003 to 2004. The trick
some
of the field information (example 2003 has customers not into 2004 and
visa
versa) is not the same.... I know a union query would get me all the
data
but
it puts them in rows instead of columns......
Click to expand...
Click to expand...
Click to expand...
 
I am going to dovetail on this information. I assume that there are
duplicate tables and queiries for each year. Or are they in different data
bases. I have a database that has over 45 queries. should I make a
duplicate for each one, and actually the underlying tables to compare? Or
should I buy the compare program for this. thanks a bunch
 
I'm not sure why you need to compare any of your 45 Queries. I hope
that you didn't change the field names in your Tables when you switched
data. If the field names have not changed, you might be able to compare
the SQL of two Queries to determine if they are identical. (For
example, in Windows there's a command-line utility called "FC" that will
compare the contents of text files.)

If the field names have changed, then you won't be able to compare the
Queries based on the separate sets of field names. You might want to
consider changing the names back to what they were originally, or
choosing a name for each field that describes the kind of datum you
store in it, and that doesn't, for example, depend on the year. (You
can store the year in a separate field reserved for it.) If you were to
change the field names in your Tables, you'd need to change the field
names in the Queries to match them, but you'd need to do that only once.
Having made the names match in the two versions of your database, you
could then compare the SQL of the corresponding Queries to look for
differences.

You can compare the contents of your Tables via a Query similar to the
one that Michel Walsh described. Since the Queries get most of their
information from Tables instead of storing it internally, I think that
the Tables are where you want to spend most of your attention.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
Back
Top