Relationships

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

Guest

I have three tables, the first, an "Account" table has two related tables,
one used to track "Transactions" one used to track "Contacts" with each
account.

There can be many transactions and many contacts for each account.

Both tables are lingked to the account table by their account number.

I am trying to create a relationship between the transactions and the
contacts. Specifically I have created a query (with help from here!) that
creates a list of the FIRST contact for each account. I want to use that
query so that I can create a report of all transactions along with info from
their first unique contact.

I am running into a problem the query builder errors that the two are not
related. I have tried to link the query of unique contacts to the table of
transactions, creating a one to many relationship and that fails as well.

Is there any way that possibly through the account table, since it has a
relationship between the two tables I am trying to link I cam make a
relationship that will allow this?

Thanks in advance for any guidance.

My tables look like this.

Account==OneToMany on account number==> Contacts
Account==OneToMany on account number==> Transactions

I WANT

Earliest Contact ==OneToMany on account number==> transactions
 
Rick,

From your description, it seems to me that what you have done should
work. You have a Totals Query where you Group By account Number and
return the FirstOf Contact, right? And then you make another query,
which includes this first query, and also the Transactions table, with a
Join on the account number field from both, right? Should work. I
don't know about the query builder, I have never used it, but if you try
to do it in the design view of a new query, just add the first query and
the Transactions table and join them, and see how you go. If you still
have trouble, please go to the design view of the first totals query,
and also the second query, swelect SQL from the View menu, and
copy/paste the SQL view of the queries into your post back here.
 
Back
Top