M2m Subform Data Source -- A Natural Alternative?

[boweeb]

I fear I may have stumped the community over at Utter Access, so I thought I'd post here as well. I'd really appreciate some feedback. Due to being a new member, I can't post links so here's my original post:

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

This also has to do with "Tables + Relationships" so I apologize if I've misplaced this thread.

My setup -- (a trimmed and zipped version of my db is attached)

I'm an amateur Access enthusiast trying to write a contact management database at work. We are a midsize construction general contractor and this db is to store contact information for our 3000+ subs. One main goal is to be able to query subs according to categories denoted by MasterFormat. I set up a many-to-many between the companies table and the MasterFormat sections table as well as the subsections tables. Then I created datasheet subforms of the sections in a company details form. Those subforms are based on queries that pull from the linking tables in the m2m relationships.
​

My problem

The current set up works if I run an update query that creates linking records in the m2m tables. However, with the number of companies I'm trying to track, the db size is quickly growing out of control. With just the 46 concrete companies I'm designing the db with (eventually becoming over 3000) multiplied by 35 sections, plus 46 companies multiplied by 1326 subsections, and I've already got over 62,000 records and a db that's over a gig in hd space! I don't anticipate that scaling well with the full number of companies.
​

My Question

There's got to be a more natural method of doing this using relationships and I'm completely stumped. I like the general idea of what my form does -- a list of sections with checkboxes. But I'm completely open to suggestions. Is there a way to get the datasheet sources to list all the sections without records in the m2m tables? That way the only data being stored (and taking hd space) are the sections the subs are actually related to (most often it will be just a couple). How might I have to adapt the form to a different table/relationship solution? Or is my original method really the only way to go and I should be migrating the back end to the SQL server?
​

A few notes: I emptied the m2m tables to save room. If you want to populate them, I have buttons on the "Scratchpad" form that will do so. Be forewarned it will take a few minutes and eat a gig of space. Also, the company information contained is all public domain -- nothing private is being shared.

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
And here's relevant follow-up:
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Jeff B. worte:

If I'm following your description, you are creating ... "dummy" records in your M2M table, one for each possible combination of sub & category. And, as you pointed out, this means there are a lot of empty, unused, unnecessary records.

Why did you decide to create one record for each possible combination? That is, what is the underlying business need that will be satsified by having all those "empties"?

... and if there isn't a business need, don't do it!

Instead, it sounds like you want to have one sub associated with a limited set of categories. Why even set those up "in advance" (i.e., a "dummy" record until filled)? Another approach would be to only create those as they are relevant. After all, won't you have to fill in the empties anyway? I may not be seeing the big picture, but it seems like you'll do the work to fill in Sub-A's records one time or another, so why bother creating a dummy before you need the record?

... or maybe I just am not getting it!

Good luck!

And I responded:

The whole point of those "dummy" records was purely for subform functionality. There needed to be a set of records in order to present the user with a list of possible sections to select with checkboxes next to them. Your question is exactly what I'm getting at. There has to be a better way to do this. But I don't know another way to create a form (or possibly alter the relationships) that can do what I want. The kicker is that MasterFormat isn't a static thing. They release updates and changes every year or so, so I was trying to account for that as well. So the short answer to your question is that they filled the recordset for the subform's datasource.

Thank you very much for engaging me on this. A note about the attached db's on this thread: in their current state they don't do exactly what I said because I had already started to tinker with the problem. So you'll find weird little things like specifying the company ID in a source's query, etc. But most importantly you can see the datasheet subform's in question on the Company Details form. With the original design the user saw the complete list of sections available to checkmark, which in turn filled the combobox with a list of sections selected. Then selecting from that cmb brings up the other subform for the subsections.

 

[stopher]

I haven't read your problem in detail because it's Friday night, and Friday night is curry night

But if I understand correctly, you want a way to provide the list will all possible options but actually only store the options that the user responds to?

I do this by making the subform link to a temporary table. I populate the temporary table with the relevant options I want the user to see. The user responds to some of them e.g. a tick option. Then when done, the temp records that have been responded to are copied to the appropriate table. So there might be ten records presented to the user but the user only completes two and then only those two actually get stored.

hth
Chris

 

[jzwp22]

Do you have the database split into front and back ends now? Generally it is the forms, queries, reports, modules (front end) that take up the space. I assume that you have run the compact and repair utility.

As to your structure, I cannot say since I only have Access 2003 here at work and therefore could not open your attachment.

 

[boweeb]

Thanks for the replies. My thread back at Utter Access came back to life and a very good suggestion came up to use paired list boxes. I found a good working example to use with the Controls_2002.mdb from helenfeddema dot com. I won't be able to work on this db for a little bit but I just wanted to relay what I think will be a suitable solution. I'll post later on what I'll end up using but for now I'm assuming it's solved. Thanks again for the replies.

@stopher -- That could be a useful idea and I may combine the paired list boxes idea with something like that.

@jzwp22 -- My primary concern wasn't the size of the files (it is split) but that the size was an indication that I had a serious design flaw.