 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / June 2006Problem with relationship??
I'm not sure if the problem I am currently having has to do with the relationship or not. I have a subform which includes a field for a seriel number. The subform draws on a query which in turn draws from three different tables (Orders, Order Details and SerielNumber). The relationship from Orders to Order Details is one-to-many with many on the Order Details table; the relationship from Order Details to SerielNumber is one to many with the many on the Order Details table. (The SQL version of the query is as follows: SELECT Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate, Orders.PurchaseOrderNumber, Orders.ShipDate, Orders.ShippingMethodID, Orders.SalesTaxRate, [Order Details].OrderID, [Order Details].ProductID, [Order Details].Quantity, [Order Details].UnitPrice, [Order Details].Price, [Order Details].Discount, SerielNumber.* FROM SerielNumber INNER JOIN (Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON SerielNumber.SerielNumberID = [Order Details].SerielNumberID; When I put a seriel number into the seriel number field of the subform I get an error message saying the following: "You cannot add or change a record because a related record is requred in table 'SerielNumber'." I need to be able to input a seriel number which is not a unique number in any way (it may be repeated several times in conjunction with several different customers for a variety of reasons). If I turn off the referential integrity I am able to input the seriel number and it will appear in the Order Details table in the SerielNumberID field, but it does not appear in my Seriel Number table. Is this the way it is supposed to be? Or did I screw up somewhere? Thanks, Teri. Teri I'm not sure, but it sounds like you could use the same serial number in more than one order detail. If so, you have a many-to-many relationship between those. In Access, you need a third table to resolve the m:m. Regards Jeff Boyce Microsoft Office/Access MVP > I'm not sure if the problem I am currently having has to do with the > relationship or not. I have a subform which includes a field for a seriel [quoted text clipped - 37 lines] > > Teri. I was thinking it was a M:M relationship. But, because I really can't seem to get a grasp on this relational thing, I don't know how to set one of those up. When I create yet another table, how do I relate it so that it does what I want and would I have to change my query at all? Thanks Jeff! Teri. > Teri > [quoted text clipped - 48 lines] > > > > Teri. First thing first, Teri. To "resolve" a m:m relationship, you have one table that holds one side, a second table that holds the other side, and a third table that show valid pairs. In your situation, you have an table for OrderDetails, a table for SerialNumbers, and a "resolver" table that holds valid combinations of OrderDetailID and SerialNumberID (the respective IDs from those two tables). Get the data structured first, then work on the queries, etc. Regards Jeff Boyce Microsoft Office/Access MVP >I was thinking it was a M:M relationship. But, because I really can't seem > to get a grasp on this relational thing, I don't know how to set one of [quoted text clipped - 72 lines] >> > >> > Teri. Jeff, I'm not entirely sure what you mean by "Get the data structured first. . .", but I created another table and called it ValidSerielNumbers. I put the primary key as ValidSeriel, then added OrderDetailID and SerielNumbersID to the table and a field to hold the seriel number. Is this what you meant? I went to relationships and related the ValidSerielNumbers table to the OrderDetails table (came up as 1:M) and then to the SerielNumbers table (also came up as 1:M). How am I doing so far? Thanks again!!!!! Teri. > First thing first, Teri. > [quoted text clipped - 87 lines] > >> > > >> > Teri. Teri Sounds like you've set it up correctly (but I don't understand about "a field to hold the Serial Number". You do have two fields, right? One for the OrderDetailID and one for the SerialNumberID? You don't need to put the actual SerialNumber in this "resolver" table, since your SerialNumber table should already have it. You are just using the resolver table to hold the respective row IDs from the other two tables. First things first -- I was suggesting that you get the data structure correct first, as everything else you'll be doing will depend on that. Regards Jeff Boyce Microsoft Office/Access MVP > Jeff, > [quoted text clipped - 118 lines] >> >> > >> >> > Teri. I now have just the two fields in my "resolver" table. But I still do not understand what you meant by structuring my data. Is that what I am doing by creating this new table? Thank you, Teri. > Teri > [quoted text clipped - 135 lines] > >> >> > > >> >> > Teri. Sorry, correction, I have three fields in my table: ValidSerielID, OrderDetailID and SerielNumbersID. > I now have just the two fields in my "resolver" table. But I still do not > understand what you meant by structuring my data. Is that what I am doing by [quoted text clipped - 143 lines] > > >> >> > > > >> >> > Teri. Yes, that's what I meant. Yes, three fields. You could also get by with just the two RowIDs, and use them as a multi-column primary key. If you ask for the 'groups' recommendations on this, brace yourself for a "religious war". Some folks swear by only including the key fields from the related tables, and using the combination as a unique primary key. Others add (as you have) a unique row identifier (?Autonumber, right?). If you will only ever have one "pair" valid in that resolver table, you'll need to add a unique index on the pair of fields (which is one of the arguments for making the combination the unique primary key). Adding the index assures you'll never have more than one set of the same pair of IDs. If, on the other hand, you might be tracking changes over time, you'd need to add begin and end date fields to the resolver table. Think of it a little like a student, a class, and an enrollment (student signed up for class). Theoretically, the same student could sign up for the same class more than once, so you'd need one/two date fields to track WHEN the sign up was. Regards Jeff Boyce Microsoft Office/Access MVP > Sorry, correction, I have three fields in my table: ValidSerielID, > OrderDetailID and SerielNumbersID. [quoted text clipped - 180 lines] >> > >> >> > >> > >> >> > Teri. I will need to create a report in the future which will simply list the seriel number and all the record numbers associated with that seriel number "since the beginning of time" for that seriel number. So I believe I would need to go with your second option. But I do need to ask, do I really need to add date fields if dates are not going to be an issue in this report? The only date that will be involved is when it is created. It will be set up similar to: Seriel Number Record Number 1133 1 2 3 268 1134 7 6 22 Date Printed: 5/30/2006 > Yes, that's what I meant. Yes, three fields. You could also get by with > just the two RowIDs, and use them as a multi-column primary key. If you ask [quoted text clipped - 204 lines] > >> > >> >> > > >> > >> >> > Teri. Your reports don't have to show 100% of what you store (as data). If you wish to prevent duplication (Same record number/serial number combination), you'll need to index that combination. If you can legitimately have more than one combination of the same serial number and record number, you need a "tie-breaker" like date/time or something. If you can only have one legitimate combination, don't bother. And yes, your report can be laid out as you described. When you get your data set up, create a query to return Serial Number and Record Number. Check on the HideDuplicates property in the report definition you create. Regards Jeff Boyce Microsoft Office/Access MVP >I will need to create a report in the future which will simply list the > seriel number and all the record numbers associated with that seriel [quoted text clipped - 259 lines] >> >> > >> >> > >> >> > >> >> > Teri. Once I have my data input, how am I going to pull my query together? I have three different tables which are all related. The reports I am going to have to pull together are based on a certain product (3 different ones) and the seriel number for that product. Am I going to pull the Product itself from the Order Details table as the ProductID and the record number from the same table and then the seriel number from the seriel number table? I really I am making sense. Thank you so much for all your help!! Teri. > Your reports don't have to show 100% of what you store (as data). > [quoted text clipped - 270 lines] > >> >> > >> >> > Number > >> >> > >> >> > table. Teri Take a look at how queries work. You can add as many (related) tables as you need to, join them together, and select (only) the fields you wish to display (within practical limits). Then you design a report, based on the data the query returns. Regards Jeff Boyce Microsoft Office/Access MVP > Once I have my data input, how am I going to pull my query together? I > have [quoted text clipped - 319 lines] >> >> >> > >> >> > Number >> >> >> > >> >> > table. Jeff, I'm sorry to be such a pest about this, and I really do appreciate all your help with it. My assumption is that I am going to use the OrdersDetail table and the SerielNumber Table to do my query. My problem with this particular query is the criteria I need to put in. This really is not a strong point of mine. I have 4 different items that I will need to do reports on for these seriel numbers. Do I need to create separate queries for each of them or is there a way I can do just one query which will ask me which product I am looking for the seriel and record numbers for? As an example, the different produts that have seriel numbers are the CCUSB, PCC16L, Box12Z and the VX3IA. If I want to list all the seriel numbers along with the record numbers for just the CCUSBs that have been shipped? Once I have this figured out, I should be good, I have a good handle on creating reports and forms. I just can't seem to get a grip on the relationship thing and queries. Again, my apologies for being such a pest. But I have had no training on using Access and am trying to teach myself. Thank you so much, Teri. > Teri > [quoted text clipped - 267 lines] > >> >> >> > >> >> > one to > >> >> >> > >> >> > many Teri Sorry for the absence... I'll recommend that you "play" with queries a bit. Take a look at "criteria" (selection criteria) in Access HELP for ideas. If you only want to know serial numbers for CCUSB product, that would be your criterion. If you want a single query to be able to look up your information AND allow you to pick a product, take a look at "parameter query" in Access HELP. Regards Jeff Boyce Microsoft Office/Access MVP > Jeff, > [quoted text clipped - 342 lines] >> >> >> >> > >> >> > one to >> >> >> >> > >> >> > many Jeff, I don't think creating the query is really going to be a big deal for me, I have a very basic handle on how to do it and have created a parameter query previously. My problem is knowing which table(s) to use for it. Any suggestions? I need to return the product itself (there are 8 different ones, so I will be using a parameter query which will ask for the product's name), the order id and the seriel number. The following is the SQL version of my query and it is not returning any records: SELECT [Order Details].ProductID, [Order Details].OrderID, [Valid Seriel Numbers].SerielNumberID FROM [Order Details] INNER JOIN [Valid Seriel Numbers] ON [Order Details].OrderDetailID = [Valid Seriel Numbers].OrderDetailID WHERE ((([Order Details].ProductID)=[Enter Product])) ORDER BY [Order Details].ProductID, [Order Details].OrderID; I have tried changing combinations of tables and have tried using just the OrderDetails table by itself (it is the only one that has all the "ID" fields that I need). The only thing I can get it to do is if I remove the [Enter Product] it will return ALL the products I have that require seriel numbers. Thanks so much!! Teri. > Teri > [quoted text clipped - 270 lines] > >> >> >> >> > >> show > >> >> >> >> > >> valid Teri Your SQL statement uses the WHERE clause: " WHERE ((([Order Details].ProductID)=[Enter Product]))" So if you're putting a Product NAME in, is that the same as your ProductID? Is there a chance one/more of your underlying tables use a "lookup" datatype field? Regards Jeff Boyce Microsoft Office/Access MVP > Jeff, > [quoted text clipped - 345 lines] >> >> >> >> >> > >> show >> >> >> >> >> > >> valid Jeff, Let me start by thanking you again. You and all the other volunteers are wonderful just for taking the time to help those of us who have no clue about what we are doing. I am no longer working at the place where I need the help for, I took another position far, far away from there. But I am curious and to continue to work on this database and others just so I can learn how to use Access correctly and efficiently. There is a Product Name field and a ProductID field. I would like to be able to put in the product name to return all the correct items if I can. And the closest thing I have to a lookup datatype field is the combo box on the orders subform where you pick the item that is being ordered. Thank you!!!! Teri. > Teri > [quoted text clipped - 271 lines] > >> >> >> >> >> am > >> >> >> >> >> doing by Teri You've been bitten by the bug! You no longer need to do this, but you're curious about how you would have?! One approach to letting the user select a "name" (Product, Person, ...), but having the SQL use the rowID: Have the SQL (or the query) refer to the form on which the user selects the name from a combo box (and have the combo box based on a query that returns the rowID (hidden) and the "name"). You'd use a reference something like: Forms!YourFormName!YourComboBoxName to get the rowID. Regards Jeff Boyce Microsoft Office/Access MVP (and thanks for the kind words. Perhaps you'll have the opportunity to continue visiting the 'groups and find that you can help others here.) > Jeff, > [quoted text clipped - 328 lines] >> >> >> >> >> >> am >> >> >> >> >> >> doing by Yes, I guess I have been bitten by the bug. But I am also kind of thinking I might use the same database in the future for my own use if I start my own business. I will try out your suggestion when I get home tonight and see if it works (if it doesn't I 'm sure it will be something I did wrong and not something you told me). Thank you again for your help! Teri. > Teri > [quoted text clipped - 272 lines] > >> >> >> >> > 22 > >> >> >> >> > Date Printed: 5/30/2006 |
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.