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. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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 TblCustomerContact CustomerContactID CustomerID LastName FirstName Phone Fax 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. |
#12
|
|||
|
|||
1st time Access Database
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 TblCustomerContact CustomerContactID CustomerID LastName FirstName Phone Fax 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. |
#13
|
|||
|
|||
1st time Access Database
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 TblCustomerContact CustomerContactID CustomerID LastName FirstName Phone Fax 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. |
#14
|
|||
|
|||
1st time Access Database
The suggested revised tables a
TblState StateID State TblCustomer CustomerID LastName FirstName Address City StateID PostalCode Phone Fax TblCustomerContact *** Optional CustomerContactID CustomerID LastName FirstName Phone Fax 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 TblCustomerContact CustomerContactID CustomerID LastName FirstName Phone Fax 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. |
#15
|
|||
|
|||
1st time Access Database
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 TblCustomerContact CustomerContactID CustomerID LastName FirstName Phone Fax 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) |
#16
|
|||
|
|||
1st time Access Database
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 TblCustomerContact CustomerContactID CustomerID LastName FirstName Phone Fax 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 |
#17
|
|||
|
|||
1st time Access Database
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 TblCustomerContact CustomerContactID CustomerID LastName FirstName Phone Fax 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 |
#18
|
|||
|
|||
1st time Access Database
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 |
#19
|
|||
|
|||
1st time Access Database
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 |
#20
|
|||
|
|||
1st time Access Database
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 |
Thread Tools | |
Display Modes | |
|
|