Join Query that needs more included

[Esaw]

I have a database with two sub forms "Contracts" and "Royalties" that I am
trying to get together to show all the contracts and what royalties they have
or have not produced. Right now this query returns all the contracts and the
royalties that are connected, but I have found that there are royalties that
could be entered and linked to different contracts (the clients combine
royalties from several contracts if we have more than one contract per artist
with them) So I am wondering if there is a way I can include all of the
royalties without having to separate each contract in the royalties statement?

Here is the query I have now.

SELECT ContractsQuery.ArtistName, ContractsQuery.ContractNumber,
ContractsQuery.StyleNumber, ContractsQuery.StyleDescription,
ContractsQuery.ClientName, ContractsQuery.ContractStart,
ContractsQuery.ContractEnd, ContractsQuery.Product, Royalties.RoyaltyAmount,
Royalties.DatePaid
FROM ContractsQuery LEFT JOIN Royalties ON ContractsQuery.ContractNumber =
Royalties.ContractNumber;


Thanks so much for any input on this.
Eisa

 

[Jeff Boyce]

You described the forms.

Access stores data in tables.

Please describe the tables you are using and their relationship.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Esaw]

I have them related by Contract Number and they are "Contracts" and "Royalties"

 

[Jeff Boyce]

Not quite specific enough information for me to be able to offer specific
suggestions.

Let me provide an example of the kind of information I was looking for:

tblPerson
PersonID
LastName
FirstName
DateOfBirth

tblClass
ClassID
ClassTitle
ClassDescription

trelEnrollment
EnrollmentID
PersonID
ClassID
EnrollmentDate

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Esaw]

Sorry about that...

tblContracts
ContractsID
ClientName
ContractStart
ContractEnd
Product
RoyaltyRate
StyleNumber
StyleDescription
ArtistName
ContractNumber

tblRoyalties
RoyaltiesID
ArtistName
ClientName
PayPeriodQuarter
PayPeriodMonth
DateReceived
RoyaltyAmount
TT%
DatePaid
Style Number
Style Description
Notes
ContractNumber

Let me know if I'm missing any other info.

 

[John W. Vinson/MVP]

I have them related by Contract Number and they are "Contracts" and "Royalties"

But you say that there are royalties which you want to see, which
apply to DIFFERENT contracts. How can Access - or you, for that matter
- identify those royalties?

 

[Esaw]

Let me try to clarify that, there are royalties that are entered without
being linked to a contract (by contract number) because the royalty could be
applied to more than one contract. So they just don't have a contract number
like the rest of the royalty records. Those are the records that I am unable
show in my query, but I want to be able to. The records that are showing up
are the contracts that I haven't assigned a contract number to yet (only
because I don't want to do all that work until I can figure out the best way
to get this data) and contracts that have royalties with contract numbers
linked. (I've taken one artists contracts and royalties and testing)

Hopefully this helps a little more. I would think there is something in the
query that would help get these records without me having to completely
restructure the database.

 

[Bob Barrows [MVP]]

To start, you have said nothing to answer John's question. "But you say
that there are royalties which you want to see, which apply to DIFFERENT
contracts. How can Access - or you, for that matter - identify those
royalties?"

It sounds to me that, rather than storing ContractNumber in
tblRoyalties, you should be storing RoyaltyID in tblContracts ... you
are describing a 1-to-many relationship between royalties and contracts
(1 royalty can be assigned to many contracts), which means the foreign
key (RoyaltyID) should be in the "many" side of the relationship
(tblContracts).

It does not sound like you have a many-to-many relationship ... do you?
Is it possible for 1 contract to be assigned to many royalties? If so,
you need a linking table (sometimes termed a "bridging" table) to link
the royalties to the contracts.

 

[Esaw]

Thank you for response. I am not very seasoned in Access and am learning as I
go so yeah, there isn't a way for me nor Access to identify the royalties, I
guess that's what I was asking, is if there's a way for me to get the
information I need.

So to answer your questions, one royalty can be from more than one contract
and one contract can have many royalties. It sounds like the linking table
may be the answer. I have not set up this before, so any help would be
appreciated.

I don't think that I have a many to many relationship. I know that's a
stupid answer but honestly I set up the database and I've been learning from
the quirks and such as I go along, so please try to bear with me, I want to
understand how to work with Access.

 

[Esaw]

the relationship between Contracts.ContractNumber and
Royalties.ContractNumber says "Indeterminate"

 

[Bob Barrows [MVP]]

Well, given "one royalty can be from more than one contract
and one contract can have many royalties", you definitely have a
many-to-many relationship.
The linking table is simply a table with two fields: ContractNumber and
RoyaltyID. Call it tblContractsRoyalties.

You create 1-to-many relationships between each "1" table and the
linking table, like this:

tblContracts tblContractsRoyalties tblRoyalties
ContractID RoyaltyID-----------------------RoyaltyID
ContracNumber-----ContractNumber etc.
etc.

So, if you have contracts 1,2 and 3, and royalties a,b,c, and royalties
need to be assigned as follows:
1 a,c
2 b,c
3 a

you would create the following records in tblContractsRoyalties:
ContractNumber RoyaltyID
1 a
1 c
2 b
2 c
3 a

 

[Bob Barrows [MVP]]

Yes, that's because the data does not fit either 1-to-1 or 1-to-many.
You have a many-to-many relationship, which needs an intermediate,
linking table to define the relationship.
See my other reply.

There is a book I've heard much praise about called "Database Design for
Mere Mortals". You may benefit from it.

 

[Esaw]

Thank you Bob, I appreciate your information, I'll try this.

 

[John W. Vinson]

Thank you for response. I am not very seasoned in Access and am learning as I
go so yeah, there isn't a way for me nor Access to identify the royalties, I
guess that's what I was asking, is if there's a way for me to get the
information I need.

So to answer your questions, one royalty can be from more than one contract
and one contract can have many royalties. It sounds like the linking table
may be the answer. I have not set up this before, so any help would be
appreciated.

I don't think that I have a many to many relationship. I know that's a
stupid answer but honestly I set up the database and I've been learning from
the quirks and such as I go along, so please try to bear with me, I want to
understand how to work with Access.

Well... the phrase

one royalty can be from more than one contract and one contract can have many
royalties

is the very precise definition of a many to many relationship.

One royalty - many contracts.
One contract - many royalties.

You need a table, let's call it ContractRoyalties, with fields for the
ContractID (a link to the primary key of the Contracts table) and RoyaltyID (a
link to the primary key of the Royalties table). This table will have multiple
records for each contract, listing all of the royalties for that contract; it
will also have multiple records for each royalty, listing the contracts
pertaining to that royalty.

You would probably use a Subform in continuous view based on this table for
data entry and viewing.

 

[Esaw]

Thanks so much John. I'll work on it and see if I can get it together.