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
|
|||
|
|||
HELP!
OK I admit that I am a beginner and know next to nothing about Access. My
boss is asking for me to create a database and include some relational, indexing, and sorting options that seem pretty basic to me. However, I am quandried as to how to accomplish this. Let me break down the project he I am creating a customer database. This database needs to include the customer ID set as the primary key. Then I need to include the year the customer was active, for example 2010. Then I need to include all the customer data, such as first name, last name, address, city, state, zip code, area code, work phone, home phone, cell phone, category of items transferred, list of items transferred, as well as the date of each transaction. My boss wants me to build this database in such a way as to eliminate repeat and duplicate data, as well as having all the appropriate information related to one another. He wants all the information properly indexed. He also wants all the data to be able to be sorted be each individual information field, such as: let's say a customer purchased a food item, he wants a sorting option that will pull up only the customers that purchased food items. I do apoligize for the length of this post, but I feel being as detailed as possible will be helpful. I have begun reading the Microsoft Press book for access 2000. I am working with access 2002. I have gone online and downloaded e-books, as well as video tutorials. Each one of these things has been very helpful, and I have gained a basic knowledge of how to work with access. With all that in mind I am still lost when it comes to tackling this monster of a database that my boss as asking for. Any help with this would be greatly appreciated. Also if you know of any templates that would work well and include all the pertinent data listed above, then please forward a link. Thank you in advance for all of your help. May God richly bless all of you. |
#2
|
|||
|
|||
HELP!
On Fri, 5 Mar 2010 14:03:42 -0800, accessnewbie79915
wrote: OK I admit that I am a beginner and know next to nothing about Access. My boss is asking for me to create a database and include some relational, indexing, and sorting options that seem pretty basic to me. However, I am quandried as to how to accomplish this. Let me break down the project he I am creating a customer database. This database needs to include the customer ID set as the primary key. Then I need to include the year the customer was active, for example 2010. Then I need to include all the customer data, such as first name, last name, address, city, state, zip code, area code, work phone, home phone, cell phone, category of items transferred, list of items transferred, as well as the date of each transaction. My boss wants me to build this database in such a way as to eliminate repeat and duplicate data, as well as having all the appropriate information related to one another. He wants all the information properly indexed. He also wants all the data to be able to be sorted be each individual information field, such as: let's say a customer purchased a food item, he wants a sorting option that will pull up only the customers that purchased food items. I do apoligize for the length of this post, but I feel being as detailed as possible will be helpful. I have begun reading the Microsoft Press book for access 2000. I am working with access 2002. I have gone online and downloaded e-books, as well as video tutorials. Each one of these things has been very helpful, and I have gained a basic knowledge of how to work with access. With all that in mind I am still lost when it comes to tackling this monster of a database that my boss as asking for. Any help with this would be greatly appreciated. Also if you know of any templates that would work well and include all the pertinent data listed above, then please forward a link. Thank you in advance for all of your help. May God richly bless all of you. This may not be as hard as you think. The key is that you need to identify the "Entities" - real-life persons, things, or events - of importance to your application. Each kind of Entity will have its own table. Some of the entities I see here a Customers - CustomerID (autonumber primary key, unless you have a unique manually assigned ID), LastName, FirstName, address, etc - just biographical and contact information, NOTHING about purchases) Items or Products - a table of what you sell, with ItemID primary key, and other fields describing the item as a thing in its own right; include an ItemType field ItemTypes - a tiny table with one row for each type of item you want to track Purchases - a table with links to the CustomerID, the ItemID, and other fields about an individual purchase (quantity, sale date, quantity, amount charged, etc.) Here are some references that may require a bit less digging than your big books: Crystal's video may be a good start. Jeff Conrad's resources page: http://www.accessmvp.com/JConrad/acc...resources.html The Access Web resources page: http://www.mvps.org/access/resources/index.html Roger Carlson's tutorials, samples and tips: http://www.rogersaccesslibrary.com/ A free tutorial written by Crystal: http://allenbrowne.com/casu-22.html A video how-to series by Crystal: http://www.YouTube.com/user/LearnAccessByCrystal MVP Allen Browne's tutorials: http://allenbrowne.com/links.html#Tutorials -- John W. Vinson [MVP] |
#3
|
|||
|
|||
HELP!
Here are some thoughts on table structure. Create one-to-many relationship
between table and select Referential Integerity and Cascade Update. Use form/subform for the one/many display and data entry. Use a combo box to pick products and set AutoExpand property to Yes for type-ahead entry. tblCustomer -- CustID – Autonumber - primary key Activated – DateTime FirstName - text LastName - text Addr1 - text Addr2 - text City - text ST - text ZipCode - text Plus4 - text WorkPhone - text HomePhone - text CellPhone - text Pager – text Discount – Etc. he wants a sorting option that will pull up only the customers that purchased food items. Not ‘sorting’ but search criteria. You will need to have a field for category for this purpose like this – tblProduct –- ProdID – Autonumber – primary key Product – Text Category – text – maybe build a table of categories and link so as to avoid typos or multiple variations of the same category (Food, canned peas, peas, prepared peas, Blackeyed peas, etc.). Size – tblCustPurch – PurchID - Autonumber - primary key CustID – Number – Long integer - Foreign key ProdID – Number – Long integer - Foreign key QTY - Number – Long integer tblPricing – PriceID - Autonumber - primary key ProdID – Number – Long integer - Foreign key Price – Currency PriceDate – DateTime – Date price posted -- Build a little, test a little. "accessnewbie79915" wrote: OK I admit that I am a beginner and know next to nothing about Access. My boss is asking for me to create a database and include some relational, indexing, and sorting options that seem pretty basic to me. However, I am quandried as to how to accomplish this. Let me break down the project he I am creating a customer database. This database needs to include the customer ID set as the primary key. Then I need to include the year the customer was active, for example 2010. Then I need to include all the customer data, such as first name, last name, address, city, state, zip code, area code, work phone, home phone, cell phone, category of items transferred, list of items transferred, as well as the date of each transaction. My boss wants me to build this database in such a way as to eliminate repeat and duplicate data, as well as having all the appropriate information related to one another. He wants all the information properly indexed. He also wants all the data to be able to be sorted be each individual information field, such as: let's say a customer purchased a food item, he wants a sorting option that will pull up only the customers that purchased food items. I do apoligize for the length of this post, but I feel being as detailed as possible will be helpful. I have begun reading the Microsoft Press book for access 2000. I am working with access 2002. I have gone online and downloaded e-books, as well as video tutorials. Each one of these things has been very helpful, and I have gained a basic knowledge of how to work with access. With all that in mind I am still lost when it comes to tackling this monster of a database that my boss as asking for. Any help with this would be greatly appreciated. Also if you know of any templates that would work well and include all the pertinent data listed above, then please forward a link. Thank you in advance for all of your help. May God richly bless all of you. |
Thread Tools | |
Display Modes | |
|
|