|
| 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 |
|
#1
July 30th, 2008, 07:30 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
1st time Access Database
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. 
|
|
#2
July 30th, 2008, 08:32 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
1st time Access Database
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.
|
|
#3
July 30th, 2008, 09:56 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
1st time Access Database
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.
|
|
#4
July 30th, 2008, 10:38 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
1st time Access Database
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.
|
|
#5
July 31st, 2008, 02:21 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
1st time Access Database
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.
|
|
#6
July 31st, 2008, 03:27 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
1st time Access Database
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.
|
|
#7
July 31st, 2008, 03:37 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
1st time Access Database
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.
|
|
#8
July 31st, 2008, 03:49 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
1st time Access Database
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.
|
|
#9
July 31st, 2008, 03:58 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
1st time Access Database
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.
|
|
#10
July 31st, 2008, 05:13 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
1st time Access Database
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.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
