Results 1 to 2 of 2
2010-10-28, 11:26 #1
- Join Date
- Oct 2004
- Minnesota, USA
- Thanked 0 Times in 0 Posts
One of our systems has a table that shows how customers are related to each other. For example, Customer X may guarantee the performance of Customer Y. In that case, Customer Y's account number would be in the Acct_Nbr field in the relationship table, and Customer X's number would be in the ILAcct_Nbr field (indirectly liable account) on the same row in the table. If Customer Y guarantees the performance of Customer X as well as Customer Z, their account number would also be in the ILAcct_Nbr field as related to Customers X and Z whose numbers would be in the Acct_Nbr field on separate rows; i.e., Customer Y in ILAcct_Nbr and Customer X in Acct_Nbr and on another row Customer Y in ILAcct_Nbr and Customer X in Acct_Nbr. A customer's relationships are scattered throughout the table; they do not appear next to each other if you open the table. There are several hundred thousand rows in the relationship table.
My goal is to be able to identify all instances where Customer Y is in either the Acct_Nbr or ILAcct_Nbr field. It's easy to do in a query where I manually input their account number on separate lines of the query, but I'd like to be able to do more than one customer at a time. For example, I would like to identify all customer relationships within a given branch.
Or, perhaps easier, let's say I identify 100 customers within branch 2 that I want to look at for whatever reason. I want to identify all of their relationships. I could do the query I described above, but is there a way to do it more programmatically if those 100 customer numbers are in another table? Is there a way to link the customer number in that table to both the Acct_Nbr and ILAcct_Nbr fields in the relationship table and indicate that I want all records where that account number is in either field?
I am using Access 2010. The source relationship table resides in SQL Server.
Any suggestions are appreciated.
2010-10-28, 18:14 #2
- Join Date
- Jun 2002
- Mt Macedon, Victoria, Australia
- Thanked 45 Times in 44 Posts
I think you need a union query. A union is really two queries with the results all added together.
Select tblsomething.* from tblsonmething
Select tblsomethingelse.* from tblsomethingelse.
This assumes that tblsomething and tblsomethingelse have the same fields.
So build one query where the relationship is between one pair of fields, then another where you use the other relationship, then combine the results with a Union.Regards