1st time Access Database

[harris128]

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.

 

[harris128]

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

 

[harris128]

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

 

[harris128]

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

 

[harris128]

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

 

[harris128]

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

 

[harris128]

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

 

[Beetle]

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.

 

[harris128]

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]

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.

 

[harris128]

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]

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.

 

[Beetle]

I might agree with *some* of what Steve said in his last post, depending
upon interpretation.

First, regarding your question about the PK/FK fields. When I read that
the first time my interpretation was that you were just asking what the
purpose of the FK field is. If you were actually asking whether the field
names
are required to be the same, then Steve is correct. They don't have to
have the same name, but they typically do just for clarity.

Additionally, in the table structure that he suggested he included a table
(tblProducts) to store information about the different product types
(silk screens, etc.). After re-reading the thread, I see that you did
mention previously that you want to keep track of this info, so you would want
to include a table like this in your structure.

However, I disagree with the structure of the TblCustomerEndProduct that
he suggested. His suggestion was;

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

Let's suppose you have a drawing with 10 revisions. With the above table
structure, you would have to enter the same drawing number 10 times, along
with different revision numbers, filing cabinet drawers, etc.

In the junction table that I suggested (modified to include a FK to
tblProducts);

tblCustomerDrawings
****************
CustomerID (FK to tblCustomers)
ProductID (FK to tblProducts)
RevisionID (FK to tblRevisions)
DateProduced

All you would need to do is enter a particular RevisionID (which, in this
case,
would typically be done via a combo box). All other data related to that
RevisionID (Drawing, File Cabinet Drawer, etc.) could be determined by
a simple query.

Again, this is based on my assumption that a Drawing can have multiple
Revisions, and each Revision could be stored in a different location,
therefore it is really the Revisions that you need to keep track of.

 

[harris128]

Thanks guys for all your help. One more point. I want to keep all the
revisions of a drawing in the same location (by customer). Normally, if the
customer sends me a new revision of the drawing, it usually replaces the
present revision. I would keep the previous revs for historical purposes to
refer back to, if need be.

I say, by customer, because I can have the same drawing for two customers,
but each customer may be building parts to different revs of that drawing.
Make sense?

harris128