Three year similar value access query

[Stephenoja]

Hello guys
I have a database with three tables each containing the following fields, AccountNo, CustomerName, Table2003,Table2004, Table2005. I would like to make a query that will produce the accountnumber, and customername for all customers with the similar balance in table2003,table2004, and table2005. Right now my query gives me the results for just about every customer with balances in all three years but i specifically want to see the ones with the same amount being carried forward for all three years. Any ideas as to how I may go about doing this would be greatly appreciated.

[nico5038]

Hello guys
I have a database with three tables each containing the following fields, AccountNo, CustomerName, Table2003,Table2004, Table2005. I would like to make a query that will produce the accountnumber, and customername for all customers with the similar balance in table2003,table2004, and table2005. Right now my query gives me the results for just about every customer with balances in all three years but i specifically want to see the ones with the same amount being carried forward for all three years. Any ideas as to how I may go about doing this would be greatly appreciated.

Basically you're design isn't normalized and you should have one table with an additional "YEAR" field.

Now first create a UNION query like:
select "2003" as Year, * from Table2003
UNION
select "2004" as Year, * from Table2004
UNION
select "2005" as Year, * from Table2005;

To get all data in one and use that query for collecting the customerdata.

Clear or do you need more info ?

Nic;o)

[Stephenoja]

Basically you're design isn't normalized and you should have one table with an additional "YEAR" field.

Now first create a UNION query like:
select "2003" as Year, * from Table2003
UNION
select "2004" as Year, * from Table2004
UNION
select "2005" as Year, * from Table2005;

To get all data in one and use that query for collecting the customerdata.

Clear or do you need more info ?

Nic;o)

hmm, i am trying to digest this Nico. Could you please give me some more info? I at first had all the data in one table but I separated them into three different tables according to year, so now i have three tables.

[nico5038]

OK, just create the UNION or use your old table.

Next place Customer, Accountnumber, Year and Balance in a query based on that.

Next change the query type to crosstable query and in the extra line with combo's from the graphical query editor use:
Customer and Accountnumber as Rowheader
Year as Columnheader
Balance as Value
Finally change the GroupBy under the Balance into Sum

When running this query you get:

Customer  Accountnumber  2003 2004 2005
CustA       123456      12   22     33
CustB       654321       2   5    133
etc.
 

This can be used to compare the balances of the different years.

Nic;o)

[Stephenoja]

OK, just create the UNION or use your old table.

Next place Customer, Accountnumber, Year and Balance in a query based on that.

Next change the query type to crosstable query and in the extra line with combo's from the graphical query editor use:
Customer and Accountnumber as Rowheader
Year as Columnheader
Balance as Value
Finally change the GroupBy under the Balance into Sum

When running this query you get:

Customer  Accountnumber  2003 2004 2005
CustA       123456      12   22     33
CustB       654321       2   5    133
etc.
 

This can be used to compare the balances of the different years.

Nic;o)

Thanks Nico. I already have that query sorted out to display the results for all three years. However, what I am having difficulties with is to now have only customers with similar balances for all three years displayed instead of every single customer. That is, if customer a has $200 as a balance in all three years, I want to see him displayed in my query. Anyfurther ideas?

[Stephenoja]

Thanks Nico. I already have that query sorted out to display the results for all three years. However, what I am having difficulties with is to now have only customers with similar balances for all three years displayed instead of every single customer. That is, if customer a has $200 as a balance in all three years, I want to see him displayed in my query. Anyfurther ideas?

Ok Nico and all you other guys you would never believe what worked. I typed in the criteria field for year 2003 "Is Not Null And [Table_2004.Balance]" and then in the criteria field for 2004 "Is Not Null And [Table_2005.Balance]" and voila! I got the results. Thank you again. I really appreciated the suggestions.

[nico5038]

Well done, you're learning fast :-)

Success with your application !

Nic;o)

[NeoPa]

Ok Nico and all you other guys you would never believe what worked. I typed in the criteria field for year 2003 "Is Not Null And [Table_2004.Balance]" and then in the criteria field for 2004 "Is Not Null And [Table_2005.Balance]" and voila! I got the results. Thank you again. I really appreciated the suggestions.

You have the perfect answer there (Who says we wouldn't believe that worked). Don't overlook Nico's point about the data normalisation though, it can actually produce faster results which are easier to work with.