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]