Bill Benson
bensonforums at gmail.com
Sat Apr 12 15:17:39 CDT 2014
Jon, you still have not said made clear what you expect to get out of this database. Who you owe reports to, what they expect to see in the reports, whether the information for historical purposes or to assist with entering information on the next call. I advised that the call details may one day not mesh with the current company contacts, and for that reason, you want a CallParticipants or other type table (if there might be more than one company participant per call) or you want the participant's details in the Call table. I think that while you might think a CompanyContact table suffices, that is more for use in determining who you might *want* to call, it is not good for documenting who participated in the call, who made the decisions, etc. And the last thing you want to do is update the CompanyContact table and have this affect a report you extract today about a call made a year ago. I trust I have said enough on this and it is time for new voices, but if you have any further questions for me directly, I am happy to continue in this thread. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jon Albright Sent: Saturday, April 12, 2014 3:22 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Many to Many relationship issue Hi Jack, We're a specialty contractor, we bid for work on many projects each month, typically over 100 each month. Of any given project, we will receive phone calls from the General contractor's company representative requesting a price quote, validating current information related to a project and/or activities related to a job we may have been awarded and are either working on or getting ready to work on. There are also the outgoing conversations regarding the same items and are done in lieu of using email (making it more personal). There are also instances where we know about a project out for bid but haven't received any calls....yet, from any customers. I currently have a set of excel workbooks used for recording jobs (I use job & project interchangeably) out for bid that several of us work from. Each workbook is for one year and the workbook sheets are for each month. These are not able to easily navigate if you are searching to see if a particular job exists, if it had already been bid once before or one that is similar in nature. As for the phone calls... hmm, that is handled using notepads which totally sucks as it is very time consuming paging through notes for a particular person that called or a call about a particular project. Multiply this by over 3 years that I have been working there. As a rule, we work/supply bids to about 3 dozen different contractors and most of the contractors have anywhere from one to a half-dozen individuals that we correspond with on a regular basis on any given date. In an effort to normalize the stored data, I began my table structure as listed below with two many to many groups of tables with two cross tables and a many to one between customers and customer contact. tblCompany PK CompanyID tbl Jobs PK JobID Cross table tblCustomer_Jobs FK CompanyID FK JobID tblCompanyContact PK ContactID FK CompanyID tblPhoneCalls PK CallID Cross table tblContact_Calls FK ContactID FK CallID My apologies for being so wordy again... -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jack drawbridge Sent: Saturday, April 12, 2014 8:21 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Many to Many relationship issue Jon, Can you list the facts related to your database and the business processes? Just to clarify the "things" you are dealing with and how they relate to each other within the business. I realize you have said "... I work for a contractor and have a need to maintain a customer table (contractor general information), a table for the contact people at the contractor's companies, a projects table (jobs we bid) and a table to log phone calls. I have the tables created with an "associate" table for the M2M relationship between the customer table and the jobs table, another "associate" table between the customer contact table and the phone calls table. I also have a one to many created between the customer table and customer contact table..." What info do you have to maintain for historic or legal or other purposes? Do you do regular reporting or analysis? If so, could you describe this? As the others have said, I think the key is to get your database structure design so that it meets your business needs. Good luck. On Sat, Apr 12, 2014 at 1:36 PM, Jon Albright <jon.albright at hawaii.rr.com>wrote: > I appreciate your input Bill and I'm sorry if you misinterpreted what > I was saying as a slam at the content you posted, I am only trying to > understand how all the parts and pieces fit together and it was > recommended that this forum is a good place for exchanging ideas and problem solving. > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bill Benson > Sent: Saturday, April 12, 2014 7:07 AM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] Many to Many relationship issue > > Maybe I used flatter incorrectly, and maybe you use dependencies in a > different context than I do. > > But I guarantee, GUARANTEE you that to keep an accurate audit and > history of all the dependencies needed to populate what happened, > when, on which jobs, for which companies, involving whom and in what > roles... and be able to trace those deta I ls from the call table back > to their components in the entity tables YOU COULD NOT DO IT while at > the same time creating future transactions with fresh, current > details. A call table is a history table, not an active environment. > The active environment changes, the history/audit/warehousing details > likely will NOT. > > Trying to preserve relationships kills, kills, kills reporting > veracity > > If you want to improve "the quality" of the thread, get to it (but I > don't like your implication that what has been offered so far is of > low quality, that's rather rude). > > I am answering from my experience, yours is a welcome voice. > > I do not have any relationship diagrams to offer you. > On Apr 12, 2014 12:38 PM, "Arthur Fuller" <fuller.artful at gmail.com> wrote: > > > Bill, et. al. on this thread, > > > > I seriously take issue with your comment that "the flatter your > > information is, the less outside dependencies..." > > > > In my opinion, this is wrong, wrong, wrong. Or to phrase it another > > way, this is the reason for Views, as opposed to direct table Selects. > > > > But this whole thread makes me wonder, Is there a way that we can > > create a relational diagram, whether in Access or SSMS or some > > similar tool, and include the diagram in the original post or its > > replies? For this sort of discussion, a picture is worth 1000 words. > > > > Can this be done? I've not yet tried to do this; hence the question. > > But I think that if it can be done, this would go a long way to > > improving the quality of the threads. > > > > Arthur > > > > > > > > -- > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com