Relationships

[Tamasta]

Please help me to establish a relationship.
I have one query that has different account numbers with different
information, the account number is not the key since there can be many
entries, think of it this way

123490 $100
123490 $200
123490 $500

In the end I add all the balances and i get the information i need.
The problem is that i have another table with information allready (the
account is not repeated here) I need the information on both, how do i
establish the realtionship>

 

[John Vinson]

Please help me to establish a relationship.
I have one query that has different account numbers with different
information, the account number is not the key since there can be many
entries, think of it this way

123490 $100
123490 $200
123490 $500

In the end I add all the balances and i get the information i need.
The problem is that i have another table with information allready (the
account is not repeated here) I need the information on both, how do i
establish the realtionship>

Unless you have SOME field in common between the tables, there is no
way whatsoever to do so.

Think about it: You've got two stacks of paper. One stack has on each
sheet an account number and an amount. The other stack has various
information on each sheet, not including an account number anywhere.

Pair these two stacks up correctly. Which sheet goes with which?

THat's the problem you're asking Access to solve; and it's just as
impossible for Access to do so as it would be for you!

Am I missing something? Are there other fields in this table which
would provide a link? What information is in the other table?

John W. Vinson[MVP]

 

[Tamasta]

Yes the field in common is the account number. The probles comes when on one
table the data is already added and on the other is not.

 

[Tamasta]

I'm sorry now I get it. On the secon table the account number appears but
only once per row (no duplicates)

 

[John Vinson]

I'm sorry now I get it. On the secon table the account number appears but
only once per row (no duplicates)

Sorry - I did misunderstand.

What's the problem? Create a new Query; add both tables; drag the
account number from the AccountID field in the second table to the
AccountID field in the first table. This is (now) a perfectly normal
one-to-many relationship query.

Have you done so, and you're having a problem? What problem?

John W. Vinson[MVP]

 

[Tamasta]

I did that, the problem is that on the new query the records (on the table
where there are no duplicates) get duplicated. I think it has to do with the
type of data in both tables, one is already sumarized and the other isn't.

 

[John Vinson]

I did that, the problem is that on the new query the records (on the table
where there are no duplicates) get duplicated. I think it has to do with the
type of data in both tables, one is already sumarized and the other isn't.

Then either change this Join query to a totals query and do the
totalling in it, or join your "one" side table to a Totals query which
does the summing.

John W. Vinson[MVP]

 

[DawnTreader]

Hello

it is also possbile to tell the query to show only one entry for a field
that causes it to repeat. check the field properties for unique values.

additionally if you want to get a total you can tell it to sum or count or
other functions using the total row in the query grid. turn it on with the
funny E like button on the toolbar.

hope this helps!