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

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

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

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

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

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

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]

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]

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]

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]