Looking Up Values in Query for Report Control Source?

[misscrf]

I have a table that is set as the control source for the report. I invoke the query builder, because the table has foreign keys to lookup tables and I need to link those in so I can display the actual values on the report.

One of my fields is cboParty. This is a field that is a lookup to a table, but the table it looks up is dependant on FKPartyType.

If FKPartyType = Singer (value 2 for the ID), then I need to look up the value of the singer in the table tblSinger. This gets more complicated, because in that table there are a bunch of foreign keys there as well tblSinger has an FKSalutation, txtFirst, txtMiddle, txtLast and FKSuffix.

I would like to bring in the txtSalutation from tblSalutation and do a concatenation of the rest with the txtSuffix from tblSuffix as well.

For the rest of this, in the main control source of this report, if the FKPartyType is any other value than 2 (Singer), then I need to look up the value in the tblPartyRecorder table. This is the same deal where I have an FK to look up from another lookup table.

Any thoughts? I started to build an IIF statement, but got stuck when I realized my dlookup took me to an FK that I need to lookup again.

MyParty: IIf([FKPartyType]=2,DLookUp([FKSalutation]&" "&[txtFirstName] & " " & [txtLastName] &" "&[FKSuffix],"tblSinger",[cboParty]=[PKSingerID]),DLookUp([FKRecorder],"tblPartyRecorder"))

My problem above is that the FKSalutation is a lookup to the salutation table. Same with FKSuffix and FKRecorder.

Any ideas how to accomplish this, please?

Thanks!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

I have not illicited any responses, so I must need to explain myself better.

I am basing this report on a query. The thing is that one of the fields of the main source table is not a lookup of just one field. This is the setup:

I am making a report for "Deals". In any deal there can be multiples parties of representation. Each party can be represented by multiple parties of representation. Each party can be a Singer or Recorder. All of the PartyRepresentation information is stored in the table
tblPartyRepresentation
That has the following fields:
PKPartyRepresentationID - unique identifier.
FKDeal - Foreign key to deal table.
FKPartyType - lookup that allows choice of Singer or Recorder.
cboParty - stores an FK ID lookup that ties to the singers in tblSinger if the FKPartyType is Singer, or the recorders in tblRecorder if the FKPartyType is Recorder.

So on my report, I want to show the info for singers if it is a singer and info for recorders if it is a recorder.

Can anyone please provide some suggestions of how I might be able to set this up in my query? I am thinking of a formula in a field that has an IF statement with a some nested dlookups in the true and false parts, but not really piecing it together successfully.

I appreciate any help.


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

Have you considered creating a union query from your singer and recorder tables? You can add a column in the union query that identifies the value you use for FKPartyType.

I'm not sure why you had two tables to begin with but will assume you had a good reason.

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

Thanks for responding and I apologize for not coming back sooner. This got back burnered, and I just got a chance to get back to this. I promise you that this is a very normalized structure. It is just a complex set of relationships. Let me see if I can give the 2 cent tour/explanation -

(I like explaining through the entry forms, because the users that enter the data tend to drive the structure).

When we come into the application, the main entry form is for Deals.

tblDeals is a table and there are lookups to any foreign keys.

In each deal there can be multiple of the following:
Singers
Recorders
Contracts
Parties of Representation
Attorneys

From that, Singers have 5 pop-up sub forms about their different siblings, contacts, resumes, activities, albums etc.

Recorders have 1 pop-up for previous records (which is totally different than the albums info for singers. They are not related at all).

The only place singers and recorders tie in is that we have parties of representation.

On this form (and in the tblRepresentation table) there is
(PK and FK to Deal)
a Party Type. This is a lookup to a foreign table for choosing the value "Singer" or "Recorder"

On the form, if the choose Singer, than they get a combo lookup to the singers that are tied to that Deal. If they choose Recorder, a different combo becomes visible to choose from a list of recorders available for that Deal.

So the end of this is that I am now trying to make 1 basic Deal report that will have a main deal report and every other section as a sub report. I've got a lot of them set up, but when I got to this part, I am stuck.

I created a subreport for Representation.

The representation table has the following:

PKRepresentationID
FKDeal
FKPartyType
FKParty
FKRep
etc

so the FKPartyType looks up Singer or Recorder (i.e. 1 or 2 PK in the tblPartyType lookup table)

If it is Singer/1 then the ID that is stored in the FKParty field ties to the id in the singer table tblSinger. If the FKPartyType field has a 2/Recorder, then the id that is stored in FKParty ties to tblRecorder table info.

Did I set that up badly? How should I set that up if not that way? It is only now that I am trying to figure out how to look up each tables' info for reporting record by record, that I am questioning how to make this structure work. I am confused!

I appreciate any help!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

You seem to have provided a lot of information without telling us the significant fields from tblRecorder and tblSinger.

My solution is to create a union query to join to the representation table.

SELECT 1 as PartyType, PK , FirstName, LastName
FROM tblSinger
UNION ALL
SELECT 2, PK, FirstName, LastName
FROM tblRecorder;

You can then join the PartyType to FKPartyType and PK to FKParty.

If you can't figure this out, come back with your significant table and field names.

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

Ok, here is problem 2 lol.

tblSinger
PKSinger
FKSalutation
txtFirst
txtMiddle
txtLast
FKSuffix
dtDateofBirth
etc

tblRecorder
PKRecorder
txtRecorder

recorder is not really a list of people's names but of a recorder group. It is hard to explain, but they don't carry the same info as singers. Can I do something like

Select 1 as PartyType, PK, (dlookup of salutationFK) & " "&txtFirst&" " &txtMiddle&" "& txtLast AS PartyName
FROM tblSinger
UNION ALL
SELECT 2 as PartyType, PK, txtRecorder as PartyName
FROM tblRecorder;

p.s. not really sure how to do that dlookup for the salutation and prefix part of that string if that would work.

Another side question, is this a good what to have my table, or before I publish this and while I have the chance, should I just change this structure to have the representation table get 1 field for singerid's and 1 for recorder id's and the form have 2 fields with different control sources?

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

Access is a relational database. I'm not sure why you would use a DLookup() when you can join a table in your query.

The union query only needs to select the same number of fields in each SELECT statement.

I would not use two fields.

Duane
Hook'D on Access
MS Access MVP