 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / June 2005Many to Many Relationship
Please Help! (Working in Access 2000 file format) Scenario: The store gets invoices. The main office processes invoices. I want to find out three things- 1) the invoices the main office processed that the store received. 2) the invoices the store got that the main office didn't process. 3) the invoices the main office processed that the store didn't receive. I think I have a many to many relationship and understands that I need a junction table in Access. Basically I have three tables. This is the setup: Table Name: Store Column Name: StoreID (autonumber, pk) InvoiceNumber (text) InvoiceDate Table Name: MainOffice Column Name: MainOfficeID (autonumber, pk) InvoiceNumPro (text) InvoiceDatePro TableName: StoreAndMainOffice Column Name: StoreID (number, pk) MainOfficeID (number, pk) I created a one to many relationship from Store to StoreAndMainOffice, and from MainOffice to StoreAndMainOffice. Thus hoping to have created a correct junction table setup. Let me know if this is incorrect. I have data for the Store table and the MainOffice table that I want to import in (basically from an excel document). And the information is already imported. How do I get Access to populate the StoreAndMainOffice table now that the Store table and Main Office Table is populated? And what are the queries that I need to get the results to the three things that I want from this database, or technically item 2 and 3, since item 1 is answered by the junction table. Feel free to past the SQL of the query so that I can copy and paste it to my database. Thanks in advance for any assistance! Crystal, Maybe someone will understand the scenario you have described, but I'm afraid I don't. Are you dealing with different organisations, or are all these Stores and MainOffices within the same organisation? Where do the Invoices come from? I assume you mean that when a Store receives an invoice, it can forward it to a number of MainOffices for processing, and equally, any given MainOffice can receive invoices from a number of Stores? Whatever the case, you wouldn't put the InvoiceNumber in the Stores table... there must be a one-to-many relationship between Stores and Invoices, so there needs to be a separate table for the Invoices, and presumably a StoreID field in the Invoices table in order to record which Store any given Invoice relates to. Anyway, I'll stop guessing... can you please give some more details about the data you are trying to manage?  SignatureSteve Schapel, Microsoft Access MVP > Please Help! (Working in Access 2000 file format) > [quoted text clipped - 36 lines] > > Thanks in advance for any assistance! Hi Steve, Thanks for attempting to understand the scenario. Let me try again. There is only one store and one main office. The store gets invoices and later forwards them to the main office to process. The main office process invoices received from the store or through the mail from vendors directly. So I want to know the three things from my earlier post. I wish I can draw circles. One circle would contain data on the invoices the store received. A second circle contain data on the invoices the main office processed. When you push the two circles slightly together (the store circle on the left and the main office circle on the right), the middle will intersect. The middle intersecting part are the invoices that the store forwarded to the main office to process. The left non-intersecting part are the invoices that the store forwarded to the main office that didn't get processed. The right non-intersecting part are the invoices that the main office processed received through the mail (not forwarded by the store). I want to know the invoices the two tables have in common and those that are not common to each other. I hope the scenario is a little clearer.... > Crystal, > [quoted text clipped - 52 lines] > > > > Thanks in advance for any assistance! Crystal, I enjoyed the story of the circles. This conveys your meaning well. In fact, it conveys it much better than your table-based explanation. This is because your table design does not adequately reflect the nature of the information you are trying to manage... if you don't mind me saying so. I would really suggest taking a deep breath here, and start again. There is no way this can be interpreted within the concept of a many-to-many relationship... it just doesn't fit that model. And the idea of using different tables to store different information about the invoices is really asking for trouble. You have Invoices. Whether any given invoice was received in the office via the store or through the mail from vendors, this is information *about* the invoice, and as such needs a *field* in the Invoices table to describe it. Similarly, whether any given invoice from the store has or hasn't been processed through the office, this is information *about* the invoice, and as such needs a *field* in the Invoices table to describe it. If you try to descibe this information according to which table it's in, by having different tables for different characteristics of the same data, this is called the "tables as data" trap. I still don't really know enough about you business procedures to get too specific. But I would say all the information about the invoices goes in one table. And imagine you would have one field which might be called ReceivedBy where the data will be entered either Store or Office, and another field called DateProcessed so if this field is empty you will know that the Office has not processed it yet. Etc. And then any queries to retrieve the kinds of data summaries that your initial question was about, will be very simple. SignatureSteve Schapel, Microsoft Access MVP > Hi Steve, > [quoted text clipped - 16 lines] > I want to know the invoices the two tables have in common and those that are > not common to each other. I hope the scenario is a little clearer.... Okay- I can start from scratch. I have two lists. Let's call them List A and List B. List A and List B each has two columns, named InvoiceNumber, Invoice Date. All I want to know is what data appears on both lists, what data appears on list A but not on list B, and what data appears on list B but not on list A. Should I ust Excel or can Access easily give me the information I'm looking for? > Crystal, > [quoted text clipped - 45 lines] > > I want to know the invoices the two tables have in common and those that are > > not common to each other. I hope the scenario is a little clearer.... Crystal, Access can easily give you the information you are looking for... as long as you set it up as a database. When I said about starting again, I did not mean starting again to explain the problem. I meant starting again to design your database. What I meant is you *should not* have two lists. You should have one list, with an additional column(s) to identify the type/stage information about the invoices. That was the whole point of my previous post... I am sorry my meaning was not clear. A database should identify information according to the data, not according to which list it's in. In you case, I suggest the "list" would have 4 columns (obviously over-simplified)... InvoiceNumber, Invoice Date, ReceivedBy, DateProcessed. With this, you will be able to use a Query to extract, count, etc according to: - the invoices received by the office by mail - the invoices received by the store but not processed by the office - the invoices received by the store and processed by the office SignatureSteve Schapel, Microsoft Access MVP > Okay- I can start from scratch. > [quoted text clipped - 3 lines] > B, and what data appears on list B but not on list A. Should I ust Excel or > can Access easily give me the information I'm looking for? Steve- Thank you. I understand what you are saying. I guess I was hoping there was a different way to accomplish what I need. Thanks again. -Crystal > Crystal, > [quoted text clipped - 21 lines] > > B, and what data appears on list B but not on list A. Should I ust Excel or > > can Access easily give me the information I'm looking for? Crystal, There is a different way to accomplish what you need... but it would be unprofessional of me to advise such a course. SignatureSteve Schapel, Microsoft Access MVP > Steve- > [quoted text clipped - 4 lines] > > -Crystal As MSAccess is a relational database with an acceptable SQL as well a VBA the relationship manager it is unneccessary and wise to not use th relationship manager. I agree with Steve, all data should kept in th 'Invoice' table. Use a query to replicate the relationship manager o use SQL. Can I assume that the difference offices traversed by the invoice handles them for different purposes. ie the first office logs the (accounts payable) and the other office actually processes the funds (Good seperation of duties) -- nhornsbyPosted from http://www.pcreview.co.uk/ newsgroup acces Crystal, I think I can understand some of the challenges that lead to your business questions: Both the store and main office get asked by vendors about the status of their payment. So both entities would like to know if the other one has information. They also are leery of paying the same invoice twice. As others have said, I think all you need are two tables: Vendor and Invoice, with a one to many join. Your invoice table could include fields such as: StoreDateRcvd, MainDateRcvd, and DatePaid. If StoreDateRcvd is blank and MainDateRcvd is not, you know the invoice must have gone directly to the main office. And if there is no DatePaid, it still is in process. And there are other queries that would answer your three questions. Your single database would contain validation and integrity rules such as preventing duplicate invoice numbers. Are the main office and the store able to share the same database? I suspect they are NOT, by the way you talk about "two lists". However that is a different sort of problem that should not affect the database design. Seems to me the database should reside at the main office (since they control and payments and presumably the accounting). The store should keep a list of invoices received (Vendor, InvNbr, DateRcvd, DateSentMain). This could be transmitted daily or weekly to the main office to be keyed into the database. Store personnel would then ask the Main Office for status questions on a particular invoice. Art > Please Help! (Working in Access 2000 file format) > [quoted text clipped - 36 lines] > > Thanks in advance for any assistance! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.