1st time Access Database

I'm sorry. I guess the point I'm trying to make is I don't need to track the
customer's orders. I have a customer table populated from quickbooks
information. I need a database to be able to physically locate the artwork
associated with each customer and each product. I do not need to keep track
of any customer orders.

I need to be physically be able to locate the thousands of different
artworks by:
Customer
Product (silk screen / metelphoto / label / other)
Drawing #
Filing Cabinet
Filing Cabinet Drawer

I will need to input all the data for each artwork already inhouse.

After that process is finished, I would like to be able to query the
database by:

Customer
Product (silk screen / metelphoto / label / other)
Drawing #

And have the query return the location by Filing Cabinet & Drawer of the
artwork query.

When a new artwork is generated, I will input all the data to keep the
database up to date.

harris128



"Steve" wrote:

Now I am confused!!!

In a previous post you said, " ..... Ok, there are no customer orders ..."

and here you say customers order and reorder ??????

Steve



"harris128" wrote in message
...
Yes the order silk screening / metalphoto ID Plates / Labels, all of which
require artwork generated. But we roll the cost of the artwork into the
quote for whatever the customer is ordering, because a lot of the
jobs/orders
are repeated, so we keep the artwork inhouse so we don't incur the expense
of
regenerating new artwork each time the customer re-orders.

harris128

"Steve" wrote:

OK but how are customers associated with the artworks? Do customers buy
artworks from you?

Steve


"harris128" wrote in message
...
These artworks are already inhouse. I am trying to automate finding
the
artwork in our vast number of file cabinets. At the moment, the
artwork
data
is written down in a spiral notebook, and we have to manually flip
through
pages upon pages to try and find anything.

harris128

"Steve" wrote:

How do customers relate to artworks? Typically customers buy (order)
products.

Steve


"harris128" wrote in message
...
Ok, there are no customer orders, since these artworks already
exist.
I
just
need a way to tie the customerIDs to the may different artworks. I
guess
this would be a one to many relationship?

harris128

"Steve" wrote:

The other three tables are not related to TblCustomer! They
identify
File
Cabinets, File Cabinet Drawers and Products (Artworks). TblCustomer
is
only
related to TblOrder to identify the customer who placed each order.

Steve


"harris128" wrote in message
...
I have created the following tables per your suggestion:

TblCustomer
CustomerID
Phone
Fax

TblFileCabinet
FileCabinetID
FileCabinetIdentification

TblFileCabinetDrawer
FileCabinetDrawerID
FileCabinetID
FileCabinetDrawerNumber

TblProduct
ProductID
ProductName
Drawing Number
Revision
DashNumber,
Serilized(Yes/No)
FileCabinetDrawerID

My question what 2 fields can I use to create a relationship
between
TblCustomer and the other 3 tables?

Thanks,
harris128

"Steve" wrote:

The suggested tables a
TblState
StateID
State

TblCustomer
CustomerID
LastName
FirstName
Address
City
StateID
PostalCode
Phone
Fax
Email

TblCustomerContact
CustomerContactID
CustomerID
LastName
FirstName
Phone
Fax
Email

TblFileCabinet
FileCabinetID
FileCabinetIdentification

TblFileCabinetDrawer
FileCabinetDrawerID
FileCabinetID
FileCabinetDrawerNumber

TblProduct
ProductID
ProductName
Drawing Number
Revision
DashNumber,
Serilized(Yes/No)
FileCabinetDrawerID
Inventory

TblOrder
OrderID
CustomerID
OrderDate
PONumber

TblOrderDetail
OrderDetailID
OrderID
ProductID
Quantity
Price
DateShipped

Steve






"harris128" wrote in
message
...
I am trying to create a database to allow our employees to
locate
artwork
for
4 different product types, silkscreens, metalphoto, labels,
and
misc.

The artwork physically resides in individual folders, in
4-drawer
file
cabinets.

I have created a customer table from quickbooks import of
Customer,
Contact,
Phone, Fax, and ProductID.

I was going to imput the artwork in a Product table, with the
following
fields:
ProductID, Customer, Product Name, Drawing Number, Revision,
DashNumber,
Serilized(Yes/No), and Location.

I want the employees to be able to query the artwork by
Customer,
ProductID,
Part Number to find the location of said artwork.

The file cabinets will be labeled:
S=silkscreen -1 thru 4 (for the drawer)
M=Metalphoto
L=Label
M=Misc.

Any help would be greatly appreciated with proper
relationships,
forms
for
data entry, query form, etc.

Thanks in advance.

Here's how I interpret what you have; correct me if this is incorrect ....
You have certain products (silk screen / metelphoto / label / other). There
are multiple items of each product. These "raw" products are stored in file
drawers in multiple file cabinets. Customers purchase a product and you
convert the product into a custom artwork. The custom artwork is also stored
in file drawers in multiple file cabinets. At some point the custom artwork
is shipped to the customer. Periodically you inventory the "raw" products
and restock your inventory.

Steve




"harris128" wrote in message
...
I'm sorry. I guess the point I'm trying to make is I don't need to track
the
customer's orders. I have a customer table populated from quickbooks
information. I need a database to be able to physically locate the
artwork
associated with each customer and each product. I do not need to keep
track
of any customer orders.

I need to be physically be able to locate the thousands of different
artworks by:
Customer
Product (silk screen / metelphoto / label / other)
Drawing #
Filing Cabinet
Filing Cabinet Drawer

I will need to input all the data for each artwork already inhouse.

After that process is finished, I would like to be able to query the
database by:

Customer
Product (silk screen / metelphoto / label / other)
Drawing #

And have the query return the location by Filing Cabinet & Drawer of the
artwork query.

When a new artwork is generated, I will input all the data to keep the
database up to date.

harris128



"Steve" wrote:

Now I am confused!!!

In a previous post you said, " ..... Ok, there are no customer orders
..."

and here you say customers order and reorder ??????

Steve



"harris128" wrote in message
...
Yes the order silk screening / metalphoto ID Plates / Labels, all of
which
require artwork generated. But we roll the cost of the artwork into
the
quote for whatever the customer is ordering, because a lot of the
jobs/orders
are repeated, so we keep the artwork inhouse so we don't incur the
expense
of
regenerating new artwork each time the customer re-orders.

harris128

"Steve" wrote:

OK but how are customers associated with the artworks? Do customers
buy
artworks from you?

Steve


"harris128" wrote in message
...
These artworks are already inhouse. I am trying to automate finding
the
artwork in our vast number of file cabinets. At the moment, the
artwork
data
is written down in a spiral notebook, and we have to manually flip
through
pages upon pages to try and find anything.

harris128

"Steve" wrote:

How do customers relate to artworks? Typically customers buy
(order)
products.

Steve


"harris128" wrote in message
...
Ok, there are no customer orders, since these artworks already
exist.
I
just
need a way to tie the customerIDs to the may different artworks.
I
guess
this would be a one to many relationship?

harris128

"Steve" wrote:

The other three tables are not related to TblCustomer! They
identify
File
Cabinets, File Cabinet Drawers and Products (Artworks).
TblCustomer
is
only
related to TblOrder to identify the customer who placed each
order.

Steve


"harris128" wrote in
message
...
I have created the following tables per your suggestion:

TblCustomer
CustomerID
Phone
Fax

TblFileCabinet
FileCabinetID
FileCabinetIdentification

TblFileCabinetDrawer
FileCabinetDrawerID
FileCabinetID
FileCabinetDrawerNumber

TblProduct
ProductID
ProductName
Drawing Number
Revision
DashNumber,
Serilized(Yes/No)
FileCabinetDrawerID

My question what 2 fields can I use to create a relationship
between
TblCustomer and the other 3 tables?

Thanks,
harris128

"Steve" wrote:

The suggested tables a
TblState
StateID
State

TblCustomer
CustomerID
LastName
FirstName
Address
City
StateID
PostalCode
Phone
Fax
Email

TblCustomerContact
CustomerContactID
CustomerID
LastName
FirstName
Phone
Fax
Email

TblFileCabinet
FileCabinetID
FileCabinetIdentification

TblFileCabinetDrawer
FileCabinetDrawerID
FileCabinetID
FileCabinetDrawerNumber

TblProduct
ProductID
ProductName
Drawing Number
Revision
DashNumber,
Serilized(Yes/No)
FileCabinetDrawerID
Inventory

TblOrder
OrderID
CustomerID
OrderDate
PONumber

TblOrderDetail
OrderDetailID
OrderID
ProductID
Quantity
Price
DateShipped

Steve






"harris128" wrote in
message
...
I am trying to create a database to allow our employees to
locate
artwork
for
4 different product types, silkscreens, metalphoto, labels,
and
misc.

The artwork physically resides in individual folders, in
4-drawer
file
cabinets.

I have created a customer table from quickbooks import of
Customer,
Contact,
Phone, Fax, and ProductID.

I was going to imput the artwork in a Product table, with
the
following
fields:
ProductID, Customer, Product Name, Drawing Number,
Revision,
DashNumber,
Serilized(Yes/No), and Location.

I want the employees to be able to query the artwork by
Customer,
ProductID,
Part Number to find the location of said artwork.

The file cabinets will be labeled:
S=silkscreen -1 thru 4 (for the drawer)
M=Metalphoto
L=Label
M=Misc.

Any help would be greatly appreciated with proper
relationships,
forms
for
data entry, query form, etc.

Thanks in advance.

Steve:

The "raw" products consist of artwork we created for the customer to make
the (silk screen / metelphoto / label / other).

The custom artwork is not shipped to the customer, but is used inhouse to
create their (silk screen / metelphoto / label / other) product. The artwork
stays inhouse in our file cabinets.

Think of the artwork as a mold we make to create an end-product the customer
orders. The customer does not need the mold, but we use it to create the
customer's end-product. Hence the mold (or artwork) stays inhouse and filed
incase the customer orders more of the end-product. We then locate the mold
(or artwork) and create more product for the customer.

And Steve, I want to thank you for being so patient with a database noob.

harris128

"Steve" wrote:

Here's how I interpret what you have; correct me if this is incorrect ....
You have certain products (silk screen / metelphoto / label / other). There
are multiple items of each product. These "raw" products are stored in file
drawers in multiple file cabinets. Customers purchase a product and you
convert the product into a custom artwork. The custom artwork is also stored
in file drawers in multiple file cabinets. At some point the custom artwork
is shipped to the customer. Periodically you inventory the "raw" products
and restock your inventory.

Steve




"harris128" wrote in message
...
I'm sorry. I guess the point I'm trying to make is I don't need to track
the
customer's orders. I have a customer table populated from quickbooks
information. I need a database to be able to physically locate the
artwork
associated with each customer and each product. I do not need to keep
track
of any customer orders.

I need to be physically be able to locate the thousands of different
artworks by:
Customer
Product (silk screen / metelphoto / label / other)
Drawing #
Filing Cabinet
Filing Cabinet Drawer

I will need to input all the data for each artwork already inhouse.

After that process is finished, I would like to be able to query the
database by:

Customer
Product (silk screen / metelphoto / label / other)
Drawing #

And have the query return the location by Filing Cabinet & Drawer of the
artwork query.

When a new artwork is generated, I will input all the data to keep the
database up to date.

harris128



"Steve" wrote:

Now I am confused!!!

In a previous post you said, " ..... Ok, there are no customer orders
..."

and here you say customers order and reorder ??????

Steve



"harris128" wrote in message
...
Yes the order silk screening / metalphoto ID Plates / Labels, all of
which
require artwork generated. But we roll the cost of the artwork into
the
quote for whatever the customer is ordering, because a lot of the
jobs/orders
are repeated, so we keep the artwork inhouse so we don't incur the
expense
of
regenerating new artwork each time the customer re-orders.

harris128

"Steve" wrote:

OK but how are customers associated with the artworks? Do customers
buy
artworks from you?

Steve


"harris128" wrote in message
...
These artworks are already inhouse. I am trying to automate finding
the
artwork in our vast number of file cabinets. At the moment, the
artwork
data
is written down in a spiral notebook, and we have to manually flip
through
pages upon pages to try and find anything.

harris128

"Steve" wrote:

How do customers relate to artworks? Typically customers buy
(order)
products.

Steve


"harris128" wrote in message
...
Ok, there are no customer orders, since these artworks already
exist.
I
just
need a way to tie the customerIDs to the may different artworks.
I
guess
this would be a one to many relationship?

harris128

"Steve" wrote:

The other three tables are not related to TblCustomer! They
identify
File
Cabinets, File Cabinet Drawers and Products (Artworks).
TblCustomer
is
only
related to TblOrder to identify the customer who placed each
order.

Steve


"harris128" wrote in
message
...
I have created the following tables per your suggestion:

TblCustomer
CustomerID
Phone
Fax

TblFileCabinet
FileCabinetID
FileCabinetIdentification

TblFileCabinetDrawer
FileCabinetDrawerID
FileCabinetID
FileCabinetDrawerNumber

TblProduct
ProductID
ProductName
Drawing Number
Revision
DashNumber,
Serilized(Yes/No)
FileCabinetDrawerID

My question what 2 fields can I use to create a relationship
between
TblCustomer and the other 3 tables?

Thanks,
harris128

"Steve" wrote:

The suggested tables a
TblState
StateID
State

TblCustomer
CustomerID
LastName
FirstName
Address
City
StateID
PostalCode
Phone
Fax
Email

TblCustomerContact
CustomerContactID
CustomerID
LastName
FirstName
Phone
Fax
Email

TblFileCabinet
FileCabinetID
FileCabinetIdentification

TblFileCabinetDrawer
FileCabinetDrawerID
FileCabinetID
FileCabinetDrawerNumber

TblProduct
ProductID
ProductName
Drawing Number
Revision
DashNumber,
Serilized(Yes/No)
FileCabinetDrawerID
Inventory

TblOrder
OrderID
CustomerID
OrderDate
PONumber

TblOrderDetail
OrderDetailID
OrderID
ProductID
Quantity
Price
DateShipped

Steve






"harris128" wrote in
message
...
I am trying to create a database to allow our employees to
locate
artwork
for
4 different product types, silkscreens, metalphoto, labels,
and
misc.

The artwork physically resides in individual folders, in
4-drawer
file
cabinets.

I have created a customer table from quickbooks import of
Customer,
Contact,
Phone, Fax, and ProductID.

I was going to imput the artwork in a Product table, with
the
following
fields:
ProductID, Customer, Product Name, Drawing Number,
Revision,
DashNumber,
Serilized(Yes/No), and Location.

I want the employees to be able to query the artwork by
Customer,
ProductID,
Part Number to find the location of said artwork.

The file cabinets will be labeled:
S=silkscreen -1 thru 4 (for the drawer)
M=Metalphoto
L=Label
M=Misc.

Any help would be greatly appreciated with proper
relationships,
forms
for
data entry, query form, etc.

Thanks in advance.

The suggested revised tables a
TblState
StateID
State

TblCustomer
CustomerID
LastName
FirstName
Address
City
StateID
PostalCode
Phone
Fax
Email

TblCustomerContact *** Optional
CustomerContactID
CustomerID
LastName
FirstName
Phone
Fax
Email

TblFileCabinet
FileCabinetID
FileCabinetIdentification

TblFileCabinetDrawer
FileCabinetDrawerID
FileCabinetID
FileCabinetDrawerNumber

TblProduct
ProductID
ProductName (silk screen / metelphoto / label / other)

TblCustomerEndProduct
CustomerEndProductID
CustomerID
ProductID
DrawingNumber
Revision
DashNumber,
Serilized(Yes/No)
FileCabinetDrawerID

Your query would include all the above tables except TblContact. The query
only needs the fields where you can set criteria and other fields you want
to display in your search results. With proper criteria you could find the
file cabinet and drawer for any piece of artwork.

Is this what you want?

Steve



"harris128" wrote in message
news
Steve:

The "raw" products consist of artwork we created for the customer to make
the (silk screen / metelphoto / label / other).

The custom artwork is not shipped to the customer, but is used inhouse to
create their (silk screen / metelphoto / label / other) product. The
artwork
stays inhouse in our file cabinets.

Think of the artwork as a mold we make to create an end-product the
customer
orders. The customer does not need the mold, but we use it to create the
customer's end-product. Hence the mold (or artwork) stays inhouse and
filed
incase the customer orders more of the end-product. We then locate the
mold
(or artwork) and create more product for the customer.

And Steve, I want to thank you for being so patient with a database noob.

harris128

"Steve" wrote:

Here's how I interpret what you have; correct me if this is incorrect
....
You have certain products (silk screen / metelphoto / label / other).
There
are multiple items of each product. These "raw" products are stored in
file
drawers in multiple file cabinets. Customers purchase a product and you
convert the product into a custom artwork. The custom artwork is also
stored
in file drawers in multiple file cabinets. At some point the custom
artwork
is shipped to the customer. Periodically you inventory the "raw" products
and restock your inventory.

Steve




"harris128" wrote in message
...
I'm sorry. I guess the point I'm trying to make is I don't need to
track
the
customer's orders. I have a customer table populated from quickbooks
information. I need a database to be able to physically locate the
artwork
associated with each customer and each product. I do not need to keep
track
of any customer orders.

I need to be physically be able to locate the thousands of different
artworks by:
Customer
Product (silk screen / metelphoto / label / other)
Drawing #
Filing Cabinet
Filing Cabinet Drawer

I will need to input all the data for each artwork already inhouse.

After that process is finished, I would like to be able to query the
database by:

Customer
Product (silk screen / metelphoto / label / other)
Drawing #

And have the query return the location by Filing Cabinet & Drawer of
the
artwork query.

When a new artwork is generated, I will input all the data to keep the
database up to date.

harris128



"Steve" wrote:

Now I am confused!!!

In a previous post you said, " ..... Ok, there are no customer orders
..."

and here you say customers order and reorder ??????

Steve



"harris128" wrote in message
...
Yes the order silk screening / metalphoto ID Plates / Labels, all of
which
require artwork generated. But we roll the cost of the artwork into
the
quote for whatever the customer is ordering, because a lot of the
jobs/orders
are repeated, so we keep the artwork inhouse so we don't incur the
expense
of
regenerating new artwork each time the customer re-orders.

harris128

"Steve" wrote:

OK but how are customers associated with the artworks? Do customers
buy
artworks from you?

Steve


"harris128" wrote in message
...
These artworks are already inhouse. I am trying to automate
finding
the
artwork in our vast number of file cabinets. At the moment, the
artwork
data
is written down in a spiral notebook, and we have to manually
flip
through
pages upon pages to try and find anything.

harris128

"Steve" wrote:

How do customers relate to artworks? Typically customers buy
(order)
products.

Steve


"harris128" wrote in
message
...
Ok, there are no customer orders, since these artworks already
exist.
I
just
need a way to tie the customerIDs to the may different
artworks.
I
guess
this would be a one to many relationship?

harris128

"Steve" wrote:

The other three tables are not related to TblCustomer! They
identify
File
Cabinets, File Cabinet Drawers and Products (Artworks).
TblCustomer
is
only
related to TblOrder to identify the customer who placed each
order.

Steve


"harris128" wrote in
message
...
I have created the following tables per your suggestion:

TblCustomer
CustomerID
Phone
Fax

TblFileCabinet
FileCabinetID
FileCabinetIdentification

TblFileCabinetDrawer
FileCabinetDrawerID
FileCabinetID
FileCabinetDrawerNumber

TblProduct
ProductID
ProductName
Drawing Number
Revision
DashNumber,
Serilized(Yes/No)
FileCabinetDrawerID

My question what 2 fields can I use to create a
relationship
between
TblCustomer and the other 3 tables?

Thanks,
harris128

"Steve" wrote:

The suggested tables a
TblState
StateID
State

TblCustomer
CustomerID
LastName
FirstName
Address
City
StateID
PostalCode
Phone
Fax
Email

TblCustomerContact
CustomerContactID
CustomerID
LastName
FirstName
Phone
Fax
Email

TblFileCabinet
FileCabinetID
FileCabinetIdentification

TblFileCabinetDrawer
FileCabinetDrawerID
FileCabinetID
FileCabinetDrawerNumber

TblProduct
ProductID
ProductName
Drawing Number
Revision
DashNumber,
Serilized(Yes/No)
FileCabinetDrawerID
Inventory

TblOrder
OrderID
CustomerID
OrderDate
PONumber

TblOrderDetail
OrderDetailID
OrderID
ProductID
Quantity
Price
DateShipped

Steve






"harris128" wrote in
message
...
I am trying to create a database to allow our employees
to
locate
artwork
for
4 different product types, silkscreens, metalphoto,
labels,
and
misc.

The artwork physically resides in individual folders, in
4-drawer
file
cabinets.

I have created a customer table from quickbooks import
of
Customer,
Contact,
Phone, Fax, and ProductID.

I was going to imput the artwork in a Product table,
with
the
following
fields:
ProductID, Customer, Product Name, Drawing Number,
Revision,
DashNumber,
Serilized(Yes/No), and Location.

I want the employees to be able to query the artwork by
Customer,
ProductID,
Part Number to find the location of said artwork.

The file cabinets will be labeled:
S=silkscreen -1 thru 4 (for the drawer)
M=Metalphoto
L=Label
M=Misc.

Any help would be greatly appreciated with proper
relationships,
forms
for
data entry, query form, etc.

Thanks in advance.

I understand what you're saying, and it shouldn't be very complicated to set
up, but there is one imortant question that I don't think has been addressed
yet (maybe it was and I missed it). Can an individual piece of artwork ever
be used to produce a product (silk screen, etc.) for more than one
customer, or is each one specific to only one customer? The answer to
this question is key to the proper table design.
--
_________

Sean Bailey


"harris128" wrote:

Steve:

The "raw" products consist of artwork we created for the customer to make
the (silk screen / metelphoto / label / other).

The custom artwork is not shipped to the customer, but is used inhouse to
create their (silk screen / metelphoto / label / other) product. The artwork
stays inhouse in our file cabinets.

Think of the artwork as a mold we make to create an end-product the customer
orders. The customer does not need the mold, but we use it to create the
customer's end-product. Hence the mold (or artwork) stays inhouse and filed
incase the customer orders more of the end-product. We then locate the mold
(or artwork) and create more product for the customer.

And Steve, I want to thank you for being so patient with a database noob.

harris128

"Steve" wrote:

Here's how I interpret what you have; correct me if this is incorrect ....
You have certain products (silk screen / metelphoto / label / other). There
are multiple items of each product. These "raw" products are stored in file
drawers in multiple file cabinets. Customers purchase a product and you
convert the product into a custom artwork. The custom artwork is also stored
in file drawers in multiple file cabinets. At some point the custom artwork
is shipped to the customer. Periodically you inventory the "raw" products
and restock your inventory.

Steve




"harris128" wrote in message
...
I'm sorry. I guess the point I'm trying to make is I don't need to track
the
customer's orders. I have a customer table populated from quickbooks
information. I need a database to be able to physically locate the
artwork
associated with each customer and each product. I do not need to keep
track
of any customer orders.

I need to be physically be able to locate the thousands of different
artworks by:
Customer
Product (silk screen / metelphoto / label / other)
Drawing #
Filing Cabinet
Filing Cabinet Drawer

I will need to input all the data for each artwork already inhouse.

After that process is finished, I would like to be able to query the
database by:

Customer
Product (silk screen / metelphoto / label / other)
Drawing #

And have the query return the location by Filing Cabinet & Drawer of the
artwork query.

When a new artwork is generated, I will input all the data to keep the
database up to date.

harris128



"Steve" wrote:

Now I am confused!!!

In a previous post you said, " ..... Ok, there are no customer orders
..."

and here you say customers order and reorder ??????

Steve



"harris128" wrote in message
...
Yes the order silk screening / metalphoto ID Plates / Labels, all of
which
require artwork generated. But we roll the cost of the artwork into
the
quote for whatever the customer is ordering, because a lot of the
jobs/orders
are repeated, so we keep the artwork inhouse so we don't incur the
expense
of
regenerating new artwork each time the customer re-orders.

harris128

"Steve" wrote:

OK but how are customers associated with the artworks? Do customers
buy
artworks from you?

Steve


"harris128" wrote in message
...
These artworks are already inhouse. I am trying to automate finding
the
artwork in our vast number of file cabinets. At the moment, the
artwork
data
is written down in a spiral notebook, and we have to manually flip
through
pages upon pages to try and find anything.

harris128

"Steve" wrote:

How do customers relate to artworks? Typically customers buy
(order)
products.

Steve


"harris128" wrote in message
...
Ok, there are no customer orders, since these artworks already
exist.
I
just
need a way to tie the customerIDs to the may different artworks.
I
guess
this would be a one to many relationship?

harris128

"Steve" wrote:

The other three tables are not related to TblCustomer! They
identify
File
Cabinets, File Cabinet Drawers and Products (Artworks).
TblCustomer
is
only
related to TblOrder to identify the customer who placed each
order.

Steve


"harris128" wrote in
message
...
I have created the following tables per your suggestion:

TblCustomer
CustomerID
Phone
Fax

TblFileCabinet
FileCabinetID
FileCabinetIdentification

TblFileCabinetDrawer
FileCabinetDrawerID
FileCabinetID
FileCabinetDrawerNumber

TblProduct
ProductID
ProductName
Drawing Number
Revision
DashNumber,
Serilized(Yes/No)
FileCabinetDrawerID

My question what 2 fields can I use to create a relationship
between
TblCustomer and the other 3 tables?

Thanks,
harris128

"Steve" wrote:

The suggested tables a
TblState
StateID
State

TblCustomer
CustomerID
LastName
FirstName
Address
City
StateID
PostalCode
Phone
Fax
Email

TblCustomerContact
CustomerContactID
CustomerID
LastName
FirstName
Phone
Fax
Email

TblFileCabinet
FileCabinetID
FileCabinetIdentification

TblFileCabinetDrawer
FileCabinetDrawerID
FileCabinetID
FileCabinetDrawerNumber

TblProduct
ProductID
ProductName
Drawing Number
Revision
DashNumber,
Serilized(Yes/No)
FileCabinetDrawerID
Inventory

TblOrder
OrderID
CustomerID
OrderDate
PONumber

TblOrderDetail
OrderDetailID
OrderID
ProductID
Quantity
Price
DateShipped

Steve






"harris128" wrote in
message
...
I am trying to create a database to allow our employees to
locate
artwork
for
4 different product types, silkscreens, metalphoto, labels,
and
misc.

The artwork physically resides in individual folders, in
4-drawer
file
cabinets.

I have created a customer table from quickbooks import of
Customer,
Contact,
Phone, Fax, and ProductID.

I was going to imput the artwork in a Product table, with
the
following
fields:
ProductID, Customer, Product Name, Drawing Number,
Revision,
DashNumber,
Serilized(Yes/No), and Location.

I want the employees to be able to query the artwork by
Customer,
ProductID,
Part Number to find the location of said artwork.

The file cabinets will be labeled:
S=silkscreen -1 thru 4 (for the drawer)

To answer Sean's question, let me explain. Many our our customers are
government contractors who are bidding on the same government projects, so it
is possible to have the same artwork for more than one customer. What has
been done in the manual system is the employee would write down in the spiral
notebook, for customer ABC, drawing 1234 (see customer XYZ).

It even becomes more complicated in the fact that one customer will request
drawing 1234 Rev A, whereas the next customer will request drawing 1234, Rev
C.

I would prefer to keep the individual drawing, with the customer that has
requested it. If a different customer requests the same drawing, I would
want to keep that drawing with that customer.

harris128

"Beetle" wrote:

I understand what you're saying, and it shouldn't be very complicated to set
up, but there is one imortant question that I don't think has been addressed
yet (maybe it was and I missed it). Can an individual piece of artwork ever
be used to produce a product (silk screen, etc.) for more than one
customer, or is each one specific to only one customer? The answer to
this question is key to the proper table design.
--
_________

Sean Bailey


"harris128" wrote:

Steve:

The "raw" products consist of artwork we created for the customer to make
the (silk screen / metelphoto / label / other).

The custom artwork is not shipped to the customer, but is used inhouse to
create their (silk screen / metelphoto / label / other) product. The artwork
stays inhouse in our file cabinets.

Think of the artwork as a mold we make to create an end-product the customer
orders. The customer does not need the mold, but we use it to create the
customer's end-product. Hence the mold (or artwork) stays inhouse and filed
incase the customer orders more of the end-product. We then locate the mold
(or artwork) and create more product for the customer.

And Steve, I want to thank you for being so patient with a database noob.

harris128

"Steve" wrote:

Here's how I interpret what you have; correct me if this is incorrect ....
You have certain products (silk screen / metelphoto / label / other). There
are multiple items of each product. These "raw" products are stored in file
drawers in multiple file cabinets. Customers purchase a product and you
convert the product into a custom artwork. The custom artwork is also stored
in file drawers in multiple file cabinets. At some point the custom artwork
is shipped to the customer. Periodically you inventory the "raw" products
and restock your inventory.

Steve




"harris128" wrote in message
...
I'm sorry. I guess the point I'm trying to make is I don't need to track
the
customer's orders. I have a customer table populated from quickbooks
information. I need a database to be able to physically locate the
artwork
associated with each customer and each product. I do not need to keep
track
of any customer orders.

I need to be physically be able to locate the thousands of different
artworks by:
Customer
Product (silk screen / metelphoto / label / other)
Drawing #
Filing Cabinet
Filing Cabinet Drawer

I will need to input all the data for each artwork already inhouse.

After that process is finished, I would like to be able to query the
database by:

Customer
Product (silk screen / metelphoto / label / other)
Drawing #

And have the query return the location by Filing Cabinet & Drawer of the
artwork query.

When a new artwork is generated, I will input all the data to keep the
database up to date.

harris128



"Steve" wrote:

Now I am confused!!!

In a previous post you said, " ..... Ok, there are no customer orders
..."

and here you say customers order and reorder ??????

Steve



"harris128" wrote in message
...
Yes the order silk screening / metalphoto ID Plates / Labels, all of
which
require artwork generated. But we roll the cost of the artwork into
the
quote for whatever the customer is ordering, because a lot of the
jobs/orders
are repeated, so we keep the artwork inhouse so we don't incur the
expense
of
regenerating new artwork each time the customer re-orders.

harris128

"Steve" wrote:

OK but how are customers associated with the artworks? Do customers
buy
artworks from you?

Steve


"harris128" wrote in message
...
These artworks are already inhouse. I am trying to automate finding
the
artwork in our vast number of file cabinets. At the moment, the
artwork
data
is written down in a spiral notebook, and we have to manually flip
through
pages upon pages to try and find anything.

harris128

"Steve" wrote:

How do customers relate to artworks? Typically customers buy
(order)
products.

Steve


"harris128" wrote in message
...
Ok, there are no customer orders, since these artworks already
exist.
I
just
need a way to tie the customerIDs to the may different artworks.
I
guess
this would be a one to many relationship?

harris128

"Steve" wrote:

The other three tables are not related to TblCustomer! They
identify
File
Cabinets, File Cabinet Drawers and Products (Artworks).
TblCustomer
is
only
related to TblOrder to identify the customer who placed each
order.

Steve


"harris128" wrote in
message
...
I have created the following tables per your suggestion:

TblCustomer
CustomerID
Phone
Fax

TblFileCabinet
FileCabinetID
FileCabinetIdentification

TblFileCabinetDrawer
FileCabinetDrawerID
FileCabinetID
FileCabinetDrawerNumber

TblProduct
ProductID
ProductName
Drawing Number
Revision
DashNumber,
Serilized(Yes/No)
FileCabinetDrawerID

My question what 2 fields can I use to create a relationship
between
TblCustomer and the other 3 tables?

Thanks,
harris128

"Steve" wrote:

The suggested tables a
TblState
StateID
State

TblCustomer
CustomerID
LastName
FirstName
Address
City
StateID
PostalCode
Phone
Fax
Email

TblCustomerContact
CustomerContactID
CustomerID
LastName
FirstName
Phone
Fax
Email

TblFileCabinet
FileCabinetID
FileCabinetIdentification

TblFileCabinetDrawer
FileCabinetDrawerID
FileCabinetID
FileCabinetDrawerNumber

TblProduct
ProductID
ProductName
Drawing Number
Revision
DashNumber,
Serilized(Yes/No)
FileCabinetDrawerID
Inventory

TblOrder
OrderID
CustomerID
OrderDate
PONumber

TblOrderDetail
OrderDetailID
OrderID
ProductID
Quantity
Price
DateShipped

Steve






"harris128" wrote in
message
...
I am trying to create a database to allow our employees to
locate
artwork
for
4 different product types, silkscreens, metalphoto, labels,
and
misc.

The artwork physically resides in individual folders, in
4-drawer
file
cabinets.

I have created a customer table from quickbooks import of
Customer,
Contact,
Phone, Fax, and ProductID.

I was going to imput the artwork in a Product table, with
the

I'm not completely clear on how you handle different revisions of
the same drawing, but for the purposes of this post, I'm going to
assume that Drawing 1234-RevA could, in theory, be kept in a
different drawer than Drawing 1234-RevC, and therefore they should
be treated as two separate drawings, even though they are based on
the same "Parent" drawing. So, essentially you have a many-to-many
relationship between the customers and each individual revision of
a drawing. Based on that assumption, an appropriate table structure
might look like;

Note: PK = Primary Key, FK = Foreign Key

tblCustomers
**********
CustomerID (PK)
LastName
FirstName
other fields related specifically to the customer

tblDrawings
*********
DrawingID (PK)
Description
other fields related to each "Parent" drawing

tblFileCabinets
***********
FileCabinetID (PK)
FileCabinetDescription
Location

tblDrawers
********
DrawerID (PK)
Description
FileCabinetID (FK to tblFileCabinets)

tblRevisions
*********
RevisionID (PK)
RevisionNumber
DrawingID (FK to tblDrawings)
DrawerID (FK to tblDrawers)

tblCustomerDrawings
****************
CustomerID (FK to tblCustomers)
RevisionID (FK to tblRevisions)
DateRequested
(in this table, you could use the above three fields as a combined PK, or
you could add your own surrofate PK like CustomerDrawingID)

If my assumption about the revisions is wrong, post back and we can give
it another go.

--
_________

Sean Bailey


"harris128" wrote:

To answer Sean's question, let me explain. Many our our customers are
government contractors who are bidding on the same government projects, so it
is possible to have the same artwork for more than one customer. What has
been done in the manual system is the employee would write down in the spiral
notebook, for customer ABC, drawing 1234 (see customer XYZ).

It even becomes more complicated in the fact that one customer will request
drawing 1234 Rev A, whereas the next customer will request drawing 1234, Rev
C.

I would prefer to keep the individual drawing, with the customer that has
requested it. If a different customer requests the same drawing, I would
want to keep that drawing with that customer.

harris128

"Beetle" wrote:

I understand what you're saying, and it shouldn't be very complicated to set
up, but there is one imortant question that I don't think has been addressed
yet (maybe it was and I missed it). Can an individual piece of artwork ever
be used to produce a product (silk screen, etc.) for more than one
customer, or is each one specific to only one customer? The answer to
this question is key to the proper table design.
--
_________

Sean Bailey


"harris128" wrote:

Steve:

The "raw" products consist of artwork we created for the customer to make
the (silk screen / metelphoto / label / other).

The custom artwork is not shipped to the customer, but is used inhouse to
create their (silk screen / metelphoto / label / other) product. The artwork
stays inhouse in our file cabinets.

Think of the artwork as a mold we make to create an end-product the customer
orders. The customer does not need the mold, but we use it to create the
customer's end-product. Hence the mold (or artwork) stays inhouse and filed
incase the customer orders more of the end-product. We then locate the mold
(or artwork) and create more product for the customer.

And Steve, I want to thank you for being so patient with a database noob.

harris128

"Steve" wrote:

Here's how I interpret what you have; correct me if this is incorrect ....
You have certain products (silk screen / metelphoto / label / other). There
are multiple items of each product. These "raw" products are stored in file
drawers in multiple file cabinets. Customers purchase a product and you
convert the product into a custom artwork. The custom artwork is also stored
in file drawers in multiple file cabinets. At some point the custom artwork
is shipped to the customer. Periodically you inventory the "raw" products
and restock your inventory.

Steve




"harris128" wrote in message
...
I'm sorry. I guess the point I'm trying to make is I don't need to track
the
customer's orders. I have a customer table populated from quickbooks
information. I need a database to be able to physically locate the
artwork
associated with each customer and each product. I do not need to keep
track
of any customer orders.

I need to be physically be able to locate the thousands of different
artworks by:
Customer
Product (silk screen / metelphoto / label / other)
Drawing #
Filing Cabinet
Filing Cabinet Drawer

I will need to input all the data for each artwork already inhouse.

After that process is finished, I would like to be able to query the
database by:

Customer
Product (silk screen / metelphoto / label / other)
Drawing #

And have the query return the location by Filing Cabinet & Drawer of the
artwork query.

When a new artwork is generated, I will input all the data to keep the
database up to date.

harris128



"Steve" wrote:

Now I am confused!!!

In a previous post you said, " ..... Ok, there are no customer orders
..."

and here you say customers order and reorder ??????

Steve



"harris128" wrote in message
...
Yes the order silk screening / metalphoto ID Plates / Labels, all of
which
require artwork generated. But we roll the cost of the artwork into
the
quote for whatever the customer is ordering, because a lot of the
jobs/orders
are repeated, so we keep the artwork inhouse so we don't incur the
expense
of
regenerating new artwork each time the customer re-orders.

harris128

"Steve" wrote:

OK but how are customers associated with the artworks? Do customers
buy
artworks from you?

Steve


"harris128" wrote in message
...
These artworks are already inhouse. I am trying to automate finding
the
artwork in our vast number of file cabinets. At the moment, the
artwork
data
is written down in a spiral notebook, and we have to manually flip
through
pages upon pages to try and find anything.

harris128

"Steve" wrote:

How do customers relate to artworks? Typically customers buy
(order)
products.

Steve


"harris128" wrote in message
...
Ok, there are no customer orders, since these artworks already
exist.
I
just
need a way to tie the customerIDs to the may different artworks.
I
guess
this would be a one to many relationship?

harris128

"Steve" wrote:

The other three tables are not related to TblCustomer! They
identify
File
Cabinets, File Cabinet Drawers and Products (Artworks).
TblCustomer
is
only
related to TblOrder to identify the customer who placed each
order.

Steve


"harris128" wrote in
message
...
I have created the following tables per your suggestion:

TblCustomer
CustomerID
Phone
Fax

TblFileCabinet
FileCabinetID
FileCabinetIdentification

TblFileCabinetDrawer
FileCabinetDrawerID
FileCabinetID
FileCabinetDrawerNumber

TblProduct
ProductID
ProductName
Drawing Number
Revision
DashNumber,
Serilized(Yes/No)
FileCabinetDrawerID

My question what 2 fields can I use to create a relationship
between
TblCustomer and the other 3 tables?

Thanks,
harris128

"Steve" wrote:

The suggested tables a
TblState
StateID
State

TblCustomer
CustomerID
LastName
FirstName
Address
City
StateID
PostalCode
Phone
Fax
Email

TblCustomerContact
CustomerContactID
CustomerID
LastName
FirstName
Phone
Fax
Email

TblFileCabinet
FileCabinetID
FileCabinetIdentification

TblFileCabinetDrawer
FileCabinetDrawerID
FileCabinetID
FileCabinetDrawerNumber

TblProduct
ProductID
ProductName
Drawing Number
Revision
DashNumber,
Serilized(Yes/No)
FileCabinetDrawerID
Inventory

TblOrder
OrderID
CustomerID
OrderDate
PONumber

TblOrderDetail
OrderDetailID
OrderID
ProductID
Quantity
Price
DateShipped

Steve






"harris128" wrote in
message
...
I am trying to create a database to allow our employees to
locate

Ok, does the PK relate to the FK by having the same field name? Hence, this
is how the "relational" part of relarionship database works?

harris128

"Beetle" wrote:

I'm not completely clear on how you handle different revisions of
the same drawing, but for the purposes of this post, I'm going to
assume that Drawing 1234-RevA could, in theory, be kept in a
different drawer than Drawing 1234-RevC, and therefore they should
be treated as two separate drawings, even though they are based on
the same "Parent" drawing. So, essentially you have a many-to-many
relationship between the customers and each individual revision of
a drawing. Based on that assumption, an appropriate table structure
might look like;

Note: PK = Primary Key, FK = Foreign Key

tblCustomers
**********
CustomerID (PK)
LastName
FirstName
other fields related specifically to the customer

tblDrawings
*********
DrawingID (PK)
Description
other fields related to each "Parent" drawing

tblFileCabinets
***********
FileCabinetID (PK)
FileCabinetDescription
Location

tblDrawers
********
DrawerID (PK)
Description
FileCabinetID (FK to tblFileCabinets)

tblRevisions
*********
RevisionID (PK)
RevisionNumber
DrawingID (FK to tblDrawings)
DrawerID (FK to tblDrawers)

tblCustomerDrawings
****************
CustomerID (FK to tblCustomers)
RevisionID (FK to tblRevisions)
DateRequested
(in this table, you could use the above three fields as a combined PK, or
you could add your own surrofate PK like CustomerDrawingID)

If my assumption about the revisions is wrong, post back and we can give
it another go.

--
_________

Sean Bailey


"harris128" wrote:

To answer Sean's question, let me explain. Many our our customers are
government contractors who are bidding on the same government projects, so it
is possible to have the same artwork for more than one customer. What has
been done in the manual system is the employee would write down in the spiral
notebook, for customer ABC, drawing 1234 (see customer XYZ).

It even becomes more complicated in the fact that one customer will request
drawing 1234 Rev A, whereas the next customer will request drawing 1234, Rev
C.

I would prefer to keep the individual drawing, with the customer that has
requested it. If a different customer requests the same drawing, I would
want to keep that drawing with that customer.

harris128

"Beetle" wrote:

I understand what you're saying, and it shouldn't be very complicated to set
up, but there is one imortant question that I don't think has been addressed
yet (maybe it was and I missed it). Can an individual piece of artwork ever
be used to produce a product (silk screen, etc.) for more than one
customer, or is each one specific to only one customer? The answer to
this question is key to the proper table design.
--
_________

Sean Bailey


"harris128" wrote:

Steve:

The "raw" products consist of artwork we created for the customer to make
the (silk screen / metelphoto / label / other).

The custom artwork is not shipped to the customer, but is used inhouse to
create their (silk screen / metelphoto / label / other) product. The artwork
stays inhouse in our file cabinets.

Think of the artwork as a mold we make to create an end-product the customer
orders. The customer does not need the mold, but we use it to create the
customer's end-product. Hence the mold (or artwork) stays inhouse and filed
incase the customer orders more of the end-product. We then locate the mold
(or artwork) and create more product for the customer.

And Steve, I want to thank you for being so patient with a database noob.

harris128

"Steve" wrote:

Here's how I interpret what you have; correct me if this is incorrect ....
You have certain products (silk screen / metelphoto / label / other). There
are multiple items of each product. These "raw" products are stored in file
drawers in multiple file cabinets. Customers purchase a product and you
convert the product into a custom artwork. The custom artwork is also stored
in file drawers in multiple file cabinets. At some point the custom artwork
is shipped to the customer. Periodically you inventory the "raw" products
and restock your inventory.

Steve




"harris128" wrote in message
...
I'm sorry. I guess the point I'm trying to make is I don't need to track
the
customer's orders. I have a customer table populated from quickbooks
information. I need a database to be able to physically locate the
artwork
associated with each customer and each product. I do not need to keep
track
of any customer orders.

I need to be physically be able to locate the thousands of different
artworks by:
Customer
Product (silk screen / metelphoto / label / other)
Drawing #
Filing Cabinet
Filing Cabinet Drawer

I will need to input all the data for each artwork already inhouse.

After that process is finished, I would like to be able to query the
database by:

Customer
Product (silk screen / metelphoto / label / other)
Drawing #

And have the query return the location by Filing Cabinet & Drawer of the
artwork query.

When a new artwork is generated, I will input all the data to keep the
database up to date.

harris128



"Steve" wrote:

Now I am confused!!!

In a previous post you said, " ..... Ok, there are no customer orders
..."

and here you say customers order and reorder ??????

Steve



"harris128" wrote in message
...
Yes the order silk screening / metalphoto ID Plates / Labels, all of
which
require artwork generated. But we roll the cost of the artwork into
the
quote for whatever the customer is ordering, because a lot of the
jobs/orders
are repeated, so we keep the artwork inhouse so we don't incur the
expense
of
regenerating new artwork each time the customer re-orders.

harris128

"Steve" wrote:

OK but how are customers associated with the artworks? Do customers
buy
artworks from you?

Steve


"harris128" wrote in message
...
These artworks are already inhouse. I am trying to automate finding
the
artwork in our vast number of file cabinets. At the moment, the
artwork
data
is written down in a spiral notebook, and we have to manually flip
through
pages upon pages to try and find anything.

harris128

"Steve" wrote:

How do customers relate to artworks? Typically customers buy
(order)
products.

Steve


"harris128" wrote in message
...
Ok, there are no customer orders, since these artworks already
exist.
I
just
need a way to tie the customerIDs to the may different artworks.
I
guess
this would be a one to many relationship?

harris128

"Steve" wrote:

The other three tables are not related to TblCustomer! They
identify
File
Cabinets, File Cabinet Drawers and Products (Artworks).
TblCustomer
is
only
related to TblOrder to identify the customer who placed each
order.

Steve


"harris128" wrote in
message
...
I have created the following tables per your suggestion:

TblCustomer
CustomerID
Phone
Fax

TblFileCabinet
FileCabinetID
FileCabinetIdentification

TblFileCabinetDrawer
FileCabinetDrawerID
FileCabinetID
FileCabinetDrawerNumber

TblProduct
ProductID
ProductName
Drawing Number
Revision
DashNumber,
Serilized(Yes/No)
FileCabinetDrawerID

My question what 2 fields can I use to create a relationship
between
TblCustomer and the other 3 tables?

Thanks,
harris128

"Steve" wrote:

The suggested tables a
TblState
StateID
State

TblCustomer
CustomerID
LastName

Yes. the table on the "many" side of the relationship has a FK field that
holds the PK value from the table on the "one" side of the relationship.
--
_________

Sean Bailey


"harris128" wrote:

Ok, does the PK relate to the FK by having the same field name? Hence, this
is how the "relational" part of relarionship database works?

harris128

"Beetle" wrote:

I'm not completely clear on how you handle different revisions of
the same drawing, but for the purposes of this post, I'm going to
assume that Drawing 1234-RevA could, in theory, be kept in a
different drawer than Drawing 1234-RevC, and therefore they should
be treated as two separate drawings, even though they are based on
the same "Parent" drawing. So, essentially you have a many-to-many
relationship between the customers and each individual revision of
a drawing. Based on that assumption, an appropriate table structure
might look like;

Note: PK = Primary Key, FK = Foreign Key

tblCustomers
**********
CustomerID (PK)
LastName
FirstName
other fields related specifically to the customer

tblDrawings
*********
DrawingID (PK)
Description
other fields related to each "Parent" drawing

tblFileCabinets
***********
FileCabinetID (PK)
FileCabinetDescription
Location

tblDrawers
********
DrawerID (PK)
Description
FileCabinetID (FK to tblFileCabinets)

tblRevisions
*********
RevisionID (PK)
RevisionNumber
DrawingID (FK to tblDrawings)
DrawerID (FK to tblDrawers)

tblCustomerDrawings
****************
CustomerID (FK to tblCustomers)
RevisionID (FK to tblRevisions)
DateRequested
(in this table, you could use the above three fields as a combined PK, or
you could add your own surrofate PK like CustomerDrawingID)

If my assumption about the revisions is wrong, post back and we can give
it another go.

--
_________

Sean Bailey


"harris128" wrote:

To answer Sean's question, let me explain. Many our our customers are
government contractors who are bidding on the same government projects, so it
is possible to have the same artwork for more than one customer. What has
been done in the manual system is the employee would write down in the spiral
notebook, for customer ABC, drawing 1234 (see customer XYZ).

It even becomes more complicated in the fact that one customer will request
drawing 1234 Rev A, whereas the next customer will request drawing 1234, Rev
C.

I would prefer to keep the individual drawing, with the customer that has
requested it. If a different customer requests the same drawing, I would
want to keep that drawing with that customer.

harris128

"Beetle" wrote:

I understand what you're saying, and it shouldn't be very complicated to set
up, but there is one imortant question that I don't think has been addressed
yet (maybe it was and I missed it). Can an individual piece of artwork ever
be used to produce a product (silk screen, etc.) for more than one
customer, or is each one specific to only one customer? The answer to
this question is key to the proper table design.
--
_________

Sean Bailey


"harris128" wrote:

Steve:

The "raw" products consist of artwork we created for the customer to make
the (silk screen / metelphoto / label / other).

The custom artwork is not shipped to the customer, but is used inhouse to
create their (silk screen / metelphoto / label / other) product. The artwork
stays inhouse in our file cabinets.

Think of the artwork as a mold we make to create an end-product the customer
orders. The customer does not need the mold, but we use it to create the
customer's end-product. Hence the mold (or artwork) stays inhouse and filed
incase the customer orders more of the end-product. We then locate the mold
(or artwork) and create more product for the customer.

And Steve, I want to thank you for being so patient with a database noob.

harris128

"Steve" wrote:

Here's how I interpret what you have; correct me if this is incorrect ....
You have certain products (silk screen / metelphoto / label / other). There
are multiple items of each product. These "raw" products are stored in file
drawers in multiple file cabinets. Customers purchase a product and you
convert the product into a custom artwork. The custom artwork is also stored
in file drawers in multiple file cabinets. At some point the custom artwork
is shipped to the customer. Periodically you inventory the "raw" products
and restock your inventory.

Steve




"harris128" wrote in message
...
I'm sorry. I guess the point I'm trying to make is I don't need to track
the
customer's orders. I have a customer table populated from quickbooks
information. I need a database to be able to physically locate the
artwork
associated with each customer and each product. I do not need to keep
track
of any customer orders.

I need to be physically be able to locate the thousands of different
artworks by:
Customer
Product (silk screen / metelphoto / label / other)
Drawing #
Filing Cabinet
Filing Cabinet Drawer

I will need to input all the data for each artwork already inhouse.

After that process is finished, I would like to be able to query the
database by:

Customer
Product (silk screen / metelphoto / label / other)
Drawing #

And have the query return the location by Filing Cabinet & Drawer of the
artwork query.

When a new artwork is generated, I will input all the data to keep the
database up to date.

harris128



"Steve" wrote:

Now I am confused!!!

In a previous post you said, " ..... Ok, there are no customer orders
..."

and here you say customers order and reorder ??????

Steve



"harris128" wrote in message
...
Yes the order silk screening / metalphoto ID Plates / Labels, all of
which
require artwork generated. But we roll the cost of the artwork into
the
quote for whatever the customer is ordering, because a lot of the
jobs/orders
are repeated, so we keep the artwork inhouse so we don't incur the
expense
of
regenerating new artwork each time the customer re-orders.

harris128

"Steve" wrote:

OK but how are customers associated with the artworks? Do customers
buy
artworks from you?

Steve


"harris128" wrote in message
...
These artworks are already inhouse. I am trying to automate finding
the
artwork in our vast number of file cabinets. At the moment, the
artwork
data
is written down in a spiral notebook, and we have to manually flip
through
pages upon pages to try and find anything.

harris128

"Steve" wrote:

How do customers relate to artworks? Typically customers buy
(order)
products.

Steve


"harris128" wrote in message
...
Ok, there are no customer orders, since these artworks already
exist.
I
just
need a way to tie the customerIDs to the may different artworks.
I
guess
this would be a one to many relationship?

harris128

"Steve" wrote:

The other three tables are not related to TblCustomer! They
identify
File
Cabinets, File Cabinet Drawers and Products (Artworks).
TblCustomer
is
only
related to TblOrder to identify the customer who placed each
order.

Steve


"harris128" wrote in
message
...
I have created the following tables per your suggestion:

TblCustomer
CustomerID
Phone
Fax

TblFileCabinet
FileCabinetID
FileCabinetIdentification

TblFileCabinetDrawer
FileCabinetDrawerID
FileCabinetID
FileCabinetDrawerNumber

TblProduct
ProductID
ProductName
Drawing Number
Revision
DashNumber,
Serilized(Yes/No)
FileCabinetDrawerID

My question what 2 fields can I use to create a relationship
between
TblCustomer and the other 3 tables?

Thanks,
harris128

"Steve" wrote:

The suggested tables a

Be careful .... You are being led down a primrose path.

....Can an individual piece of artwork ever be used to produce a product
(silk screen, etc.) for more than one
customer......

This scenario is covered in the design of the tables I gave you.
TblCustomerEndProduct
CustomerEndProductID
CustomerID
ProductID
DrawingNumber
Revision
DashNumber,
Serilized(Yes/No)
FileCabinetDrawerID

If it was the same artwork,
ProductID
DrawingNumber
Revision
DashNumber,
Serilized(Yes/No)
would all be the same. Maybe FileCabinetDrawerID would also be the same. All
it takes is to add a new record to TblCustomerEndProduct where CustomerID
would be different and Access would generate a different
CustomerEndProductID. For this scenario, you would thus have two records in
TblCustomerEndProduct.

To answer your question in this post ...
...does the PK relate to the FK by having the same field name...
The answer is NO! A PK relates to a FK SOLELY by connecting the PK in a
table to a field in another table in the Relationships Window or
programatically connecting the PK in a table to a field in another table.
Either way, the relationship holds everywhere in the database. Connecting
two fields can also be done in the query window but that relationship only
holds for the query and nowhere else in the database.The field names are
irrelvant! However good design practice names the fields the same. It's just
like putting ID at the end of a field name. ID shows the field is a PK or FK
but that's all.

Steve





"harris128" wrote in message
...
Ok, does the PK relate to the FK by having the same field name? Hence,
this
is how the "relational" part of relarionship database works?

harris128

"Beetle" wrote:

I'm not completely clear on how you handle different revisions of
the same drawing, but for the purposes of this post, I'm going to
assume that Drawing 1234-RevA could, in theory, be kept in a
different drawer than Drawing 1234-RevC, and therefore they should
be treated as two separate drawings, even though they are based on
the same "Parent" drawing. So, essentially you have a many-to-many
relationship between the customers and each individual revision of
a drawing. Based on that assumption, an appropriate table structure
might look like;

Note: PK = Primary Key, FK = Foreign Key

tblCustomers
**********
CustomerID (PK)
LastName
FirstName
other fields related specifically to the customer

tblDrawings
*********
DrawingID (PK)
Description
other fields related to each "Parent" drawing

tblFileCabinets
***********
FileCabinetID (PK)
FileCabinetDescription
Location

tblDrawers
********
DrawerID (PK)
Description
FileCabinetID (FK to tblFileCabinets)

tblRevisions
*********
RevisionID (PK)
RevisionNumber
DrawingID (FK to tblDrawings)
DrawerID (FK to tblDrawers)

tblCustomerDrawings
****************
CustomerID (FK to tblCustomers)
RevisionID (FK to tblRevisions)
DateRequested
(in this table, you could use the above three fields as a combined PK, or
you could add your own surrofate PK like CustomerDrawingID)

If my assumption about the revisions is wrong, post back and we can give
it another go.

--
_________

Sean Bailey


"harris128" wrote:

To answer Sean's question, let me explain. Many our our customers are
government contractors who are bidding on the same government projects,
so it
is possible to have the same artwork for more than one customer. What
has
been done in the manual system is the employee would write down in the
spiral
notebook, for customer ABC, drawing 1234 (see customer XYZ).

It even becomes more complicated in the fact that one customer will
request
drawing 1234 Rev A, whereas the next customer will request drawing
1234, Rev
C.

I would prefer to keep the individual drawing, with the customer that
has
requested it. If a different customer requests the same drawing, I
would
want to keep that drawing with that customer.

harris128

"Beetle" wrote:

I understand what you're saying, and it shouldn't be very complicated
to set
up, but there is one imortant question that I don't think has been
addressed
yet (maybe it was and I missed it). Can an individual piece of
artwork ever
be used to produce a product (silk screen, etc.) for more than one
customer, or is each one specific to only one customer? The answer to
this question is key to the proper table design.
--
_________

Sean Bailey


"harris128" wrote:

Steve:

The "raw" products consist of artwork we created for the customer
to make
the (silk screen / metelphoto / label / other).

The custom artwork is not shipped to the customer, but is used
inhouse to
create their (silk screen / metelphoto / label / other) product.
The artwork
stays inhouse in our file cabinets.

Think of the artwork as a mold we make to create an end-product the
customer
orders. The customer does not need the mold, but we use it to
create the
customer's end-product. Hence the mold (or artwork) stays inhouse
and filed
incase the customer orders more of the end-product. We then locate
the mold
(or artwork) and create more product for the customer.

And Steve, I want to thank you for being so patient with a database
noob.

harris128

"Steve" wrote:

Here's how I interpret what you have; correct me if this is
incorrect ....
You have certain products (silk screen / metelphoto / label /
other). There
are multiple items of each product. These "raw" products are
stored in file
drawers in multiple file cabinets. Customers purchase a product
and you
convert the product into a custom artwork. The custom artwork is
also stored
in file drawers in multiple file cabinets. At some point the
custom artwork
is shipped to the customer. Periodically you inventory the "raw"
products
and restock your inventory.

Steve




"harris128" wrote in
message
...
I'm sorry. I guess the point I'm trying to make is I don't
need to track
the
customer's orders. I have a customer table populated from
quickbooks
information. I need a database to be able to physically locate
the
artwork
associated with each customer and each product. I do not need
to keep
track
of any customer orders.

I need to be physically be able to locate the thousands of
different
artworks by:
Customer
Product (silk screen / metelphoto / label / other)
Drawing #
Filing Cabinet
Filing Cabinet Drawer

I will need to input all the data for each artwork already
inhouse.

After that process is finished, I would like to be able to
query the
database by:

Customer
Product (silk screen / metelphoto / label / other)
Drawing #

And have the query return the location by Filing Cabinet &
Drawer of the
artwork query.

When a new artwork is generated, I will input all the data to
keep the
database up to date.

harris128



"Steve" wrote:

Now I am confused!!!

In a previous post you said, " ..... Ok, there are no customer
orders
..."

and here you say customers order and reorder ??????

Steve



"harris128" wrote in
message
...
Yes the order silk screening / metalphoto ID Plates /
Labels, all of
which
require artwork generated. But we roll the cost of the
artwork into
the
quote for whatever the customer is ordering, because a lot
of the
jobs/orders
are repeated, so we keep the artwork inhouse so we don't
incur the
expense
of
regenerating new artwork each time the customer re-orders.

harris128

"Steve" wrote:

OK but how are customers associated with the artworks? Do
customers
buy
artworks from you?

Steve


"harris128" wrote in
message
...
These artworks are already inhouse. I am trying to
automate finding
the
artwork in our vast number of file cabinets. At the
moment, the
artwork
data
is written down in a spiral notebook, and we have to
manually flip
through
pages upon pages to try and find anything.

harris128

"Steve" wrote:

How do customers relate to artworks? Typically customers
buy
(order)
products.

Steve


"harris128" wrote
in message
...
Ok, there are no customer orders, since these artworks
already
exist.
I
just
need a way to tie the customerIDs to the may different
artworks.
I
guess
this would be a one to many relationship?

harris128

"Steve" wrote:

The other three tables are not related to
TblCustomer! They
identify
File
Cabinets, File Cabinet Drawers and Products
(Artworks).
TblCustomer
is
only
related to TblOrder to identify the customer who
placed each
order.

Steve


"harris128"
wrote in
message
...
I have created the following tables per your
suggestion:

TblCustomer
CustomerID
Phone
Fax

TblFileCabinet
FileCabinetID
FileCabinetIdentification

TblFileCabinetDrawer
FileCabinetDrawerID
FileCabinetID
FileCabinetDrawerNumber

TblProduct
ProductID
ProductName
Drawing Number
Revision
DashNumber,
Serilized(Yes/No)
FileCabinetDrawerID

My question what 2 fields can I use to create a
relationship
between
TblCustomer and the other 3 tables?

Thanks,
harris128

"Steve" wrote:

The suggested tables a
TblState
StateID
State

TblCustomer
CustomerID
LastName