Little help (1 Viewer)

[jeremie_ingram]

I have a mdb that I am working on, and have encountered a problem. It deals with junction tbls, and how to bring the data from both tbls into a useable format.

In the mdb I have 2 tbls, one containing NC(neighborhood codes) and another containing TB(taxing Bodies). Since there are multiple TB for each NC, and each TB can be assigned to multiple NC's - I have the junction tbl. My goal is to create the junction tbl to relate the multiple TBs to each NC and include the year and rate. I would like to create a form that would show the NC at the top, allow for a selection of year, and then have the user select the TBs via checkbox.
Where I hit the brick wall is when I attempt to structure this. I understand the necessity for the junction tbl, and know that is where the year and rate need to go. When I attempt to create the form I cant get it to work as described above.
One other note, when a user enters a new year/rate, I would need to selection of TBs to default to the previous year/rate selections.

Any suggestions or ideas?

Thanks.

 

[fluffyozzy]

Here's a little sample I believe Pat Hartman posted sometime ago. It takes a bit of studying to understand what everything is doing but I have found it immensely useful in the past.

Pay special attention to the queries underlying the forms and the combo boxes. I'm sure you will understand how things are structured.

 

[jeremie_ingram]

Close

Thanks for the submission, but either I cannot see how the logic can be applied to my situation OR I am just not getting my idea across right. I have attached mock data (ExampleData.txt) and the database with the two tbls. The txt is a reflection of what I receive in print form, and the database only contains the two tbls that I have established. You can see from the text file the relationships that are needed, and why. What I need to create is a junction tbl that relates the two tbls and includes year and rate.
My thought process leads me to this.
tblJCT
PKey
NC (from tblNC)
TB (from tblTB)
TxYr
TxRt

But it has to create a single record for each associated TB. The next train of thought I went down was to establish a relation from NC to a tbl for TxYr and xRt, then build a junction to the tblTB. I could not get this to create a form with the TB displayed on a subform w/ the checkboxes.

I feel like I am running in circles, and always hitting the same brick wall. I just cant get my train of thought on the right tracks.

 

[fluffyozzy]

Hi jeremie,

Okay, you do need a junction table. See the attachment, easier than explaining it with words.

Have a look at the relationship window, you will see how they are connected together. The way you connect the two tables together is by their Primary Key fields (NCPKey and ID), not NC and TB. Also, you may want to consider setting txYr and txRt fields as Number, as you may need to calculate stuff in the future.

This is only the beginning of course, you will need a few queries to be able to build the forms as you want it. Maybe you can try to incorporate Pat's example I posted above now that the relationships are set up correctly. Let me know how it goes.

 

[jeremie_ingram]

So far.....

Well, I have looked at the example (thanks) and a few more from the MS website that they have for download.

What I guess I am trying to get at is this. I would like to structure the tables properly so that I can design a form that would allow for a selection (from all TB) for each NC & Ty/TR. I cannot get the subform to display ALL tb and I think it has to deal with the relationship within the junction tbl.

I will search to forums a bit more for examples.

Thanks for the input.

 

[fluffyozzy]

I think it can be done how you want it - everytime I have to do a form based on junction table I despair, if I'm honest LOL

Apologies for not being more helpful. Fairly sure there are others here will know exactly what to do. Good luck

 

[Banana]

Jeremie, would a multi-select listbox accomplish this for you? This require quite fair amount of VBA, but worth it to the users, I'd think.

Search the forums on multi-select listbox if interested.