A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Relationships



 
 
Thread Tools Display Modes
  #11  
Old January 23rd, 2007, 10:23 PM posted to microsoft.public.access.tablesdbdesign
Joan Wild
external usenet poster
 
Posts: 642
Default 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  
Old January 24th, 2007, 03:01 AM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 1,555
Default 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  
Old January 24th, 2007, 02:11 PM posted to microsoft.public.access.tablesdbdesign
Melinda
external usenet poster
 
Posts: 178
Default 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  
Old January 24th, 2007, 03:36 PM posted to microsoft.public.access.tablesdbdesign
Melinda
external usenet poster
 
Posts: 178
Default 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  
Old January 24th, 2007, 03:50 PM posted to microsoft.public.access.tablesdbdesign
Joan Wild
external usenet poster
 
Posts: 642
Default 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  
Old January 24th, 2007, 04:07 PM posted to microsoft.public.access.tablesdbdesign
Melinda
external usenet poster
 
Posts: 178
Default 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  
Old January 24th, 2007, 04:15 PM posted to microsoft.public.access.tablesdbdesign
Joan Wild
external usenet poster
 
Posts: 642
Default 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  
Old January 24th, 2007, 04:37 PM posted to microsoft.public.access.tablesdbdesign
Melinda
external usenet poster
 
Posts: 178
Default 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  
Old January 24th, 2007, 10:08 PM posted to microsoft.public.access.tablesdbdesign
Joan Wild
external usenet poster
 
Posts: 642
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:46 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.