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]