Relationships question

Niniel

Registered User.
Local time
Today, 15:39
Joined
Sep 28, 2006
Messages
191
Hello,

I have a table "Speakers". For each speaker, there can be more than one
disclosure [say, in the table "Disclosures"]. Yet at the same time, only one
of those disclosures is current, the others are old data that needs to be
kept around for analytical purposes.
When I link speaker and disclosures in a one-to-many relationship, I can
assign several disclosures to one speaker, and when I link disclosures and
speakers in a one-to-many relationship, I can assign a specific disclosure to
a speaker.
But I need to do both at the same time, and I have not been able to.
What do I need to do differently?

Thank you.
 
What I would do is add a boolean field for "Active" and select speakers and only those disclosures that are active.
 
Interesting idea, but I don't think that'll work for me because I have to be able to go back in time and look up disclosures for previous years for a particular speaker. The disclosure data is simply a checkbox to see if there was a disclosure, and a date that records when that disclosure was received.
The date is really the important part because it needs to be checked against a date in the main table that stores the date of the speaking engagement; the disclosure has to be received so many days before that other date.

Hope this explains things a bit better.
 
If one disclosure can only be linked to one speaker, then you have a one (speaker) to many (disclosures) relationship.

If a disclosure can be linked to more than one speaker, you have a many to many relationship.

I think you have a one to many relationship. In this case you need to hold the primary key of the speaker as a foreign key field in the disclosure table. If you link the two tables in a query, you can use Max on the date field in the disclosure table to get only the most recent disclosure for each speaker. Is that what you want?
 
No, not quite, because the disclosures get updated every year. I need to retain the old disclosure information and create a report that says in 2005, speaker x made a disclosure and sent it in on date a, whereas in 2006, he made another disclosure and sent it in on date b, and in 2007, he sent it in on date c.
 
Yes, but only one would be active at a time. Now, you can go through the process of pulling the one with the max date, but to simplify, I would just mark the latest received as "Active" and pull accordingly. You can still have the date sent in the record.
 
The more I think about it, the more confused I get. :confused:
Originally, my disclosures were tied to the speaker, the goal there was to just pick a speaker, and the disclosure information would be filled in automatically. That method would have replaced old information with new information periodically.
As it turns out, the old information must not be overwritten but preserved in order to create a history. Which is the part I am having trouble with because I haven't found a way yet to track the disclosures over time and by speaker.
 
Is each disclosure tied to only one speaker? Or is a certain disclosure good for multiple speakers?
 
What about adding a disclosure date or an Auto Number disclosure ID. Then the disclosure with the max(Disclosure Date) or Max(Disclosure ID) for each speaker is the active disclosure?
 
The disclosures are speaker specific, ie. need to be tracked for every speaker. So in that regard, this is a one-to-many relationship between speakers and disclosures.
I designed the database that way but then thought that this setup won't allow the user to pick an old event, see who the speakers were, and when they sent in their disclosures.
I've been running around in circles ever since. But maybe there isn't a problem and I'm only imagining it. :)
 
I think the problem is that you need to have a speaker table where each speaker has a unique ID, plus a disclosure table where each disclosure has the speaker ID as a foreign key.

Then write a query that JOINs speaker table to disclosure table on this speaker ID field.

Now for reporting purposes, use the QUERY, not either table, as the driver for your report. Sort in any way you need, order by date or by speaker name (or ID) or some combination. Group by speaker ID, year, whatever else you need for your report.

Just remember this (if you didn't know it already): All a form or report needs is a record source. Most of the time (with the exception of aggregate queries), a query and a table behave identically as record sources.
 
To expound on Doc Man's response about the query for reporting purposes, that's one reason why I had suggested a field that was "Active" so you could easily pull the one that was currently active. But, you can do that also with a Max Date grouping in a query.
 
All right, I shall follow your advice and see how it works out.
Thank you very much.

But first, I'll have to change a few things in my db design.
 
Ok, I am using the max function. But I have run into a problem - this function does not seem to include empty fields. But I have some instances where there is no date, and I need to show those as well. How can I do that?
 
Ok, I think there's been a misunderstanding.
I didn't want to list just the records without a date, I was trying to include them.
As it turned out, my initial observation was wrong - Access does include records without a date, I had just looked up the wrong table where there are not any records without a date and concluded from that that Access' Max function had filtered them out.
My bad, sorry.
 
Last edited:
Still, it's not working.
I have two subforms side by side, which are linked. One is a continuous form that shows several records, and the other one is a single form. Clicking on a record in the continuous form updates the record in the single form [a date]. Some records in the continuous form don't have a date associated with them. That is not a problem, until I try to use the MAX function, which breaks the system in that for records without a date, the subform that shows the date totally disappears and an error message pops up.

Does anybody have an idea why this might be happening?
 
Turns out my query had a problem, and then I forgot to update the name of the control from the original name to MaxOf... but after that I figured all that out, it's working.

I feel a bit stupid now. :D
 
Hm, or not.
When there is no speaker, eg. in the case of a new records, I get error 2427: You entered an expression that has no value.
 

Users who are viewing this thread

Back
Top Bottom