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 |
#1
|
|||
|
|||
Relationships
I am starting a new db for Budgets and I am getting off to a "rocky start".
We write purchase orders at our place of business and then each department receives goods towards the money encumbered on the puchase orders. I have 3 tables, tblvendor tblticketorder tblticket details taxid/ssn (PK) ticket number (autonumber) taxid/ssn (pk) vendorname po invoice address dvtype datereceived city budgetyr qty state unit desc unitprice comments Relationships are one tblvendor to many tblticketorder; one tblticketorder to many tblticketdetails. When I run it through the form wizard I end up with one form or possibly two. I am wanting one main form with two subforms. What am I doing wrong and is the autonumber (pk) in the tblticketorder causing the problem. Thanks Melinda |
#2
|
|||
|
|||
Relationships
Melinda
Based on your table description, one Vendor can have only one TicketDetail .... you have the same primary key for both. If the relationship should be that one Vendor can have many TicketDetail records, TicketDetail needs a primary key of its own, plus a field that serves as a foreign key (holding taxid-ssn), pointing back to Vendor. -- 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 ... I am starting a new db for Budgets and I am getting off to a "rocky start". We write purchase orders at our place of business and then each department receives goods towards the money encumbered on the puchase orders. I have 3 tables, tblvendor tblticketorder tblticket details taxid/ssn (PK) ticket number (autonumber) taxid/ssn (pk) vendorname po invoice address dvtype datereceived city budgetyr qty state unit desc unitprice comments Relationships are one tblvendor to many tblticketorder; one tblticketorder to many tblticketdetails. When I run it through the form wizard I end up with one form or possibly two. I am wanting one main form with two subforms. What am I doing wrong and is the autonumber (pk) in the tblticketorder causing the problem. Thanks Melinda |
#3
|
|||
|
|||
Relationships
I took your advice and set my tables up a bit differently. In
tblticketdetail I have the Primary Key as Invoice and the foreign key as TaxID/SSN. I really think where the problem seems to be, for me anyways, is that I want the primary key in the tblticketno to be an autonumber, so I set it up in its own table so that it would not autonumber each detail line. Our company can receive several items on the detail portion, but only provide one ticket no for the entire receiving ticket. I will give this a shot. Thanks Jeff Melinda Programmer Specialist Ohio Dept. of Transportation "Jeff Boyce" wrote: Melinda Based on your table description, one Vendor can have only one TicketDetail .... you have the same primary key for both. If the relationship should be that one Vendor can have many TicketDetail records, TicketDetail needs a primary key of its own, plus a field that serves as a foreign key (holding taxid-ssn), pointing back to Vendor. -- 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 ... I am starting a new db for Budgets and I am getting off to a "rocky start". We write purchase orders at our place of business and then each department receives goods towards the money encumbered on the puchase orders. I have 3 tables, tblvendor tblticketorder tblticket details taxid/ssn (PK) ticket number (autonumber) taxid/ssn (pk) vendorname po invoice address dvtype datereceived city budgetyr qty state unit desc unitprice comments Relationships are one tblvendor to many tblticketorder; one tblticketorder to many tblticketdetails. When I run it through the form wizard I end up with one form or possibly two. I am wanting one main form with two subforms. What am I doing wrong and is the autonumber (pk) in the tblticketorder causing the problem. Thanks Melinda |
#4
|
|||
|
|||
Relationships
I'm back--when I run the form wizard I get a main form and one subform,
combining the tblticketno and the tblticketdetail. I want the tblticketno to be a sub form by itself so that the invoice no. (PK-autonumber) does not autonumber each detail line. When they are combined into one subform it autonumbers each detail line. If you receive several items from that vendor it auto numbers each line. I have: tblvendors One to Many to the tblticketdetails tblticketno One to Many to tblvendors tblticketno One to One to tblticketdetails Thanks for being so patient. Thanks "Melinda" wrote: I took your advice and set my tables up a bit differently. In tblticketdetail I have the Primary Key as Invoice and the foreign key as TaxID/SSN. I really think where the problem seems to be, for me anyways, is that I want the primary key in the tblticketno to be an autonumber, so I set it up in its own table so that it would not autonumber each detail line. Our company can receive several items on the detail portion, but only provide one ticket no for the entire receiving ticket. I will give this a shot. Thanks Jeff Melinda Programmer Specialist Ohio Dept. of Transportation "Jeff Boyce" wrote: Melinda Based on your table description, one Vendor can have only one TicketDetail .... you have the same primary key for both. If the relationship should be that one Vendor can have many TicketDetail records, TicketDetail needs a primary key of its own, plus a field that serves as a foreign key (holding taxid-ssn), pointing back to Vendor. -- 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 ... I am starting a new db for Budgets and I am getting off to a "rocky start". We write purchase orders at our place of business and then each department receives goods towards the money encumbered on the puchase orders. I have 3 tables, tblvendor tblticketorder tblticket details taxid/ssn (PK) ticket number (autonumber) taxid/ssn (pk) vendorname po invoice address dvtype datereceived city budgetyr qty state unit desc unitprice comments Relationships are one tblvendor to many tblticketorder; one tblticketorder to many tblticketdetails. When I run it through the form wizard I end up with one form or possibly two. I am wanting one main form with two subforms. What am I doing wrong and is the autonumber (pk) in the tblticketorder causing the problem. Thanks Melinda |
#5
|
|||
|
|||
Relationships
Melinda
Are you saying that a ticket can only have one ticketdetail? It seems to me (but I'm not there, I can't see your data) that one vendor could have many tickets, and one ticket could have many ticketdetails. Can you confirm? -- 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 ... I'm back--when I run the form wizard I get a main form and one subform, combining the tblticketno and the tblticketdetail. I want the tblticketno to be a sub form by itself so that the invoice no. (PK-autonumber) does not autonumber each detail line. When they are combined into one subform it autonumbers each detail line. If you receive several items from that vendor it auto numbers each line. I have: tblvendors One to Many to the tblticketdetails tblticketno One to Many to tblvendors tblticketno One to One to tblticketdetails Thanks for being so patient. Thanks "Melinda" wrote: I took your advice and set my tables up a bit differently. In tblticketdetail I have the Primary Key as Invoice and the foreign key as TaxID/SSN. I really think where the problem seems to be, for me anyways, is that I want the primary key in the tblticketno to be an autonumber, so I set it up in its own table so that it would not autonumber each detail line. Our company can receive several items on the detail portion, but only provide one ticket no for the entire receiving ticket. I will give this a shot. Thanks Jeff Melinda Programmer Specialist Ohio Dept. of Transportation "Jeff Boyce" wrote: Melinda Based on your table description, one Vendor can have only one TicketDetail .... you have the same primary key for both. If the relationship should be that one Vendor can have many TicketDetail records, TicketDetail needs a primary key of its own, plus a field that serves as a foreign key (holding taxid-ssn), pointing back to Vendor. -- 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 ... I am starting a new db for Budgets and I am getting off to a "rocky start". We write purchase orders at our place of business and then each department receives goods towards the money encumbered on the puchase orders. I have 3 tables, tblvendor tblticketorder tblticket details taxid/ssn (PK) ticket number (autonumber) taxid/ssn (pk) vendorname po invoice address dvtype datereceived city budgetyr qty state unit desc unitprice comments Relationships are one tblvendor to many tblticketorder; one tblticketorder to many tblticketdetails. When I run it through the form wizard I end up with one form or possibly two. I am wanting one main form with two subforms. What am I doing wrong and is the autonumber (pk) in the tblticketorder causing the problem. Thanks Melinda |
#6
|
|||
|
|||
Relationships
Correct, One ticket can have many lines of ticketdetail. I worked on it for
the entire day yesterday, got a bit farther, but still having some trouble. I now have one main form with a ticket sub-form and a detail sub-form, which is what I need, now I need to tweak it. I need the tblticketno to autonumber the receiving ticket, but yet it can have as many lines of detail as needed. I now have: 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. I checked your web site out, wonderful!! Thanks for all your help Melinda "Jeff Boyce" wrote: Melinda Are you saying that a ticket can only have one ticketdetail? It seems to me (but I'm not there, I can't see your data) that one vendor could have many tickets, and one ticket could have many ticketdetails. Can you confirm? -- 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 ... I'm back--when I run the form wizard I get a main form and one subform, combining the tblticketno and the tblticketdetail. I want the tblticketno to be a sub form by itself so that the invoice no. (PK-autonumber) does not autonumber each detail line. When they are combined into one subform it autonumbers each detail line. If you receive several items from that vendor it auto numbers each line. I have: tblvendors One to Many to the tblticketdetails tblticketno One to Many to tblvendors tblticketno One to One to tblticketdetails Thanks for being so patient. Thanks "Melinda" wrote: I took your advice and set my tables up a bit differently. In tblticketdetail I have the Primary Key as Invoice and the foreign key as TaxID/SSN. I really think where the problem seems to be, for me anyways, is that I want the primary key in the tblticketno to be an autonumber, so I set it up in its own table so that it would not autonumber each detail line. Our company can receive several items on the detail portion, but only provide one ticket no for the entire receiving ticket. I will give this a shot. Thanks Jeff Melinda Programmer Specialist Ohio Dept. of Transportation "Jeff Boyce" wrote: Melinda Based on your table description, one Vendor can have only one TicketDetail .... you have the same primary key for both. If the relationship should be that one Vendor can have many TicketDetail records, TicketDetail needs a primary key of its own, plus a field that serves as a foreign key (holding taxid-ssn), pointing back to Vendor. -- 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 ... I am starting a new db for Budgets and I am getting off to a "rocky start". We write purchase orders at our place of business and then each department receives goods towards the money encumbered on the puchase orders. I have 3 tables, tblvendor tblticketorder tblticket details taxid/ssn (PK) ticket number (autonumber) taxid/ssn (pk) vendorname po invoice address dvtype datereceived city budgetyr qty state unit desc unitprice comments Relationships are one tblvendor to many tblticketorder; one tblticketorder to many tblticketdetails. When I run it through the form wizard I end up with one form or possibly two. I am wanting one main form with two subforms. What am I doing wrong and is the autonumber (pk) in the tblticketorder causing the problem. Thanks Melinda |
#7
|
|||
|
|||
Relationships
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] |
#8
|
|||
|
|||
Relationships
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] |
#9
|
|||
|
|||
Relationships
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] |
#10
|
|||
|
|||
Relationships
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] |
|
Thread Tools | |
Display Modes | |
|
|