Relationship Trouble (ha ha!)

[BkPlanner]

Hi all -

So i've been gradually adding forms and queries to a database that OBP from this site helped me get started on, but I've sort of run into a stumbling block with a particular set of tables and relationships.

So.. the background information. The primary organizational concept is the "Project". Each PROJECT may affect one or more CommunityDistricts (ComDist), and each PROJECT may have a number of ULURPActions. I managed to get it working up to that point. I'm able to add multiple ComDist and ULURPActions to each PROJECT.

This issue now, is that each ComDist gets to issue a recommendation about each ULURPAction. Remember there may be multiple ComDists and ULURPActions for each project. Therefore, I need a table that holds the recommendations for each ULURPAction for each ComDist within a project.

It seems like it would be straight forward, but I cant get the table (and in turn the query or form to behave the way I want it to).

Everything is "controled" from the ProjectDashboard form, so taht wold be the destination of a tab which shows the recommendation for each ULURP action for each ComDist.

Hopefully that makes sense. I've attached the database. The Tables that are giving me projects are ULURP-ComDist, which I had imagined to be a junction table of Project-ULURP and Project-ComDist. However, I may be completely offbase with that.

any help or suggestions would be apprecaited.

best.

eric

[OBP]

eric, in the ULURP-ComDist table is the PCID the Com Dist ID?
What arethe Vote For, Vote Against, CDRecommendationID and CDRecommendationText going to be used for?
What is the Recommendations Tabel going to be used for?


Wouldn't the logical place for the ComDist Recommendation be on the Com Dist Subform?
Or at least coming from it?
It already has 2/3rds of the data in place.

[BkPlanner]

OBP -

Thanks for the reply. Hopefully this will clear some things up.

pcID the primary key in the Project-ComDist Table. It records a unique value for every Project-ComDist pair.

I included it in the ULURP-ComDist table because essentially what i need to do is generate 1 entry for each ULURPAction for each Project-ComDist pair.

In other word, there might be a project which happens in two Community Districts (a geographical location with boundaries). That project may also have multiple ULURPActions that need to be tracked (i.e. property disposition, rezoning, map change, etc...). Since the project affects more than one CommunityDistrict (ComDist), each CommunityDistrict would get to vote on it and issue separate recommendations.

So what I would need for that project is essenatially a list:
Project 1, ComDist1, ULURPAction1, VotesFor, VotesAgainst
Project 1, ComDist2, ULURPAction1, VotesFor, VotesAgainst
Project 1, ComDist1, ULURPAction2, VotesFor, VotesAgainst
Project 1, ComDist2, ULURPAction2, VotesFor, VotesAgainst
etc...

The RecommendationID: there are 4 possible recommendations: 1. Approve, 2. Approve with Modifications, 3. Disapprove, 4. Disapprove with Modifications. These values woudl be held in a small separate table which I had inteded to join through a query rather than a formal relationship (like I did with the different calendarIDs in the ULURPActions table.

I think the issue with puttin the recommendations on the ComDistSubform is that each comdist gets to issue separate recommendations on each ULURPAction, and I didnt know a good way to present alist of the all ULURPActions for each Community District using continuous forms. I'm open to suggestions.

thanks again!
e

[OBP]

eric, I will get back to you tomorrow after I have had a good look at it.

[OBP]

eric, I have looked at your Tables, Relationships and the Project dashboard and logically the Recommendations should be an "offshoot" of the Com Dist Tab. It could even be a Subform on the Subform if the Com Dist Subform was reset to Single Form mode
The reasoning behind this is that you already have 2/3rds of the data available on that tab and it is a logical trail to follow. Whereas a Tab on the Project Dashboard means that you will have to select a Com Dist and a ULURP Action as well as entering the Com Dist Recommendations.
I am not absolutely sure that the Recommendations need their own table, it would be possible to have the data in the Project/Com Dist Table.
Can you let me know which route you want to pursue?

[BkPlanner]

OBP -

I am willing to give the way you've suggested a shot.

I guess the only thing is that its helpful to be able to see all ComDists and Recommendations at the same time, so the single form option wouldn't let you compare the Recommendations of one ComDists agains another visually (but that could be done in a report or query later on).

Would it be possible to have a list box on the Project-ComDist Sub form which shows all the ULURP Actions and the Votes/recommendations for each ?

thanks for your patience and your assistance.

best.

eric

[OBP]

eric, have a look at this version, I have incorporated the ULURP_ComDist Table in to the ProjectComDist Table, so it would no longer be necessary.
I have created a new query for the ProjectComDist Subform which has the ULURP Action and the Recommendation in it.
I rearranged the Subform (not finished or very well formatted) so that you can see how it would look. It would still need the ULURP Action Description Field replaced by a Combo like the Recommendations one.
But you should get a good idea of whether you want to pursue that version.
Some of the "ID" fields should be set to invisible as users do not need to see them, I have just made them very "short" in height.

[BkPlanner]

Tony -

Thanks for the work on the database. However, I'm not sure that adding the Votes, etc to the ComDist tab will work for what is needed.

I'll try to describe the problem. We may have a project called "Sea Park West". As part of that project we have to get the city council to approve changes in the land use designation of the properties involved in the project in order to allow it to proceed. Unfortunatley those land use changes (ULURP Actions) may include more than one approval. So the city council may have to approve a rezoning (ULURP Action 1) and the sale of city owner property (ULURP Action 2).

If that were the end of it, we could have the approvals, votes, etc.. on the ULURP ACTIONS tab alone. However, somtimes the project spans two council districts and that means that each district gets to vote on it. SO... that means that for each ULURP action we need to keep track of the votes and recommendations from EACH community board.

The form you created only allows us to track ONE approval for each community district, which would be fine if the approval/recommendation applied to all ULURP actions, but they get to approve or disapprove each ULURP Action in each ComDist.

I've posted a new version. There is a query called "ULURP-ComDistQuery". If you run the query, you'll see that the project "Sea Park West" happend in two community districts (10 and 13) and has two associated ULURP Actions. I need to keep track of the votes/reommendation for each line of this query, but cant seem to figure out the tables/relationships that will allow me to do that..

thanks again for all your help.

best!

eric

[OBP]

eric, I think that you will find that the Table & Subform that I proposed does exactly what your Query shows, although I don't know what you are using for the Voting and Text which I thought was supposed to come from the Recommendations Table.
I have populated my version of the ProjectComDist Table with your Query values and you can see what you will get in the Com Dist Tab(subform).
You get 2 items in the Commerce Bank Project and 2 Items in the Sea West Project.

[matthewspatrick]

Your question title reminds me of a time I put a Q at Experts Exchange, "Access and Effective Dating".

:doh:

[BkPlanner]

toony -

here is the latest version!

thanks

eric

[OBP]

eric, I still say that the current set up does it, I have added your combo data to the ProjectComDist table and it shows up as you want on the ComDist Subform.
Are we talking at cross purposes here, do you want something else showing up on the ComDist Subform?

[BkPlanner]

Tony -

but now i cant add ComDist Values to projects that don't already have the data in there, or add new ComDist values/recommendation/votes to projects that have multiple ones.

e

[OBP]

eric, I will post something tomorrow.

[OBP]

eric, I think version does what you want.
Note the change to the ProjectComDist Query links.
I have also modified the Com Dist Combo so that it only has the required Fields in it.

[BkPlanner]

Tony -

I made a form that does essentailly what I want it to do, but the problem is getting to to dynamically update when information on the ComDist and/or ULURP Actions tab changes.

essentially there is now a table between those two, that needs to be updated dynamically. Can that be done with a query or do you think it needs to be done in VB ?

thanks for all your help.
e

[BkPlanner]

and.. the attachment.
sorry i forgot.

[BkPlanner]

oh... and the Moffat gardens project has appropriate data in it. Its the table when ComDist or ULURPActions are changed.

[BkPlanner]

So i was so proud of myslef for actually figuring out how to do what i wanted to do.

I managed to figure out how to use an append query to add only rows that didnt exist to the ULURP-ComDist table when new ULURP Actions or a new ComDist was added to a project from the OnAddition event procedure. That all works fine and it presents the data in a way that I think would make sense for our process (since the Recommendation may sometimes come months after we know which community boards will be voting on the Actions).

However, the one issue is now, the way it's structured, I cant seem to add votes or recommendations to the ULURP-ComDistSubform from the project dashboard. Any suggestions ?

eric

[OBP]

This version is working Ok.
You obviously didn't read my last post where I talked about Query Links.:rolleyes:

[BkPlanner]

Tony -

You're a genius. I did read it. However, understanding it is a whole different story. However, the good news is that now i think about 99% of the inputs are DONE!!!!!!!! That means now i can start badgering you about Reporing and Output. I'll do my best to give you a long weekend off, though.

thanks again.

eric

[OBP]

eric, Searching, Summaries and Reports, like the combos and Subforms, success is all in the Queries.
Will you require Graphical output?
Do you want to see a Comprehensive Search Form/Query, or don't you think that you will need one?