Create query but need to change tables

[Esaw]

I have 2 tables that I need to connect so I can run a query to see what
contracts we are getting royalties on. The first table is Contracts and the
second is Royalties. They are now related to each other by ArtistName.

The Contracts table has the fields: ID, ClientName, ContractStart,
ContractEnd, Product, RoyaltyRate, StyleNumber, StyleDescription (which in
the subform is a lookup) and ArtistName.

The Royalties table has the fields: ID, ArtistName, ClientName,
PayPeriodMonth, PayPeriodQuarter, DateReceived, RoyaltyAmount, TT%, DatePaid,
StyleNumber, StyleDescription (set up the same as the contracts subform) and
Notes.

The StyleNumber can have several contracts related to it and the ClientName
can be the same for many contracts.

I’m wondering if there is a way that I could add a field to the Royalties
table to be able to link it to a certain contract (there's over 700 contracts
in there right now and over 300 royalties entries) so I can create a query
that will show me what contracts we’ve been paid on.

I’m sure there’s more information that you’ll need to help but hopefully
this is enough for you to understand what I’m trying to do.

Thanks for any help with this

 

[Jeff Boyce]

If you truly are using artists' names, how do you differential between two
individuals with the same name?

What do you do when (?if?) you pay royalties to more than one Artist on the
same Contract (or do you only have One Contract per Artist)?

Do you ever pay Royalties to an Artist without a Contract? ... or to a
Contract without an Artist?

I don't think I have enough information to help yet...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Esaw]

Thanks for your responses. The artist's names are all different but even if
there would be two of the same they are entered into the database with a
unique ID that's stored in the ContactList table.

There is only one artist's name on a contract but there could be several
StyleNumbers on one contract (The StyleNumbers also are stored with a ID in
the Art table)

There should always be a contract that goes with a royalty, from now on.
Being that I came in and created all this, there are some that are missing
contract info that goes with the royalties. This can be added though.

Maybe this will clarify what I'm looking for: We will get a contract from a
client with an artist's name and the style number of the art they want to use
in it. After production of the product or in some cases there is an advance
payment made, we will receive a royalty check. I enter the contract
information when we receive the contract and the royalty info when we get the
royalty checks. So we would like to find a way to make sure that all the
contracts that we have, we've gotten paid on or find out that they haven't
made us any money. Which can happen...

I hope this helps. I of course feel like it's a complicated database, but
then again I'm pretty new to Access.

Thanks again for the help!