If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Relationships
OK so the PONumber isn't the PK, that's fine. I would move the Invoice No
to the tblTicketNo, yes. TaxID/SSN does not belong in the tblTicketDetails. A TicketDetail belongs to a TicketNo, which in turn belongs to a Vendor. There is no need to store any Vendor information in the TicketDetail as this information is connected via the TicketNo table. So you should have tblVendors: TAXid(PK), address, city, state, zip, SSN tblTicketNo: recTicketNo (PK), TAXid (FK), PONumber, InvoiceNo, InvoiceDate tblTicketDetails: recTicketNo (FK), Unit(FK), qty, priceperunit. tblUnits - Unit(PK), UnitDescription, CurrentPrice tblVendors 1-M tblTicketNo tblTicketNo 1-M tblTicketDetails tblUnits 1-M tblTicketDetails -- Joan Wild Microsoft Access MVP "Melinda" wrote in message ... The reason I can not have the PO number as the (PK) is because we have a standard purchase order DV6107, debit voucher money or credit card purchases that we use that PO number over and over, and select DVtype, what kind of purchase it is, fuel, parts, utilites, etc., etc. I tried it that way, but of course wouldn't allow duplicates. In the tblticketdetails number, we can have several lines of details to one receiving ticket and I was having to type the invoice no on each line, so possibly moving the InvoiceNo to the ticket table would work there. I put the TaxID/SSN in the details table with the advice of Jeff, I believe, that I needed to link it to the vendors table. Is that wrong, I can delete that link? Where I seem to struggle is when I get several tables and have to link them together. thank "Joan Wild" wrote: Your description got a bit messed up but I think what you want is the following: tblVendors: TAXid(PK), address, city, state, zip tblTicketNo: recTicketNo (PK), TAXid (FK), PONumber (couldn't this be the PK?), PODate If PONumber is unique (and I'd think it would be), you can use it as the PK. If you do this, then in tblTicketDetails, remove recTicketNo and replace it with PONumber tblTicketDetails: recTicketNo (FK), invoiceid (PK) - this seems odd wouldn't the invoice apply to the Ticket, not the details? Also you wouldn't have an invoice date for each line item. Unit, qty, priceperunit. There is no need for the unit description or the Taxid in this table. You'd have a separate table for the units tblUnits - Unit, UnitDescription, CurrentPrice tblVendors 1 - M tblTicketNo - linked on TAXid tblTicketNo 1 - M tblTicketDetails - linked on recTicketNo tblUnits 1 - M tblTicketDetails - linked on Unit -- Joan Wild Microsoft Access MVP "Melinda" wrote in message ... I think at this point I have myself so confused I have comtemplating starting over. Bottom line I have a a table with vendors, a table with ticket numbers (PKautonumber) and a table with the details. In a sense I am using the tblticket no as a junction table, but it doesn't seem to be working properly. tblvendors tblticketno tblticketdetails TAXid(PK) recticketno (PKautonumber) invoiceid (pk) address POnumber invoice no city dvtype invoicedate state year qty zip priceperunit unit description recticketno taxid tblvendor 1:M to tblticketdetails .... taxid/ssn tblticketno 1:M to tblvendors ....recticketno tblticketno 1:1 to tblticketdetails .......recticketno this is pretty much my layout. I agree that there should be many ticket details to one ticket no. I wanted the RecTicketNo of the ticket table to be an autonumber and that is why I put it in a seperate table. So that I can have one ticket no to many lines of ticket details. Maybe I am going about this all wrong. It seems as though once I get the design and the tables set I up I don't struggle as much. I ordered a book today and I hoping I can learn a bit more. Thanks for your patience. "John Vinson" wrote: On Tue, 23 Jan 2007 06:24:04 -0800, Melinda wrote: tblvendors 1:M to the tblticketdetails linking with the TaxID/SSN tblticket 1:M to the tblvendors linking with the ReCTicketNo tblticketdetails 1:M to the tblticketno with the InvoiceNo I am thinking that the tblticketno should be on the 1 side to the tblticketdetails on the many side. I can't seem to get that switched no matter what I try. If tblTicketDetails is m:1 with tblTicket, then surely you want to have the ticket number as a foreign key in tblTicketDetails? Why would the InvoiceNo be in tblTicketDetails? You'll link from the primary key of the "one" side table to a foreign key field (same datatype) in the "many" side table. John W. Vinson[MVP] |
#12
|
|||
|
|||
Relationships
Melinda
I probably suggested that before I learned enough about the relationships, which sound like 1:m:m. -- Regards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ "Melinda" wrote in message ... The reason I can not have the PO number as the (PK) is because we have a standard purchase order DV6107, debit voucher money or credit card purchases that we use that PO number over and over, and select DVtype, what kind of purchase it is, fuel, parts, utilites, etc., etc. I tried it that way, but of course wouldn't allow duplicates. In the tblticketdetails number, we can have several lines of details to one receiving ticket and I was having to type the invoice no on each line, so possibly moving the InvoiceNo to the ticket table would work there. I put the TaxID/SSN in the details table with the advice of Jeff, I believe, that I needed to link it to the vendors table. Is that wrong, I can delete that link? Where I seem to struggle is when I get several tables and have to link them together. thank "Joan Wild" wrote: Your description got a bit messed up but I think what you want is the following: tblVendors: TAXid(PK), address, city, state, zip tblTicketNo: recTicketNo (PK), TAXid (FK), PONumber (couldn't this be the PK?), PODate If PONumber is unique (and I'd think it would be), you can use it as the PK. If you do this, then in tblTicketDetails, remove recTicketNo and replace it with PONumber tblTicketDetails: recTicketNo (FK), invoiceid (PK) - this seems odd wouldn't the invoice apply to the Ticket, not the details? Also you wouldn't have an invoice date for each line item. Unit, qty, priceperunit. There is no need for the unit description or the Taxid in this table. You'd have a separate table for the units tblUnits - Unit, UnitDescription, CurrentPrice tblVendors 1 - M tblTicketNo - linked on TAXid tblTicketNo 1 - M tblTicketDetails - linked on recTicketNo tblUnits 1 - M tblTicketDetails - linked on Unit -- Joan Wild Microsoft Access MVP "Melinda" wrote in message ... I think at this point I have myself so confused I have comtemplating starting over. Bottom line I have a a table with vendors, a table with ticket numbers (PKautonumber) and a table with the details. In a sense I am using the tblticket no as a junction table, but it doesn't seem to be working properly. tblvendors tblticketno tblticketdetails TAXid(PK) recticketno (PKautonumber) invoiceid (pk) address POnumber invoice no city dvtype invoicedate state year qty zip priceperunit unit description recticketno taxid tblvendor 1:M to tblticketdetails .... taxid/ssn tblticketno 1:M to tblvendors ....recticketno tblticketno 1:1 to tblticketdetails .......recticketno this is pretty much my layout. I agree that there should be many ticket details to one ticket no. I wanted the RecTicketNo of the ticket table to be an autonumber and that is why I put it in a seperate table. So that I can have one ticket no to many lines of ticket details. Maybe I am going about this all wrong. It seems as though once I get the design and the tables set I up I don't struggle as much. I ordered a book today and I hoping I can learn a bit more. Thanks for your patience. "John Vinson" wrote: On Tue, 23 Jan 2007 06:24:04 -0800, Melinda wrote: tblvendors 1:M to the tblticketdetails linking with the TaxID/SSN tblticket 1:M to the tblvendors linking with the ReCTicketNo tblticketdetails 1:M to the tblticketno with the InvoiceNo I am thinking that the tblticketno should be on the 1 side to the tblticketdetails on the many side. I can't seem to get that switched no matter what I try. If tblTicketDetails is m:1 with tblTicket, then surely you want to have the ticket number as a foreign key in tblTicketDetails? Why would the InvoiceNo be in tblTicketDetails? You'll link from the primary key of the "one" side table to a foreign key field (same datatype) in the "many" side table. John W. Vinson[MVP] |
#13
|
|||
|
|||
Relationships
I am getting set up accordingly, but have one slight problem. When I link
the tblticketno to the tblticketdetails I get a 1:1 relationship instead of a 1:M relationship. I am having trouble linking the recTicketNo being that it is a autonumber in the tblTicketNo and just a number in the tblTicketDetails. I was thinking last evening that possibly I want to change my PK in the tblticketno to the Invoice No. and have that relate the ticket and details table. Thanks so much for all your help. "Joan Wild" wrote: OK so the PONumber isn't the PK, that's fine. I would move the Invoice No to the tblTicketNo, yes. TaxID/SSN does not belong in the tblTicketDetails. A TicketDetail belongs to a TicketNo, which in turn belongs to a Vendor. There is no need to store any Vendor information in the TicketDetail as this information is connected via the TicketNo table. So you should have tblVendors: TAXid(PK), address, city, state, zip, SSN tblTicketNo: recTicketNo (PK), TAXid (FK), PONumber, InvoiceNo, InvoiceDate tblTicketDetails: recTicketNo (FK), Unit(FK), qty, priceperunit. tblUnits - Unit(PK), UnitDescription, CurrentPrice tblVendors 1-M tblTicketNo tblTicketNo 1-M tblTicketDetails tblUnits 1-M tblTicketDetails -- Joan Wild Microsoft Access MVP "Melinda" wrote in message ... The reason I can not have the PO number as the (PK) is because we have a standard purchase order DV6107, debit voucher money or credit card purchases that we use that PO number over and over, and select DVtype, what kind of purchase it is, fuel, parts, utilites, etc., etc. I tried it that way, but of course wouldn't allow duplicates. In the tblticketdetails number, we can have several lines of details to one receiving ticket and I was having to type the invoice no on each line, so possibly moving the InvoiceNo to the ticket table would work there. I put the TaxID/SSN in the details table with the advice of Jeff, I believe, that I needed to link it to the vendors table. Is that wrong, I can delete that link? Where I seem to struggle is when I get several tables and have to link them together. thank "Joan Wild" wrote: Your description got a bit messed up but I think what you want is the following: tblVendors: TAXid(PK), address, city, state, zip tblTicketNo: recTicketNo (PK), TAXid (FK), PONumber (couldn't this be the PK?), PODate If PONumber is unique (and I'd think it would be), you can use it as the PK. If you do this, then in tblTicketDetails, remove recTicketNo and replace it with PONumber tblTicketDetails: recTicketNo (FK), invoiceid (PK) - this seems odd wouldn't the invoice apply to the Ticket, not the details? Also you wouldn't have an invoice date for each line item. Unit, qty, priceperunit. There is no need for the unit description or the Taxid in this table. You'd have a separate table for the units tblUnits - Unit, UnitDescription, CurrentPrice tblVendors 1 - M tblTicketNo - linked on TAXid tblTicketNo 1 - M tblTicketDetails - linked on recTicketNo tblUnits 1 - M tblTicketDetails - linked on Unit -- Joan Wild Microsoft Access MVP "Melinda" wrote in message ... I think at this point I have myself so confused I have comtemplating starting over. Bottom line I have a a table with vendors, a table with ticket numbers (PKautonumber) and a table with the details. In a sense I am using the tblticket no as a junction table, but it doesn't seem to be working properly. tblvendors tblticketno tblticketdetails TAXid(PK) recticketno (PKautonumber) invoiceid (pk) address POnumber invoice no city dvtype invoicedate state year qty zip priceperunit unit description recticketno taxid tblvendor 1:M to tblticketdetails .... taxid/ssn tblticketno 1:M to tblvendors ....recticketno tblticketno 1:1 to tblticketdetails .......recticketno this is pretty much my layout. I agree that there should be many ticket details to one ticket no. I wanted the RecTicketNo of the ticket table to be an autonumber and that is why I put it in a seperate table. So that I can have one ticket no to many lines of ticket details. Maybe I am going about this all wrong. It seems as though once I get the design and the tables set I up I don't struggle as much. I ordered a book today and I hoping I can learn a bit more. Thanks for your patience. "John Vinson" wrote: On Tue, 23 Jan 2007 06:24:04 -0800, Melinda wrote: tblvendors 1:M to the tblticketdetails linking with the TaxID/SSN tblticket 1:M to the tblvendors linking with the ReCTicketNo tblticketdetails 1:M to the tblticketno with the InvoiceNo I am thinking that the tblticketno should be on the 1 side to the tblticketdetails on the many side. I can't seem to get that switched no matter what I try. If tblTicketDetails is m:1 with tblTicket, then surely you want to have the ticket number as a foreign key in tblTicketDetails? Why would the InvoiceNo be in tblTicketDetails? You'll link from the primary key of the "one" side table to a foreign key field (same datatype) in the "many" side table. John W. Vinson[MVP] |
#14
|
|||
|
|||
Relationships
I am surprised you can figure out what I am doing, because I am basically
self teaching myself. I have written 3 other databases, but this the first one that involves alot of tables. If you have any insight into "good" material let me know. I have purchased the Access Bible, The Complete Reference Access 2002, and I just ordered O'Reillys Access Cookbook and design and databases, that is really where I seem to struggle, getting the design and the tables to talk to one another. Thanks for all your help. Melinda Programmer Ohio Dept. of Transportation "Jeff Boyce" wrote: Melinda I probably suggested that before I learned enough about the relationships, which sound like 1:m:m. -- Regards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ "Melinda" wrote in message ... The reason I can not have the PO number as the (PK) is because we have a standard purchase order DV6107, debit voucher money or credit card purchases that we use that PO number over and over, and select DVtype, what kind of purchase it is, fuel, parts, utilites, etc., etc. I tried it that way, but of course wouldn't allow duplicates. In the tblticketdetails number, we can have several lines of details to one receiving ticket and I was having to type the invoice no on each line, so possibly moving the InvoiceNo to the ticket table would work there. I put the TaxID/SSN in the details table with the advice of Jeff, I believe, that I needed to link it to the vendors table. Is that wrong, I can delete that link? Where I seem to struggle is when I get several tables and have to link them together. thank "Joan Wild" wrote: Your description got a bit messed up but I think what you want is the following: tblVendors: TAXid(PK), address, city, state, zip tblTicketNo: recTicketNo (PK), TAXid (FK), PONumber (couldn't this be the PK?), PODate If PONumber is unique (and I'd think it would be), you can use it as the PK. If you do this, then in tblTicketDetails, remove recTicketNo and replace it with PONumber tblTicketDetails: recTicketNo (FK), invoiceid (PK) - this seems odd wouldn't the invoice apply to the Ticket, not the details? Also you wouldn't have an invoice date for each line item. Unit, qty, priceperunit. There is no need for the unit description or the Taxid in this table. You'd have a separate table for the units tblUnits - Unit, UnitDescription, CurrentPrice tblVendors 1 - M tblTicketNo - linked on TAXid tblTicketNo 1 - M tblTicketDetails - linked on recTicketNo tblUnits 1 - M tblTicketDetails - linked on Unit -- Joan Wild Microsoft Access MVP "Melinda" wrote in message ... I think at this point I have myself so confused I have comtemplating starting over. Bottom line I have a a table with vendors, a table with ticket numbers (PKautonumber) and a table with the details. In a sense I am using the tblticket no as a junction table, but it doesn't seem to be working properly. tblvendors tblticketno tblticketdetails TAXid(PK) recticketno (PKautonumber) invoiceid (pk) address POnumber invoice no city dvtype invoicedate state year qty zip priceperunit unit description recticketno taxid tblvendor 1:M to tblticketdetails .... taxid/ssn tblticketno 1:M to tblvendors ....recticketno tblticketno 1:1 to tblticketdetails .......recticketno this is pretty much my layout. I agree that there should be many ticket details to one ticket no. I wanted the RecTicketNo of the ticket table to be an autonumber and that is why I put it in a seperate table. So that I can have one ticket no to many lines of ticket details. Maybe I am going about this all wrong. It seems as though once I get the design and the tables set I up I don't struggle as much. I ordered a book today and I hoping I can learn a bit more. Thanks for your patience. "John Vinson" wrote: On Tue, 23 Jan 2007 06:24:04 -0800, Melinda wrote: tblvendors 1:M to the tblticketdetails linking with the TaxID/SSN tblticket 1:M to the tblvendors linking with the ReCTicketNo tblticketdetails 1:M to the tblticketno with the InvoiceNo I am thinking that the tblticketno should be on the 1 side to the tblticketdetails on the many side. I can't seem to get that switched no matter what I try. If tblTicketDetails is m:1 with tblTicket, then surely you want to have the ticket number as a foreign key in tblTicketDetails? Why would the InvoiceNo be in tblTicketDetails? You'll link from the primary key of the "one" side table to a foreign key field (same datatype) in the "many" side table. John W. Vinson[MVP] |
#15
|
|||
|
|||
Relationships
"Melinda" wrote in message
... I am getting set up accordingly, but have one slight problem. When I link the tblticketno to the tblticketdetails I get a 1:1 relationship instead of a 1:M relationship. Probably with all your troubleshooting, you had set the recTicketNo field in the tblticketdetails to be unique. That will cause Access to think it's a 1:1 relationship. I am having trouble linking the recTicketNo being that it is a autonumber in the tblTicketNo and just a number in the tblTicketDetails. Yes it should be a number, of size 'Long Integer' I was thinking last evening that possibly I want to change my PK in the tblticketno to the Invoice No. and have that relate the ticket and details table. Thanks so much for all your help. Now you're on to something. That is likely what you want to do. An Invoice No is likely unique and so is a good candidate for the PK in tblticketno. Get rid of recTicketNo in tblticketno and in tblticketdetails. Make InvoiceNo the PK in tblticketno and add InvoiceNo as a field in tblticketdetails (make sure it's of the same data type/size; establish the relationship between these two tables using that field. You would find the resources at http://home.bendbroadband.com/conrad...abaseDesign101 a worthwhile read. -- Joan Wild Microsoft Access MVP |
#16
|
|||
|
|||
Relationships
I went into the tabledetails and changed in the indexing, that is where my
problems were. By splitting the units into its own table from the detail table, what will I gain? I am a newbie, and I am self teaching myself. "Joan Wild" wrote: "Melinda" wrote in message ... I am getting set up accordingly, but have one slight problem. When I link the tblticketno to the tblticketdetails I get a 1:1 relationship instead of a 1:M relationship. Probably with all your troubleshooting, you had set the recTicketNo field in the tblticketdetails to be unique. That will cause Access to think it's a 1:1 relationship. I am having trouble linking the recTicketNo being that it is a autonumber in the tblTicketNo and just a number in the tblTicketDetails. Yes it should be a number, of size 'Long Integer' I was thinking last evening that possibly I want to change my PK in the tblticketno to the Invoice No. and have that relate the ticket and details table. Thanks so much for all your help. Now you're on to something. That is likely what you want to do. An Invoice No is likely unique and so is a good candidate for the PK in tblticketno. Get rid of recTicketNo in tblticketno and in tblticketdetails. Make InvoiceNo the PK in tblticketno and add InvoiceNo as a field in tblticketdetails (make sure it's of the same data type/size; establish the relationship between these two tables using that field. You would find the resources at http://home.bendbroadband.com/conrad...abaseDesign101 a worthwhile read. -- Joan Wild Microsoft Access MVP |
#17
|
|||
|
|||
Relationships
Actually Melinda, I think you should go with the InvoiceNo as the PK, not
recTicketNo. The units should be in its own table. You should enter information about a unit only once. Then in your ticket details, you only need to enter (actually choose the item via a combobox in a form) the Unit ID, not the description or other information about that item. Much like you separated the TicketNo information from the ticket details. You don't want to enter the Ticket information for every ticket detail record. You just enter the InvoiceNo (assuming you change that to the PK) in the ticket details; you don't enter the rest of the Ticket info as that can be looked up in the tblTicketNo table using the InvoiceNo. I highly recommend you read the references at http://home.bendbroadband.com/conrad...abaseDesign101 There's lots of good information with examples that will help you understand. It is really worth it to get the design right; everything else will just fall into place then. -- Joan Wild Microsoft Access MVP "Melinda" wrote in message ... I went into the tabledetails and changed in the indexing, that is where my problems were. By splitting the units into its own table from the detail table, what will I gain? I am a newbie, and I am self teaching myself. "Joan Wild" wrote: "Melinda" wrote in message ... I am getting set up accordingly, but have one slight problem. When I link the tblticketno to the tblticketdetails I get a 1:1 relationship instead of a 1:M relationship. Probably with all your troubleshooting, you had set the recTicketNo field in the tblticketdetails to be unique. That will cause Access to think it's a 1:1 relationship. I am having trouble linking the recTicketNo being that it is a autonumber in the tblTicketNo and just a number in the tblTicketDetails. Yes it should be a number, of size 'Long Integer' I was thinking last evening that possibly I want to change my PK in the tblticketno to the Invoice No. and have that relate the ticket and details table. Thanks so much for all your help. Now you're on to something. That is likely what you want to do. An Invoice No is likely unique and so is a good candidate for the PK in tblticketno. Get rid of recTicketNo in tblticketno and in tblticketdetails. Make InvoiceNo the PK in tblticketno and add InvoiceNo as a field in tblticketdetails (make sure it's of the same data type/size; establish the relationship between these two tables using that field. You would find the resources at http://home.bendbroadband.com/conrad...abaseDesign101 a worthwhile read. -- Joan Wild Microsoft Access MVP |
#18
|
|||
|
|||
Relationships
I really wanted to use Invoice No. as my PK, but our business is building and
maintaining roads, Ohio Dept. of Transportation. We have an accounting office and we have 8 county garages, not mention the 17 other departments within the main complex. The invoices from the vendors are sent to our Accounting office and the 8 county garages have to account for the material being purchased, never seeing the invoices, they send the receiving tickets into the Accounting department where the invoices are matched up with the tickets ---so they do not know the invoice number at the time they are receiving the material. I have racked my brain trying to figure out a different "unique number" as opposed to the Receiving Ticket --being the PK and auto numbering the tickets for me. I totally agree with the fact that once you get the design right, the road seems a bit smoother. You have been a wonderful help, thanks so much for patiences and I will check out the website and keep plugging along. "Joan Wild" wrote: Actually Melinda, I think you should go with the InvoiceNo as the PK, not recTicketNo. The units should be in its own table. You should enter information about a unit only once. Then in your ticket details, you only need to enter (actually choose the item via a combobox in a form) the Unit ID, not the description or other information about that item. Much like you separated the TicketNo information from the ticket details. You don't want to enter the Ticket information for every ticket detail record. You just enter the InvoiceNo (assuming you change that to the PK) in the ticket details; you don't enter the rest of the Ticket info as that can be looked up in the tblTicketNo table using the InvoiceNo. I highly recommend you read the references at http://home.bendbroadband.com/conrad...abaseDesign101 There's lots of good information with examples that will help you understand. It is really worth it to get the design right; everything else will just fall into place then. -- Joan Wild Microsoft Access MVP "Melinda" wrote in message ... I went into the tabledetails and changed in the indexing, that is where my problems were. By splitting the units into its own table from the detail table, what will I gain? I am a newbie, and I am self teaching myself. "Joan Wild" wrote: "Melinda" wrote in message ... I am getting set up accordingly, but have one slight problem. When I link the tblticketno to the tblticketdetails I get a 1:1 relationship instead of a 1:M relationship. Probably with all your troubleshooting, you had set the recTicketNo field in the tblticketdetails to be unique. That will cause Access to think it's a 1:1 relationship. I am having trouble linking the recTicketNo being that it is a autonumber in the tblTicketNo and just a number in the tblTicketDetails. Yes it should be a number, of size 'Long Integer' I was thinking last evening that possibly I want to change my PK in the tblticketno to the Invoice No. and have that relate the ticket and details table. Thanks so much for all your help. Now you're on to something. That is likely what you want to do. An Invoice No is likely unique and so is a good candidate for the PK in tblticketno. Get rid of recTicketNo in tblticketno and in tblticketdetails. Make InvoiceNo the PK in tblticketno and add InvoiceNo as a field in tblticketdetails (make sure it's of the same data type/size; establish the relationship between these two tables using that field. You would find the resources at http://home.bendbroadband.com/conrad...abaseDesign101 a worthwhile read. -- Joan Wild Microsoft Access MVP |
#19
|
|||
|
|||
Relationships
OK, you have to work with what you have, so go with the recTicketNo.
Good luck. -- Joan Wild Microsoft Access MVP "Melinda" wrote in message ... I really wanted to use Invoice No. as my PK, but our business is building and maintaining roads, Ohio Dept. of Transportation. We have an accounting office and we have 8 county garages, not mention the 17 other departments within the main complex. The invoices from the vendors are sent to our Accounting office and the 8 county garages have to account for the material being purchased, never seeing the invoices, they send the receiving tickets into the Accounting department where the invoices are matched up with the tickets ---so they do not know the invoice number at the time they are receiving the material. I have racked my brain trying to figure out a different "unique number" as opposed to the Receiving Ticket --being the PK and auto numbering the tickets for me. I totally agree with the fact that once you get the design right, the road seems a bit smoother. You have been a wonderful help, thanks so much for patiences and I will check out the website and keep plugging along. "Joan Wild" wrote: Actually Melinda, I think you should go with the InvoiceNo as the PK, not recTicketNo. The units should be in its own table. You should enter information about a unit only once. Then in your ticket details, you only need to enter (actually choose the item via a combobox in a form) the Unit ID, not the description or other information about that item. Much like you separated the TicketNo information from the ticket details. You don't want to enter the Ticket information for every ticket detail record. You just enter the InvoiceNo (assuming you change that to the PK) in the ticket details; you don't enter the rest of the Ticket info as that can be looked up in the tblTicketNo table using the InvoiceNo. I highly recommend you read the references at http://home.bendbroadband.com/conrad...abaseDesign101 There's lots of good information with examples that will help you understand. It is really worth it to get the design right; everything else will just fall into place then. -- Joan Wild Microsoft Access MVP "Melinda" wrote in message ... I went into the tabledetails and changed in the indexing, that is where my problems were. By splitting the units into its own table from the detail table, what will I gain? I am a newbie, and I am self teaching myself. "Joan Wild" wrote: "Melinda" wrote in message ... I am getting set up accordingly, but have one slight problem. When I link the tblticketno to the tblticketdetails I get a 1:1 relationship instead of a 1:M relationship. Probably with all your troubleshooting, you had set the recTicketNo field in the tblticketdetails to be unique. That will cause Access to think it's a 1:1 relationship. I am having trouble linking the recTicketNo being that it is a autonumber in the tblTicketNo and just a number in the tblTicketDetails. Yes it should be a number, of size 'Long Integer' I was thinking last evening that possibly I want to change my PK in the tblticketno to the Invoice No. and have that relate the ticket and details table. Thanks so much for all your help. Now you're on to something. That is likely what you want to do. An Invoice No is likely unique and so is a good candidate for the PK in tblticketno. Get rid of recTicketNo in tblticketno and in tblticketdetails. Make InvoiceNo the PK in tblticketno and add InvoiceNo as a field in tblticketdetails (make sure it's of the same data type/size; establish the relationship between these two tables using that field. You would find the resources at http://home.bendbroadband.com/conrad...abaseDesign101 a worthwhile read. -- Joan Wild Microsoft Access MVP |
|
Thread Tools | |
Display Modes | |
|
|